Gap locking DeadLocks

MySQL & MariaDB Other MySQL® Questions
1

Hi !
I’m using MySQL 5.5 INNODB, on Amazon RDS ,
recently almost every day I got a deadlock.
When I check the server with ‘SHOW ENGINE INNODB STATUS’ , I see that the deadlock caused by 2 transactions :

LATEST DETECTED DEADLOCK

141230 1:09:27
*** (1) TRANSACTION:
TRANSACTION 69880, ACTIVE 80 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 10852 lock struct(s), heap size 981432, 401304 row lock(s)
MySQL thread id 217497, OS thread handle 0x2b30ef700, query id 1429148
INSERT INTO tmp_table
SELECT x,
y,
FROM PRTable
WHERE
date > DATE_SUB(now(), INTERVAL 31 DAY)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2565513 page no 383779 n bits 120 index PRIMARY of table A3UPWUL52.PRTable
trx id 692BA80 lock mode S waiting
Record lock, heap no 41 PHYSICAL RECORD: n_fields 49; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 69E831, ACTIVE 7 sec inserting
mysql tables in use 5, locked 5
117 lock struct(s), heap size 14776, 2411 row lock(s), undo log entries 792
MySQL thread id 23332, OS thread handle 0x2b2ac2f700, query id 1432667 Sending data

insert into PRTable (…)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 256213 page no 323779 n bits 120 index PRIMARY of table A3UPWUL52.
PRTable trx id 6922A80 lock_mode X locks rec but not gap
Record lock, heap no 41 PHYSICAL RECORD: n_fields 49; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 275513 page no 383779 n bits 128 index PRIMARY of table A3UPWUL52.
PRTable trx id 6922A80 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 41 PHYSICAL RECORD: n_fields 49; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

I read about gap-locking and tried to set isolation in amazon parameter group to READ-COMMITTED , but it didn’t help.
The variable innodb_locks_unsafe_for_binlog is uneditable in amazon , so I can’t change it to 1.
What Can I do to solve it? There is option that MySQL restart the transaction after the ROLL BACK done?

Thank you!!

2

Hi talmo11,

It’s a classic case where transactions starts fighting to acquire an exclusive lock (X) on a specific object and the trx that has manipulating less bytes within its execution will be rolled back. I think and I suggest you that it’s better to identify what is causing this deadlock, investigating your app flow and fix it. Another thing you can do is to raise up an exception every time a transaction is rolled back. Doing like that you’ll have an opportunity to reexecute that failed transaction, guarantying that databases are not inconsistent.

Things you can do to [COLOR=#FF0000]TEMPORARILY mitigate the problem:

You can raise up the innodb_lock_wait_timeout, but, it won’t fix the problem. If the problem is with a transaction that’s coming from the replication threads, you can raise up the value of slave_transaction_retries to try to reexecute the transaction more times until it get properly executed.

=> [URL=“MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables”]http://dev.mysql.com/doc/refman/5.5/...k_wait_timeout[/URL]
=> [URL=“http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#sysvar_slave_transaction_retries”]http://dev.mysql.com/doc/refman/5.5/...action_retries[/URL]

3

Hi Thank you very much!
Actually I know what causing the deadlock . I run 2 Stored Procedures , the first houry , and the second daily.
When they run together sometimes I got a deadlock.
I see that the ‘innodb_lock_wait_timeout’ parameter set to 120 .(sec?) , but when I run manually both of the SP and get deadlock , MySQL immediately do ROLL Back without waiting.

4

Both are different things

One of them is a trx waiting in line to acquire a lock on a specific object locked by another; it’s going to wait until it receive a timeout (innodb_lock_wait_timeout).

The other is, when InnoDB detects a deadlock, the trx that manipulate less bytes is rolled back immediately.

5

Hi Thanks again,
I raised the parameter ‘innodb_lock_wait_timeout’ , but still get deadlocks.
I also try to change to value of 'slave_transaction_retries’ , but it doesn’t appear in amazon rds parameter group , so I can’t set it .
Maybe do you have more recommendations?

Thank you!

6

Hi talmo11;

Since your deadlocks are happening every time, increasing any of the timeout settings is likely just going to make your app hang and not help any. Note the below:

“The lock wait timeout value does not apply to deadlocks, because InnoDB detects them immediately and rolls back one of the deadlocked transactions.”

[url]MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables

That is why you are seeing the rollback right away; MySQL knows it’s going to fail, so it just returns immediately.

What you need to do is fix the issue with your app (and/or stored procedures) to prevent the deadlocks to begin with as WB mentioned above.

Below are a couple links with good information on deadlocks (including additional debug information) and conceptually how to avoid them:

[url]http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html[/url]
[url]http://www.percona.com/blog/2014/10/28/how-to-deal-with-mysql-deadlocks/[/url]

-Scott