MySQL: SELECT ... FOR UPDATE creates unexpected deadlock

Question

Our application performs multiple identical requests using SELECT … FOR UPDATE and ends-up deadlocking each other. I thought using FOR UPDATE should serialize the request, forcing the second one to wait for the first one to finish and to start after, creating small contention, but avoiding at least deadlock.

SELECT id, namespace_id, name, type, parent_property_id, custom_scope_type, metadata, state, created_at, updated_at
FROM property
WHERE (namespace_id = '3' AND id = '1100')
   OR (namespace_id = '3' AND id = '1103')
   OR (namespace_id = '3' AND id = '3100')
   OR (namespace_id = '3' AND id = '1110')
   OR (namespace_id = '3' AND id = '1113')
   OR (namespace_id = '3' AND id = '1112')
   OR (namespace_id = '3' AND id = '1111')
   OR (namespace_id = '3' AND id = '3140')
   OR (namespace_id = '3' AND id = '1104')
FOR UPDATE

When I look at the logs, it seems like even though we used FOR UPDATE, the row was first acquired with a "SHARED" lock, and then for each request it tried to promote from "SHARED" to "EXCLUSIVE" lock, but then a deadlock happen because 2 requests already have a SHARED lock on it. Is there a way to force FOR UPDATE to take directly an EXCLUSIVE lock which would have prevented this issue?

*** (1) TRANSACTION:
TRANSACTION 38289, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1128, 9 row lock(s), undo log entries 7
MySQL thread id 53132, OS thread handle 123145496965120, query id 379474 localhost 127.0.0.1 uber executing
SELECT id, namespace_id, name, type, parent_property_id, custom_scope_type, metadata, state, created_at, updated_at FROM property WHERE (namespace_id = '3' AND id = '3180') OR (namespace_id = '3' AND id = '1100') OR (namespace_id = '3' AND id = '3181') OR (namespace_id = '3' AND id = '3140') OR (namespace_id = '3' AND id = '3121') OR (namespace_id = '3' AND id = '3150') FOR UPDATE

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 166 page no 4 n bits 120 index PRIMARY of table `my_database`.`property` trx id 38289 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 4; hex 8000044c; asc    L;;
 1: len 4; hex 80000003; asc     ;;
 2: len 6; hex 000000008f27; asc      ';;
 3: len 7; hex 81000000980110; asc        ;;
 4: len 30; hex 6261636b77617264735f636f6d70617469626c655f726567696f6e5f6964; asc backwards_compatible_region_id;;
 5: len 30; hex 50524f44554354535f50524f50455254595f545950455f524547494f4e5f; asc PRODUCTS_PROPERTY_TYPE_REGION_; (total 32 bytes);
 6: len 4; hex 80000064; asc    d;;
 7: len 4; hex 6e6f6e65; asc none;;
 8: len 18; hex 7b226973496e6465786564223a747275657d; asc {"isIndexed":true};;
 9: len 4; hex 648158b9; asc d X ;;
 10: len 4; hex 648158b9; asc d X ;;
 11: len 6; hex 414354495645; asc ACTIVE;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 166 page no 4 n bits 120 index PRIMARY of table `my_database`.`property` trx id 38289 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 4; hex 8000044c; asc    L;;
 1: len 4; hex 80000003; asc     ;;
 2: len 6; hex 000000008f27; asc      ';;
 3: len 7; hex 81000000980110; asc        ;;
 4: len 30; hex 6261636b77617264735f636f6d70617469626c655f726567696f6e5f6964; asc backwards_compatible_region_id;;
 5: len 30; hex 50524f44554354535f50524f50455254595f545950455f524547494f4e5f; asc PRODUCTS_PROPERTY_TYPE_REGION_; (total 32 bytes);
 6: len 4; hex 80000064; asc    d;;
 7: len 4; hex 6e6f6e65; asc none;;
 8: len 18; hex 7b226973496e6465786564223a747275657d; asc {"isIndexed":true};;
 9: len 4; hex 648158b9; asc d X ;;
 10: len 4; hex 648158b9; asc d X ;;
 11: len 6; hex 414354495645; asc ACTIVE;;

