xiawen0731

Mysql锁分析

2018/06/08 数据库

默认事务隔离级别为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;

详见一个最不可思议的MySQL死锁分析

Links

Search

    Post Directory