Php Catch Mysql Deadlock and Try Again

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and non completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. Though an occasional deadlock is non something to worry near, frequent occurrences call for attention.

Earlier MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. Simply with Percona Toolkit'southward pt-deadlock-logger y'all can have deadlock data retrieved from SHOW ENGINE INNODB STATUS at a given interval and saved to a file or table for late diagnosis. For more information on using pt-deadlock-logger, run into this post. With MySQL v.6, you lot tin can enable a new variable innodb_print_all_deadlocks to take all deadlocks in InnoDB recorded in mysqld error log.

Before and above all diagnosis, information technology is ever an important exercise to have the applications catch deadlock error (MySQL error no. 1213) and handles it by retrying the transaction.

How to diagnose a MySQL deadlock

A MySQL deadlock could involve more than two transactions, but the LATEST DETECTED DEADLOCK section only shows the last two transactions. As well, it only shows the last statement executed in the ii transactions and locks from the two transactions that created the bicycle. What is missed are the earlier statements that might have really acquired the locks. I volition show some tips on how to collect the missed statements.

Let's look at two examples to see what information is given. Instance 1:

1 2 iii 4 5 6 seven 8 9 10 xi 12 13 14 fifteen 16 17 18 nineteen 20

1 141013 six : 06 : 22 2 *** ( 1 ) TRANSACTION : 3 TRANSACTION 876726B90 , ACTIVE vii sec setting automobile-inc lock 4 mysql tables in use ane , locked 1 v LOCK Wait 9 lock struct ( s ) , heap size 1248 , four row lock ( s ) , undo log entries 4 6 MySQL thread id 155118366 , Os thread handle 0x7f59e638a700 , query id 87987781416 localhost msandbox update 7 INSERT INTO t1 ( col1 , col2 , col3 , col4 ) values ( 10 , xx , 30 , 'hello' ) 8 *** ( 1 ) WAITING FOR THIS LOCK TO BE GRANTED : nine Tabular array LOCK table ` mydb ` . ` t1 ` trx id 876726B90 lock mode Machine-INC waiting 10 *** ( ii ) TRANSACTION : 11 TRANSACTION 876725B2D , ACTIVE 9 sec inserting 12 mysql tables in utilise 1 , locked 1 13 876 lock struct ( s ) , heap size 80312 , 1022 row lock ( s ) , undo log entries 1002 fourteen MySQL thread id 155097580 , OS thread handle 0x7f585be79700 , query id 87987761732 localhost msandbox update fifteen INSERT INTO t1 ( col1 , col2 , col3 , col4 ) values ( vii , 86 , 62 , "a lot of things" ) , ( 7 , 76 , 62 , "many more" ) 16 *** ( two ) HOLDS THE LOCK ( S ) : 17 Table LOCK table ` mydb ` . ` t1 ` trx id 876725B2D lock mode AUTO-INC 18 *** ( two ) WAITING FOR THIS LOCK TO Exist GRANTED : 19 RECORD LOCKS infinite id 44917 page no 529635 due north bits 112 alphabetize ` PRIMARY ` of table ` mydb ` . ` t2 ` trx id 876725B2D lock mode Due south locks rec but not gap waiting 20 *** WE Scroll Dorsum TRANSACTION ( 1 )

Line one gives the time when the deadlock happened. If your application code catches and logs deadlock errors, which it should, then you can match this timestamp with the timestamps of deadlock errors in the application log. Yous would have the transaction that got rolled back. From in that location, think all statements from that transaction.

Line 3 & xi, take note of Transaction number and Agile fourth dimension. If yous log SHOW ENGINE INNODB STATUS output periodically(which is a good practice), then you tin can search previous outputs with a transaction number to hopefully come across more than statements from the aforementioned transaction. The Agile sec gives a hint on whether the transaction is a single statement or multi-statement i.

Line 4 & 12, the tables in use and locked are simply with respect to the current statement. So having i tabular array in utilise does non necessarily hateful that the transaction involves 1 tabular array only.

Line 5 & xiii, this is worthy of attention as it tells how many changes the transaction had fabricated, which is the "undo log entries" and how many row locks it held which is "row lock(s)". This info hints the complexity of the transaction.

