众所周知,在mysql数据库内要想一次更改多条数据,且保证此期间不被其他语句打断,大家都会选择事务和锁。但是事务内的锁到底是锁住了全表还是锁住某行呢,与什么有关呢?什么情况容易造成死锁呢?
纸上得来终觉浅,绝知此事要躬行 。在看完极客时间的mysql实战课程和搜索了大量博客后,我做了如下测试以验证这些结论。
1、单一索引/unique列/主键 查询到已有行:
mysql:5.7.32; innodb引擎
表模型:
CREATE TABLE `transaction_test` (
`id` bigint(255) NOT NULL,
`idx` bigint(255) DEFAULT NULL,
`unq` bigint(255) DEFAULT NULL,
`normal` bigint(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unq` (`unq`),
KEY `idx` (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据 (1,1,1,1);(2,2,2,2);(3,3,3,3);(4,4,4,4)
1.1 update锁
先给结论:update如果是根据普通列进行搜索对应行进行更新则锁住全表直到事务结束才释放,而如果搜索列是主键或索引或者unique列,则只锁对应行也是等到事务结束才释放。【一定要注意本节大标题,是前提!!!】
test1: 锁全表
begin; update `transaction_test` set idx=11 where normal=1; |
|
begin; update `transaction_test` set idx=222 where normal=2; //阻塞(超过50s后:1205 - Lock wait timeout exceeded; try restarting transaction) |
|
commit; (1,11,1,1) |
|
commit; //执行成功(2,222,2,2) |
两个事务操作不是同一行但依旧会被阻塞,直到另一个事务释放。当把set idx换成normal,unq,测试结论一致。
test2: 锁行
begin; update `transaction_test` set normal=11 where idx=1; |
|
begin; update `transaction_test` set normal=222 where idx=2; |
|
upd |