Oracle死锁问题及解决办法

死锁通常是2个及以上线程共同竞争同一资源而造成的一种互相等待的僵局。

我们看下图所示场景。线程1执行的事务先更新资源1，然后更新资源2。线程2涉及到的事务先更新资源2，然后更新资源1。这种情况下，很容易出现你等我我等你，导致死锁。

我用Oracle数据库来模拟这种场景的死锁。

●service类

如下PayAccountServiceMock类， up方法和up2方法，这2个方法使用了spring事务，逻辑是根据账户id来更新两条账户的金额。不过，两个方法更新两条账户记录的顺序是相反的。我们用后面的testcase很容易就能模拟出Oracle死锁。

package com.xxx.accounting;

import org.springframework.transaction.annotation.Transactional;

@Service
@Slf4j
public class PayAccountServiceMock {
    @Autowired
    private TAccTransService tAccTransService;

    @Transactional
    public void up() throws InterruptedException {
        tAccTransService.updateBalance("89900000426016346075");

        Thread.sleep(RandomUtils.nextInt(100, 300));
        select("89900000426016346075");

        tAccTransService.updateBalance("PF00060");
    }

    @Transactional
    public void up2(TAccTrans at4) throws InterruptedException {
        tAccTransService.updateBalance("PF00060");

        Thread.sleep(550);

        tAccTransService.updateBalance("89900000426016346075");
    }

    @Transactional
    public void select(String id) {

        tAccTransService.selectByPrimaryKey(id);
        try {
            Thread.sleep(1100);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}

●testcase类

如下Junit测试类，使用倒计数门栓（CountDownLatch，就是JUC包下倒计时门栓，个人觉得用“倒计数门栓”感觉更合适~）来保证多线程同时执行，达到并行处理的效果。

package com.xxx.accounting;


@Slf4j
public class PayAccountingServiceTest extends BaseTest {
    @Autowired
    private PayAccountServiceMock payAccountingServiceMock;
    
    @Test
    public void testDeadlock() throws InterruptedException {
        CountDownLatch latch = new CountDownLatch(1);
        ExecutorService pool = Executors.newFixedThreadPool(3);

        // first
        pool.execute(() -> {
                    try {
                        latch.await();
                        log.info("thread begin"); 
                        
                        payAccountingServiceMock.up();
                    } catch (Exception e) {
                        log.error("-----------异常：", e);
                    }
                }
        );
        // second
        pool.execute(() -> {
                    try {
                        latch.await();
                        log.info("thread begin"); 
                        
                        payAccountingServiceMock.up2();
                    } catch (Exception e) {
                        log.error("-----------异常：", e);
                    }
                }
        );
        // third
        pool.execute(() -> {
                    try {
                        latch.await();
                        log.info("thread begin"); 
                        
                        payAccountingServiceMock.select();
                    } catch (Exception e) {
                        log.error("-----------异常：", e);
                    }
                }
        );

        Thread.sleep(100);
        latch.countDown();
        pool.awaitTermination(5, TimeUnit.SECONDS);
    }
}

●运行testcase

接下来，运行testcase，出现“ORA-00060: 等待资源时检测到死锁”。

org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

具体日志如下：

13:50:40,297 [pool_5_thread_1] [com.xxx.accounting.PayAccountingServiceTest:114] thread await
13:50:40,297 [pool_5_thread_3] [com.xxx.accounting.PayAccountingServiceTest:160] thread await
13:50:40,297 [pool_5_thread_2] [com.xxx.accounting.PayAccountingServiceTest:141] thread await
13:50:40,482 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] ==>  Preparing: select * from T_ACC_TRANS where ID = ? 
13:50:40,483 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] ==> Parameters: PF00060(String)
13:50:40,525 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] <==      Total: 1
13:50:40,636 [pool_5_thread_1] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==>  Preparing: update T_ACC_TRANS set ...  where ID = ? ... 
13:50:40,638 [pool_5_thread_1] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==> Parameters: ... , 89900000386316297067(String), ... 
13:50:40,698 [pool_5_thread_1] [com.xxx.dao.TAccTransDAO.updateBalance:145] <==    Updates: 1
13:50:41,658 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==>  Preparing: update T_ACC_TRANS set ...  where ID = ? ... 
13:50:41,660 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==> Parameters: ... , PF00060(String), ... 
13:50:41,668 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] <==    Updates: 1
13:50:45,705 [pool_5_thread_1] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==>  Preparing: update T_ACC_TRANS set ...  where ID = ? ... 
13:50:45,707 [pool_5_thread_1] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==> Parameters: ... , PF00060(String), ... 
13:50:46,680 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==>  Preparing: update T_ACC_TRANS set ...  where ID = ? ... 
13:50:46,681 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==> Parameters: ... , 89900000386316297067(String), ... 
13:50:49,194 [pool_5_thread_1] [org.springframework.beans.factory.xml.XmlBeanDefinitionReader:317] Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
13:50:49,247 [pool_5_thread_1] [org.springframework.jdbc.support.SQLErrorCodesFactory:126] SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
13:50:49,262 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] <==    Updates: 1
13:50:49,272 [pool_5_thread_1] [com.xxx.accounting.PayAccountingServiceTest:121] -----------异常：
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