Line 6 & fourteen, take note of thread id, connecting host and connecting user. If you utilize different MySQL users for unlike application functions which is some other good do, and then you tin can tell which application expanse the transaction comes from based on the connecting host and user.

Line 9, for the first transaction, information technology only shows the lock it was waiting for, in this case, the Car-INC lock on table t1. Other possible values are S for shared lock and X for exclusive with or without gap locks.

Line 16 & 17, for the second transaction, information technology shows the lock(south) it held, in this example, the AUTO-INC lock which was what TRANSACTION (i) was waiting for.

Line 18 & nineteen shows which lock TRANSACTION (ii) was waiting for. In this case, it was a shared not gap record lock on some other tabular array's primary key. There are only a few sources for a shared record lock in InnoDB:
1) use of SELECT … LOCK IN SHARE MODE
2) on strange central referenced record(s)
3) with INSERT INTO… SELECT, shared locks on the source table
The current statement of trx(two) is a simple insert to tabular array t1, so i and 3 are eliminated. By checking Prove CREATE Table t1, you could ostend that the S lock was due to a foreign primal constraint to the parent table t2.

Example 2: With MySQL customs version, each tape lock has the tape content printed:

1 2 3 4 v 6 7 8 9 x 11 12 13 14 15 sixteen 17 xviii xix 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

1 2014-10-eleven 10 : 41 : 12 7f6f912d7700 2 *** ( 1 ) TRANSACTION : three TRANSACTION 2164000 , ACTIVE 27 sec starting index read 4 mysql tables in utilize 1 , locked 1 5 LOCK Wait three lock struct ( southward ) , heap size 360 , two row lock ( s ) , undo log entries 1 6 MySQL thread id 9 , Os thread handle 0x7f6f91296700 , query id 87 localhost ro ot updating vii update t1 set name = 'b' where id = 3 8 *** ( one ) WAITING FOR THIS LOCK TO BE GRANTED : 9 RECORD LOCKS infinite id 1704 folio no three n bits 72 alphabetize ` PRIMARY ` of table ` tes t ` . ` t1 ` trx id 2164000 lock_mode Ten locks rec but not gap waiting 10 Record lock , heap no 4 PHYSICAL RECORD : n_fields five ; meaty format ; info bit s 0 11 0 : len four ; hex 80000003 ; asc ; ; 12 1 : len half dozen ; hex 000000210521 ; asc ! ! ; ; 13 2 : len seven ; hex 180000122117cb ; asc ! ; ; xiv 3 : len 4 ; hex 80000008 ; asc ; ; xv four : len 1 ; hex 63 ; asc c ; ; sixteen 17 *** ( 2 ) TRANSACTION : eighteen TRANSACTION 2164001 , ACTIVE 18 sec starting index read 19 mysql tables in utilise one , locked i 20 3 lock struct ( southward ) , heap size 360 , ii row lock ( s ) , undo log entries 1 21 MySQL thread id 10 , Bone thread handle 0x7f6f912d7700 , query id 88 localhost r oot updating 22 update t1 fix name = 'c' where id = 2 23 *** ( 2 ) HOLDS THE LOCK ( Southward ) : 24 Record LOCKS infinite id 1704 folio no 3 n bits 72 index ` Chief ` of table ` tes t ` . ` t1 ` trx id 2164001 lock_mode X locks rec but not gap 25 Record lock , heap no four PHYSICAL Record : n_fields 5 ; compact format ; info bit s 0 26 0 : len 4 ; hex 80000003 ; asc ; ; 27 1 : len half-dozen ; hex 000000210521 ; asc ! ! ; ; 28 2 : len 7 ; hex 180000122117cb ; asc ! ; ; 29 three : len 4 ; hex 80000008 ; asc ; ; thirty 4 : len i ; hex 63 ; asc c ; ; 31 32 *** ( ii ) WAITING FOR THIS LOCK TO Exist GRANTED : 33 RECORD LOCKS space id 1704 page no three northward bits 72 index ` PRIMARY ` of table ` tes t ` . ` t1 ` trx id 2164001 lock_mode X locks rec but non gap waiting 34 Record lock , heap no three Physical RECORD : n_fields 5 ; compact format ; info bit s 0 35 0 : len 4 ; hex 80000002 ; asc ; ; 36 ane : len 6 ; hex 000000210520 ; asc ! ; ; 37 2 : len seven ; hex 17000001c510f5 ; asc ; ; 38 iii : len 4 ; hex 80000009 ; asc ; ; 39 iv : len 1 ; hex 62 ; asc b ; ;

