MySQL Update deadlock problem

Previously wrote a blog http://leihuang.org/2015/07/22/concurrence-lock/

is how to use optimistic lock to solve concurrency problems, but the project pushed to the line after the error, as follows

-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 =? < 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 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 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) UnsignedNot NULL DEFAULT' 0 'COMMENT' The user has received the number of coupons for the event ', PRIMARY key (' id '), key ' Coup_usr_idx ' (' coup_usr ' ), KEY ' spec_idx ' (' spec_id ') ENGINE=InnoDB auto_ INCREMENT=8508 DEFAULT CHARSET=UTF8 COMMENT=' coupon ';      

Where coup usr and spec ID are indexed,

MySQL's transactional support is related to the storage engine, MyISAM does not support transactions, InnoDB supports transactions, and is used for row-level locks when updating. The use of InnoDB as a storage engine means that the UPDATE statement will be handled as a transaction. The previous mention of row-level locks must be based on the index, which uses the index, so there is definitely a row-level lock. A row-level lock is not a direct lock record, but a lock index, and if a SQL statement uses a primary key index, MySQL locks the primary key index, and if a statement operates a non-primary key index, MySQL locks the non-primary key index and then locks the primary key index. This UPDATE statement performs the following steps: 1, due to the use of non-primary key index, first need to get the normal index row-level lock 2, followed by the primary key to update, so need to get the primary key row-level lock, 3, after the update, commit, and release all locks.

If a statement is suddenly inserted between steps 1 and 2: UPDATE coupon SET coup num usr = coup num usr + 1 WHERE coup usr =? and specid =? and coup num usr <?

A deadlock occurs because one statement acquires the lock of the normal index, waits for the primary key lock, and another statement acquires the primary key lock, waits for the non-primary key index, and the deadlock occurs.

How to fix update ... where ... What about the deadlock problem with statements? We can detach it, first using the Where condition to find the primary key, and then using these primary keys to update the data.

Because select * Where ... The statement is not locked, so there is no lock in the Where condition inside the field, there will be no deadlock, only when the update back to lock the primary key.

So change 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 that need to update the rows, and then one more update.

In MySQL with InnoDB, the update operation will add row-level locks by default, row-level locks are index-based, and you need to query the MySQL execution plan before parsing the deadlock to see if the index is used and which index is used, and table-level locks are used for operations that do not use indexes. If the operation uses the primary key index, it will first lock on the primary key index and then lock on the other indexes, otherwise the lock order is reversed. In high-concurrency applications, batch updates must take the primary key of the record, taking precedence over the lock on the primary key, which can reduce the occurrence of deadlocks.

MySQL Update deadlock problem