背景
业务系统在进行压测的时候，反馈账户系统部分请求未成功，查询日志发现出现了 dead lock，查询当前测试环境数据库事务隔离级别为 SERIALIZABLE
01 排查命令
1.1 查询锁信息
查询当前锁
Mysql8.0 之前使用：select * from information_schema.innodb_locks;
Mysql8.0 使用：select * from performance_schema.data_locks;
查询等待锁
Mysql8.0 之前使用：select * from information_schema.innodb_lock_waits;
Mysql8.0 使用：select * from performance_schema.data_lock_waits;
1.2 事务隔离级别
修改全局事务隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
修改当前会话事务隔离级别
SET transaction_isolation = 'READ-COMMITTED';
查询全局事务隔离级别
select @@global.tx_isolation;
查询事务隔离级别
select @@tx_isolation;
1.3 查询死锁信息
命令可以查询到最近一次死锁的日志
show engine innodb status\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-04-14 15:23:17 0x7fe5d82ae700
*** (1) TRANSACTION:
TRANSACTION 1854, ACTIVE 37 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 140625150658304, query id 239 localhost root updating
UPDATE tb_account SET balance= 101 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`tb_account` trx id 1854 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000073d; asc =;;
2: len 7; hex ac000001200110; asc ;;
3: len 8; hex 8000000000000309; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 10; hex 80000000002b67000000; asc +g ;;
*** (2) TRANSACTION:
TRANSACTION 1855, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13, OS thread handle 140625150928640, query id 240 localhost root updating
UPDATE tb_account SET balance= 101 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`tb_account` trx id 1855 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000073d; asc =;;
2: len 7; hex ac000001200110; asc ;;
3: len 8; hex 8000000000000309; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 10; hex 80000000002b67000000; asc +g ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`tb_account` trx id 1855 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000073d; asc =;;
2: len 7; hex ac000001200110; asc ;;
3: len 8; hex 8000000000000309; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 10; hex 80000000002b67000000; asc +g ;;
*** WE ROLL BACK TRANSACTION (1)
lock_mode X locks rec but not gap waiting Record lock 表示这是一个 X 记录锁
HOLDS THE LOCK(S): 表示当前持有的锁
WAITING FOR THIS LOCK TO BE GRANTED: 表示当前需要获取的锁
03 复现
3.1 修改当前事务隔离级别
SET transaction_isolation = 'SERIALIZABLE';
3.2 初始化数据
创建表
CREATE TABLE `tb_account` (
`id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`account_type` int(11) NOT NULL,
`balance` decimal(20,6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_id_account_type` (`user_id`,`account_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
插入数据
INSERT INTO `tb_coin_account95` (`id`, `user_id`, `account_type`, `balance`, `create_time`, `last_update_time`, `version`, `expire_at`, `freeze_amount`)
VALUES
(743576, 1239095, 32, 11111330.000000, '2022-09-22 02:46:45', '2024-04-12 16:30:21', 182, 0, 313.000000);
3.3 执行语句
- 开始两个事务并且同时执行查询语句
事务1
begin;
select * from tb_account where id = 1;
事务2
begin;
select * from tb_account where id = 1;
- 在两个事务上分别执行
UPDATE tb_account SET balance= 101 WHERE id = 1;
- 执行结果如下
事务1:
事务2:
可以看到出现了死锁
- 执行
show engine innodb status\G;命令查询死锁信息
04 思考
-
对于一个事务想要获取到某条记录的 X 锁，需要其它事务没有持有这条记录的 S 锁或等待其它事务释放 S 锁。
-
SERIALIZABLE这种隔离级别普通的查询也会获取记录的 S 锁。 -
S 锁可以重复获取，但事务 A 等待事务 B 释放 S 锁，事务 B 等待事务 A 释放 S 锁，由于循环等待造成了死锁，而正常的读已提交读的是视图，是不会获取 S 锁的