^-^
后端技术_MySQL事务原理分析
最近更新：2024-09-23   |   字数总计：3.1k   |   阅读估时：12分钟   |   阅读量：次
  1. 事务
    1. MySQL事务语句
    2. acid特性
      1. 原子性
        1. undolog
      2. 一致性
      3. 隔离性
      4. 持久性
        1. redolog
    3. 隔离级别
      1. serializable
      2. repeatable read（*）
      3. read committed（*）
      4. read uncommitted
      5. 案例
        1. read uncommitted
        2. read committed
        3. repeatable read（通常用这个）
    4. 并发读异常（总结）
      1. 脏读
      2. 不可重复读
      3. 幻读
      4. 丢失更新
      5. 总结
    5. MVCC
      1. read view
      2. 隐藏列
      3. 事务可见性问题
      4. read committed中的mvcc
      5. repeatable read中的mvcc
      6. 快照读
      7. 当前读
    6. 锁
      1. 全局锁
      2. 表级锁
        1. 表锁
        2. 元数据锁
        3. 意向锁
        4. auto-inc锁
      3. 行级锁
        1. 记录锁（record lock）
        2. 间隙锁（gap lock）
        3. 临键锁（next-key lock）
      4. CURD与锁
      5. 锁兼容
    7. 锁对象分别讨论
  2. 死锁

事务

  • 前提：并发连接访问
  • 定义：用户定义的一系列操作，这些操作要么都做，要么都不做，是一个不可分割的单位

MySQL事务语句

  • begin/start transaction
  • commit
  • rollback

acid特性

原子性

  • 要么都做，要么都不做。
  • 支持回滚到最开始的状态。
undolog
  • mvcc记录事务DML操作提交后产生的行数据版本信息
  • 记录DML操作步骤，用于回滚；通过逆运算回滚

一致性

  • 数据库完整约束。严格保证唯一约束，多个用户操作后仍然严格保证唯一
  • 逻辑上

隔离性

  • 防止多个并发事务交叉执行导致数据不一致
  • 通常通过：加锁和mvcc来实现

持久性

  • 确保内存中的事务执行数据真正落盘，保证宕机后数据仍然存在
redolog
  • 事务提交后，记录DML操作对应物理页修改的内容

隔离级别

  • 唯一我们可以用来破坏，以增加并发性能的特性
  1. serializable（可串行化，最严格）
  2. repeatable read（可重复读）
  3. read committed（读已提交）
  4. read uncommitted（读未提交）

serializable

  • 读：自动加S锁（next-key lock，针对一行）
  • 写：自动加X锁（针对一行 + 范围锁）

repeatable read（*）

  • 读：mvcc（一致性非锁定读），读取事务开始前版本的行数据
  • 写：自动加X锁（针对一行 + 范围锁）

read committed（*）

  • 读：mvcc（一致性非锁定读），读取最新版本的行数据
  • 写：自动加X锁（针对一行）

read uncommitted

  • 读：不做任何处理
  • 写：自动加X锁（针对一行）

案例

