¡¾ÏîĿʵս¾­Ñé¡¿Ò»Îĸ㶮ÔÆÊý¾Ý¿âPostgreSQLÓëMySQLʵ¼ù°¸Àý

CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  active TINYINT(1) NOT NULL
);

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  quantity TINYINT(4) NOT NULL
);

org.springframework.dao.CannotAcquireLockException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve xxxMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO xxx
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

@timestamp September 1st
# @version  1
t LOG_DATEFORMAT_PATTERN    yyyy-MM-dd HH:mm:ss.SSS
t LOG_LEVEL_PATTERN %5p
t _id   VMaG
t _index    applog-2021.09.01
# _score    1
t _type doc
t appindex  applog
t appname   app
t host  10.0.74.157
t level ERROR
# level_value   40,000
t logger_name   ExceptionLogCollector
t message   δ֪Òì³£[500] => Transaction rolled back because it has been marked as rollback-only
# port  10,792
t stack_trace   org.springframework.transaction.UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:873) ~[spring-tx-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:710) ~[spring-tx-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533) ~[spring-tx-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304) ~[spring-tx-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.1.4.RELEASE.jar!/:5.1.4.RELEASE]
spring-tx-5.1.4.RELEASE.jar-
org.springframework.transaction.interceptor.TransactionInterceptor#ÊÂÎñÀ¹½ØÆ÷
avatar

@Nullable
protected Object invokeWithinTransaction(Method method, @Nullable Class<?> targetClass,
            final InvocationCallback invocation) throws Throwable
·½·¨ÎªprotectedµÄ£¬¸ù¾ÝÔ´´úÂë×¢ÊͽâÎö

@Override
    public final void commit(TransactionStatus status) throws TransactionException {
        if (status.isCompleted()) {
            throw new IllegalTransactionStateException(
                    "Transaction is already completed - do not call commit or rollback more than once per transaction");
        }

        DefaultTransactionStatus defStatus = (DefaultTransactionStatus) status;
        if (defStatus.isLocalRollbackOnly()) {
            if (defStatus.isDebug()) {
                logger.debug("Transactional code has requested rollback");
            }
            processRollback(defStatus, false);
            return;
        }

        if (!shouldCommitOnGlobalRollbackOnly() && defStatus.isGlobalRollbackOnly()) {
            if (defStatus.isDebug()) {
                logger.debug("Global transaction is marked as rollback-only but transactional code requested commit");
            }
            ÈÕÖ¾×·×ÙµÄ710ÐÐ-----¼Çס´Ë´¦´«true
            processRollback(defStatus, true);
            return;
        }

        processCommit(defStatus);
    }


private void processRollback(DefaultTransactionStatus status, boolean unexpected) {
        try {
            Èë²ÎΪtrue
            boolean unexpectedRollback = unexpected;

            try {
                triggerBeforeCompletion(status);

                if (status.hasSavepoint()) {
                    if (status.isDebug()) {
                        logger.debug("Rolling back transaction to savepoint");
                    }
                    status.rollbackToHeldSavepoint();
                }
                else if (status.isNewTransaction()) {
                    if (status.isDebug()) {
                        logger.debug("Initiating transaction rollback");
                    }
                    doRollback(status);
                }
                else {
                    // Participating in larger transaction
                    if (status.hasTransaction()) {
                        if (status.isLocalRollbackOnly() || isGlobalRollbackOnParticipationFailure()) {
                            if (status.isDebug()) {
                                logger.debug("Participating transaction failed - marking existing transaction as rollback-only");
                            }
                            doSetRollbackOnly(status);
                        }
                        else {
                            if (status.isDebug()) {
                                logger.debug("Participating transaction failed - letting transaction originator decide on rollback");
                            }
                        }
                    }
                    else {
                        logger.debug("Should roll back transaction but cannot - no transaction available");
                    }
                    // Unexpected rollback only matters here if we're asked to fail early
                    if (!isFailEarlyOnGlobalRollbackOnly()) {
                        unexpectedRollback = false;
                    }
                }
            }
            catch (RuntimeException | Error ex) {
                triggerAfterCompletion(status, TransactionSynchronization.STATUS_UNKNOWN);
                throw ex;
            }

            triggerAfterCompletion(status, TransactionSynchronization.STATUS_ROLLED_BACK);
            ÈÕÖ¾×·×ÙµÄ873ÐÐ  Å׳öÒì³£
            // Raise UnexpectedRollbackException if we had a global rollback-only marker
            if (unexpectedRollback) {
                throw new UnexpectedRollbackException(
                        "Transaction rolled back because it has been marked as rollback-only");
            }
        }
        finally {
            cleanupAfterCompletion(status);
        }
    }

