CODEWITHSUNDEEP
mysqltransactionsinnodbdeadlock
Honza Bednář
Honza Bednář

Reputation: 406

How to solve the MySQL deadlock

the following deadlock occurs in MySQL.

*** (1) TRANSACTION:
TRANSACTION 1367965, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 992, 8 row lock(s), undo log entries 4
MySQL thread id 100, OS thread handle 0x1fbc, query id 825084 localhost ::1 root updating
UPDATE `products` SET `quantity`=1333 WHERE (`id` = 3355)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2503 page no 212 n bits 96 index `PRIMARY` of table `emerx2`.`products` trx id 1367965 lock_mode X locks rec but not gap waiting
Record lock, heap no 22 PHYSICAL RECORD: n_fields 66; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 1367967, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
2231 lock struct(s), heap size 125816, 43033 row lock(s)
MySQL thread id 87, OS thread handle 0x3a98, query id 825080 localhost 127.0.0.1 root updating
UPDATE products SET updated = NOW(), active = 1, supplier_id = '109', delivery_date = '2019-02-21 16:00:43', quantity = '1' WHERE supplier_id = '109' AND domain_id = 16

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2503 page no 212 n bits 96 index `PRIMARY` of table `emerx2`.`products` trx id 1367967 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

Any possible solution? I have no idea what to do because following updates don't have anything in common only the table.

Upvotes: 0

Views: 165

Answers (2)

Rick James
Rick James

Reputation: 142208

Simply have INDEX(supplier_id, domain_id). Do not first get the id; let MySQL take care of that. And it will avoid having to scan the table -- which tends to lead to deadlocks or delays.

Upvotes: 1

Honza Bednář
Honza Bednář

Reputation: 406

The problem was that in the update command I used 2 non indexed columns. So now I select primary key by these two columns and then update with the primary key and it works and unexpectedly it's much more faster.

Upvotes: 0

Related Questions