Mysql悲观锁踩坑测试

mingjie

发布于 2022-05-12 09:48:57

7470

mysql for update

今天遇到一个高并发悲观锁的问题，活跃连接堆积恶性循环最后DB卡死了。做下测试总结。看看这类SQL能扛多少，以后遇到问题心里也有底了。这是出问题前的截图，QPS继续涨连接就开始堆积了，SQL还是这些频率高了。还有一点TOP1的SQL有热点的行for update。

悲观锁业务场景

对应的索引访问条目进行上排他锁（X 锁），也就是说这个语句对应的锁就相当于update带来的效果。使用场景是事务内要求RR级别，避免RC出现不可重复读。
为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到 for update 子句。

测试表结构，数据生成方法

CREATE TABLE `__test_t1` (
    `id` BIGINT (20) NOT NULL AUTO_INCREMENT,
    `c1` VARCHAR (20) NOT NULL,
    `c2` INT (11) NOT NULL,
    `c3` datetime NOT NULL,
    `c4` INT (11) NOT NULL,
    `c5` VARCHAR (20) NOT NULL,
    `c6` VARCHAR (20) NOT NULL,
    PRIMARY KEY (`id`),
    KEY(`c1`),
    KEY(`c2`,`c3`,`c6`),
    KEY(`c4`,`c5`)
);

mysql> select * from __test_t1 limit 9;
+----+--------+----+---------------------+----+------+----------+
| id | c1     | c2 | c3                  | c4 | c5   | c6       |
+----+--------+----+---------------------+----+------+----------+
|  1 | CTrosW |  1 | 2020-02-15 16:07:20 | 72 | KXnS | 2sYjxyY6 |
|  2 | qDLIav | 70 | 2020-02-15 16:07:20 | 50 | yDnO | J40D0WyN |
|  3 | 9bm2ZE | 97 | 2020-02-15 16:07:20 | 41 | hxHH | dSxWVEil |
|  4 | JqNw3x | 22 | 2020-02-15 16:07:20 | 96 | lbIR | 0jmHh7CA |
|  5 | Ssw5Jd | 53 | 2020-02-15 16:07:20 | 54 | g2fX | Sm1S7PDx |
|  6 | Ac5GYG | 18 | 2020-02-15 16:07:20 | 33 | gHyv | JXr8eA5v |
|  7 | 5Loyoa | 32 | 2020-02-15 16:07:20 | 62 | hVC8 | HSb5LnsZ |
|  8 | q0AKPK | 92 | 2020-02-15 16:07:20 | 86 | Ikkt | gHBHzz08 |
|  9 | vW2dM7 | 90 | 2020-02-15 16:07:20 | 65 | IZD4 | i4kf7FQ2 |
+----+--------+----+---------------------+----+------+----------+

结论先行

事务一执行for update，不提交

事务二执行：

select：不等锁
select lock in share mod：需要S锁，等锁
select for update：需要X锁，等锁
update：需要X锁，等锁

索引的影响：

有索引只锁索引和主键上的行。
无索引只能把主键都锁了一遍，不匹配的再把锁释放掉。

测试记录：主键for update

锁主键事务一for update

锁主键事务二

select：不等锁
select lock in share mod：需要S锁，等锁
select for update：需要X锁，等锁
update：需要X锁，等锁

（事务一）

for update锁行

set session transaction isolation level read committed;
-- set session transaction isolation level repeatable read;


set autocommit=0;
select * from __test_t1 where id=1 for update;
+----+--------+----+---------------------+----+------+----------+
| id | c1     | c2 | c3                  | c4 | c5   | c6       |
+----+--------+----+---------------------+----+------+----------+
|  1 | CTrosW |  1 | 2020-02-15 16:07:20 | 72 | KXnS | 2sYjxyY6 |
+----+--------+----+---------------------+----+------+----------+

TRANSACTION 12119367, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 317, OS thread handle 140540943374080, query id 192968878 localhost root

（事务二）

for update等锁

lock_mode X locks rec but not gap waiting

select * from __test_t1 where id=1 for update;


--- TRANSACTION 12119368, ACTIVE 194 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 318, OS thread handle 140540932421376, query id 192968887 localhost root statistics
select * from __test_t1 where id=1 for update

---- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 7 n bits 320 index PRIMARY of table `db1`.`__test_t1` trx id 12119368 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000b8ed15; asc       ;;
 2: len 7; hex e60000015c0110; asc     \  ;;
 3: len 6; hex 4354726f7357; asc CTrosW;;
 4: len 4; hex 80000001; asc     ;;
 5: len 5; hex 99a59f01d4; asc      ;;
 6: len 4; hex 80000048; asc    H;;
 7: len 4; hex 4b586e53; asc KXnS;;
 8: len 8; hex 3273596a78795936; asc 2sYjxyY6;;

（事务二）

lock in share mode等锁

lock mode S locks rec but not gap waiting

select * from __test_t1 where id=1 lock in share mode;


--- TRANSACTION 12119368, ACTIVE 685 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 318, OS thread handle 140540932421376, query id 192968891 localhost root statistics
select * from __test_t1 where id=1 lock in share mode
---- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 7 n bits 320 index PRIMARY of table `db1`.`__test_t1` trx id 12119368 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000b8ed15; asc       ;;
 2: len 7; hex e60000015c0110; asc     \  ;;
 3: len 6; hex 4354726f7357; asc CTrosW;;
 4: len 4; hex 80000001; asc     ;;
 5: len 5; hex 99a59f01d4; asc      ;;
 6: len 4; hex 80000048; asc    H;;
 7: len 4; hex 4b586e53; asc KXnS;;
 8: len 8; hex 3273596a78795936; asc 2sYjxyY6;;

