文章首发于公众号：BiggerBoy
有读者说面试被问到怎么用SQL模拟数据库死锁？
这位读者表示对Java中的死锁还是略知一二的，但是突然用SQL写死锁的案例之前还真没遇到过，这个问题没答上来。所以今天就带大家一起来看下怎么用SQL让数据库中产生死锁。
什么是死锁
说到死锁，我们来回顾一下什么是死锁。
[En]
Speaking of deadlocks, let’s review what deadlocks are.
死锁是指在执行过程中，由于两个或多个进程之间竞争资源或通信而导致的阻塞现象，如果没有外力，就无法向前推进。此时，就会说系统处于死锁状态或者系统出现了死锁，而这些总是在等待对方的进程被称为死锁进程。
[En]
Deadlock refers to a blocking phenomenon caused by competition for resources or communication between two or more processes in the process of execution, which will not be able to move forward without external force. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes.
数据库死锁是指两个资源互相等待，如果需要”修改”一条数据，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。锁定(Locking)发生在当一个事务获得对某一资源的”锁”时，这时，其他的事务就不能更改这个资源了，这种机制的存在是为了保证数据一致性。
数据库死锁示例
好，我们复习之后再回到今天的话题。
[En]
All right, let’s get back to today’s topic after review.
有如下两个事务：
事务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的向下箭头表示时间线)
[En]
Let’s draw a picture to understand how deadlocks are caused. (the downward arrows for transaction 1 and transaction 2 indicate the timeline)
当事务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完毕");
}
然后进行单元测试，打开两个线程，模拟多个用户请求，触发不同的业务操作数据库。
[En]
Then we do the unit test, open two threads, simulate multiple user requests, and trigger different business operation databases.
@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抛出异常。
[En]
Run the above code, and the execution result is as follows. Through the log, we find that transaction 1 executes smoothly and transaction 2 throws an exception.
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]
You think it’s over? After answering this question, the interviewer has a series of questions:
- 什么是死锁？如何避免？
- 数据库锁与隔离级别有什么关系？
[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?
关于上述问题，我们将在下一期中谈到。
[En]
With regard to the above questions, we will talk about them in the next issue.
如果对你有帮助，可以关注公众号BiggerBoy支持一下，第一时间获取文章干货。感谢！
Original: https://www.cnblogs.com/ibigboy/p/16202718.html
Author: 问北
Title: 面试官：请用SQL模拟一个死锁
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505446/
转载文章受原作者版权保护。转载请注明原作者出处！