try

    {
        methodA()
    }catch

    {
    }

    @Transactional(rollbackFor = Exception.class)
    public methodA() {
        methodB()
    }

    @Transactional(rollbackFor = Exception.class)
    public methodB() {
        try {
            methodC()
        } catch {
        }
    }

    methodC() {
        µ±C·½·¨Å׳öÒ쳣ʱ
    }

@Slf4j
@Service
public class DemoService {

	@Autowired
	private Test1Mapper test1Mapper;
	
	@Autowired
	private TestMapper testMapper;
	
	@Autowired
	private InvalidTransactionService invalidTransactionService;
	
	@Autowired
	private ExecutorService executorService;
	
	@Autowired
	private DemoService _self;
	
	@Autowired
	private ValidTransactionService validTransactionService;
	
	@Autowired
	private RequireNewTransactionService requireNewTransactionService;
	
	/********************************************************
	 * ÊÂÎñ²»ÉúЧ³¡¾°1
	 * Ï൱ÓÚµ÷ÓÃthisµ÷Óã¬Ã»ÓвúÉú´úÀí¶ÔÏóµ÷Ó㬽â¾ö·½·¨£¬×Ô¼º°Ñ×Ô¼º×¢ÈëÒÔºóµ÷ÓÃ
	 ********************************************************/
	public void demo1() {

		invalidTransaction();
		
		//TODO other logic code here
	}
	
	@Transactional
	public void invalidTransaction() {
		TestDO test = new TestDO();
		test.setName("11111");
		testMapper.insert(test);
		
		Test1DO test1 = new Test1DO();
		test1.setCust("2222");
		test1Mapper.insert(test1);
		throw new WMSException(ErrorCodeEnum.BD10001001.code(),"ÊÂÎñ²»ÉúЧ³¡¾°1");
	}
	
	/********************************************************
	 * ÊÂÎñ²»ÉúЧ³¡¾°¶þ
	 * Õâ¸öÀý×ÓµÄÄ¿µÄÊÇΪÁËcatchסÄÚ²ãÊÂÎñµÄÒì³££¬ÈÃÍâ²ãÊÂÎñ³É¹¦£¬µ«ÊÇʵ¼ÊÉÏûÓÐÄÚÍâ²ãÊÂÎñ¶¼»Ø¹öÁË
	 *
	 * ÕâÀïAºÍB¶¼ÊÜÊÂÎñ¿ØÖÆ£¬²¢ÇÒÊÇ´¦ÓÚͬһ¸öÊÂÎñµÄ¡£
	 * Aµ÷ÓÃB£¬AÖÐ×¥ÁËBµÄÒì³££¬µ±B·¢ÉúÒì³£µÄʱºò£¬BµÄ²Ù×÷Ó¦¸Ã»Ø¹ö£¬µ«ÊÇA³ÔÁËÒì³££¬A·½·¨ÖÐûÓвúÉúÒì³££¬ËùÒÔAµÄ²Ù×÷ÓÖÓ¦¸ÃÌá½»£¬¶þÕßÊÇÏ໥ì¶ÜµÄ¡£
	 * springµÄÊÂÎñ¹ØÁªÀ¹½ØÆ÷ÔÚ×¥µ½BµÄÒì³£ºó¾Í»á±ê¼Çrollback-onlyΪtrue£¬µ±AÖ´ÐÐÍê×¼±¸Ìá½»ºó£¬·¢ÏÖrollback-onlyΪtrue£¬Ò²»á»Ø¹ö£¬²¢Å׳öÒì³£¸æËßµ÷ÓÃÕß¡£
	 *
	 * ±¨´íÌáʾ£ºTransaction rolled back because it has been marked as rollback-only
	 *
	 * Èç¹ûÏëʹÍâ²ãÊÂÎñÉúЧ¿ÉÒÔ°ÑÄÚ²ãÊÂÎñ´«²¥ÌØÐÔÐÞ¸ÄΪ£º@Transactional(propagation = Propagation.REQUIRES_NEW)
	 * 
	 ********************************************************/
	@Transactional
	public void demo2() {
		TestDO test = new TestDO();
		test.setName("3333");
		testMapper.insert(test);
		try {
			invalidTransactionService.transaction();
		}catch (Exception e) {
			log.error("·þÎñÒì³££¬Òì³£±»²¶»ñ", e);
		}
	}
	
