面试官：请用SQL模拟一个死锁

文章首发于公众号：BiggerBoy

有读者说面试被问到怎么用SQL模拟数据库死锁？
这位读者表示对Java中的死锁还是略知一二的，但是突然用SQL写死锁的案例之前还真没遇到过，这个问题没答上来。所以今天就带大家一起来看下怎么用SQL让数据库中产生死锁。

什么是死锁

说到死锁，我们来回顾一下什么是死锁。

死锁是指在执行过程中，由于两个或多个进程之间竞争资源或通信而导致的阻塞现象，如果没有外力，就无法向前推进。此时，就会说系统处于死锁状态或者系统出现了死锁，而这些总是在等待对方的进程被称为死锁进程。

数据库死锁是指两个资源互相等待，如果需要”修改”一条数据，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。锁定(Locking)发生在当一个事务获得对某一资源的”锁”时，这时，其他的事务就不能更改这个资源了，这种机制的存在是为了保证数据一致性。

数据库死锁示例

好，我们复习之后再回到今天的话题。

有如下两个事务：
事务1先执行SQL1，更新id=1的，然后执行SQL2，更新id=2的。
事务2恰恰相反，它先更新id=2的，再更新id=1的。

SQL代码如下：

-- 事务1
begin;
-- SQL1更新id为1的
update user set age = 1 where id = 1;
-- SQL2更新id为2的
update user set age = 2 where id = 2;
commit;

-- 事务2
begin;
-- SQL1更新id为2的
update user set age = 3 where id = 2;
-- SQL2更新id为1的
update user set age = 4 where id = 1;
commit;

我们怎么手动操作模拟一下呢？

先执行事务1的SQL1

再执行事务2的SQL1

此时不会有什么问题。
接着，我们执行事务1的SQL2。此时这条SQL没有执行成功，一直在等待，如下如所示，”查询时间”一直在增加

然后执行事务2的SQL2，事务2报错，”Deadlock found when trying to get lock; try restarting transaction”，即数据库发现死锁了。

此时执行事务1的commit操作，再查看数据，id为1和2的age字段分别被修改为了1和2，即事务1执行成功。事务2即使再执行commit数据也不会发生变化，因为事务2报错终止操作被回滚了。

怎么造成死锁的呢？

让我们画一幅图来了解死锁是如何引起的。(事务处理1和事务处理2的向下箭头表示时间线)

当事务1和事务2都开始执行，如果都执行到第一个SQL时，是不会产生死锁的，因为操作的是不同的行，此时事务1对id=1的这条记录加了独占锁，事务2对id=2的这条记录加了独占锁，由于事务都没提交，所以这两个独占锁都没有释放。
然后两个事务都继续往下执行，我们手动控制了事务1先执行它的SQL2，即更新id=2的这条记录，由于id=2的这条记录被事务2锁着，所以这条SQL语句会被阻塞，一直等待，也就是上述图中显示的”查询时间”。
接着事务2执行它的SQL2，即更新id=1的这条记录，又因为事务1锁着id=1的这条记录，所以，此时形成了相互等待对方持有的锁的局面，即发生了死锁。但，数据库不会任由这两个事务一直等待下去，所以事务2执行SQL2时提示死锁，”Deadlock found when trying to get lock; try restarting transaction”，事务1不受影响，commit之后事务1执行成功。
此时可以通过看数据库状态，找到死锁相关的信息
SHOW ENGINE INNODB STATUS;

将status字段内容复制出来，由于内容太多，这里只贴出和死锁相关的，如下：

2022-04-23 15:47:53 0x10d08
*** (1) TRANSACTION:
TRANSACTION 202027, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 68972, query id 398 localhost ::1 root updating
-- SQL2更新id为2的
update user set age = 2 where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202027 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003152c; asc      ,;;
 2: len 7; hex 4000000132303f; asc @   20?;;
 3: len 16; hex 77616c6b696e67383634353532303835; asc walking864552085;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 202028, ACTIVE 12 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 68872, query id 402 localhost ::1 root updating
-- SQL2更新id为1的
update user set age = 4 where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202028 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000003152c; asc      ,;;
 2: len 7; hex 4000000132303f; asc @   20?;;
 3: len 16; hex 77616c6b696e67383634353532303835; asc walking864552085;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table walking_mybatis.user trx id 202028 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000003152b; asc      +;;
 2: len 7; hex 3f000001c31070; asc ?     p;;
 3: len 16; hex 77616c6b696e67313533323639323335; asc walking153269235;;
 4: len 1; hex 30; asc 0;;
 5: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

