Lock wait timeout exceeded; try restarting transaction-Mysql [How to Solve]

1. Origin of the problem

Now I’m working as the background of a small program, written in Java and MySQL used in the database. I’ve been doing well in debugging before. Today, when I was debugging, I suddenly reported an error:

### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; Try restarting transaction
#### the error may involve defaultparametermap
### the error occurred while setting parameters
### SQL: update table name set update_time = now(), pet_state = ?, first_ interaction_time = now(), stock_count = stock_count + 1, update_name = ? Where pet_id = ?
### 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

Because the business operation is complex, it is just one of the operations, including adding, querying and modifying. In short, there are a lot of operations.

After writing the code, it is also the first time for me to test. How can I suddenly report this error? The general meaning is: the lock waiting timeout is exceeded; Attempt to restart the transaction.

2. Problem analysis

Get this bug and immediately use the search engine to search. Netizens gave some answers. Some said they added and modified the same data in the same transaction.

Some say that a transaction takes too long to execute. Just kill it. In a word, this operation is carried out in a transaction for unknown reasons

This leads to long execution and eventually problems. I have debugged myself for two or three times, and the results are the same. I simply hit a breakpoint to debug and see what the problem is. Fortunately, I found it soon

The cause of the problem. I accidentally wrote an infinite loop in a piece of code, and the infinite loop was executed in a transaction. Finally, there was a problem
III. solutions

After finding the cause of the problem, modify the code immediately, test again, and solve the problem. Although the problem is caused by carelessness when writing code, let yourself learn

At a new knowledge point, MySQL transaction execution has a set duration. If it exceeds this duration, problems in the title may occur. A fall into a ditch makes you wiser.

Similar Posts: