Deadlock between Update and Insert queries

9
votes

I have faced a deadlock in my application between an Update and an Insert query and I am unable to understand why locks are given in a way which causes deadlock.

Environment-

  • Application - Django
  • Database - MySQL 5.7
  • Engine - Innodb
  • Isolation Level - READ COMMITTED.
  • Tables (names changed for security)-
    1. M - primary key - id
    2. MSC - has a foreign-key to M.id
      • Indexes on MSC
        1. Index on M(FK)
        2. Index on S(FK)
        3. Index on C(FK)
        4. Index on unique together constraint (M, S, C)

Queries- Following two queries (Queries truncated to show only relevant columns)-

  • Update-

    UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362

  • Insert-

    INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd')

Deadlock-

  • First the update query is triggered and then insert query is triggered but the output of SHOW ENGINE INNODB STATUS\G; shows that insert query initiated earlier.
  • From the output, timing of their execution seems to be in below manner causing the deadlock-
    • Insert gets the exclusive(X) lock on MSC and waiting for shared(S) lock on foreign key M.
    • Update gets the exclusive(X) lock on M and waiting for exclusive(X) lock on foreign key MSC.
  • Following is the full output-

    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2017-03-17 15:41:03 0x7f8039550700
    * (1) TRANSACTION:
    TRANSACTION 7784084, ACTIVE 2 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 11 lock struct(s), heap size 1136, 46 row lock(s), undo log entries 25
    MySQL thread id 493648, OS thread handle 140188693010176, query id 55263589 ip-10-198-7-203.ec2.internal 10.198.7.203 root update
    INSERT INTO MSC (m_id, s_id, c_id) VALUES (110, 1235, '9b39cd')
    * (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 1377 page no 10 n bits 152 index PRIMARY of table "db"."M" trx id 7784084 lock mode S locks rec but not gap waiting
    Record lock, heap no 67 PHYSICAL RECORD: n_fields 42; compact format; info bits 0
     0: len 4; hex 800000ac; asc     ;;
     1: len 6; hex 00000076c69f; asc    v  ;;
     2: len 7; hex 76000001cb24c5; asc v    $ ;;
     3: len 8; hex 999be72e2e07032e; asc    ..  .;;
     4: len 8; hex 999c22fa43025221; asc   " C R!;;

*** (2) TRANSACTION:
TRANSACTION 7784095, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 493645, OS thread handle 140188694415104, query id 55263635 ip-10-198-3-73.ec2.internal 10.198.3.73 root updating
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE `MSC`.`id` = 54362
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1377 page no 10 n bits 152 index PRIMARY of table "db"."M" trx id 7784095 lock_mode X locks rec but not gap
Record lock, heap no 67 PHYSICAL RECORD: n_fields 42; compact format; info bits 0
 0: len 4; hex 800000ac; asc     ;;
 1: len 6; hex 00000076c69f; asc    v  ;;
 2: len 7; hex 76000001cb24c5; asc v    $ ;;
 3: len 8; hex 999be72e2e07032e; asc    ..  .;;
 4: len 8; hex 999c22fa43025221; asc   " C R!;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1410 page no 261 n bits 104 index PRIMARY of table "db"."MSC" trx id 7784095 lock_mode X locks rec but not gap waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 16; compact format; info bits 0
 0: len 4; hex 800038e2; asc   8 ;;
 1: len 6; hex 00000076c694; asc    v  ;;
 2: len 7; hex 6f0000055b2a0e; asc o   [* ;;
 3: len 8; hex 999c22fa0d08a51c; asc   "     ;;
 4: len 8; hex 999c22fa3b0dffd8; asc   " ;   ;;

*** WE ROLL BACK TRANSACTION (2)

Questions- I am unable to understand the followings- 1. Why did the update query have to wait and couldn't get the locks when insert query got one? 2. Why does the update query needs/takes exclusive(X) lock on M table.

Please share your thoughts here. Let me know if any extra info is required.

2
mysqldjangodatabasedeadlock
Saurabh, I'm not a MySQL expert but the output suggests that at least the transaction #1 does more than just this one query: see 46 row lock(s), undo log entries 25 which is quite strange for a single INSERT. So should guess what scenario results in this INSERTquery and analyze whole transaction. The same might be the case for the second transaction. When you see all queries in both transactions, it will be easier to understand what actually happens. P.S. Is the output in the question a full output or was there anything else? That conflict alone seems not enough for a deadlock. – SergGr
i am also not so expert in lock and isolation level.but dealock in your case is well understood.One sql have acquire exclusive lock oncolumn which other sql is waiting for and vice versa.I doubts the FK-PK constraint.Want to see if it is cyclic or circular ?these table relation in other table and whether any cascade is define. – KumarHarsh
i was also thinking on same line i.e. writing 2 statement in 2 diff trans.But still I would probe other thing.Becasue your problem isn't over. – KumarHarsh
Please provide all the indexes for MSC. Please provide the rest or the queries between the BEGIN and the queries that got in trouble. – Rick James
Do you have any stored procedures that could be causing the issue based on insert and/or update? Also are you sure based on your keys that your update is not causing a collision based on the key uniqueness? – Shawn

2 Answers

3
votes

Does the id value of 110 exist in the M table? Also it may be useful to wrap these individual transactions in START TRANSACTION; and COMMIT; commands to ensure the insert completes before the update tries to run.

Example:

START TRANSACTION;
INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd')
COMMIT;

START TRANSACTION;
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1234, `c_id` = '9b39cd', WHERE 
`MSC`.`id` = 54362
COMMIT;
0
votes

As said @SergGr your two queries cant cause deadlock. But possible next situation. For example, we have next records in MSC table:

id      m_id  s_id  c_id
54362   109   1235  9b39cd

Now we trying to run next queries in parallel (I changed your update and wrote 1235 instead of 1234):

UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', 
WHERE `MSC`.`id` = 54362;

INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');

We must have problem with unique index on (m_id, s_id, c_id).

Update and insert may start in parallel, because there is no problem with constraint before start of execution. But queries cant finished, because they both must produce equal lines and they must be conflicted with unique constraint.

To avoid this situation you may use forced locks. For example, 

START TRANSACTION;
SELECT * FROM M WHERE id = 110 FOR UPDATE;
UPDATE `MSC` SET `m_id` = 110, `s_id` = 1235, `c_id` = '9b39cd', 
WHERE `MSC`.`id` = 54362;
COMMIT;

START TRANSACTION;
SELECT * FROM M WHERE id = 110 FOR UPDATE;
INSERT INTO `MSC` (`m_id`, `s_id`, `c_id`) VALUES (110, 1235, '9b39cd');
COMMIT;

I dont like similar locks because after that problem may be solved here but moved into up level. If it is possible, revise your database schema or algorithm. May be you will find more elegancy way to store and update your data without probability of deadlocks.