Locking order in a nested query

Question

I'm trying to prevent deadlocks in my code because i may use both in different parts of my project.

Do they lock rows in the same order?

1. Nested

   select * from `carts`
   where `carts`.`user_id` = ? and exists (
       select * from `cart_items` where `carts`.`id` = `cart_items`.`cart_id` for update
   ) for update
   

2. Separate

   select * from `carts` where `carts`.`user_id` = ? for update
   

   select * from `cart_items` where `cart_id` = ? for update
   

Answer 1

Assuming the START TRANSACTION ie before it/them and the COMMIT is after, think of it this way: The locking starts at the time of the START.

Or this way... If two transactions are doing

UPDATE ... WHERE id IN (11,22)

one can wait for the other to finish (cf lock_wait_timeout).

If, instead, another did WHERE id (22, 11), there could be a deadlock. In that case, one would be rolled back, and the other would continue to completion.

I suspect that a statement with a nested EXISTS is faster than two statements, so I would try to do the former in both cases.

Also, be aware, that sometimes "gaps" around ids are locked. And sometimes the gap locking either does not make sense or does not show up clearly in SHOW ENGINE INNODB STATUS;.

Bottom line: plan for the worst. Be ready to replay any transaction that was rolled back due to a deadlock.