面試官：請用SQL模擬一個死鎖

問北發表於2022-04-28
面試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
image

再執行事務2的SQL1
image

此時不會有什麼問題。
接著，我們執行事務1的SQL2。此時這條SQL沒有執行成功，一直在等待，如下如所示，“查詢時間”一直在增加
image

然後執行事務2的SQL2，事務2報錯，“Deadlock found when trying to get lock; try restarting transaction”，即資料庫發現死鎖了。
image

此時執行事務1的commit操作，再檢視資料，id為1和2的age欄位分別被修改為了1和2，即事務1執行成功。事務2即使再執行commit資料也不會發生變化，因為事務2報錯終止操作被回滾了。
image

怎麼造成死鎖的呢？

下面給大家畫個圖，理解一下怎麼造成死鎖的。（事務1、事務2向下的箭頭表示時間線）
image

當事務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;
image

將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\

image

詳細錯誤堆疊如下：

事務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支援一下，第一時間獲取文章乾貨。感謝！

相關文章