	/********************************************************
	 * ÊÂÎñ²»ÉúЧ³¡¾°Èý
	 *
	 * ÒòΪ¿ªÁËÏß³ÌÒì²½Ö´ÐУ¬µÈÓÚÊÂÎñÍêÈ«ÔÚÁ½¸öÏß³ÌÄÚ£¬²»ÔÚÒ»¸öỊ̈߳¬ËùÒÔ¼´Ê¹Å×´í£¬Ò²ÊÇÒ»¸öÉúЧһ¸ö²»ÉúЧ£¬
	 * ÊÂÎñûÓлعö
	 * 
	 ********************************************************/
	@Transactional
	public void demo3() {
		TestDO test = new TestDO();
		test.setName("5555");
		testMapper.insert(test);
		
		executorService.execute(() -> {
			Test1DO test1 = new Test1DO();
			test1.setCust("6666");
			test1Mapper.insert(test1);
		});
		
		throw new WMSException(ErrorCodeEnum.BD10001001.code(),"ÊÂÎñ²»ÉúЧ³¡¾°3");
	}
	
	/********************************************************
	 * ÊÂÎñ²»ÉúЧ³¡¾°°Ë
	 * SpringĬÈÏÇé¿öÏ»á¶ÔÔËÐÐÆÚÀýÍâ(RunTimeException)½øÐÐÊÂÎñ»Ø¹ö¡£Õâ¸öÀýÍâÊÇunchecked£¬Èç¹ûÓöµ½checkedÒâÍâ¾Í²»»Ø¹ö¡£
	 * Exception°üº¬RuntimeExceptionÌåϵºÍÆäËû·ÇRuntimeExceptionµÄÌåϵ
	 * ErrorºÍRuntimeException¼°Æä×ÓÀà³ÉΪδ¼ì²éÒì³££¨unchecked£©£¬ÆäËüÒì³£³ÉΪÒѼì²éÒì³££¨checked£©¡£
	 * springÉùÃ÷ʽÊÂÎñ¹ÜÀíĬÈ϶ԷǼì²éÐÍÒì³£ºÍÔËÐÐʱÒì³£½øÐÐÊÂÎñ»Ø¹ö£¬¶ø¶Ô¼ì²éÐÍÒì³£Ôò²»½øÐлعö²Ù×÷
	 *
	 *
	 *ÄÇôʲôÊǼì²éÐÍÒ쳣ʲôÓÖÊǷǼì²éÐÍÒì³£ÄØ£¿
	 * 1.¼Ì³Ð×Ôruntimeexception»òerrorµÄÊǷǼì²éÐÍÒì³££¬¶ø¼Ì³Ð×ÔexceptionµÄÔòÊǼì²éÐÍÒì³££¨µ±È»£¬runtimeexception±¾ÉíÒ²ÊÇexceptionµÄ×ÓÀࣩ¡£
	 * 2.¶Ô·Ç¼ì²éÐÍÀàÒì³£¿ÉÒÔ²»Óò¶»ñ£¬¶ø¼ì²éÐÍÒì³£Ôò±ØÐëÓÃtryÓï¾ä¿é½øÐд¦Àí»òÕß°ÑÒì³£½»¸øÉϼ¶·½·¨´¦Àí×ÜÖ®¾ÍÊDZØÐëд´úÂë´¦ÀíËü¡£ËùÒÔ±ØÐëÔÚservice²¶»ñÒì³££¬È»ºóÔÙ´ÎÅ׳ö£¬ÕâÑùÊÂÎñ·½²ÅÆðЧ¡£
	 *
	 * @throws IOException 
	 * 
	 ********************************************************/
	@Transactional
	public void demo8() throws IOException {
		TestDO test = new TestDO();
		test.setName("11111");
		testMapper.insert(test);
		
		Test1DO test1 = new Test1DO();
		test1.setCust("2222");
		test1Mapper.insert(test1);
		throw new IOException("ÊÂÎñ²»ÉúЧ³¡¾°8");
	}
	
