一、MySQL 锁的介绍

1.1、InnoDB 中的锁

1.1.1、排他锁和共享锁(Shared and Exclusive Locks)
  • 排他锁： 允许事务删除、更新一行数据

  • 共享锁：允许事物读取一行数据

    1.1.2、意向锁(Intention Locks)

  • 意向共享锁： 事务想要表中的几行共享锁

  • 意向排他锁： 事务想要表中的几行排他锁

    1.1.3、行锁（Record Locks）

  • 单个记录上的锁

  • 只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)

    1.1.4、间隙锁(Gap Locks)

  • 锁定一个范围，不包含记录本身

  • 如果索引列是唯一索引，那么只会锁住这条记录(只加行锁)，而不会锁住间隙。

  • 对于联合索引且是唯一索引，如果 where 条件只包括联合索引的一部分，那么依然会加间隙锁。

    1.1.5、Next-Key Locks

  • 行锁（Record Locks） + 间隙锁(Gap Locks) 的组合

  • 对于主键和唯一索引才是行锁
  • 在 RR 隔离级别下，InnoDB 使用 next-key lock 主要是防止幻读问题产生
    1.1.6、插入意向锁（Insert Intention Locks）
    1.1.7、自增锁(AUTO-INC Locks)
    1.1.8、Predicate Locks for Spatial Indexes

1.2、死锁的条件

  • 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
  • 请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
  • 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
  • 环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

1.3、相关命令

  • 查看锁的状态

    1. show engine innodb status;

  • 查看当前进程

    1. show full processlist;

  • 查看当前锁和事务的表 ```sql — mysql 8

— 查看锁 — mysql 5.x select from information_schema.INNODB_LOCKS; select from performance_schema.data_locks dl — 查看锁等待 — mysql 5.x select from information_schema.INNODB_LOCKS_WAITS; select from performance_schema.data_lock_waits dlw — 查看事务 select * from information_schema.INNODB_TRX it ;

— kill 线程 kill 线程ID

  2. <a name="g5TTh"></a>
  3. ### 1.4、锁是否兼容
  4. 横向是已持有锁，纵向是正在请求的锁：<br />![](https://cdn.nlark.com/yuque/0/2022/jpeg/438760/1646788678183-6ebc336c-9722-4406-8d27-591b6e0c7b39.jpeg#clientId=u1f648cb6-482b-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=yDCUq&margin=%5Bobject%20Object%5D&originHeight=198&originWidth=690&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=u5d87c6fe-3bf4-45cc-af92-e2b427e1f37&title=)
  6. <a name="LCDsF"></a>
  7. ## 二、锁实践
  8. <a name="fFS45"></a>
  9. ### 2.1、Next-Key Locks 
  10. 在默认的隔离等级，可重复读。
  12. - 准备sql
  13. ```sql
  14. CREATE TABLE `t` (
  15.   `id` int unsigned NOT NULL AUTO_INCREMENT,
  16.   PRIMARY KEY (`id`)
  17. ) ENGINE=InnoDB
  19. -- 插入数据
  20. insert into t select 1;
  21. insert into t select 2;
  22. insert into t select 3;
  23. insert into t select 6;
  24. insert into t select 8;

  • 在事务一中使用共享锁查询

    1. select * from t where id <6 lock in share mode;

  • 在事物二中，插入 4或5数据，会被阻塞

    1. insert into t select 5;

    因为 Next-Key Locks 算法下，会锁住范围数据 (-oo,6) 的数据，所以导致插入小于6的数据阻塞。

2.2、事务并发 insert 唯一键冲突

事务隔离级别在 RR 可重复读

  • 准备sql ```sql CREATE TABLE test.t7 ( id INT auto_increment NOT NULL, a INT NULL, CONSTRAINT t7_pk PRIMARY KEY (id), CONSTRAINT t7_un UNIQUE KEY (a) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t7 select 1,1; insert into t7 select 5,4; insert into t7 select 25,12; insert into t7 select 20,20;

  2. - 事务2执行
  3. ```sql
  4. begin;insert into t7(id,a)values(26,10);
  5. -- 语句 insert 成功，持有 a=10 的 排它行锁
  7. -- 转到事务1执行
  9. -- 再转到事务2执行
  10. begin;insert into t7(id,a)values(40,9);
  11. -- 该语句插入的 a=9 的值在事务 T1 申请的 gap 锁4-10之间， 
  12. -- 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,
  • 事务1执行
    1. begin;insert into t7(id,a)values(30,10);
    2. -- 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,
    3. -- 需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 
    4. --这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。

2.3、先 update 再 insert 的并发死锁问题

使用 示例中的sql

  • 事务一 ```sql

— 事务一先执行 ，更新不存在的记录，唯一key — 会产生 gap 间隙锁 update t7 set a = 000 where id = 3;

— 转到事务2执行

— 回到事务1 执行 — 产生插入意向锁， 与间隙锁冲突，阻塞 insert into t7(id,a)values(3,000);

— 再到事务2执行

  2. - 事务二
  3. ```sql
  4. -- 事务二执行，更新不存在的记录
  5. -- 会产生 gap 间隙锁 ，间隙锁是兼容的，不会阻塞
  6. update t7 set a = 111 where id = 4;
  9. -- 插入记录
  10. -- 产生插入意向锁， 与间隙锁冲突，相互阻塞，造成死锁
  11. insert into t7(id,a)values(4,111);

三、避免死锁

  • 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
  • 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
  • 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
  • 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
  • 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
  • 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。

参考