MySQL deadlock check

MySQL deadlock check

I read a blog about the deadlock check today.

Advanced InnoDB Deadlock troubleshooting–what SHOW InnoDB STATUS doesn ' t tell you, and what diagnostics you should be Lo OKing at

One common cause for deadlocks when using InnoDB tables are from the existence of foreign key constraints and the shared Lo CKS (S-lock) They acquire on referenced rows.

The reason I want to discuss them though is because they be often a bit tricky to diagnose, especially if you were only lo OKing at the SHOW ENGINE INNODB STATUS output (which might bes a bit counter-intuitive since one would expect it to contain This info).

Let me show a deadlock error to illustrate (below are from show ENGINE INNODB status\g):

------------------------LATEST detected DEADLOCK------------------------111109 20:10:03*** (1) TRANSACTION: TRANSACTION 65839, ACTIVE sec, OS thread ID 4264 starting index readmysql tables in use 1, locked 1LOCK WAIT 6 Lock Str UCT (s), Heap size 1024x768, 3 row lock (s), undo log Entries 1MySQL thread ID 3, query id localhost 127.0.0.1 root Updatingu PDATE parent SET age=age+1 WHERE id=1*** (1) Waiting for this LOCK to be Granted:record LOCKS Space ID 6833 page No 3 n bi  TS index ' PRIMARY ' of table ' test '. ' Parent ' Trx ID 65839 lock_mode X locks Rec but not gap Waitingrecord lock, Heap No 2 Physical Record:n_fields 4; Compact format; Info bits 0*** (2) transaction:transaction 65838, ACTIVE sec, OS thread ID 768 starting index Read,thread declared Insi De InnoDB 500mysql tables in use 1, locked lock struct (s), Heap size 1024x768, 4 row lock (s), undo log Entries 2MySQL Threa D ID 4, query id 127.0.0.1 localhost root updatingupdate parent SET age=age+1 WHERE id=2*** (2) holds the LOCK (S): RECORD LOCKS Space ID 6833 page No 3 n bits index ' PRIMARY ' of table ' test '. ' Parent ' Trx ID 65838 lock_mode X LOCKS re C but not Gaprecord lock, heap No 2 physical record:n_fields 4; Compact format; Info bits 0*** (2) waiting for this LOCK to be Granted:record LOCKS Space ID 6833 page No 3 n bits net index ' PRIMARY ' of t  Able ' test '. ' Parent ' Trx ID 65838 lock_mode X locks Rec but not gap Waitingrecord lock, heap No 3 physical record:n_fields 4; Compact format; Info bits 0*** WE Roll back TRANSACTION (1)

Now, we don't see a lot for what's caused the deadlock above, but we were only seeing *half* of the picture.

Allow me to explain.

First of all, note transaction #1 have been running for seconds, while transaction #2 for seconds. So, the output was referring to the newer transaction as #1 and the older as #2 (also somewhat counter-intuitive, but good To be aware of).

Now, what we can see clearly are this:

Transaction #1 ("UPDATE Parent: WHERE Id=1″) is waiting on a lock from Transaction #2 ("UPDATE Parent: WHERE Id=2″).

Thus TX #2 holds a lock (record LOCKS Space ID 6833 page No 3 n bits index, heap No 2), but was waiting on (record LOCKS Space ID 6833 page No 3 n bits index, heap no 3–held by TX #1).

Clearly the 2 updates should not cause a conflict in and of themselves. Thus We know something must has happened earlier in the transaction (s).

For reference, here's how to reproduce it:

CREATE TABLE ' parent ' (' id ' int not NULL auto_increment, ' age ' int not null,primary KEY (' id ')) engine=innodb; CREATE TABLE ' child ' (' id ' int not null auto_increment, ' age ' int not null, ' parent_id ' int not null,primary key (' ID '), key ' parent_id ' (' parent_id '), CONSTRAINT ' fk_parent_id ' FOREIGN KEY (' parent_id ') REFERENCES ' parent ' (' id ')) engine=innodb INSERT into the parent (ID, age) VALUES (1, +), insert into parent (ID, age) VALUES (2, $); INSERT into child (ID, age, Paren T_ID) VALUES (1, 1); INSERT into child (ID, age, parent_id) VALUES (2, 20, 1);

