Tags

, ,

In my application, I found Query failed several times with this error report.

‘Deadlock found when trying to get lock; try restarting transaction’

I will not discuss about deadlock and why deadlock occurs. We all know that more than one operations in a single resource make deadlock situation.

So, how to fix this?

At first we have to know the details of the deadlock. I know these two ways.

  1. The latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command.
  2. 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.

https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html

You will see like this.

------------------------
LATEST DETECTED DEADLOCK
------------------------
160428 12:13:23
*** (1) TRANSACTION:
TRANSACTION 766ABFF1, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 3, OS thread handle 0x2ab5dc319940, query id 14760240 10.231.0.28 root Searching rows for update
UPDATE smsoutbox SET InstanceID = 28 WHERE schedule < NOW() AND ServiceID = '21270_Game_Factory_Daily' AND srcMN = '21270' AND msgStatus = 'QUE' AND InstanceID IS NULL Order by msgID LIMIT 50
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1662 n bits 168 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFF1 lock_mode X locks rec but not gap waiting
Record lock, heap no 87 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 00034b45; asc   KE;;
 1: len 6; hex 000043ae5f5e; asc   C _^;;
 2: len 7; hex 430000110f1de6; asc C      ;;
 3: len 5; hex 3231323730; asc 21270;;
 4: len 13; hex 38383031393430343939393437; asc 8801940499947;

*** (2) TRANSACTION:
TRANSACTION 766ABFC8, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
27209 lock struct(s), heap size 2652600, 1843804 row lock(s)
MySQL thread id 1, OS thread handle 0x2ab5dc081940, query id 14720720 10.231.0.28 root Updating
UPDATE smsoutbox SET msgStatus = 'que', retryCount = retryCount+1, IsChargingEnable = 0 WHERE msgStatus = 'failed' AND retryCount < 3  and (NOW() - sentTime)> 10 AND InstanceID = 28 AND (CASE WHEN IsChargingEnable = 1 THEN ChargeResponse WHEN IsChargingEnable = 0 THEN '0' END) = '0'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1662 n bits 168 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFC8 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;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 00034af0; asc   J ;;
 1: len 6; hex 000043ae5f5e; asc   C _^;;
 2: len 7; hex 430000110f1445; asc C     E;;
 3: len 4; hex 32353830; asc 2580;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 38404 n bits 120 index `PRIMARY` of table `smsgw_4_0`.`smsoutbox` trx id 766ABFC8 lock_mode X waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 4; hex 0022aa2a; asc  " *;;
 1: len 6; hex 0000766abff2; asc   vj  ;;
 2: len 7; hex c20000019e0110; asc        ;;
 3: len 4; hex 32303038; asc 2008;;

*** WE ROLL BACK TRANSACTION (1)
------------

Here you can find those two queries which have made the deadlock with details ( 2nd transactions holds the lock, 1st transaction waits).

Now where is the problem?

Two queries are different, where conditions are also different. So, two queries should not make deadlock. But it creates deadlock. Now after some studies I found the main reason.

InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. The locks are normally next-key locks that also block inserts into the gap immediately before the record. (http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html)

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

As both of my queries use same index (index ‘Primary’), deadlock is created. So, indexing is the solution. I have added index.

If two queries use different index, they will not make deadlock.

Now how to be sure that two queries are using different index? Use EXPLAIN keyword in front of your queries and see which key is using.

EXPLAIN SELECT * FROM smsoutbox
WHERE SCHEDULE < NOW() AND ServiceID = ‘21270_Game_Factory_Daily’ AND srcMN = ‘21270’ AND msgStatus = ‘QUE’ AND InstanceID IS NULL ORDER BY msgID LIMIT 50.Capture

So, if you are working with InnoDB, you have to think about index to avoid deadlock.

Thank you.

You can find some details from this link also. https://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/

Advertisements