Exception Information
Ccom.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
Reasons for the problem
When optimistic locks modify data, the data version number has been modified, causing the modification to fail. When retrying the modification, each time the data read from the database is not the latest version of the database, causing an infinite number of retries until the thing exits automatically after a timeout
Use the CAS mechanism to update a record, query a data from the database, find out that the data version number is 1, when modifying the data, use the data version number as one of the modification conditions, update `table_Name` set version=`2`where version=`1`, if the modification fails, the data has been updated, the data has been re-read, and the data has been retried. In Mysql database, after opening a thing, the value of multiple queries in the current thing will be the same, that is, the modification fails and the data is re-read.The resulting data version number is the version number of the first read in the transaction (i.e., repeatable reads in the isolation level of the database transaction, avoiding dirty reads (multiple inconsistent reads). Similar to opening a transaction, each read copies the queried data into the transaction space, and the actual data in the table is not read when the current transaction reads the database.Instead, read the data from the object space. For this reason, the version number will never keep up with the new one, so the modification will always fail.
Problem code representation
`[@Override](https://my.oschina.net/u/1162528)` `@Transactional(rollbackFor = Exception.**class**) // Start Things` `**public** Tuple2<TradeVO, WalletVO> earn(EarnDTO earnDTO) {` `**return** retryEarn(earnDTO);// Reind Retry Inside Things` `}` `// Retry Mechanism` `**private** Tuple2<TradeVO, WalletVO> retryEarn(EarnDTO earnDTO) {` `Tuple2<TradeVO, WalletVO> res = self. realEarn(earnDTO);` `**while** (Objects._isNull_(res)) { // Retry if Modification Fails` `res = realEarn(earnDTO);` `}` `**return** res;` `}` `// Actual modifications` `**private** Tuple2<TradeVO, WalletVO> realEarn(EarnDTO earnDTO) {` `// Question/problem` `// This line of things always reads data in the current thing space` `// CAS version number is never updated under retry mechanism, so there will be unlimited updates until timeout or lock wait timeout` `Wallet wallet = **baseMapper**.selectOne(QueryUtils._eq_(Wallet::getUserId, userId))` `(update(wallet)){` `return new Tuple2(); // Modification Successful Return Modification Information` `}` `**return** **null**; // Modification Failed Return null` `}`
Solution
Solution ideas
Now that you've found the reason, it's good to start with the reason so that each time you query, the version number of the data can be updated
Try again outside of the box
Try again outside the earn() method, not inside it, and start a new thing each time you try again, so there will be no problem with the data version number being unable to keep up with the new one
`[@Override](https://my.oschina.net/u/1162528)` `@Transactional(rollbackFor = Exception.**class**) // Start Things` `**public** Tuple2<TradeVO, WalletVO> earn(EarnDTO earnDTO) {` `**return** realEarn(earnDTO);` `}` `// Actual modifications` `**private** Tuple2<TradeVO, WalletVO> realEarn(EarnDTO earnDTO) {` `// Question/problem` `// This line of things always reads data in the current thing space` `// CAS version number is never updated under retry mechanism, so there will be unlimited updates until timeout or lock wait timeout` `Wallet wallet = **baseMapper**.selectOne(QueryUtils._eq_(Wallet::getUserId, userId))` `(update(wallet)){` `return new Tuple2(); // Modification Successful Return Modification Information` `}` `**return** **null**; // Modification Failed Return null` `}` `// Retry Mechanism` `// Retry Outside of Things` `**public** Tuple2<TradeVO, WalletVO> retryEarn(EarnDTO earnDTO) {` `Tuple2<TradeVO, WalletVO> res = earn(earnDTO)` `**while** (Objects._isNull_(res)) { // Retry if Modification Fails` `res = earn(earnDTO);` `}` `**return** res;` `}`
Isolate query SQL statements from things
Propagation = Propagation. **REQUIRES_using the @Transaction event propagation attributeNEW** isolates the query SQL statements so that each time you query, you get the latest data version number, so you don't have to retry indefinitely
It is important to note that calls made within the code using this.method will not work because data transactions are woven into AOP and Spring proxy objects are required to make **REQUIRES_NEW** property valid
`@Override` `@Transactional(rollbackFor = Exception.**class)**` `**public** Tuple2<TradeVO, WalletVO> earn(EarnDTO earnDTO) {` `**return** retryEarn(earnDTO);` `}` `**private** Tuple2<TradeVO, WalletVO> retryEarn(EarnDTO earnDTO) {` `Tuple2<TradeVO, WalletVO> res = self. realEarn(earnDTO);` `**while** (Objects._isNull_(res)) {` `res = realEarn(earnDTO);` `}` `**return** res;` `}` `**private** Tuple2<TradeVO, WalletVO> realEarn(EarnDTO earnDTO) {` `// Note that this is self. not this. ` `Wallet wallet = self.getWallet();` `(update(wallet)){` `return new Tuple2();` `}` `**return** **null**;` `}` `// Open a new standalone thing to read data and avoid database hallucinations` `@Override` `@Transactional(readOnly = **true**, propagation = Propagation.**_REQUIRES_NEW_**)` `**public** Wallet getWallet(String userId) {` `**return** **baseMapper**.selectOne(QueryUtils._eq_(Wallet::getUserId, userId));` `}`