I plan to write a series of deadlock analysis examples, which will be usually encountered deadlock examples recorded, good record, also as accumulation.

Deadlock output

2017-10-10 17:07:21 7f45a5104700InnoDB: transactions deadlock detected, dumping detailed information.
2017-10-10 17:07:21 7f45a5104700
*** (1) TRANSACTION:
TRANSACTION 47225424098, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 40396441, OS thread handle 0x7f569a68e700, query id 9746347697 10.200.181.72 trade updating
update table_b
        set updated_at = now(),
         price = 36900,
        where id = 1 and sku_id = 36171933 AND goods_id = 2
        and kdt_id = 3 and offline_id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424098 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 47225424090, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 13 row lock(s), undo log entries 1
MySQL thread id 40397515, OS thread handle 0x7f45a5104700, query id 9746347700 10.200.181.72 trade updating
update table_a
        set updated_at = now(),
        stock_num = 0,
        where goods_id = 2
        and offline_id = 1
        and kdt_id = 3
        and id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424090 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13451 page no 193 n bits 192 index `PRIMARY` of table `dbname`.`table_a` trx id 47225424090 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
Copy the code

The index of the table_a

UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`),
Copy the code

The index of the table_b

UNIQUE KEY `uniq_gid_oid_sid` (`goods_id`,`offline_id`,`sku_id`)
Copy the code

Specific table names and key information have been desensitized, using table_A, table_B.

Deadlock analysis

To understand deadlocks, you must first understand the specific transaction logic, so communicate with the developer about the logical process of the transaction

  • First, a session is started to query table TABLE_A based on (goods_id,offline_id) to see if corresponding records exist. If yes, perform step 2; if no, perform Step 3
  • Open another transaction, Select * from table_A where goods_id=xx and offline_id=yy for update and update table_B Table corresponding to (goods_id,offline_id,sku_id) and then update table table_A again (by ID)
  • Select * from table_A where goods_id=xx and offline_id=yy Update table_A (by ID); otherwise, insert table_B + insert table_A

Look at the deadlock output wait + business operation process, draw wait matrix graph.

The whole wait is shown in the above TABLE. Sess 1 updates TABLE B at @t4, and the wait occurs because Sess 2 updates TABLE B at @t3. Sess 2 updates TABLE_A at @t5 because Sess 1 updates at @t2.

Sess 1 cannot obtain the X lock of TABLE A after Sess 2 performs A FOR UPDATE on Table_A.

Analyzing the business logic again, we found that in the operation process of the third step, if the first query does not exist, the results of the second query and the first query may be inconsistent, that is to say, records may be queried in the transaction. SELECT TABLE_A from TABLE_A where TABLE_A is not used FOR UPDATE.

Then the entire execution process should be as follows:

Sess1 of @T4 is waiting for Sess2 of @t3, and Sess2 of @T5 is waiting for Sess1 of @T2, forming a typical cross wait. Sess2 does not execute FOR UPDATE.

The entire business logic is:

  • Sess 2 query finds no record and starts a transaction
  • Sess 1 query found records (other session inserts), start a transaction, execute FOR UPDATE
  • Sess 2 performs the update operation on table B
  • Sess 1 perform the update operation on table A… So how do you avoid this classic deadlock?
  • Modify the service logic. When querying table A for the first time, if A record is found, A FLAG can be passed to the transaction. Then the transaction will perform the insert operation
  • SQL > alter table A for update, then update A, then update B, the entire logic of the operation is always A, B, do not form A deadlock because the execution order is not the same

summary

Deadlock analysis, must be combined with the business execution process, otherwise imagine speculation, brain cells are not enough ha ha.

Source |urlify.cn/VzAbMj