【MySQL高级篇】08-事务篇

最新推荐文章于 2024-09-07 08:25:54 发布

开五档的蒙奇

最新推荐文章于 2024-09-07 08:25:54 发布

阅读量1k

点赞数 10

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43916081/article/details/136626175

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的事务基础知识，包括事务的完成过程、显式和隐式事务、事务的ACID特性、隔离级别、不同引擎的支持、锁的概念和操作以及多版本并发控制(MVCC)。通过实例演示了事务的开启、提交、回滚和隔离级别的应用。

摘要由CSDN通过智能技术生成

第13章:事务基础知识

#09-事务的基础知识

#1.事务的完成过程
#步骤1：开启事务
#步骤2：一系列的DML操作 
#....
#步骤3：事务结束的状态：提交的状态(COMMIT) 、 中止的状态(ROLLBACK)

#2. 显式事务

#2.1 如何开启？ 使用关键字：start transaction  或 begin

# start transaction 后面可以跟：read only / read write (默认) / with consistent snapshot 


#2.2 保存点(savepoint)

13.1 数据库事务概述

13.1.1 存储引擎支持情况

13.1.2 基本概念

13.1.3 事务的ACID特性

13.1.4 事务的状态

13.2 如何使用事务

13.2.1 显式事务

13.2.2 隐式事务

13.2.3 隐式提交数据的情况

#3. 隐式事务

# 3.1 关键字：autocommit 
#set autocommit = false;

SHOW VARIABLES LIKE 'autocommit';#默认是ON

UPDATE account SET balance = balance - 10 WHERE id = 1; #此时这条DML操作是一个独立的事务

UPDATE account SET balance = balance + 10 WHERE id = 2; #此时这条DML操作是一个独立的事务

#3.2 如何关闭自动提交？
#方式1：
SET autocommit = FALSE; #针对于DML操作是有效的，对DDL操作是无效的。

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

#方式2：我们在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据

START TRANSACTION;

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

13.2.4 使用举例1:提交与回滚

#4. 案例分析
#SET autocommit = TRUE; 
#举例1： commit 和 rollback

USE atguigudb2;
#情况1：
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('李四'); #此时不会自动提交数据
INSERT INTO user3 VALUES('李四'); #受主键的影响，不能添加成功
ROLLBACK;

SELECT * FROM user3;

#情况2：
TRUNCATE TABLE user3;  #DDL操作会自动提交数据，不受autocommit变量的影响。

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

INSERT INTO user3 VALUES('李四');# 默认情况下(即autocommit为true)，DML操作也会自动提交数据。
INSERT INTO user3 VALUES('李四'); #事务的失败的状态

ROLLBACK;

SELECT * FROM user3;


#情况3：
TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUES('张三'); 
COMMIT;


SELECT * FROM user3;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); 

ROLLBACK;


SELECT * FROM user3;

13.2.5 使用举例2:测试不支持事务的engine

#举例2：体会INNODB 和 MyISAM

CREATE TABLE test1(i INT) ENGINE = INNODB;

CREATE TABLE test2(i INT) ENGINE = MYISAM;

#针对于innodb表
BEGIN
INSERT INTO test1 VALUES (1);
ROLLBACK;

SELECT * FROM test1;


#针对于myisam表:不支持事务
BEGIN
INSERT INTO test2 VALUES (1);
ROLLBACK;

SELECT * FROM test2;

13.2.6 使用举例3:SAVEPOINT

#举例3：体会savepoint

CREATE TABLE user3(NAME VARCHAR(15),balance DECIMAL(10,2));

BEGIN
INSERT INTO user3(NAME,balance) VALUES('张三',1000);
COMMIT;

SELECT * FROM user3;


BEGIN;
UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

SAVEPOINT s1;#设置保存点

UPDATE user3 SET balance = balance + 1 WHERE NAME = '张三';

ROLLBACK TO s1; #回滚到保存点


SELECT * FROM user3;

ROLLBACK; #回滚操作

SELECT * FROM user3;

13.3 事务隔离级别

13.3.1 数据准备

13.3.2 数据并发问题

13.3.3 SQL中的四种隔离级别

13.3.4 MySQL支持的四种隔离级别

13.3.5 如何设置事务的隔离级别

13.3.6 不同隔离级别举例

13.4 事务的常见分类

第14章:MySQL事务日志

14.1 redo日志

14.1.1 为什么需要REDO日志

14.1.2 REDO日志的好处、特点