Line 9 & x: The 'space id' is tablespace id, 'page no' gives which page the record lock is on inside the tablespace. The 'n bits' is not the page offset, instead, the number of bits in the lock bitmap. The page beginning is the 'heap no' on line 10,

Line 11~xv: Information technology shows the record data in hex numbers. Field 0 is the cluster alphabetize(primary key). Ignore the highest bit, the value is 3. Field ane is the transaction id of the transaction which last modified this record, decimal value is 2164001 which is TRANSACTION (2). Field two is the rollback pointer. Starting from field iii is the remainder of the row data. Field 3 is integer column, value 8. Field four is a cord column with grapheme 'c'. Past reading the data, we know exactly which row is locked and what is the electric current value.

What else can we learn from the analysis?

Since most MySQL deadlocks happen between 2 transactions, we could starting time the analysis based on that assumption. In Instance 1, trx (2) was waiting on a shared lock, then trx (ane) either held a shared or exclusive lock on that primary key record of table t2. Permit's say col2 is the foreign key column, by checking the current statement of trx(1), we know it did not require the same record lock, so information technology must be some previous statement in trx(1) that required S or X lock(south) on t2'due south PK record(s). Trx (1) only made 4 row changes in 7 seconds. So you learned a few characteristics of trx(1): it does a lot of processing just a few changes; changes involve table t1 and t2, a single tape insertion to t2. This data combined with other data could help developers to locate the transaction.

Where else can we find previous statements of the transactions?

A helper query to excerpt the history of a transaction is the following where <PROCESSID> is the ID of the offending connectedness.

SELECT * FROM performance_schema .events_statements_history WHERE thread_id = ( SELECT THREAD_ID FROM performance_schema .threads WHERE PROCESSLIST_ID = < PROCESSID > ) \ G

More details for the events_statements_history table can exist found hither.

Likewise application log and previous Evidence ENGINE INNODB STATUS output, you may also leverage binlog, slow log and/or general query log. With binlog, if binlog_format=argument, each binlog consequence would accept the thread_id. Only committed transactions are logged into binlog, so nosotros could only wait for Trx(2) in binlog. In the case of Example i, nosotros know when the deadlock happened, and nosotros know Trx(2) started 9 seconds ago. We tin can run mysqlbinlog on the right binlog file and expect for statements with thread_id = 155097580. It is e'er practiced to then cross reference the statements with the application code to confirm.

$ mysqlbinlog -vvv --outset-datetime="2014-10-13 6 : 06 : 12" --cease-datatime="2014-ten-13 vi : 06 : 22" mysql-bin . 000010 > binlog_1013_0606 .out

With Percona Server 5.5 and in a higher place, y'all can set up log_slow_verbosity to include InnoDB transaction id in the slow log. Then if yous accept long_query_time = 0, yous would be able to catch all statements including those rolled dorsum into the ho-hum log file. With general query log, the thread id is included and could be used to await for related statements.

How to avoid a MySQL deadlock

There are things nosotros could do to eliminate a deadlock later we understand it.

– Make changes to the application. In some cases, y'all could greatly reduce the frequency of deadlocks by splitting a long transaction into smaller ones, and then locks are released sooner. In other cases, the deadlock rises because two transactions touch on the aforementioned sets of data, either in one or more tables, with different orders. And so alter them to access information in the same society, in some other give-and-take, serialize the access. That way you would have lock wait instead of deadlock when the transactions happen concurrently.

– Make changes to the tabular array schema, such every bit removing strange key constraint to detach two tables, or adding indexes to minimize the rows scanned and locked.

– In example of gap locking, you may change the transaction isolation level to read committed for the session or transaction to avoid information technology. But and then the binlog format for the session or transaction would accept to be ROW or MIXED.

longtwold1986.blogspot.com

Source: https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/

Share this post