Mysql行锁机制引发的血案

最近在开发中使用到了多线程对同个表进行读写操作，由于数据库基础渣渣，写完代码后程序跑起来出现了死锁。于是对日志进行分析跟踪，发现在执行以下SQL时出现死锁:

UPDATE linkgoo_message_queue SET state = 3 where state = 2;

很明显在执行上述SQL时，由于某个原因导致表linkgoo_message_queue被锁或者表中相关记录被锁。那么问题来了，Mysql中Update的时候会怎么去加锁呢？

网上查找资料并进行试验后得到如下结论：

1.Mysql行锁除了会锁记录之外，还有可能对索引加锁，这取决于你的SQL写法；

2.UPDATE数据时，若条件中包含主键，那么只锁该主键对应的记录；（注：网上找到的资料还提及：若操作了某个索引字段，比如 SET state=3 ，state为索引字段，那么state这个索引也会被锁上。但通过实验证明，若两条SQL条件中都存在主键，且操作了相同的索引字段，是不会引发资源等待的！如果第一条SQL以主键为条件操作了索引字段，第二条SQL以索引字段为条件进行UPDATE，那么第二条SQL需要等待直到第一条SQL释放锁资源）

3.UPDATE数据时，若条件中不包含主键但含有索引字段，那么Mysql会先对索引加锁，再对受影响的记录加锁。且如果SQL中还对索引字段进行操作，那么还会对该索引字段追加锁。（注意加锁是有顺序的！正是因为这样才导致的死锁）

4.通过实验证明，对索引加锁，是对整个字段加锁，而不是对索引的某个值（比如state=3）加锁。

通过上述的信息，我们再来分析一下这条SQL：

UPDATE linkgoo_message_queue SET state = 3 where state = 2;

首先Mysql会对state这个索引加锁，然后再对state=2的所有记录加锁。由于存在SET state = 3，还会对state这个索引进行加锁，但之前已经加过了，不会再重复加。

OK,我们再回顾一下《单片机原理与嵌入式系统设计》里边（别问我为什么是这本书，我会告诉你我是自动化专业出身的万金油吗？）对死锁的定义：指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。

对于上边的SQL，由于其占用了state索引和相关的数据库记录，倘若要在并发条件下发生死锁，那么若存在有线程先把记录给锁了，再要锁state索引，那么就有可能出现死锁。

重新梳理代码和业务逻辑，在处理完message后，会根据message.id对message的state进行更新。问题到此已经很明显了，执行UPDATE linkgoo_message_queue SET state = 3 where state = 2时，若state索引已经锁了，但还未对记录进行上锁。此时根据message.id 去更新state,会对id=xxx这条记录进行上锁，并等待state索引的锁释放。而第一条SQL进入记录上锁阶段发现记录已经被锁了，进入了死循环发生死锁。

那么上边的问题怎么去解决呢？

在Mysql中UPDATE时不要直接将索引字段放到条件中进行筛选，应该采用SELECT先找出需要修改的数据，再逐条对数据进行UPDATE。注意需要考虑如何让SELECT出来的记录在修改前还是查询时的状态，而不是被其他线程UPDATE。