从上面的日志中我们可以找到发生死锁的SQL和线程ID等相关信息。
通过以上的分析大家知道怎么模拟数据库中的死锁了吧。其实和Java多线程的死锁道理都是相通的，无非就是满足四个必要条件，即：
1、互斥条件：一个资源每次只能被一个进程使用；
2、请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；
3、不剥夺条件：进程已获得的资源，在未使用完之前，不能强行剥夺；
4、循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

Java应用中数据库死锁的表现

通过Java操作数据库，模拟在实际应用中的数据库死锁。
首先是第一个业务方法，其实和上面用SQL模拟死锁的思路是一样的，这里的业务也很简单，先更新id为1的，再更新id为2的

@Transactional(rollbackFor = Exception.class)
public void updateById() {
    User record1 = new User();
    record1.setId(1);
    record1.setAge(1);
    userMapper.updateByPrimaryKey(record1);
    System.out.println("事务1 执行第一条SQL完毕");

    User record2 = new User();
    record2.setId(2);
    record2.setAge(2);
    userMapper.updateByPrimaryKey(record1);
    System.out.println("事务1 执行第二条SQL完毕");
}

然后第二个业务方法，同样，模拟上面的SQL死锁，先更新id为2的，然后为了使这个先后顺序更加明显，效果更突出，我们让第二个业务方法休眠30毫秒，再更新id为1的

@Transactional(rollbackFor = Exception.class)
public void updateById1() {
    User record1 = new User();
    record1.setId(2);
    record1.setAge(3);
    userMapper.updateByPrimaryKeySelective(record1);
    System.out.println("事务2 执行第一条SQL完毕");
    //休眠，保证先后执行顺序
    try {
        Thread.sleep(30);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    User record2 = new User();
    record2.setId(1);
    record2.setAge(4);
    userMapper.updateByPrimaryKeySelective(record2);
    System.out.println("事务2 执行第二条SQL完毕");
}

然后进行单元测试，打开两个线程，模拟多个用户请求，触发不同的业务操作数据库。

@Test
public void testDeadLock() {
    new Thread(() -> {
      userService.updateById();
      System.out.println("事务1 执行完毕");
    }).start();

    new Thread(() -> {
      userService.updateById1();
      System.out.println("事务2 执行完毕");
    }).start();
    Thread.sleep(2000);//休眠，等待两个线程，确保都能执行
}

运行上述代码，执行结果如下。通过日志，我们发现事务1执行顺利，事务2抛出异常。

Exception in thread "Thread-5" org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction\

详细错误堆栈如下：

事务1 执行第一条SQL完毕
事务2 执行第一条SQL完毕
事务1 执行第二条SQL完毕
事务1 执行完毕
Exception in thread "Thread-5" org.springframework.dao.DeadlockLoserDataAccessException:
### 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 file [E:\idea_project\springboot-mybatis-demo\target\classes\mapper\UserMapper.xml]
### The error may involve com.wenbei.mapper.UserMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update user      SET age = ?      where id = ?
### 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
  at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
  at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
  at com.sun.proxy.$Proxy81.update(Unknown Source)
  at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
  at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)
  at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
  at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
  at com.sun.proxy.$Proxy82.updateByPrimaryKeySelective(Unknown Source)
  at com.wenbei.service.UserService.updateById1(UserService.java:50)
  at com.wenbei.service.UserService$$FastClassBySpringCGLIB$$de54ea56.invoke()
  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
  at com.wenbei.service.UserService$$EnhancerBySpringCGLIB$$4badf6b6.updateById1()
  at com.wenbei.AppTests.lambda$testDeadLock$1(AppTests.java:54)
  at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
  at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
  at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
  at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
  at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
  at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
  at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
  at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
  at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
  at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
  at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
  ... 20 more

你觉得一切都结束了吗？在回答完这个问题后，面试官有一系列的问题：

• 什么是死锁？如何避免？
• 数据库锁与隔离级别有什么关系？
  
  [En]

  what is the relationship between database locks and isolation levels?*

• 数据库锁的类型有哪些？
• MySQL中InnoDB引擎的行锁模式及其是如何实现的？
• 数据库的乐观和悲观锁是什么，如何实施？
  
  [En]
  what are the optimistic and pessimistic locks of the database, and how can they be implemented?

关于上述问题，我们将在下一期中谈到。

如果对你有帮助，可以关注公众号BiggerBoy支持一下，第一时间获取文章干货。感谢！

Original: https://www.cnblogs.com/ibigboy/p/16202718.html
Author: 问北
Title: 面试官：请用SQL模拟一个死锁