read uncommitted
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 连接A
use edu_svc;
set transaction isolation level read uncommitted; -- 设置隔离级别
-- 连接B
use edu_svc;
set transaction isolation level read uncommitted;
-- 连接A
begin; -- 开启事务 , 此时表中A用户的money为1000
-- 连接B
begin;
update account_t set money = money - 100 where name = 'A'; -- 执行一行
-- 连接B
select money from account_t where name = 'A'; -- 返回money == 900
-- A连接还没有commit事务，但是B连接读出的数据已经被修改了，证明read uncommitted隔离级别进行中，别的连接可以读到事务的中间状态
read committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 连接A
use edu_svc;
set transaction isolation level read committed; -- 设置隔离级别
-- 连接B
use edu_svc;
set transaction isolation level read committed;
-- 连接A
begin; -- 开启事务 , 此时表中A用户的money为900
-- 连接B
begin;
update account_t set money = money + 100 where name = 'A'; -- 执行一行
-- 连接B
select money from account_t where name = 'A'; -- 返回money == 900
-- A连接还没有commit事务，但是B连接读出的数据并没有看到中间状态，证明read committed隔离级别进行中，别的连接可以只能读到已经提交的数据
-- 连接A
commit; -- 此时A提交事务
-- 连接B
select money from account_t where name = 'A'; -- 返回money == 1000
-- A连接已经commit事务，B连接没有commit事务，但是可以读出最新的数据，证明read committed隔离级别进行中，一次事务可以读出不同的结果，这破坏了逻辑一致性，仅有在不会在一次事务中执行多次相同的操作时才考虑
repeatable read（通常用这个）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- 连接A
use edu_svc;
set transaction isolation level repeatable read; -- 设置隔离级别
-- 连接B
use edu_svc;
set transaction isolation level repeatable read;
-- 连接A
begin; -- 开启事务 , 此时表中A用户的money为1000
-- 连接B
begin;
update account_t set money = money + 100 where name = 'A'; -- 执行一行
-- 连接B
select money from account_t where name = 'A'; -- 返回money == 1000
-- A连接还没有commit事务，但是B连接读出的数据并没有看到中间状态，证明repeatable read隔离级别进行中，别的连接可以不能读到未提交的数据
-- 连接A
commit; -- 此时A提交事务
-- 连接B
select money from account_t where name = 'A'; -- 返回money == 1000
-- A连接已经commit事务，B连接没有commit事务，不会读出别的连接提交的事务，证明repeatable read隔离级别进行中，一次事务不会读出不同的结果

-- repeatable read的幻读问题
-- 连接A
begin; -- 此时A用户的money为1100
-- 连接B
begin;
select * from account_t where id >= 2; -- B money==900 ; A money==1100 ; M money==1000
-- 连接A
insert into account_t(id, name, money) value(4,'D',1000);
commit; -- A已经提交了
-- 连接B
-- 一般在业务中，我们会判断id的值，此时在业务中4判断肯定是不存在的
insert into account_t(id, name, money) value(4,'E',1000); -- ERROR 1062： Duplicate entry '4' for key 'primary'
-- 产生了业务逻辑的冲突，破坏了业务逻辑的一致性

-- 解决幻读问题
-- 连接A
begin;
-- 连接B
begin;
-- 连接A
insert into account_t(id, name, money) value(5,'F',1000);
-- 连接B
select * from account_t for update; -- 我们在读的时候加S锁，此时B连接会发生阻塞，因为A连接还没有commit
-- 连接A
commit;
-- 连接B
-- 返回查询结果，阻塞结束

并发读异常（总结）

脏读

  • 事务A可以读到另一个事务B中未提交的数据，也就是事务A读到脏数据。

不可重复读

  • 事务A可以读到B中提交的数据，也就是事务A中两次读到的数据不一致（同一行）。

幻读

  • 两次读取同一个范围内记录得到的记录不一样，也就是事务A查询某个唯一键明明不存在，但是插入的时候却提示重复。
  • 解决：读加锁

丢失更新

  • 两个事务都是写的情况下，覆盖提交。

总结

隔离级别 脏读 不可重复读 幻读 提交覆盖
read uncommitted yes yes yes yes
read committed no yes yes yes
repeatable read no no yes（手动加锁） yes（手动加锁）
serializable no no no no

MVCC

  • 名称：一致性非锁定读
  • mvcc实现原理：read view（视图）、行隐藏列、事务可见性

read view

  1. m_ids，创建read view时，已启动但未提交的事务id列表
  2. min_trx_id，创建read view，已启动但未提交的最小事务id
  3. max_trx_id，创建read view时，预分配给下一个未开启事务的id
  4. creator_trx_id，创建该read view的事务id

隐藏列

  • 每一行都存在的隐藏列
  1. trx_id，事务修改记录时，trx_id记录该修改事务id
  2. roll_pointer，事务修改记录时，该指针指向旧版本记录，旧记录也指向更旧记录（rollback时使用）