	/********************************************************
	 * ÊÂÎñ²»ÉúЧ³¡¾°¾Å
	 * @throws IOException 
	 * 
	 ********************************************************/
	
	public void demo9(){
		invalidTransaction2();
	}
	
	@Transactional
	private void invalidTransaction2() {
		TestDO test = new TestDO();
		test.setName("11111");
		testMapper.insert(test);
		
		Test1DO test1 = new Test1DO();
		test1.setCust("2222");
		test1Mapper.insert(test1);
		throw new WMSException("ÊÂÎñ²»ÉúЧ³¡¾°9");
	}
	
	/********************************************************
	 * ÊÂÎñÉúЧ³¡¾°1
	 * 
	 ********************************************************/
	public void demo4() {
		
		_self.invalidTransaction();
		
		//TODO other logic code here
	}
	
	/********************************************************
	 * ÊÂÎñÉúЧ³¡¾°¶þ
	 *
	 * ÒòΪÄÚ²ãûÓÐÊÂÎñ¿ØÖÆ£¬ËùÒÔÄڲ㱨´í£¬²»»á»ì»Ø¹ö£¬Í¬ÑùÍâ²ãcatchס£¬ËùÒÔÍâ²ãÒµÎñ³É¹¦
	 ********************************************************/
	@Transactional
	public void demo5() {
		TestDO test = new TestDO();
		test.setName("7777");
		testMapper.insert(test);
		
		try {
			validTransactionService.transaction();
		}catch (Exception e) {
			log.error("·þÎñÒì³££¬Òì³£±»²¶»ñ", e);
		}
	}
	
	/********************************************************
	 * ÊÂÎñÉúЧ³¡¾°Èý
	 *
	 *ÄÚ²ãÊÂÎñÅäÖõÄÊÇREQUIRES_NEW£¬±íʾ×Ô¼ºÓÃ×Ô¼ºµÄ£¬²»ºÍÍâ²ãÓÐÇ£Á¬£¬ÄÚ²ãÈç¹û±¨´í£¬ÊÂÎñ»á»Ø¹ö
	 * Íâ²ãÈç¹ûcatchסÁË£¬¾Í¿ÉÒÔÕý³£Ö´ÐУ¬Íâ²ãÉúЧ£¬ÄÚ²ã»Ø¹ö
	 ********************************************************/
	@Transactional
	public void demo6() {
		TestDO test = new TestDO();
		test.setName("9999");
		testMapper.insert(test);
		
		try {
			requireNewTransactionService.transactionWithException();
		}catch (Exception e) {
			log.error("·þÎñÒì³££¬Òì³£±»²¶»ñ", e);
		}
	}
	
	/********************************************************
	 * ¶ÀÁ¢ÊÂÎñ
	 * ÄÚÍâ²ãÊÂÎñ¶ÀÁ¢£¬ÄÚ²ã²Ù×÷䱨´í£¬ÊÂÎñÕý³£Ö´ÐУ¬Íâ²ãÓÐ´í£¬ÊÂÎñ»Ø¹ö¡£
	 ********************************************************/
	@Transactional
	public void demo7() {
		TestDO test = new TestDO();
		test.setName("9999");
		testMapper.insert(test);
		
		requireNewTransactionService.transaction();
		throw new WMSException(ErrorCodeEnum.BD10001001.code(),"¶ÀÁ¢ÊÂÎñ");
	}
	
}