Skip to main content
added 255 characters in body
Source Link
Naman Gala
  • 175
  • 1
  • 9

Number of records in M_SAMP table is 3524 and in MM_RVW_SAMP is 2.

Below is the result of explainEXPLAIN query.

Below is the result of explain query.

Number of records in M_SAMP table is 3524 and in MM_RVW_SAMP is 2.

Below is the result of EXPLAIN query.

added 255 characters in body
Source Link
Naman Gala
  • 175
  • 1
  • 9
UPDATE M_SAMP SET FLAG=0MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

Thank you.

Update:

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

UPDATE M_SAMP SET FLAG=0 WHERE M_ID IN (SELECT M_ID FROM MM_RVW_SAMP WHERE TARGET_M_ID IN(19))

Thank you.

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

Thank you.

Update:

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

Source Link
Naman Gala
  • 175
  • 1
  • 9

MySQL Deadlock - accessing different primary key values also creating deadlock

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 FLAG=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.