（事务二）

select不等锁

（事务二）

update等锁

lock_mode X locks rec but not gap waiting

update __test_t1 set c2=2 where id=1;


---TRANSACTION 12119368, ACTIVE 994 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 318, OS thread handle 140540932421376, query id 192968894 localhost root updating
update __test_t1 set c2=2 where id=1
Trx read view will not see trx with id >= 12119369, sees < 12119367
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 7 n bits 320 index PRIMARY of table `db1`.`__test_t1` trx id 12119368 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000b8ed15; asc       ;;
 2: len 7; hex e60000015c0110; asc     \  ;;
 3: len 6; hex 4354726f7357; asc CTrosW;;
 4: len 4; hex 80000001; asc     ;;
 5: len 5; hex 99a59f01d4; asc      ;;
 6: len 4; hex 80000048; asc    H;;
 7: len 4; hex 4b586e53; asc KXnS;;
 8: len 8; hex 3273596a78795936; asc 2sYjxyY6;;

测试记录：非唯一索引for update

锁多行的场景，看起来和逐渐for update，点查有索引的话只锁对应行

（事务一）

for update锁多行

-- PRIMARY KEY (`id`),
-- KEY `c1` (`c1`),
-- KEY `c2` (`c2`,`c3`,`c6`),
-- KEY `c4` (`c4`,`c5`)

set session transaction isolation level read committed;
-- set session transaction isolation level repeatable read;
set autocommit=0;


select count(*) from __test_t1 where c1=10;
+----------+
| count(*) |
+----------+
|      286 |
+----------+


select * from __test_t1 where c1=10 for update;

---TRANSACTION 12119372, ACTIVE 3 sec
3986 lock struct(s), heap size 450768, 286 row lock(s)
MySQL thread id 320, OS thread handle 140540942563072, query id 192968938 localhost root

（事务二）

从c1走X锁

select * from __test_t1 where c1=10 for update;

---TRANSACTION 12119373, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3520, 4246 row lock(s)
MySQL thread id 324, OS thread handle 140540942022400, query id 192968949 localhost root Sending data
select * from __test_t1 where c1=10 for update
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 54 n bits 320 index PRIMARY of table `db1`.`__test_t1` trx id 12119373 lock_mode X waiting
Record lock, heap no 89 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 8000000000001085; asc         ;;
 1: len 6; hex 000000b8ed15; asc       ;;
 2: len 7; hex e6000001601ad9; asc     `  ;;
 3: len 6; hex 31304e4f7551; asc 10NOuQ;;
 4: len 4; hex 8000002a; asc    *;;
 5: len 5; hex 99a59f01d5; asc      ;;
 6: len 4; hex 8000000a; asc     ;;
 7: len 4; hex 6f5a4253; asc oZBS;;
 8: len 8; hex 7067564672307a45; asc pgVFr0zE;;

（事务二）

从主键走X锁，只锁行

select * from __test_t1 where id=991700 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from __test_t1 where id=991701 for update;
+--------+--------+----+---------------------+----+------+----------+
| id     | c1     | c2 | c3                  | c4 | c5   | c6       |
+--------+--------+----+---------------------+----+------+----------+
| 991701 | kpVglL | 46 | 2020-02-15 16:09:47 | 51 | mCQf | uzdejLFT |
+--------+--------+----+---------------------+----+------+----------+


select * from __test_t1 where c1=10 for update;


---TRANSACTION 12119374, ACTIVE 3 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 324, OS thread handle 140540942022400, query id 192968952 localhost root statistics
select * from __test_t1 where id=991700 for update
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 12879 n bits 320 index PRIMARY of table `db1`.`__test_t1` trx id 12119374 lock_mode X locks rec but not gap waiting
Record lock, heap no 126 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 80000000000f21d4; asc       ! ;;
 1: len 6; hex 000000b8ed15; asc       ;;
 2: len 7; hex e6000057263356; asc    W&3V;;
 3: len 6; hex 31304e514474; asc 10NQDt;;
 4: len 4; hex 8000000c; asc     ;;
 5: len 5; hex 99a59f026f; asc     o;;
 6: len 4; hex 80000046; asc    F;;
 7: len 4; hex 6b51354d; asc kQ5M;;
 8: len 8; hex 734d6d6a696e5230; asc sMmjinR0;;

测试记录：范围for update

锁响应行

测试记录：无索引for update

只锁一行

select * from __test_t1 where c6='W2cJT7Jb' for update;
+--------+--------+----+---------------------+----+------+----------+
| id     | c1     | c2 | c3                  | c4 | c5   | c6       |
+--------+--------+----+---------------------+----+------+----------+
| 964139 | 10Kzrm | 12 | 2020-02-15 16:09:42 |  4 | 0hea | W2cJT7Jb |
+--------+--------+----+---------------------+----+------+----------+


---TRANSACTION 12119376, ACTIVE 5 sec
3986 lock struct(s), heap size 450768, 1 row lock(s)
MySQL thread id 320, OS thread handle 140540942563072, query id 192968962 localhost root

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-04-03，如有侵权请联系 cloudcommunity@tencent.com 删除

腾讯云测试服务

sql