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

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.