mysql 死锁语句_MySQL死锁

死锁产生

行锁的具体实现算法有三种：record lock、gap lock以及next-key lock。

record lock是专门对索引项加锁；

gap lock是对索引项之间的间隙加锁；

next-key lock则是前面两种的组合，对索引项及其之间的间隙加锁。

只在可重复读或以上隔离级别下的特定操作才会取得gap lock或next-key lock，在Select、Update和Delete时，除了基于唯一索引的查询之外，其它索引查询时都会获取gap lock或next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用gap lock或next-key lock。

在MySQL中，gap lock默认是开启的，即innodb_locks_unsafe_for_binlog参数值是disable的，且MySQL中默认的是RR事务隔离级别。

当执行以下查询SQL时，由于order_no列为非唯一索引，此时又是RR事务隔离级别，所以SELECT的加锁类型为gap lock，这里的gap范围是(4,+∞)。

1 SELECT id FROM demo.order_record where order_no = 4 for update;

执行查询SQL语句获取的gap lock并不会导致阻塞，而当执行以下插入SQL时，会在插入间隙上再次获取插入意向锁。

插入意向锁其实也是一种gap锁，它与gap lock是冲突的，所以当其它事务持有该间隙的gap lock时，需要等待其它事务释放gap lock之后，才能获取到插入意向锁。

以上事务A和事务B都持有间隙(4,+∞)的gap锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的gap锁释放，于是就造成了循环等待，导致死锁。

1 INSERT INTO demo.order_record(order_no, status, create_date) VALUES (5, 1, '2019-08-30 12:22:22');

可以通过以下锁的兼容矩阵图，来查看锁的兼容性：

避免死锁的措施

避免死锁最直观的方法就是在两个事务相互等待时，当一个事务的等待时间超过设置的某一阈值，就对这个事务进行回滚，另一个事务就可以继续执行了。这种方法简单有效，在InnoDB中，参数innodb_lock_wait_timeout是用来设置超时时间的。

另外，还可以将order_no列设置为唯一索引列。虽然不能防止幻读，但可以利用它的唯一性来保证订单记录不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会抛出异常。

还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用Redis以及ZooKeeper来实现，运行效率比数据库更佳。

常见死锁的问题

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立。

InnoDB存储引擎的主键索引为聚簇索引，其它索引为辅助索引。

如果之前使用辅助索引来更新数据库，就需要修改为使用聚簇索引来更新数据库。

如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

综上可知，在更新操作时，应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

解决死锁的最佳方式就是预防死锁：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；

在允许幻读和不可重复读的情况下，尽量使用RC事务隔离级别，可以避免gap lock导致的死锁问题；

更新表时，尽量使用主键更新；

避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；

设置锁等待超时参数，可以通过innodb_lock_wait_timeout设置合理的等待超时阈值，特别是在一些高并发的业务中，可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。