mysql分区表导致的死锁

死锁异常：

org.springfreamwork.dao.DeadlockLoserDataAccessException:......

Cause:java.sql.BatchUpdateException:Deadlock found when trying to get lock;try restarting transaction at.....

1、java代码如下

@Transactional

public void handle(){

selectDao.select1(...);

updateDao.insert(...);

}

2、和java代码(碰巧)同时运行的job中的sql代码如下：

alter table add partition .....（添加或者删除分区）

3、被操作的表是一个分区表

4、java代码在一个线程中，job在另外一个线程中

发生死锁的原因：

1、java中发起的sql（dao的select方法和insert方法中的sql语句）中的where语句没有分区键，所以就锁定了全部分区。

注：dml sql语句执行前是一定要申请mdl读锁的（mdl表示meta data lock）。目的是防止在dml语句执行期间有其它ddl语句的执行（ddl语句执行前要申请mdl写锁）。而mdl读锁是可以共享的（mdl读和mdl读之间共享），但是和mdl写锁是互斥的。

2、job中的sql因为是alter table的语句（ddl语句），所以要申请mdl写锁（mdl写和mdl写或者mdl写和mdl读之间是互斥的）。

注：ddl sql语句（包括alter table）执行前是一定要申请mdl写锁的。目的是防止在ddl期间其它dml语句执行。

3、如果mdl写锁被block后需要等待持有mdl读锁的事务结束后才可以获取到写锁，而不是事务中的某个sql结束后就能够获取到写锁

4、mdl读、写锁是公平锁。事务中的第一条dml语句先持有了mdl读锁、job发起了mdl写锁申请、同一个事务中的第二条dml语句再发起mdl读锁申请的时候就需要排队等待mdl写锁的释放。

分析：T2时刻在等待整个事务的释放以便获取到mdl写锁，T3时刻等待T2时刻申请的mdl写锁释放掉。即，T2等T3释放资源、而T3也在等T2释放资源，于是死锁。

解决办法：把java代码方法上的事务删除掉，使得select语句结束后持有的mdl读锁就立刻释放掉。

思考：

1、如果java方法上的事务必须使用，那么在方法中的sql语句中的where语句上使用分区键是否可以解决问题？

因为mysql的分区表是innodb引擎层的概念，在server层仍然认为是一张表，也只有一个mdl锁（所有分区共用一个mdl锁）；

2、如果java事务必须存在（比如事务中有两个insert语句），那么这个死锁的发生是不可避免的？那么最终如何解决？

死锁是不可避免的。只能尽量使用短事务。可以在ddl语句上使用timeout，具体而言：

对于分区表的一些知识补充：

1、所谓分区是对innodb存储层而言的。在上层（比如server层或者应用层）看来这就是一张表。对于mysql分区表而言，是由server层来决定使用哪个分区，而对于手工分表（比如sharding jdbc）而言是由应用层代码决定使用哪个分区。而从innodb角度看来，多个分区到底是由server层还是由代码来决定的，这两种方式并无区别。

2、在server层看来，分区表就是一张表，比如，一张表的多个分区共用一个mdl锁。

3、mysql在第一次访问分区表的时候必须要访问所有分区，因此性能会有损失（相对于真正的单表而言）

4、在innodb层看来，分区表就是多张表，因此mdl锁之后的执行过程（比如dml或者ddl）会根据分区表规则只访问必要的分区，这相对于单表而言又有了一定性能优势（如果是等值分区键那么性能优势明显，如果是范围那么可能没那么明显）。

5、使用分区表的场景：业务代码更简介，因为对于业务代码而言这就是一张表；删除历史记录更容易，因为可以直接alter table drop partition，效果和drop 单表类似。

6、对业务代码而言，到底使用分区表还是使用分库分表中间件，这个要看中间件的成熟度。

7、分区表如果使用了自增主键，mysql要求主键中必须包含分区字段（唯一索引也必须包含分区字段），这对于业务编码而言也是有一定复杂性的。