事务可见性问题

  1. trx_id < min_trx_id 已提交，可见
  2. trx_id >= max_trx_id 后启动，不可见
  3. min_trx_id <= trx_id < max_trx_id
    1. trx_id in m_ids 已启动但未提交，不可见
    2. trx_id not in m_ids 已提交，可见

read committed中的mvcc

  • 每次读取数据时，生成新的read view

repeatable read中的mvcc

  • 启动事务时，生成新的read view，一直使用直到事务提交

快照读

  • 基于roll_pointer及mvcc策略进行读取操作，读到的是基于历史数据
  1. select * from table where ?

当前读

  • 基于数据库行当前的实际数据进行读取
  1. select * from table where ? lock in share mode; # 加S锁，读锁
  2. select * from table where ? for update; # 加X锁，写锁
  3. insert into table values(…); # 正是因为DML操作是当前读操作，而select上述是快照读操作，所以会产生逻辑冲突
  4. update table set ? where ?;
  5. delete from table where?;

锁

全局锁

  • 整个数据库处于只读状态
  1. 加锁：flush tables with read lock;
  2. 解锁：unlock tables;
  • 用于全库备份

表级锁

表锁
  1. lock tables’table’[read/write]
  2. unlock tables
元数据锁
  1. crud
  2. alter
意向锁
  • 目的：快速判断表里是否有记录加锁（只要一个表中有行或者其他加锁了，该表会自动加上意向锁）
auto-inc锁
  • 特殊表锁，用来实现自增约束，语句结束后释放锁（而非在事务结束时释放）

行级锁

  • 只有记录锁是我们使用的，gap锁和临键锁都是隔离级别自动实现的
记录锁（record lock）
  1. S锁 共享锁，手动加锁：lock in share mode
  2. X锁 排他锁，手动加锁：for update
间隙锁（gap lock）
  • RR级别（repeatable read），RC级别（read committed）
  • 防止其他事务在记录间插入新的记录，从而避免幻读现象
  • 开区间锁
临键锁（next-key lock）
  • RR级别
  • 是间隙锁和记录锁之和
  • 左开右闭区间锁

CURD与锁

  • 查询：
    1. mvcc（RR，RC）
    2. S锁，手动加
    3. X锁，手动加
    4. 不做处理，（RU级别）
  • 删除、更新
    1. 自动添加X锁
  • 插入
    1. insert intention lock（特殊的gap锁，与gap锁互斥，可能会引起死锁）
    2. auto-inc lock

锁兼容

  • 除了AI，在该行操作完成后释放，其余均在commit时才释放
  • 注意：意向锁的冲突，只有在全表扫描的时候生效，其余情况不会阻塞
锁 S X IS IX AI
S 兼容 冲突 兼容 冲突 冲突
X 冲突 冲突 冲突 冲突 冲突
IS 兼容 冲突 兼容 兼容 兼容
IX 冲突 冲突 兼容 兼容 兼容
AI 冲突 冲突 兼容 兼容 冲突
  • 重点：插入意向锁和gap锁是冲突的，可能引起死锁
锁 GAP（持有） Insert Intention（持有） Record（持有） Next-key（持有）
GAP（请求） 兼容 兼容 兼容 兼容
Insert Intention（请求） 冲突 兼容 兼容 冲突
Record（请求） 兼容 兼容 冲突 冲突
Next-key（请求） 兼容 兼容 冲突 冲突

锁对象分别讨论

