执行select ... where ... for update语句遇到的情况

如果where的后面条件列没加索引，则会锁全表；如果条件列加了索引，会根据where的条件锁当前行记录或者行记录的区间。

例子： 一张订单表，其中 id 字段为主键索引，order_no 字段普通索引，也就是非唯一索引。

CREATE TABLE t_order (

id int NOT NULL AUTO_INCREMENT,

order_no int DEFAULT NULL,

create_date datetime DEFAULT NULL,

PRIMARY KEY (id),

KEY index_order (order_no)

USING BTREE ) ENGINE=InnoDB ;

数据项：

id  order_no  create_date

1 1001 2023-09-06 13:29:01

2 1002 2023-09-06 13:29:02

3 1003 2023-09-06 13:29:03

4 1004 2023-09-06 13:29:04

5 1005 2023-09-06 13:29:05

6 1006 2023-09-06 13:29:06

此时有2个事务A、B。一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以两个事务先要查询该订单是否存在，不存在才插入记录。

此时，两个事务都陷入了等待状态（前提没有打开死锁检测），也就是发生了死锁，因为都在相互等待对方释放锁。

A持有的锁： 使用命令select * from performance_schema.data_locks\G;

可以查看A事务获取的为：行锁（Record）X类型的next-key lock。此时事务A在二级索引（index_name:index_order）上家的是X类型的next_ke锁，锁的范围是(1006,+∞]。

死锁的原因：

        1、当我们执行以上的insert插入语句时，会在插入间隙上获取"插入意向锁"，而"插入意向锁"与"间隙锁"是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以所以两个事务中 select ... for update 语句并不会相互影响。

        2、案例中的事务 A 和事务 B 在执行完后 select ... for update 语句后都持有范围为(1006,+∞]的next-key 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。