MySQL Deadlock Analysis and solution

Exception Log

# # # Error updating database. Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; Try restarting transaction

# # # The error may involve Defaultparametermap

# # # The error occurred while setting parameters

# # # Sql:update t_withdraw_apply SET last_apply_time=? WHERE user_id =? and state = 0

# # # Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; Try restarting transaction

; SQL []; Deadlock found when trying to get lock; Try restarting transaction; Nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to Get lock; Try restarting transaction

Obviously, the SQL statement that is deadlocked is the UPDATE t_withdraw_apply SET last_apply_time=? WHERE user_id =? and state = 0

Look at the business logic again

int i = Withdrawapplydao.add (apply);

if (i = = 1) {

Get the user's earliest pending withdrawal request time

Timestamp earliertime = Withdrawapplydao.getearlierapplytime (userId);

Withdrawapplydao.updateearlierapplytime (userid,earliertime);//Update redundant fields

Deduct account balance, then write to Gold_log log

Int J = Userdao.updateusergold (0-amount, userId);

......

So why is there a deadlock?

This is the case, the user clicked too fast and submitted two withdrawal requests

Then it will open two transactions

Transaction 1 Insert an apply

Transaction 2 also inserts an apply

Transaction 1 performs update updateearlierapplytime, because the data that is inserted by transaction 2 also needs to be updated, so this time transaction 1 waits for transaction 2 to commit before executing

The Transaction 2 page performs update updateearlierapplytime, as well as the need to update the input for Transaction 1 insertions, and to wait for transaction 1 to complete before continuing execution, so that a deadlock occurs

How to solve this problem?

The first reason for this problem is that the transaction 1 updated data contains the data inserted by transaction 2, and the Transaction 2 update also contains the data inserted by transaction 1, then we can change the code to

Withdrawapplydao.updateearlierapplytime (userid,earliertime);//Update redundant fields

Withdrawapplydao.add (apply);

This will solve the deadlock problem.

Second, the problem occurs when the user repeats the submission, so there should be a limit for repeated submissions,

MySQL Deadlock Analysis and solution