2

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

EXPLAIN QUERY RESULT

  • Please provide your indexes for those tables (SHOW CREATE TABLE) and your GLOBAL transaction-isolation value. – jynus Feb 23 '15 at 11:13
  • There are no indexes on those tables other then the mentioned primary key. And tx_isolation is REPEATABLE-READ – Naman Gala Feb 23 '15 at 11:21
  • Ok, then, is this 5.5 or lower? Because in that case, the REPEATABLE-READ + FULL table scan due to the IN would be the root cause. – jynus Feb 23 '15 at 11:30
  • Server version: 5.6.16 – Naman Gala Feb 23 '15 at 11:34
  • 1
    Then please EXPLAIN the first query, maybe it is still doing the scan. In that case, setting the mode READ-COMMITTED or doing a JOIN would also fix the issue. – jynus Feb 23 '15 at 11:38
2

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;
| improve this answer | |
  • Trying your solution of reducing the transaction-isolation mode. – Naman Gala Feb 24 '15 at 10:37
  • After reducing to READ_COMMITTED also, I was getting deadlock. So I used your other solution of doing JOIN and it reduced the table scan which solved my problem. So my new working query is UPDATE M_SAMP M INNER JOIN MM_RVW_SAMP RV ON M.M_ID = RV.M_ID SET M.MM_Q_IND=1 WHERE RV.TARGET_M_ID IN(19). Can you please include your JOIN related suggestion in your answer. Thanks for the help. – Naman Gala Feb 25 '15 at 12:22
  • @NamanGala I've done it as requested. – jynus Feb 25 '15 at 18:26
  • On changing it to READ_COMMITTED also, update is scanning same as REPEATABLE_READ. I guess in that case also it is locking all the indexes which are scanned. Causing deadlock in my case. – Naman Gala Feb 26 '15 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.