Then, open 2 connections (T1 and t2–note order are opposite compared to what's shown in SHOW INNODB STATUS):

T1:

BEGIN; UPDATE child SET age=age+1, parent_id=2 WHERE id=1; UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN; UPDATE child SET age=age+1, parent_id=2 WHERE id=2; UPDATE parent SET age=age+1 WHERE id=1;

<--T2 Hangs

T1:

UPDATE parent SET age=age+1 WHERE id=2;

<--Deadlock (T1 completes, T2 was rolled back)

But what does this deadlock? Well, it's due to the foreign key. In fact, this example would not deadlock at all if no foreign key is defined on ' parent '. ' ID '.

So and what exactly are preventing T2 from completing?

Here are happening behind the scenes, So-to-speak:

T1#1:

Obtains the following 2 locks:

X lock on ' child '  where id=1 <--due to the actual "UPDATE child" statement Itselfs lock on ' parent ' where id=2 < ;--due to the FK on Parent.id

(Note this S lock means other s locks can be obtained on this row, but not X locks--which was the crux of this issue).

T1#2:

Obtains the following lock:

X lock on ' parent ' where id=1 <--due to the actual "UPDATE parent" statement itself

T2#1:

Obtains the following 2 locks:

X lock on ' child '  where id=2 <--due to the actual "UPDATE child" statement Itselfs lock on ' parent ' where id=2 < ;--Due to the FK (again, which is okay since it's also a s-lock)

T2#2:

Tries to obtain the following, but hangs due to the existing x-lock from t1#2:

X lock on parent where id=1

T1#3:

Tries to obtain the following lock:

X lock on parent where id=2

However, since there is 2 s-locks on this row already (one from T1 and T2), and T1 now wants a x-lock on the same row, T Hen there is a conflict.

Now this would normally just wait for the s-locks to being released, but since T2 was already "hung" waiting on the other lock To is released from T1, we are now having the deadlock.

The T1 wins the dispute, T2 rolls back thus releasing its locks, and T1 completes.

So all in all, quite a bit is going on there, if you have see about half of this information from the LATEST detected DEA Dlock section of SHOW ENGINE INNODB STATUS output. And had I not posted the SHOW CREATE TABLE status (and prior TX statements), it ' d is unclear as to what happened exactly.

Well, why does you find out exactly-happened when locking problems happen?

SHOW INNODB STATUS only tells your so much. Furthermore, once the deadlock occurs, the winner moves on, and the loser are rolled back. *meaning*, there is no longer any information on these "transactions" in the output as they was in the ' past ' now.

Therefore, in general, if you have any locking issues (deadlocks, lock wait timeouts, hangs due to Semaphore waits, a nd so forth), does yourself a favor and capture all of the following outputs at the time, if possible, to give Likelihood in tracking down the issue:

2. SHOW ENGINE INNODB STATUS
   • This was generally very good, but it can get truncated, and simply could not contain every bit of info for you need.
4. Enable InnoDB lock Monitor (enable the InnoDB lock monitor by simply creating any InnoDB table named Innodb_lock_monitor)
   • This is logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.
6. Run "Mysqladmin Debug"
   • Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which don't appear in SHOW INNODB ST ATUs even if on a InnoDB table, because LOCK table is external to the InnoDB storage engine. Do great because a bit cryptic to read, and I wouldn ' t solely reply on it, as it's often most helpful in conjuntion WI th other details).
8. SHOW Full Processlist
   • This'll show all connected threads. Specifically, when it comes to ' hidden ' locks, it would show a user that had been connected, but the Who could had issued a LOCK TABLE command).
10. Error Log
    • Of course, always check out the error log for messages and/or anything out of the ordinary. (not to mention extra data would be logged to it from "Mysqladmin Debug" and Innodb_lock_monitor.)
12. SELECT * from INFORMATION_SCHEMA. Innodb_locks, Innodb_lock_waits, and Innodb_trx tables
    • This is a available as of MySQL 5.1 and newer (and you must being using the InnoDB Plugin), but since most of you'll be Running this, they contain great information. In fact, I ' ve written a past post on debugging InnoDB Locks using the new InnoDB Plugin ' s information Schema Tables which Contains even more details on using the these 3 tables for troubleshooting.
14. SHOW CREATE table outputs for each table involved

Combined before looking at Ho Dengcheng technology Blog. I have the following summary:

1, to try to make their own updates, delete, query operations to go index. On the one hand, indexes can improve query efficiency, on the other hand can reduce unnecessary record lock.

2, pay attention to repeatable-read this transaction isolation level of the gap lock, if the business allows the phenomenon of illusory, you can consider the use of read-commited transaction isolation level.

3, the business should pay attention to the order of SQL lock, ensure that multiple transactions do not use inconsistent order to lock records. Also, it is important to analyze the implicit locking of clustered indexes (that is, primary key indexes) brought by level two indexes. You can refer to the analysis here: http://hedengcheng.com/?p=771

4, business less use or no lock in share mode, for update such a display lock, and should try to take the snapshot read.

5, write SQL, pay attention to use the Explain Command analysis execution plan, can walk Index walk index, can walk overlay index (avoid query unnecessary column).

But there is no more useful deadlock detection tool, this piece is under study ...

The previous blog introduction of the more useful is the SELECT * from INFORMATION_SCHEMA. Innodb_locks, Innodb_lock_waits, and Innodb_trx tables

This SQL, query lock information is being learned in the.

MySQL deadlock check