数据库死锁问题排查

表结构

CTRATE TABLE base_privilege(
id,
user_id,
card_number,
state
)

已有索引

聚簇索引：主键id
非聚簇索引：(user_id,card_number(20))

获取死锁日志

show engine innodb status

获取日志得到导致死锁的两条sql:

update base_privilege set status = 2 where user_id = 111 and card_number = '111111111111111111111';

update base_privilege set status = 2 where user_id = 111 and card_number = '111111111111111111112';

定位代码

定位到代码发现代码中锁同一个事务执行了两条update语句，并且操作的还是同一条记录，第一个update走主键索引，第二个update走联合索引。

死锁分析

在MYSQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条语句操作了主键索引那么就会锁定这条主键索引，如果一条语句操作了非主键索引，Mysql会先锁定该非主键索引，再锁定相关的主键索引。

（1）事务1执行第一条update语句锁定第一条主键id = 1
（2）事务2执行第一条update语句锁定第一条主键id = 2
（3）事务1执行第二条update语句的时候因为和之前操作的是同一条记录，其实第二条用的是非主键索引，锁完非主键索引之后它去寻找对应的主键索引，结果通过索引找到了两条记录，它就等待获取id= 2的锁
（4）事务2执行第二条update语句的时候，它和事务1索引值是一样的（这两条记录的卡号恰巧前20位锁相同的），所以它又去等待获取事务1的非主键索引，造成死锁。

解决方式

• 将索引中卡号字段的长度扩大
• 或者update都通过主键id来进行
• 在同一事务中，避免对同一条记录进行多次update操作