死锁

  • 死锁：两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象；
  • MySQL 中采用 wait-for graph （等待图-采用非递归深度优先的图算法实现）的方式来进行死锁检测；
  • 异常报错：deadlock found when trying to get lock;
  1. 相反加锁顺序死锁
    • 不同表的加锁顺序相反或者相同表不同行加锁顺序相反造成死锁；其中相同表不同行加锁顺序相反造成死锁有很多变种，其中容易忽略的是给辅助索引行加锁的时候，同时会给聚集索引行加锁；同时还可能出现在外键索引时，给父表加锁，同时隐含给子表加锁；触发器同样如此，这些都需要视情况分析；
  2. 锁冲突死锁
    • innodb 在 RR 隔离级别下，最常见的是插入意向锁与 gap 锁冲突造成死锁；主要原理为：一个事务想要获取插入意向锁，如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
DROP TABLE IF EXISTS `dl_account_t`;
CREATE TABLE `dl_account_t` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`money` INT(11) DEFAULT 0,
	`account_id` INT(11) DEFAULT 0,
	PRIMARY KEY (`id`),
	UNIQUE `uk_account_id` (`account_id`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;

select * from dl_account_t
INSERT INTO `dl_account_t`(`name`,`money`,`account_id`) VALUES ('C', 1000, 10001),('B', 1000, 10002),('A', 1000, 10003),('ZZ', 1000, 10005)

-- 相反加锁顺序死锁1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
-- 死锁事务1
UPDATE `dl_account_t` SET `money` = `money` - 100 WHERE `id` = 1;
-- 死锁事务2
-- UPDATE `dl_account_t` SET `money` = `money` - 100 WHERE `id` = 2;
-- 死锁事务1
UPDATE `dl_account_t` SET `money` = `money` + 100 WHERE `id` = 2;
-- 死锁事务2
-- UPDATE `dl_account_t` SET `money` = `money` - 100 WHERE `id` = 1;

rollback

DROP TABLE IF EXISTS `dl_mark_t`;
CREATE TABLE `dl_mark_t` (
  `a` INT(11) NOT NULL DEFAULT '0',
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `dl_mark_t` VALUES (1,1),(5,4),(20,20),(25,12);
-- 死锁情况 1
/*
  事务一在插入时由于跟事务二插入的记录唯一键冲突，所以对 b=10 这个唯一索引加 S 锁（Next-key）并处于锁等待，事务二再插入 b=9 这条记录，需要获取插入意向锁（lock_mode X locks gap before rec insert intention）和事务一持有的 Next-key 锁冲突，从而导致死锁。
*/
BEGIN
-- 死锁事务 2
insert into `dl_mark_t` values(26,10);
-- 死锁事务 1
insert into `dl_mark_t` values(30,10);
-- 死锁事务 2
insert into `dl_mark_t` values(40,9);


-- 死锁情况 2
/*
1. 三个事务依次执行 insert 语句，由于 b是唯一索引，所以后两个事务会出现唯一键冲突。但此时要注意的是事务一还没有提交，所以并不会立即报错。事务二和事务三为了判断是否出现唯一键冲突，必须进行一次当前读，加的锁是 Next-Key 锁，所以进入锁等待。要注意的是，就算在 RC 隔离级别下，一样会加 Next-Key 锁，所以说出现 GAP 锁不一定就是 RR 隔离级别;
2. 事务一回滚，此时事务二和事务三成功获取记录上的 S 锁；
3. 事务二和事务三继续执行插入操作，需要依次请求记录上的插入意向锁（插入意向锁和 GAP 锁冲突，所以事务二等待事务三，事务三等待事务二，形成死锁。
*/
BEGIN
-- 死锁事务 1
insert into `dl_mark_t` values(27, 29);
-- 死锁事务 2
insert into `dl_mark_t` values(28, 29);
-- 死锁事务 3
insert into `dl_mark_t` values(29, 29);
-- 死锁事务 1
ROLLBACK;

2024-03-12 该篇文章被 Cleofwine 归为分类: 服务端

上一篇：后端技术_MySQL缓存策略 下一篇：后端技术_MySQL索引原理以及SQL优化
© 2023-2026 Cleofwine / Email / GitHub / 浙ICP备2024136127号
🌙赠我弯弯一枚月，也赠予我晚星⭐ 本站由Hexo驱动｜使用Hexo-theme-A4主题