Where deadlock happen in mysql

Question

I am unable to read below logs of mysql after executing SHOW ENGINE INNODB STATUS command. Can anyone please help me out where the deadlock has happen?

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-10 11:39:35 7f12f044d700
*** (1) TRANSACTION:
TRANSACTION 364307, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 1066 lock struct(s), heap size 112168, 71366 row lock(s), undo log entries 12
MySQL thread id 16407, OS thread handle 0x7f12f28a3700, query id 176797 localhost 127.0.0.1 synapqadb updating
DELETE FROM E_CUST WHERE M_ID=7419
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364307 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800000e8; asc     ;;
 1: len 6; hex 00000000196a; asc      j;;
 2: len 7; hex f2000001a00110; asc        ;;
 3: len 4; hex 80000053; asc    S;;
 4: len 7; hex 43555354303038; asc CUST008;;
 5: len 14; hex 435553545f504f5354414c5f4344; asc CUST_POSTAL_CD;;

*** (2) TRANSACTION:
TRANSACTION 364314, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
259 lock struct(s), heap size 46632, 77517 row lock(s), undo log entries 7
MySQL thread id 16405, OS thread handle 0x7f12f044d700, query id 176810 localhost 127.0.0.1 synapqadb updating
UPDATE R_CUST SET RES_LABEL= '' , DESCRIPTION='Postal Code Missing|Address, Phone And Email Are Invalid / Missing|Error Record From Wiley', COMMENTS=CONCAT(IFNULL(COMMENTS,''),'') WHERE STATUS='0' AND TYPE='DATA VALIDATION' AND M_ID=19191
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 139 page no 4 n bits 384 index `PRIMARY` of table `xyz`.`E_CUST` trx id 364314 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Thank you.

Answer 1

In short: SHOW INNODB STATUS will give you the last detected deadlock. use SHOW ENGINE INNODB STATUS for MySQL 5.x

Line 1 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 application log. You would have the transaction that got rolled back. From there, retrieve all statements from that transaction.

Line 3 & 11, take note of Transaction number and ACTIVE time. 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. The ACTIVE sec gives a hint on whether the transaction is a single statement or multi-statement one.

Line 4 & 12, the tables in use and locked are only with respect to the current statement. So having 1 table in use does not necessarily mean that the transaction involves 1 table only.

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)”. These info hints the complexity of the transaction.

Line 6 & 14, take note of thread id, connecting host and connecting user. 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 9, for the first transaction, it only shows the lock it was waiting for, in this case the AUTO-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, it shows the lock(s) it held, in this case the AUTO-INC lock which was what TRANSACTION (1) was waiting for.

How to avoid a MySQL deadlock There are things we could do to eliminate a deadlock after we understand it.

– Make changes to the application. In some cases, you could greatly reduce the frequency of deadlocks by splitting a long transaction into smaller ones, so locks are released sooner. In other cases, the deadlock rises because two transactions touch the same sets of data, either in one or more tables, with different orders. Then change them to access data in the same order, in another word, serialize the access. That way you would have lock wait instead of deadlock when the transactions happen concurrently.

– Make changes to the table schema, such as removing foreign key constraint to detach two tables, or adding indexes to minimize the rows scanned and locked.

– In case of gap locking, you may change transaction isolation level to read committed for the session or transaction to avoid it. But then the binlog format for the session or transaction would have to be ROW or MIXED.

Answer 2

SHOW CREATE TABLE E_CUST;

I'm guessing that R_CUST is a VIEW, and the underlying E_CUST does not have a good INDEX for the UPDATE. Keep in mind that VIEWs do not optimize well.

I would guess from the number of locks, that M_ID is not indexed.