In-depth analysis of MySQL update deadlock

As a social App, we have many operations at the same time. To ensure data consistency, we will use database things.

For example, now we have a thumbs up operation. After the thumbs up operation is successful, we need to change the popularity of the article. The following are SQL statements:

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 their atomicity, that is, either the two statements are successfully executed or all are rolled back.

Problem

After the local test, no problems were found, but after the deployment was launched, an error log was printed, as shown below:

### Error updating database.
### Cause: com. mysql. jdbc. exceptions. jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
SQL: UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = ?;

The error indicates that the following update statement has a deadlock.

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

This is the first time we encountered this problem. We probably know where a deadlock exists, that is, when hot = hot + 1 is updated, hot may be locked by a lock, in the next operation, you need to wait for the lock to be released before you can obtain the hot value and perform hot = hot + 1. If the operation is too fast, a deadlock may occur.

However, I still don't know why it is. What is the root cause? So I checked the information and found a small friend who explained it clearly. Below is his blog post.

Analysis and solution

Problem analysis and solution reference

Is how to use optimistic locks to solve the concurrency problem, but after the project is pushed online, an error is reported, as shown below:

-08-13 15:12:44 [ERROR] com. zhubajie. coupon. app. CouponReceiveAppServiceImpl {CouponReceiveAppServiceImpl. java: 50 }-
### 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 cpn_core_coupon SET coup_num_usr = coup_num_usr + 1 WHERE coup_usr =? AND spec_id =? AND coup_num_usr <?
### 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

The error indicates that the following SQL statement has a deadlock.

UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE coup_usr =? AND spec_id =? AND coup_num_usr <?
First, let's take a look at the table structure of coupon.

Create table 'coupon '(
'Id' int (11) not null AUTO_INCREMENT COMMENT '',
'Spec _ id' char (20) not null comment 'coupon activity number ',
'Coup _ usr' char (11) default null comment 'coupon users ',
'Coup _ num_usr' int (11) unsigned not null default '0' Comment' The number of coupons that the user has received this activity ',
Primary key ('id '),
KEY 'coup _ usr_idx' ('coup _ usr '),
KEY 'Spec _ idx' ('Spec _ id ')
) ENGINE = InnoDB AUTO_INCREMENT = 8508 default charset = utf8 COMMENT = 'coupon ';

Coup usr and spec id are indexes,

Mysql transaction support is related to the storage engine, MyISAM does not support transactions, INNODB supports transactions, and RoW-level locks are used when updating. INNODB is used as the storage engine, which means that the update statement will be processed as a transaction. As mentioned above, row-level locks must be built on the basis of indexes. This update statement uses indexes, so the row-level locks must be added here. Row-level locks do not directly lock records, but lock indexes. If an SQL statement uses a primary key index, mysql locks the primary key index. If a statement operates a non-primary key index, mysql locks the non-primary key index and then the primary key index. This update statement performs the following steps: 1. Because a non-primary key index is used, you must first obtain the row-level lock on a common index. 2. Then update the index based on the primary key, therefore, you need to obtain the row-level lock on the primary key. 3. After the update is complete, submit and release all the locks.

If a statement is inserted between step 1 and Step 2: UPDATE coupon SET coup num usr = coup num usr + 1 WHERE coup usr =? AND spec id =? AND coup num usr <?

A deadlock occurs because one statement acquires the lock of a common index and waits for the primary key lock. The other statement acquires the primary key lock and waits for the non-primary key index.

How can we solve the deadlock problem of the update... where... statement? We can separate them. First, we can use the where condition to find the primary key and then use the primary key to update the data.

Because the select * where... statement does not have a lock, the fields in the where condition will be locked and no deadlock will occur. The primary key will be locked only during update.

So change it to the following two statements:

SELECT id WHERE coup_usr =? AND spec_id =?

UPDATE coupon SET coup_num_usr = coup_num_usr + 1 WHERE id =? AND coup_num_usr <?

The first statement finds all the primary key IDs of the rows to be updated, and then updates them one by one.

In MySQL that uses INNODB, row-level locks are applied by default for update operations. Row-level locks are based on indexes. Before analyzing deadlocks, you need to query the mysql execution plan to see if indexes are used, which index is used? Table-level locks are used for operations that do not use indexes. If the operation uses the primary key index, it will first lock the primary key index and then lock other indexes. Otherwise, the lock order is the opposite. In applications with high concurrency, batch update must carry the primary key of the record and obtain the lock on the primary key first. This can reduce the occurrence of deadlocks.