高并发情况下，MYSQL的锁等待问题分析和解决方案

问题描述

在进行高并发性能调优的时候发现了如下的一个问题：

1. 在一个事务中同时包括了SELECT，UPDATE语句

2. SELECT和UPDATE涉及到的数据为同一张表中的同一记录

3. 在并发为10的情况下就会触发数据库锁等待和死锁的情况

问题分析

在问题分析之前引入几个概念

事务隔离级别

参考文章《事务的ACID特性》

共享锁（S锁）

SELECT 语句时对查询行加的锁类型为共享锁。

共享锁的特性为：不允许其他事务对该记录加排他锁，但是允许加共享锁。

保持时间：可重复度级别中共享锁会保持到事务结束。

排他锁（X锁）

MYSQL的默认隔离级别（可重复度）中，UPDATE，INSERT和DELETE语句对操作语句加的锁类型为排他锁

排他锁的特性为：不允许其他事务对该记录加共享锁和排他锁

保持时间：会保持到事务结束。

更新锁（U锁）

UPDATE语句在更新前需要对整表上更新锁，在找到记录后对需要操作记录上排他锁并释放更新锁

更新锁特性为：用来预定要对此页施加排他锁，它允许其他事务加共享锁，但不允许再施加更新锁或排他锁

保持时间：会保持到事务结束。

分析

在MYSQL中使用的默认隔离接级别为可重复读，那么SELECT语句在执行时会给记录增加共享锁，UPDATE语句会给数据上更新锁和排他锁，INSERT语句会给数据上排他锁（数据为新增，在事务结束之前是对其他事务不可见的，可忽略）。

这样我们可以很清晰的看出，假设当前并行执行两个事务A和B。

1. 当A开始执行事务中的SELECT语句时，会给该条记录加共享锁，这样事务B不能够对该记录加排他锁，需要等待A事务提交

2. 当A开始执行事务中的UPDATE语句时，首先会给该页记录加更新锁，这样事务B不能够对该页记录加排他锁，需要等待A事务提交；同时在定位记录后给记录增加排他锁，这样事务B不能对该记录加共享锁和排他锁，需要等待A事务提交。

3. 由于A事务执行时间过长，在整个执行事务中还存在调用其他服务，查询其他表的操作。导致事务提交时间过长，B事务一直等待，导致B事务锁等待时间过长。引起文章标题中的问题。

解决方案

方案一：缩小事务范围，只把DML语句（UPDATE,INSERT,DELETE）包裹在事务中

方案二：尽可能减少事务中包含的DML语句。提高事务的执行时间。

注意

1. Mybatis会再插入或删除后，进行更新操作时使用同一个sqlSession，查询时新建sqlSession。这样先插入（删除）后更新可以更新到新的数据（更新失败）；先插入（更新或者删除）后查询，则不会查询到新的数据。参考《mysql同一个事务中先插入再查询与先删除再查询结果分析》