锁问题

脏读

事务修改数据但是还未提交事务的时候, 此时数据在缓冲池中, 这种数据被认为是脏数据

而脏读就是事务B读取到事务A还未提交的数据, 被称之为脏读

脏数据和脏页的区别

脏页是指数据被修改, 但是还没同步到磁盘中的页, 脏页和磁盘是异步刷新的, 但是又不会影响到mysql的其他操作, 所以是一个提高性能的方法, 类似于cpu架构中的write buffer, 可以被读取, 但是还未同步到高速缓存中

同时, 脏页中的数据是有用数据, 而脏读中的脏数据可能会被回滚从而失效

解决方案

数据库提供了隔离级别: 读已提交, 用于解决脏读问题

解决方法就是事务只能读取到另一个事务已提交的数据

也就说事务在未提交之前, 都是在一个类似快照上修改的, 直到事务提交才会真正的修改数据

这种快照的实现原理使用了 undo log, 用于记录各个版本的记录, 换句话说, 同一条记录可能在 undo log 中存在多条数据

不可重复读

事务A两次读取同一条记录, 读取到的记录的内容是不同的,原因是因为事务B修改了这条记录中的数据

为什么会导致不可重复读?

我的猜想:

前面的脏读, 我们使用读已提交进行解决, 我们只能读取到其他事务提交后的数据, 这里就会直接产生不可重复读的问题

我们每次读取数据都是从缓冲区或者磁盘中读取最新数据, 但缓冲区和磁盘的数据并不是只有一个事务能修改, 其他事务只能读取的, 他是高并发情况, 只要有另一个事务提交, 那么磁盘数据就被修改

本质问题: 事务两次从磁盘或者缓冲区读取数据

解决办法

从快照中读取数据

也就是说我们在查询完毕之后直接生成针对该事务的快照, 然后每次先从快照中读取, 如果快照没有记录再从磁盘中读取记录

也就是事务隔离级别的 可重复读

丢失更新

就是高并发中常见的 ABA 问题, 在高并发中的解决方案是 A1-B2-A3

为每次修改添加版本号就可以解决高并发中的ABA问题

那么在mysql中呢?

记录初始值为 50, 事务A修改了记录为100, 事务B修改该记录为50

此时事务A的持有者发现, 诶, 我刚刚不是才给该记录捐了50块钱么? 怎么没捐上钱? 再捐一次

这都是小问题, 可以发送通知说你转账成功了, 这样即便钱变回原样了, 捐款的那位用户也知道自己捐成功了

另一个场景下问题就严重了

原本公司账户存在 8000w元, 现在 用户1 和 用户2 同时发现有 8000w , 用户1 转账 5000w 给老板, 用户2 转账 1w 给自己, 正常情况下, 现在银行账户中应该只有 2999w, 但是由于丢失更新问题, 丢失了转账 5000w 的更新, 只发现了 1w 转账更新, 所以银行中还剩下 7999w

这个问题是怎么产生的?

用户1: 获知 8000

用户2: 获知 8000

用户1: 8000 - 5000 = 3000, 写入数据库

用户2: 8000 - 1 = 7999, 写入数据库

数据库: 剩下 7999

if 账户余额是否充足?
    转账
else
    msg("账户余额不足")

那么解决方法是什么?

在数据库 update 命令的时候加上 where 账户=8000 and 其他判断条件

update account set cash = 3000 where cash=8000 and 其他判断条件

注意这里我们是将 8000 - 5000 = 3000 计算出来了, 然后在 update 的

这样他在更新的时候会判断一下数据库是否真的存在 8000, 存在则更新, 不存在则再次读取余额再更新

但是你要保证update是在事务中

其实解决这个问题就非常简单, 我们不需要计算什么多余的东西直接
update account set cash = cash - 5000 where 其他判断条件
这样就完事了, 因为他会上锁, 如果是mysql默认的隔离级别的话, 会在那行row上X锁

虽然说直接用上面这种cash = cash-5000的方法可以直接解决问题, 但是很多时候我们并不直接这么操作

所以我们离开了mysql的保护, 就需要使用分布式锁方案或者我刚刚说的给 update where 添加条件

死锁

多个事务争夺锁资源导致相互等待的现象

死锁解决方案

如果两个事务死锁导致等待, 那么innodb有一个操作时间的阈值(innodb_lock_wait_timeout), 如果超时, 那么将一个事务回滚, 另一个等待的事务将正常执行

但是这种解决方案存在问题, 如果事务大量死锁怎么办? 而且回滚所带来的资源消耗可能更多, 并不合适通用场景

数据库还提供了其他方案: wait for garph

wait for graph(等待图)

会要求数据库保存:

锁的信息链表
事务等待链表

这是一张事务和资源之间等待的关系图, 如果这张图存在回路的情况, 那就意味着发生了死锁

这张图要怎么看呢?

可以发现存在 List 和两个资源 row1 和 row2

List 中存在 4 个事务 t1~4

row1存在两个事务, 一个 t2的排他锁, 然后是 t1 的共享锁, 所以导致 t1 需要等待 t2, 这里就产生了一条线

t1 --> t2

然后看 row2 资源, 发现 t1 和 t4 是共享锁, 兼容的, 所以可以同时读取

那么row2现在有两个事务在等待 t1 和 t4分别是 t2 t3

t2 t3 --> t1 t4

按照从上到下的顺序 t3 还需要等待 t2因为他们都是排它锁

所以综合一下最终产生的图是这样的:

t1 和 t2 存在回路, 那么就可能产生死锁

可能?