14.1.2.1 好处

14.1.2.2 特点

14.1.3 redo的组成

14.1.4 redo的整体流程

14.1.5 redo log的刷盘策略

14.1.6 不同刷盘策略演示

14.1.6.1 流程图

14.1.6.2 举例

#10-事务日志

USE atguigudb3;

CREATE TABLE test_load(
a INT,
b CHAR(80)
)ENGINE=INNODB;


#创建存储过程，用于向test_load中添加数据
DELIMITER//
CREATE PROCEDURE p_load(COUNT INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80)DEFAULT REPEAT('a',80);
WHILE s<=COUNT DO
INSERT INTO test_load SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END //
DELIMITER;

#测试1：
#设置并查看：innodb_flush_log_at_trx_commit

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#set GLOBAL innodb_flush_log_at_trx_commit = 1;

#调用存储过程
CALL p_load(30000); #1min 28sec

#测试2：
TRUNCATE TABLE test_load;

SELECT COUNT(*) FROM test_load;

SET GLOBAL innodb_flush_log_at_trx_commit = 0;

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#调用存储过程
CALL p_load(30000); #37.945 sec

#测试3：
TRUNCATE TABLE test_load;

SELECT COUNT(*) FROM test_load;

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#调用存储过程
CALL p_load(30000); #45.173 sec

14.1.7 写入redo log buffer过程

14.1.7.1 补充概念:Mini-Transaction

14.1.7.2 redo日志写入log buffer

14.1.7.3 redo log block的结构图

14.1.8 redo log file

14.1.8.1 相关参数设置

14.1.8.2 日志文件组

14.1.8.3 checkpoint

14.1.9 redo log 小结

14.2 Undo日志

14.2.1 如何理解Undo日志

14.2.2 Undo日志的作用

14.2.3 undo的存储结构

14.2.3.1 回滚段与undo页

14.2.3.2 回滚段与事务

14.2.3.3 回滚段中的数据分类

14.2.4 undo的类型

14.2.5 undo log的生命周期

14.2.5.1 简要生成过程

14.2.5.2 详细生成过程

14.2.5.3 undo log是如何回滚的

14.2.5.4 undo log的删除

14.2.6 小结

第15章:锁

15.1 概述

15.2 MySQL并发事务访问相同记录

15.2.1 读-读情况

15.2.2 写-写情况

15.2.3 读-写或写-读情况

15.2.4 并发问题的解决方案

15.3 锁的不同角度分类

15.3.1 从数据操作的类型划分:读锁、写锁

15.3.2 从数据操作的粒度划分:表级锁、页级锁、行锁

15.3.2.1 表锁(Table Lock)

15.3.2.2 InnoDB中的行锁

15.3.2.3 页锁

15.3.3 从对待锁的态度划分:乐观锁、悲观锁

15.3.3.1 悲观锁(Pessimistic Locking)

15.3.3.2 乐观锁(Optimistic Locking)

15.3.3.3 两种锁的使用场景

15.3.4 按加锁的方式划分:显式锁、隐式锁

15.3.4.1 隐式锁

15.3.4.2 显式锁

15.3.5 其它锁之:全局锁

15.3.6 其它锁之:死锁

15.4 锁的内存结构

15.5 锁监控

15.6 附录

第16章:多版本并发控制

16.1 什么是MVCC

16.2 快照读与当前读

16.2.1 快照读

16.2.2 当前读

16.3 复习

16.3.1 再谈隔离级别

16.3.2 隐藏字段、Undo Log版本链

16.4 MVCC实现原理之ReadView

16.4.1 什么是ReadView

16.4.2 设计思路

16.4.3 ReadView的规则

16.4.4 MVCC整体操作流程

16.5 举例说明

16.5.1 READ COMMITTED隔离级别下

16.5.2 REPEATABLE READ隔离级别下

16.5.3 如何解决幻读

16.6 总结

开五档的蒙奇

关注

10
点赞
踩
26

收藏

觉得还不错? 一键收藏
打赏
0
评论
【MySQL高级篇】08-事务篇

3. 隐式事务# 3.1 关键字：autocommit#默认是ON#此时这条DML操作是一个独立的事务#此时这条DML操作是一个独立的事务#3.2 如何关闭自动提交？#方式1：#针对于DML操作是有效的，对DDL操作是无效的。COMMIT;#或rollback;#方式2：我们在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据COMMIT;#或rollback;
复制链接

扫一扫