MySQL Deadlock case analysis and solutions

MySQL Deadlock case analysis and solutions

Phenomenon:

Database query:

SQL Statement Analysis:

Mysql. Concurrent Delete same row record, occasional deadlock. Delete from x_table where id=? Deadlock 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:

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

2. Update the record again.

That is what the code should say:

1. Select ID from x_table where id=?
2. Delete from x_table where id=?  

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.

MySQL Deadlock case analysis and solutions