I went through MySQL logs and found the reason for deadlock
1st thread is trying to execute below query. Which is waiting for thread 2.
UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
The result of inner query (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19)
) is M_ID = 3562
.
2nd thread is executing below query.
DELETE FROM M_SAMP WHERE M_ID=3455
There is no foreign key
relation defined in M_SAMP and MM_RVW_SAMP tables. And M_ID is primary key of M_SAMP table. And engine is InnoDB
. And this issue is repeating.
Can any one help me, how locks are granted because of which deadlock is happening?
Logs
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-23 10:52:28 de8
*** (1) TRANSACTION:
TRANSACTION 13275344, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 25 lock struct(s), heap size 2408, 1394 row lock(s), undo log entries 1
MySQL thread id 1455, OS thread handle 0x11ac, query id 39660 xyz 192.168.1.108 userName Sending data
UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275344 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 13275335, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
254 lock struct(s), heap size 27512, 21595 row lock(s), undo log entries 7
MySQL thread id 1458, OS thread handle 0xde8, query id 39664 xyz 192.168.1.108 userName updating
DELETE FROM M_SAMP WHERE M_ID=3455
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 61569 page no 65 n bits 176 index `PRIMARY` of table `dbName`.`m_samp` trx id 13275335 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
Thank you.
Update:
Number of records in M_SAMP
table is 3524
and in MM_RVW_SAMP
is 2
.
Below is the result of EXPLAIN
query.
EXPLAIN UPDATE M_SAMP SET MM_Q_IND=1 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))
Best Answer
So, I will answer the question "why it is creating problem with whole query":
In MySQL's
REPEATABLE-READ
transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking).As MySQL happens to be executing the read on the first table using an index scan, what MySQL considers a gap is probably larger than expected, as it uses the primary key to check that no newer rows are inserted that could affect the query with that particular scan type. As a result, a large part of the table may be affected.
Reducing the transaction-isolation mode should avoid the gap-locking and only lock the specific row being updated and improve the general concurrency while avoiding deadlocks as only one row is affected for that particular query.
Update: It seems that the lower isolation level doesn't fix the locking for the update + subselect. Rewriting it as a JOIN does actually do the trick? (Maybe the UPDATE has a different optimization than the SELECT?). The idea of the JOIN rewrite is doing something like this: