mysql-inappropriate UPDATE statement causes MySQL deadlock with primary key and index

Background knowledge:
MySQL has three levels of Lock: page level, table level, row level.

The MyISAM and memory storage engines use table-level locks (table-level locking), BDB storage engines use page locks (page-level locking), but table-level locks are also supported The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

MySQL features of these 3 types of locks can be broadly summarized as follows:

Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

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.
Problem phenomenon:

Error log on the line: MySQL deadlock problem.



Troubleshooting process:


Ask the DBA to help you check the MySQL error log and discover that there is a deadlock problem.

The deadlock is the above two SQL, the two request time interval is only 1 milliseconds.
The database tables involved are as follows:

According to the background of the red Description: If the primary key index is used, MySQL locks the primary key index, if the non-primary key index is used, MSYQL will first lock the non-primary key index, and then lock the primary key index. Therefore, Next_consume_time is locked in SQL (1) and the primary key index is locked, and SQL (2) locks the primary key index directly, and the set in its UPDATE statement uses Next_consume_time. You also need to next_consume_time this non-primary key index. As a result, two SQL is competing against index resources, causing deadlocks.

Business considerations: Two SQL is a request from two machines, and these two SQL are in order in business, first execute SQL (1) occupy the table records that need to execute, perform the business operation in SQL (2). It is found in the log that the 54 machine executes SQL (2), then the 54 machine must have executed SQL (1), when the record is already occupied, and the 55 machine executes SQL (2)? Is it not occupied?
The DBA also looked at MySQL's binlog, found that queue_id was 283410, and did normally execute SQL (1), and why did the 55 machine Execute SQL (1) again?
Also check the open platform log, 54 machine thread startup time is:
2016-09-25 11:12:40,463] [dbMsgConsumer-3] [INFO] [Tasklogger] [////]-[Queueconsumetask started]
55 Machine Thread Start-up time:
2016-09-25 11:12:40,583] [dbMsgConsumer-2] [INFO] [Tasklogger] [////]-[Queueconsumetask started]

The two differ by only 120 milliseconds.

At present we mysql the default transaction isolation level is repetable read (repeatable read), that is, within the same transaction, multiple query results are consistent.
When the 54 machine turns on the transaction, executes the SQL (1), the transaction is not committed, the 55 machine also executes SQL (1), at this time the 55 machine queries to the record is before the update, 54 machine commits the transaction, then executes the SQL (2), at this time because the SQL (1) is the scope query, SQL (2) The execution time is much less than SQL (1), will cause 54 machine execution SQL (2), 55 machine has not executed the completion of SQL (1), causing two machines to preempt each other resources, causing deadlock. 54 and 552 machines are executed as follows:



Workaround:
modifying SQL (1) is a two-step operation in which a condition-compliant record is first queried and then updated based on the primary key ID of the result of the query.
After modifying SQL, perform the query operation first:


Then perform the modify operation, using the obtained primary key ID


The code is modified as follows:

Lessons learned:
E-commerce, regardless of the front-end procedures, should not exist only based on a few fields of the non-primary key to check the scene update/delete. Even if you do, you should first isolate the record you want to update and then update it by pressing the primary key ID.

Original blog:http://blog.csdn.net/lzy_lizhiyang/article/details/52678446

mysql-inappropriate UPDATE statement causes MySQL deadlock with primary key and index