### The error may involve com.xxx.dao.TAccTransDAO.updateBalance-Inline
### The error occurred while setting parameters
### SQL: update T_ACC_TRANS set CASH_AMT =CASH_AMT + ?,     CASH_FREEZE =CASH_FREEZE+ ?,     MANUAL_FREEZE=MANUAL_FREEZE + ?,     SEQ = SEQ+1,     UPDATE_TIME = sysdate,     mac=MD5(ID||(CASH_AMT+?)||(CASH_FREEZE + ?)|| (MANUAL_FREEZE+ ?)||TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS'))     where ID = ?     and (MAC=MD5(ID||CASH_AMT||CASH_FREEZE||MANUAL_FREEZE||TO_CHAR(UPDATE_TIME,'YYYY-MM-DD HH24:MI:SS')) or MAC IS NULL)     and CASH_AMT + ? >= 0     and CASH_FREEZE + ? >= 0     and MANUAL_FREEZE + ? >= 0     and CASH_AMT >=CASH_FREEZE+?     and STATE in (0, 2) and ACCOUNT_TYPE in(0,1,2)             and (BANK_ID = ' ' or BANK_ID = ?)
### Cause: java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

; SQL []; ORA-00060: 等待资源时检测到死锁
; nested exception is java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
    at com.sun.proxy.$Proxy30.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy38.updateBalance(Unknown Source)
    at com.xxx.accounting.PayAccountingService.up(PayAccountingService.java:669)
    at com.xxx.accounting.PayAccountingService$$FastClassBySpringCGLIB$$7c2d7604.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
    at com.xxx.accounting.PayAccountingService$$EnhancerBySpringCGLIB$$a9c6994a.up(<generated>)
    at com.xxx.accounting.PayAccountingServiceTest.lambda$pTest$3(PayAccountingServiceTest.java:116)
    at com.xxx.accounting.PayAccountingServiceTest$$Lambda$77/1402979793.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-00060: 等待资源时检测到死锁

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198)
    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:497)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy78.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    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:198)
    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:497)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
    ... 21 more
13:50:49,275 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] ==>  Preparing: select * from T_ACC_TRANS where ID = ? 
13:50:49,276 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] ==> Parameters: PF00060(String)
13:50:49,283 [pool_5_thread_3] [com.xxx.dao.TAccTransDAO.selectByPrimaryKey:145] <==      Total: 1
13:50:49,389 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==>  Preparing: update T_ACC_TRANS set ...  where ID = ? ... 
13:50:49,390 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] ==> Parameters: ... , PF00060(String), ... 
13:50:49,396 [pool_5_thread_2] [com.xxx.dao.TAccTransDAO.updateBalance:145] <==    Updates: 1

 死锁解决办法

1. 很显然，调整各事务所执行的资源操作的顺序，让各操作按照相同的顺序执行。

2. 实际情况中，就拿我们的系统来说，系统业务比较复杂，并不像上面service里那样简单明了，一眼就可以看到问题。而是许多业务（充值、付款请求、调账、付款完成）都操作原子性的动账方法，这时，梳理起来也是比较耗费时间和精力的。此时呢，我们采用了利用redis分布式锁来保证线程（进程）同步。具体来说，就是同时只有一个线程来更改同一账户的数据记录，此时其他线程将等待，直到分布式锁得到释放。