*** (2) TRANSACTION:
TRANSACTION 38290, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1128, 9 row lock(s), undo log entries 7
MySQL thread id 53131, OS thread handle 123145495900160, query id 379475 localhost 127.0.0.1 uber executing
SELECT id, namespace_id, name, type, parent_property_id, custom_scope_type, metadata, state, created_at, updated_at FROM property WHERE (namespace_id = '3' AND id = '3180') OR (namespace_id = '3' AND id = '1100') OR (namespace_id = '3' AND id = '3181') OR (namespace_id = '3' AND id = '3140') OR (namespace_id = '3' AND id = '3121') OR (namespace_id = '3' AND id = '3150') FOR UPDATE

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 166 page no 4 n bits 120 index PRIMARY of table `my_database`.`property` trx id 38290 lock mode S locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 4; hex 8000044c; asc    L;;
 1: len 4; hex 80000003; asc     ;;
 2: len 6; hex 000000008f27; asc      ';;
 3: len 7; hex 81000000980110; asc        ;;
 4: len 30; hex 6261636b77617264735f636f6d70617469626c655f726567696f6e5f6964; asc backwards_compatible_region_id;;
 5: len 30; hex 50524f44554354535f50524f50455254595f545950455f524547494f4e5f; asc PRODUCTS_PROPERTY_TYPE_REGION_; (total 32 bytes);
 6: len 4; hex 80000064; asc    d;;
 7: len 4; hex 6e6f6e65; asc none;;
 8: len 18; hex 7b226973496e6465786564223a747275657d; asc {"isIndexed":true};;
 9: len 4; hex 648158b9; asc d X ;;
 10: len 4; hex 648158b9; asc d X ;;
 11: len 6; hex 414354495645; asc ACTIVE;;

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 166 page no 4 n bits 120 index PRIMARY of table `my_database`.`property` trx id 38290 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 4; hex 8000044c; asc    L;;
 1: len 4; hex 80000003; asc     ;;
 2: len 6; hex 000000008f27; asc      ';;
 3: len 7; hex 81000000980110; asc        ;;
 4: len 30; hex 6261636b77617264735f636f6d70617469626c655f726567696f6e5f6964; asc backwards_compatible_region_id;;
 5: len 30; hex 50524f44554354535f50524f50455254595f545950455f524547494f4e5f; asc PRODUCTS_PROPERTY_TYPE_REGION_; (total 32 bytes);
 6: len 4; hex 80000064; asc    d;;
 7: len 4; hex 6e6f6e65; asc none;;
 8: len 18; hex 7b226973496e6465786564223a747275657d; asc {"isIndexed":true};;
 9: len 4; hex 648158b9; asc d X ;;
 10: len 4; hex 648158b9; asc d X ;;
 11: len 6; hex 414354495645; asc ACTIVE;;

*** WE ROLL BACK TRANSACTION (2)

Note that I'm totally fine with the contention and for one request to be delayed by the other one, I just don't understand why it creates a deadlock.

---

FYI:

In that transaction, before I'm doing an INSERT INTO to a * separate table *, but the row that I'm inserting in that other table has a FK to the rows I'm trying to select here in this table in a subsequent request. Could it be related? Could it be that the INSERT INTO to Table A actually lock as the rows in Table B (FOR SHARE) because of the FK?

Answer 1

Possibly the other queries in the same transaction that came before this SELECT are contributing to the deadlock. What were they?

It would probably help if you could change the WHERE clause to

WHERE namespace_id = 3
  AND id IN (1100, 1103, ...)

And have INDEX(namespace_id, id).

It would be wise to sort the numbers. At a low level, each row will be locked one at a time. Let's say one thread has locked item 123 and is about to lock 234. And another thread has locked 234 and is about to lock 123 -- Well, that is the classic deadlock. If the numbers were in the same order, then one thread will go straight through while the other waits.

(I think the Optimizer will sort items in IN, but not is your OR example. But I am not sure.)