MySQL Update deadlock problem

As a social App, we have a lot of things happening at the same time, in order to ensure the consistency of data, will take the database.

For example, now we have a praise operation, like success, you need to change the heat of the article. The following is the SQL statement:

INSERT into User_praise (uid,plan_id,stage_id) VALUES (123456,14456,10023);

UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = 14456;

Here we need to use things to ensure that it is atomic, that is, either the two statements are all successful, or all rolled back.

Problem arises

After testing locally, we found that there was no problem, but after the deployment went live, the error log was printed, as follows

# # # Error updating database.  # #    Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Dead        WHERE plan_id =?;

The following UPDATE statement appears as a deadlock in the error

UPDATE plan_hot SET hot = hot + 1 WHERE plan_id =?;

The problem is the first encounter, probably also know where the deadlock occurred, that is, hot = hot + 1 When the hot may be locked by a lock, and the next operation will need to wait for the release of the lock, and then get to hot value, hot = hot + 1. If the operation is too fast, it may cause a deadlock.

But still do not understand why this, the root cause is what, so check the information, found a small partner explained quite clear, the following is his blog.

Analytical Solutions

Problem analysis and Solution reference

MySQL Update deadlock problem

Thank him for sharing, but also hope that more friends can share experience.

Http://www.tuicool.com/articles/nQniiaa

MySQL Update deadlock problem