![]() The current statement of trx(2) is a simple insert to table t1, so 1 and 3 are eliminated. There are only a few sources for a shared record lock in InnoDB:ģ) with INSERT INTO… SELECT, shared locks on source table In this case, it was a shared not gap record lock on another table’s primary key. ![]() Line 18 & 19 shows which lock TRANSACTION (2) was waiting for. Line 16 & 17, for the second transaction, it shows the lock(s) it held, in this case the AUTO-INC lock which was what TRANSACTION (1) was waiting for. Other possible values are S for shared lock and X for exclusive with or without gap locks. Line 9, for the first transaction, it only shows the lock it was waiting for, in this case the AUTO-INC lock on table t1. If you use different MySQL users for different application functions which is another good practice, then you can tell which application area the transaction comes from based on the connecting host and user. Line 6 & 14, take note of thread id, connecting host and connecting user. These info hints the complexity of the transaction. Line 5 & 13, this is worth of attention as it tells how many changes the transaction had made, which is the “undo log entries” and how many row locks it held which is “row lock(s)”. So having 1 table in use does not necessarily mean that the transaction involves 1 table only. Line 4 & 12, the tables in use and locked are only with respect to the current statement. The ACTIVE sec gives a hint on whether the transaction is a single statement or multi-statement one. If you log SHOW ENGINE INNODB STATUS output periodically(which is a good practice), then you can search previous outputs with Transaction number to hopefully see more statements from the same transaction. Line 3 & 11, take note of Transaction number and ACTIVE time. ![]() From there, retrieve all statements from that transaction. You would have the transaction that got rolled back. 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 application log. Line 1 gives the time when the deadlock happened. Example 1:ģ TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lockĥ LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4Ħ MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox updateħ INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello')Ĩ *** (1) WAITING FOR THIS LOCK TO BE GRANTED:ĩ TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waitingġ1 TRANSACTION 876725B2D, ACTIVE 9 sec insertingġ3 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002ġ4 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox updateġ5 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more")ġ7 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INCġ8 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:ġ9 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting Let’s look at two examples to see what information is given. I will show some tips on how to collect the missed statements. What are missed are the earlier statements that might have really acquired the locks. Also it only shows the last statement executed in the two transactions, and locks from the two transactions that created the cycle. How to diagnose a MySQL deadlockĪ MySQL deadlock could involve more than two transactions, but the LATEST DETECTED DEADLOCK section only shows the last two transactions. 1213) and handle it by retrying the transaction. With MySQL 5.6, you can enable a new variable innodb_print_all_deadlocks to have all deadlocks in InnoDB recorded in mysqld error log.īefore and above all diagnosis, it is always an important practice to have the applications catch deadlock error (MySQL error no. ![]() For more information on using pt-deadlock-logger, see this post. But with Percona Toolkit’s pt-deadlock-logger you can have deadlock information retrieved from SHOW ENGINE INNODB STATUS at a given interval and saved to a file or table for late diagnosis. Though an occasional deadlock is not something to worry about, frequent occurrences call for attention.īefore MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. It uses a metric to pick the easiest transaction to rollback. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. In a transaction system, deadlocks are a fact of life and not completely avoidable. A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. ![]()
0 Comments
Leave a Reply. |