2
面试官：请用SQL模拟一个死锁
source link: https://www.cnblogs.com/ibigboy/p/16202718.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
文章首发于公众号：BiggerBoy
有读者说面试被问到怎么用SQL模拟数据库死锁？
这位读者表示对Java中的死锁还是略知一二的，但是突然用SQL写死锁的案例之前还真没遇到过，这个问题没答上来。所以今天就带大家一起来看下怎么用SQL让数据库中产生死锁。
什么是死锁
说到死锁，还是先来复习下什么是死锁吧。
死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
数据库死锁是指两个资源互相等待，如果需要“修改”一条数据，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。锁定(Locking)发生在当一个事务获得对某一资源的“锁”时，这时，其他的事务就不能更改这个资源了，这种机制的存在是为了保证数据一致性。
数据库死锁示例
好了，复习完回到今天的正题。
有如下两个事务：
事务1先执行SQL1，更新id=1的，然后执行SQL2，更新id=2的。
事务2恰恰相反，它先更新id=2的，再更新id=1的。
SQL代码如下：
-- 事务1begin;-- SQL1更新id为1的update user set age = 1 where id = 1;-- SQL2更新id为2的update user set age = 2 where id = 2;commit;
-- 事务2begin;-- 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字段内容复制出来，由于内容太多，这里只贴出和死锁相关的，如下：
------------------------LATEST DETECTED DEADLOCK------------------------2022-04-23 15:47:53 0x10d08*** (1) TRANSACTION:TRANSACTION 202027, ACTIVE 20 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL 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 waitingRecord 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 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL 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 gapRecord 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 waitingRecord 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完毕");}
然后我们进行单元测试，开两个线程，模拟多个用户请求，触发不同的业务操作数据库
@Testpublic 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(<generated>) 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(<generated>) 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
你以为这就完了吗？回答完这个问题，面试官还有一连串的问题：
- 什么是死锁？如何避免？
- 数据库的锁与隔离级别的关系？
- 数据库锁的类型有哪些？
- MySQL中InnoDB引擎的行锁模式及其是如何实现的？
- 什么是数据库的乐观锁和悲观锁，如何实现？
关于以上问题，咱们下期再讲~
如果对你有帮助，可以关注公众号BiggerBoy支持一下，第一时间获取文章干货。感谢！
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK