MySQL Deadlock analysis

Familiar with or understand the database of friends are aware of the concept of lock, here do not do too much analysis! There are many kinds of locks, and different database lock management methods are different. Here is the main talk about the deadlock under the MySQL InnoDB engine.

The common thing about deadlocks is that 2 of transactions ask each other to hold locks, which can cause 2 transactions to wait for each other to release the lock resources, so this is a deadlock.

transaction a	transaction b
begin;
select * from T where a = 1 for update;	begin;
	select * from t where a = 2 for upd ate;
Select * FROM t where a = 2 for update; #等待
	Select * FROM t where a = 1 for update; #ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

First, the InnoDB engine does not roll back most exceptions, except for deadlocks. Once the deadlock is discovered, InnoDB will immediately roll back one of the transactions (where it is rolled back and is designed to be weighted). In the example, transaction B is rolled back! This is the most common example of a deadlock.

There is another deadlock situation with MySQL: The current transaction holds an X lock (exclusive lock) for the next record to be inserted, but a deadlock may occur when there is a request for an S lock in the waiting queue. First create a test table and insert the data:

CREATE TABLE test (a int primary key) Engine=innodb;

INSERT into test values (1), (2), (4), (5);

Time	Transaction A	Transaction b
1	Begin
2		Begin
3	SELECT * FROM Test where A = 4 for update;
4		SELECT * FROM Test where a<=4 lock in Share mode; #等待
5	INSERT into test values (3) ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction
6		#事务获得锁, normal operation

The x lock is already held in transaction a for record 4, but session a inserts record 3 to cause a deadlock to occur. This problem arises from transaction B in the request to record 4 S lock and wait, but before the request lock for the primary key record 1, 2 has been successful, if the time 5 can insert the record, then transaction b in the acquisition of Record 4 holds the S lock, also need to get back record 3 records, this seems unreasonable. So the InnoDB engine chooses the deadlock here.

This article is from the "Jeff on the Go" blog, so be sure to keep this source http://nrain.blog.51cto.com/11930278/1895270

MySQL Deadlock analysis