MySql database deadlock

Today, I encountered a Mysql DeadLock problem in the project performance test. Then, based on the database days, find the cause and simulate the situation. The innoDB used by the database. InnoDB row locks are implemented by locking the index items on the index. The implementation of InnoDB row locks means that InnoDB only enables data retrieval through the index conditions.

Today, I encountered a Mysql DeadLock problem in the project performance test. Then, based on the database days, find the cause and simulate the situation. The innoDB used by the database. InnoDB row locks are implemented by locking the index items on the index. The implementation of InnoDB row locks means that InnoDB only enables data retrieval through the index conditions.

Today, I encountered a Mysql DeadLock problem in the project performance test.

Then, based on the database days, find the cause and simulate the situation.

The innoDB used by the database.

InnoDB row locks are implemented by locking the index items on the index. The implementation of InnoDB row locks means that InnoDB uses row-level locks only when data is retrieved through the index conditions. Otherwise, innoDB will use table lock!

In the following example, the id is the primary key, the operation is indexed, and the lock is row lock.

Table Data

Two threads are used to perform the following operations. The deadlock will occur. One thread is successfully executed, and the other thread fails.

Final result: the first data entry is changed, and the first data entry is unchanged.

MySql InnoDB, \ records indexed by the select statement of the transaction will be added with the S lock, insert, delete, and update will be added with the X lock.

Set thread 1 on the left and thread 2 on the right.

Thread 1 adds the S lock to the record with id = 1. When thread 2 wants to update this record, it needs to add the X lock to the record, in this case, wait for thread 1 to release the S lock on the id = 1 record.

Thread 2 adds the S lock to the record with id = 3. When thread 1 wants to update this record, it needs to add the X lock to the record, in this case, wait for thread 2 to release the S lock on the id = 3 record.

Wait for each other, and then the deadlock occurs.

Mysql releases the deadlock mechanism. Depending on the number of locked records, transactions with multiple locked records are generally allowed to be rolled back. When the range is the same, it should be the order of the X lock request.

Note: If the condition field following the where clause does not have an index, InnoDB locks the table. Only query or operation records are locked by indexing. Be careful when querying conditions in InnoDB transactions

Mutual Exclusion	S	X
S	No	Yes
X	Yes	Yes

Write so much first.