MySQL Analysis of a deadlock

The deadlock information is as follows:

(1) TRANSACTION:

TRANSACTION 4363766192, ACTIVE 0 sec

MySQL tables in use 2, locked 2

Lock WAIT 9 lock struct (s), heap size 1248, 2 row lock (s), Undo log Entries 6

MySQL thread ID 8822753, OS thread handle 0x7fca3025b700, query ID 2302320886 *.*.*.* cashcoupon_oper sending data

Update Keap_cash_coup_type A, (select sum (freezed_amount) freezedamount,cash_coupon_type_id from Keap_cash_ transcation where transcation_id = 10000001415322882 GROUP by cash_coupon_type_id) b Set a.amount = A.amount-b.freezedamou Nt,a.locked_amount=a.locked_amount+b.freezedamount where a.cash_coupon_type_id=b.cash_coupon_type_id

(1) Waiting for this LOCK to be granted:

RECORD LOCKS Space ID 2280 page No 3 n bits 176 index ' PRIMARY ' of table ' Keap_ticket_cash '. ' Keap_cash_transcatio N ' Trx ID 4363766192 lock mode S Locks Rec but not gap waiting

(2) TRANSACTION:

TRANSACTION 4363766191, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4999

MySQL tables in use 2, locked 2

9 lock struct (s), heap size 1248, 2 row lock (s), Undo log Entries 6

MySQL thread ID 8822751, OS thread handle 0x7fc8718a1700, query ID 2302320895 *.*.*.* cashcoupon_oper sending data

Update Keap_cash_coup_type A, (select sum (freezed_amount) freezedamount,cash_coupon_type_id from Keap_cash_ transcation where transcation_id = 10000001415322879 GROUP by cash_coupon_type_id) b Set a.amount = A.amount-b.freezedamou Nt,a.locked_amount=a.locked_amount+b.freezedamount where a.cash_coupon_type_id=b.cash_coupon_type_id

(2) holds the LOCK (S):

RECORD LOCKS space ID 2280 page No 3 n bits 176 index ' PRIMARY ' of table ' Keap_ticket_cash '. ' Keap_cash_transcatio N ' Trx ID 4363766191 lock_mode X locks Rec but not gap

(2) Waiting for this LOCK to be granted:

RECORD LOCKS space ID 2280 page No 3 n bits 176 index ' PRIMARY ' of table ' Keap_ticket_cash '. ' Keap_cash_transcatio N ' Trx ID 4363766191 lock mode S Locks Rec but not gap waiting

Record Lock, Heap no 103 physical record:n_fields 12; Compact format; Info bits 0

Information shows two multi-link update transactions, transaction one in the Wait Table keap_cash_transcation table primary key index s lock, position on the 3rd page of 176 bytes, transaction two got the corresponding position of the lock, and waiting for the position S lock, this lock wait looks a bit strange, Clearly already got the position of the X lock why to get S lock, all know that MySQL in the unique index to do update and insert is the first to get S lock and then get the x lock, it feels a bit like, step by step troubleshooting analysis

First, check the isolation level to determine the granularity of lock:

Mysql> Show global variables like "%iso%";

+---------------+----------------+

| variable_name | Value |

+---------------+----------------+

| tx_isolation | read-committed |

+---------------+----------------+

1 row in Set (0.00 sec)

Is the RC commit read isolation level, know that there is no gap lock, only for row lock case, and then carefully look at two transactions of SQL Discovery waiting for the lock table Keap_cash_transcation just as the association condition did not update the field, the view table structure is only the primary key, Transcation_ The ID is not indexed, and the two zero-only table for the primary key is tested:

Structure:

CREATE TABLE ' t1 '/' T2 ' (

' id ' int (one) DEFAULT NULL,

' Name ' varchar () DEFAULT NULL,

' Id_1 ' int (one) not NULL auto_increment,

PRIMARY KEY (' id_1 ')

) Engine=innodb auto_increment=7

Test:

Session 1:	Session 2:
SELECT * from T1 for update;
	Update T2 join T1 on t1.id=t2.id set t2.age=10; Occurs waiting
	Mysql> Select A.lock_trx_id,b.trx_mysql_thread_id,b.trx_query,a.lock_mode,a.lock_type from INNODB_LOCKs a join Innodb_trx b on A.lock_trx_id=b.trx_id\g; 1. Row *************************** lock_trx_id:18944085 trx_mysql_thread_id:33762 Trx_query:update T2 join T1 on T1.id=t2.id set t2.age=10 lock_mode:s Lock_type:record Lock_page:3 Lock_table: ' Test '. ' T1 ' 2. Row *************************** lock_trx_id:18944084 trx_mysql_thread_id:33761 Trx_query:null lock_mode:x Lock_type:record Lock_page:3 Lock_table: ' Test '. ' T1 ' 2 rows in Set (0.00 sec)

See that the update for transaction two gets the S lock on the T1 table, but this statement only queries the T1 table for matching operations, and the statements executed by two transactions are ordered to see the results

Session 1	Session 2
Update T2 join T1 on t1.id=t2.id set t2.age=10;
	SELECT * from T1 for update; Occurs waiting
	1. Row *************************** lock_trx_id:18944086 trx_mysql_thread_id:33761 Trx_query:select * from T1 for update lock_mode:x Lock_type:record Lock_page:3 Lock_table: ' Test '. ' T1 ' 2. Row *************************** lock_trx_id:18944085 trx_mysql_thread_id:33762 Trx_query:null lock_mode:s Lock_type:record Lock_page:3 Lock_table: ' Test '. ' T1 ' 2 rows in Set (0.01 sec)

Transaction two at this point is to obtain the X lock, notice that the deadlock shows are acquiring the same position of the lock, and update to have an X lock, transaction one of the statement is obviously first from the T1 table to obtain the S lock, then get the x lock, finally get s lock

Add the index and test again:

Session 1	Session 2
Update T2 join T1 on (t1.id=t2.id and t1.id=4) set t2.age=10;
	SELECT * from t1 where id=4 for update; Normal non-blocking!

Summarize:

It is clear from the above test that MySQL is associated with the update, just as the table of the association query if there is no corresponding index will be the row data that satisfies the condition of the lock operation, in the T1 table for data query when the id=4 condition of all data will be added S lock, and T2 Table Association to judge the data and do update the corresponding line request x lock, when the data update is completed will release the X lock and request S Lock, the entire process is s-> x->s, if the T1 table is not specified conditions and as a driver table, it will cause T1 table records will be locked, The T1 table does not block after the conditional field ID has been indexed, and the statement that has this associated update in the production environment requires an index issue.

This article is from the "D-Tone de silent" blog, please be sure to keep this source http://xiaozhong991.blog.51cto.com/2354914/1887014

MySQL Analysis of a deadlock