默认事务隔离级别为RR
mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
在使用之前需要打开innodb lock monitor,这样在查看 engine innodb status 的时候可以更加清晰的查到到锁的情况
查看资源情况
set GLOBAL innodb_status_output_locks=ON;
show engine innodb status;
初始测试数据
-- 测试表
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `tu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 测试数据
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('1', '11', '14');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('2', '12', '15');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('3', '13', '16');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('4', '14', '17');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('5', '15', '18');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('6', '16', '19');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('7', '17', '20');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('9', '20', '22');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('10', '20', '23');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('11', '21', '24');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('12', '22', '25');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('13', '23', '26');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('14', '24', '27');
INSERT INTO `t` (`id`, `c1`, `c2`) VALUES ('15', '18', '21');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('1', '11', '14');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('2', '12', '15');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('3', '13', '16');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('4', '14', '17');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('5', '15', '18');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('6', '16', '19');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('7', '17', '20');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('9', '20', '22');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('10', '26', '23');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('11', '21', '24');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('12', '22', '25');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('13', '23', '26');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('14', '24', '27');
INSERT INTO `tu` (`id`, `c1`, `c2`) VALUES ('15', '18', '21');
删除SQL加锁分析
根据非唯一索引删除一条存在记录
-- 根据非唯一索引删除一条存在记录
start transaction;
delete from t where c1=15;
---TRANSACTION 3142, ACTIVE 6 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 4604, query id 246 localhost ::1 root
TABLE LOCK table `lock_test`.`t` trx id 3142 lock mode IX
RECORD LOCKS space id 2 page no 5 n bits 88 index idx_c1 of table `lock_test`.`t` trx id 3142 lock_mode X
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 80000005; asc ;;
RECORD LOCKS space id 2 page no 4 n bits 88 index PRIMARY of table `lock_test`.`t` trx id 3142 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000000c46; asc F;;
2: len 7; hex 020000011a0110; asc ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 80000012; asc ;;
RECORD LOCKS space id 2 page no 5 n bits 88 index idx_c1 of table `lock_test`.`t` trx id 3142 lock_mode X locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000010; asc ;;
1: len 4; hex 80000006; asc ;;
锁情况为:
- 4 lock struct(s):4种锁结构,分别为IX,idx_c1和主键的行锁,还有idx_c1的gap锁
- 3 row lock(s):有3个行锁,除去IX的都是算在row lock里面
根据唯一索引删除一条存在记录
-- 根据唯一索引删除一条存在记录
start transaction;
delete from tu where c1=15;
---TRANSACTION 3144, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 4604, query id 262 localhost ::1 root
TABLE LOCK table `lock_test`.`tu` trx id 3144 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index uniq_c1 of table `lock_test`.`tu` trx id 3144 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000f; asc ;;
1: len 4; hex 80000005; asc ;;
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `lock_test`.`tu` trx id 3144 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000000c48; asc H;;
2: len 7; hex 010000011b0110; asc ;;
3: len 4; hex 8000000f; asc ;;
4: len 4; hex 80000012; asc ;;
锁情况为:
- 3 lock struct(s):3种锁结构,分别为IX,idx_c1和主键的行锁,没有gap锁
- 2 row lock(s):有2个行锁,除去IX的都是算在row lock里面,没有gap,因此为2个
根据主键删除一条存在记录
-- 根据主键删除一条存在记录
start transaction;
delete from tu where id=2;
---TRANSACTION 3150, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 4604, query id 278 localhost ::1 root
TABLE LOCK table `lock_test`.`tu` trx id 3150 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `lock_test`.`tu` trx id 3150 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000000c4e; asc N;;
2: len 7; hex 020000011c0110; asc ;;
3: len 4; hex 8000000c; asc ;;
4: len 4; hex 8000000f; asc ;;
锁情况为:
- 2 lock struct(s):2种锁结构,分别为IX和主键的行锁,没有gap锁
- 1 row lock(s):有1个行锁,就主键记录上的行锁,没有gap,因此为1个
根据非唯一索引删除一条不存在记录
-- 根据非唯一索引删除一条不存在记录
start transaction;
delete from t where c1 = 4;
---TRANSACTION 3152, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24, OS thread handle 4604, query id 294 localhost ::1 root
TABLE LOCK table `lock_test`.`t` trx id 3152 lock mode IX
RECORD LOCKS space id 2 page no 5 n bits 88 index idx_c1 of table `lock_test`.`t` trx id 3152 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 80000001; asc ;;
锁情况为:
- 2 lock struct(s):2种锁结构,分别为IX和X类型的gap锁
- 1 row lock(s):有1个行锁,为非唯一索引的gap锁
根据唯一索引删除一条不存在记录
-- 根据唯一索引删除一条不存在记录
start transaction;
delete from tu where c1 = 4;
---TRANSACTION 3153, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24, OS thread handle 4604, query id 310 localhost ::1 root
TABLE LOCK table `lock_test`.`tu` trx id 3153 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 80 index uniq_c1 of table `lock_test`.`tu` trx id 3153 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 4; hex 80000001; asc ;;
锁情况为:
- 2 lock struct(s):2种锁结构,分别为IX和X类型的gap锁
- 1 row lock(s):有1个行锁,为唯一索引的gap锁
根据主键删除一条不存在记录
-- 根据主键删除一条不存在记录
start transaction;
delete from tu where id = 100;
---TRANSACTION 3154, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24, OS thread handle 4604, query id 326 localhost ::1 root
TABLE LOCK table `lock_test`.`tu` trx id 3154 lock mode IX
RECORD LOCKS space id 3 page no 4 n bits 80 index PRIMARY of table `lock_test`.`tu` trx id 3154 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
锁情况为:
- 2 lock struct(s):2种锁结构,分别为IX和X类型的gap锁
- 1 row lock(s):有1个行锁,为主键上的gap锁
总结
- 在非唯一索引的情况下,删除一条存在的记录是有gap锁,锁住记录本身和记录之前的gap
- 在唯一索引和主键的情况下删除一条存在的记录,因为都是唯一值,进行删除的时候,是不会有gap存在
- 非唯一索引,唯一索引和主键在删除一条不存在的记录,均会在这个区间加gap锁
- 通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候,都会请求该记录的行锁,同时锁住记录之前的gap
- RC 情况下是没有gap锁的,除了遇到唯一键冲突的情况,如插入唯一键冲突。
一个最不可思议的MySQL死锁分析
网上找到的一个大佬的详细分析流程,正好是项目中遇到的一个找了好久的死锁问题,特此记录一下
业务异常日志,其中 device_id
和 push_app_id
是唯一索引
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [delete from t_device_app_41 where device_id = ? and push_app_id=? ]; Deadlock found when trying to get lock; try restarting transaction;
Links