文章首发于公众号: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字段内容复制出来,由于内容太多,这里只贴出和死锁相关的,如下:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 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(<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支持一下,第一时间获取文章干货。感谢!