背景

最近在做聊天室的应用，在用户离开房间时会到数据库里更新离开的时间，sql 语句很简单

  update leave_log set leave_time=?, leave_type=?
  where room_id=? and uid=?

房主关闭房间时，该语句为批量执行，如下

  update leave_log set leave_time=?, leave_type=?
  where room_id=? and uid in (?, ?, ?, ...)

奇怪的是，这个 sql 每天都有那么个别的死锁，日志如下

exception from queueThreadpool-23, errmsg=
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in class path resource [mapper/LeaveLog.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update leave_log set leave_time=?, leave_type=? where room_id=? and uid=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

我的困惑

我对死锁的理解，是 2 个或者多个事务，各自占有一项或多项对方等待的资源，这样就都进入了一种动弹不得的状态，死锁达成
可是我这个 sql， 只更新一条记录，是怎么占有了别人的资源的？

间隙锁

经过研究和学习，我认识到我的 sql 语句应该是锁定了不止一条记录。

首先看下 leave_log 这张表，room_id 和 uid 上都建了索引，但是都不是唯一索引，只有 room_id+uid 才是唯一的，这个很容易理解，一个房间可以有多个用户，而一个用户也可以出入多个房间

当更新时，无论是使用 room_id 上的索引，还是 uid 上的索引，都没有办法唯一确定一条记录，这样 mysql 会对多条记录进行加锁

• 如果使用 room_id 索引，那么会锁住所有这个 room 里的 uid 的记录
• 如果使用 uid 索引，那么会锁住这个 uid 的所有 room 的记录

这样解释好像就挺有道理的，由于我的 sql 里 room_id 这个条件靠前，所以最终使用的是 room_id 上的索引，虽然只更新一条记录，但是实际上却锁住了 room 里所有的 uid
如果有多个用户同时执行这个 sql，那么就可能大家都锁住了一部分 uid，等另一部分 uid 释放的场景，造成死锁

解决办法

我用 room_id+uid 建了个联合索引，死锁再也没有发生过了