是的, 可能, 时间上存在问题, 如果回路之间的时间间隔较长, 事务都执行完毕了, 才产生了另一个事务, 那么就不会看到死锁问题

怎么定位死锁`sql`?

两种方法:

show engine innodb status
配置全局变量innodb_print_all_deadlocks, 默认是关闭的, 开启可以在错误日志中看到死锁信息

怎么看到错误日志在哪里?

SHOW VARIABLES LIKE 'log_error';

怎么配置变量值?

# 查看参数是否开启
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+

# 开启innodb_print_all_deadlocks,此参数是全局参数，可以动态调整。记得要加入到配置文件中
mysql> set global innodb_print_all_deadlocks = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | ON    |
+----------------------------+-------+

就可以在错误日志中看到:

TRANSACTION 159031, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 19, OS thread handle 4588, query id 1198 localhost ::1 root statistics
SELECT * from t where t.id = 0 for UPDATE
RECORD LOCKS space id 259 page no 4 n bits 80 index PRIMARY of table `test`.`t` trx id 159031 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000026b07; asc     k ;;
 2: len 7; hex 81000000bf0110; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000003; asc     ;;

RECORD LOCKS space id 259 page no 4 n bits 80 index PRIMARY of table `test`.`t` trx id 159031 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 000000026b06; asc     k ;;
 2: len 7; hex 82000001240110; asc     $  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;

TRANSACTION 159030, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 23, OS thread handle 6436, query id 1202 localhost ::1 root statistics
SELECT * from t where t.id = 1 for UPDATE
RECORD LOCKS space id 259 page no 4 n bits 80 index PRIMARY of table `test`.`t` trx id 159030 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000000; asc     ;;
 1: len 6; hex 000000026b06; asc     k ;;
 2: len 7; hex 82000001240110; asc     $  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 259 page no 4 n bits 80 index PRIMARY of table `test`.`t` trx id 159030 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000026b07; asc     k ;;
 2: len 7; hex 81000000bf0110; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000003; asc     ;;

死锁案例分析

死锁只有在并发的情况下才会产生, 如果程序是串行执行的, 那么就不会产生死锁问题

案例一

书本上给出了一个非常简单的死锁示例

案例二

书上的另一个案例, 这个案例也存在逻辑上的相互等待

A lock a=4

B lock a <=4

A 插入意向 a = 3

此时B需要等待 A 释放资源, 然后 A 又往 B 锁的范围内添加新的 row

此时即便 B 并没有给 a=3 这一行上锁, 但是在 mysql 分析器中还是被认为是逻辑上的死锁

并没有给 a = 3 上任何锁, 但是逻辑上 a=3 这一行应该是上锁的

所以他还是会死锁

案例三

CREATE TABLE `tt` (
  `id` int DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

会话1	会话2
BEGIN; insert into tt values(1,'1');	BEGIN; insert into tt values(2,'2');
update tt set name ='update 1' where id =1; -- 阻塞

	update tt set name='update 2' where id =2;
	死锁

我们会发现会话1在等待会话2的记录锁

如果现在会话2也等待会话1中的锁, 那么就产生了死锁问题

产生死锁后使用show engine innodb status指令去查看死锁信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-07-20 20:43:49 0xf34 # 发生死锁的四时间
*** (1) TRANSACTION: 
TRANSACTION 159146, ACTIVE 17 sec fetching rows # 发生死锁的事务id
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 42, OS thread handle 512, query id 2552 localhost ::1 root updating
update tt set name ='update 1' where id =1 # 死锁执行的 sql

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 262 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`tt` trx id 159146 lock_mode X locks rec but not gap # 这是一个排他记录锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000003315; asc     3 ;;
 1: len 6; hex 000000026daa; asc     m ;;
 2: len 7; hex 020000015e1f08; asc     ^  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 8; hex 7570646174652031; asc update 1;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 262 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`tt` trx id 159146 lock_mode X waiting # 事务 159146 等待一个排它锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000003316; asc     3 ;;
 1: len 6; hex 000000026daf; asc     m ;;
 2: len 7; hex 81000001350110; asc     5  ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 1; hex 32; asc 2;;


*** (2) TRANSACTION:
TRANSACTION 159151, ACTIVE 14 sec starting index read # 另一个导致产生死锁的事务
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 37, OS thread handle 2568, query id 2556 localhost ::1 root updating
update tt set name='update2' where id =2 # 产生死锁的 sql

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 262 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`tt` trx id 159151 lock_mode X locks rec but not gap # 可以看到是一个记录锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000003316; asc     3 ;;
 1: len 6; hex 000000026daf; asc     m ;;
 2: len 7; hex 81000001350110; asc     5  ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 1; hex 32; asc 2;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 262 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`tt` trx id 159151 lock_mode X waiting # 事务 159151 等待排它锁
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000003315; asc     3 ;;
 1: len 6; hex 000000026daa; asc     m ;;
 2: len 7; hex 020000015e1f08; asc     ^  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 8; hex 7570646174652031; asc update 1;;

*** WE ROLL BACK TRANSACTION (2) # 回滚了事务2

我排查死锁的方法

上图是三次 sql 分别执行的过程

每次都是执行一个sql, 查询一次data_locks表, 然后在回滚再次执行, 连续执行三次

第二次事务2执行后, 发现 a=2 id=2 这一行资源已经被占用, 所以

事务2阻塞, 等待事务1的资源

然后, 事务1需要共享锁资源也包括 a=2 id=2 这一行, 所以

事务1 阻塞等待 事务1和事务2的资源

现在两个事务相互阻塞等待, 死锁诞生了

mysql死锁问题排查技巧

锁问题

脏读