MySQL Deadlock - accessing different primary key values also creating deadlock

Question

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))

Answer 1

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:

UPDATE M_SAMP M 
JOIN MM_RVW_SAMP MM 
ON M.M_ID = MM. M_ID 
SET M.MM_Q_IND = 1 
WHERE MM.TARGET_M_ID = 19;