How to avoid database deadlocks?

Some database features, such as SELECT ... FOR UPDATE and ON DELETE CASCADE, are implicitly vulnerable to deadlocks because the database does not specify what locking order will be used. I found two discussions that hint that this behavior isn’t specified by the SQL standard, not to mention specific implementations. As such, I’m operating under the assumption that we cannot control the locking order (at least, it’s not obvious how to do so).

How are we supposed to avoid database deadlocks if we cannot rely on the locking order?

If we’re not supposed to avoid deadlocks (you’re going to have to fight very hard to convince me of this) then what are we supposed to do?

This question is meant to be database-agnostic so please don’t ask me which database I’m using.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>