MySQL Batch update deadlock case ANALYSIS--Reprint

Problem description

In the process of doing the project, because write SQL too arbitrary, accidentally threw a deadlock exception, as follows:

[Java]View Plaincopyprint?

2. Com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Deadlock found when trying to get lock; Try restarting transaction
4. At Sun.reflect.GeneratedConstructorAccessor247.newInstance (Unknown Source)
6. At Sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Delegatingconstructoraccessorimpl.java:27)
8. At Java.lang.reflect.Constructor.newInstance (Constructor.java:513)
10. At Com.mysql.jdbc.Util.handleNewInstance (Util.java:406)
12. At Com.mysql.jdbc.Util.getInstance (Util.java:381)
14. At Com.mysql.jdbc.SQLError.createSQLException (Sqlerror.java:1045)
16. At Com.mysql.jdbc.SQLError.createSQLException (Sqlerror.java:956)
18. At Com.mysql.jdbc.MysqlIO.checkErrorPacket (Mysqlio.java:3491)
20. At Com.mysql.jdbc.MysqlIO.checkErrorPacket (Mysqlio.java:3423)
22. At Com.mysql.jdbc.MysqlIO.sendCommand (Mysqlio.java:1936)
24. At Com.mysql.jdbc.MysqlIO.sqlQueryDirect (Mysqlio.java:2060)
26. At Com.mysql.jdbc.ConnectionImpl.execSQL (Connectionimpl.java:2542)
28. At Com.mysql.jdbc.PreparedStatement.executeInternal (Preparedstatement.java:1734)
30. At Com.mysql.jdbc.PreparedStatement.executeUpdate (Preparedstatement.java:2019)
32. At Com.mysql.jdbc.PreparedStatement.executeUpdate (Preparedstatement.java:1937)
34. At Com.mysql.jdbc.PreparedStatement.executeUpdate (Preparedstatement.java:1922)

The table structure is as follows:

[SQL]View Plaincopyprint?

2. CREATE TABLE ' User_item ' (
4. ' ID ' BIGINT () not NULL,
6. ' user_id ' BIGINT () not NULL,
8. ' item_id ' BIGINT () not NULL,
10. ' Status ' TINYINT (4) not NULL,
12. PRIMARY KEY (' id '),
14. KEY ' idx_1 ' (' user_id ', ' item_id ', ' status ')
16. ) Engine=innodb DEFAULT charset=utf-8

The SQL statements are as follows:

[SQL]View Plaincopyprint?

1. Update User_item set Status=1 where user_id=?  and item_id=?

Cause analysis

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 idx_1, 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 obtain the row-level lock on the idx_1

2, immediately according to the primary key to update, so need to get the primary key on the row-level lock;

3, after the update is complete, submit, and release all locks.

If you suddenly insert a statement between steps 1 and 2: Update User_item .... where id=? and user_id=, this statement locks the primary key index first and then locks the idx_1.

The condition of the egg ache appears, a statement obtains the lock on the idx_1, waits for the lock on the primary key index, the other statement obtains the lock on the primary key, waits for the lock on the idx_1, thus appears the deadlock.

Solution Solutions

1. Get the primary key of the record you want to update first

[SQL]View Plaincopyprint?

1. Select ID from user_item where user_id=?  and item_id=?

2. Update

[Java]View Plaincopyprint?

1. for (Long id:idlist) {
2. Useritemdao.updatestatus (ID, userId, 1);
3. }

[SQL]View Plaincopyprint?

1. Update User_item set status=? where id=?  and user_id=?

This seems to solve, is to operate on a single, are first to get the lock on the primary key, and then get the lock on the idx_1.

However, this solution is not the same as the previous UPDATE statement, where the previous UPDATE statement updates all records in a single transaction, and is not in the same transaction after the loop update, so a transaction is opened outside the for loop.

[Java]View Plaincopyprint?

2. Exception e = (Exception) getdbfeeltransactiontemplate (). Execute (new Transactioncallback () {
4. Public Object dointransaction (transactionstatus status) {
6. try {
8. For (Long id:idlist) {
10. <span style="White-space:pre" > </span>useritemdao.updatestatus (Id,userid,1)
12. }
14. return null;
16. }catch (Daoexception e) {
18. Status.setrollbackonly ();
20. return e;
22. }
24. catch (Exception e) {
26. Status.setrollbackonly ();
28. return e;
30. }
32. }
34. });

Summary: in MySQL with InnoDB, the update operation by default will add row-level locks, row-level locks are based on the index, before the deadlock analysis, you need to query the MySQL execution plan, to see if the index is used, which index is used, for non-indexed operation will adopt table-level lock. 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.

Original: http://blog.csdn.net/aesop_wubo/article/details/8286215

MySQL Batch update deadlock case ANALYSIS--Reprint