mysql Innodb锁的使用

前言

看了很多mysql innodb锁的原来，但是实际项目中，用的相对少，这次准备覆盖一下基本的场景进行实践一下

锁的使用

建库建表

create database lock_test default character set utf8mb4 collate utf8mb4_unicode_ci;

CREATE TABLE `lock_tab` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT ,
  `index_id` bigint unsigned NOT NULL DEFAULT '0' ,
  `first_uk_id` bigint unsigned NOT NULL DEFAULT '0',
  `second_uk_id` bigint unsigned NOT NULL DEFAULT '0',
  `num` int unsigned NOT NULL DEFAULT '0',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_index_id` (`index_id`), 
  UNIQUE  KEY `first_uk_id_UNIQUE` (`first_uk_id`),
  UNIQUE  KEY `second_uk_id_UNIQUE` (`second_uk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into lock_tab (index_id, first_uk_id, second_uk_id) values 
(1,2,3),
(4,5,6),
(7,8,20),
(8,10, 10);

不同场景使用锁

锁住全表

session1:  begin;
session2:  begin;
session1:  select * from lock_tab where num = 1 for update;
session2:  update lock_tab set num = 5 where id = 1;

观察到的现象就是session2被阻塞然后一段时间后报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这是因为num字段没有索引，那么where 条件用num时，会锁住全表

再继续, 会成功

session1 : commit;
session2:  update lock_tab set num = 5 where id = 1; 
					commit;

查看数据

+----+----------+-------------+--------------+-----+---------------------+
| id | index_id | first_uk_id | second_uk_id | num | update_time         |
+----+----------+-------------+--------------+-----+---------------------+
|  1 |        1 |           2 |            3 |   5 | 2022-04-20 09:23:48 |
|  2 |        4 |           5 |            6 |   0 | 2022-04-20 09:18:49 |
|  3 |        7 |           8 |           20 |   0 | 2022-04-20 09:18:49 |
|  4 |        8 |          10 |           10 |   0 | 2022-04-20 09:18:49 |
+----+----------+-------------+--------------+-----+---------------------+

然后接下来，再继续重复

session1:  begin;
session2:  begin;
session1:  select * from lock_tab where num = 0 for update;
session2:  update lock_tab set num = 0 where id = 1;

session 2依然会被阻塞，可以看到，虽然session1表面上num = 0会锁住num = 0的，实际上，会锁住全表。

可以这么理解，session 1想要保证锁住num = 0的所有记录，那么依然

锁住某条记录

session1: begin;
session2: begin;
session1: select * from lock_tab where index_id = 4 for update;
session2: update lock_tab set num = 1 where index_id = 4;  // 阻塞

这个很好理解，显然会被阻塞

继续

先crt+z，取消session2的阻塞，再继续
session2: update lock_tab set index_id = 4 where id = 3;  //阻塞
crt+z，取消session2的阻塞，再继续
session2:  update lock_tab set index_id = 4 where id = 1; //阻塞

显然，这是在索引index_id 加了next-key锁, 当有index_id 在(1, 4]和[4, 7]中间插入值时会被阻塞

更复杂场景

session1: begin;
session2: begin;
session1: delete from lock_tab where id = 3;
session2: delete from lock_tab where id = 4;
session1: insert into lock_tab (index_id, first_uk_id, second_uk_id) values (7,8,20);   //阻塞

对于这个场景有些好奇，命名两个session都是删除，自己的记录，然后新增自己的记录，但是为什么发生阻塞了。

原因如下：

1、delete时，由于有唯一索引，因此，当删除时，会在唯一索引加上next-key，避免其他事务插入

2、由于唯一索引，除了加上next-key，还会加上共享锁，允许其他事务读，但是不允许其他事务写，对于事务2，那么就是first_uk_id，的5和10之间加了共享锁，事务1，insert就会阻塞

总结

经常在一些文章中学到很多关于锁的使用场景，其实个人觉得，这些概念或许难以记住，但是我觉得可以从使用场景上来进行理解，比如上述的唯一索引的锁，可能就会忽略了共享锁，然后对于为什么会发生锁超时有疑问，其实结合唯一索引的约束可以更快找到原因