How to avoid database locks?

Question

Some database functions, such as SELECT ... FOR UPDATE and ON DELETE CASCADE , are implicitly vulnerable to deadlocks, as the database does not indicate which lock order to use. I found two discussions that hint that this behavior is not defined by the SQL standard, not to mention specific implementations. Thus, I work under the assumption that we cannot control the lock order (at least it is not obvious how to do this).

How should we avoid database locks if we cannot rely on the locking order?

If we should not avoid deadlocks (you have to fight very hard to convince me of this), then what should we do?

This question is for database agnostic, so please do not ask me which database I am using.

Answer 1

A few years wiser, I am revising the accepted answer, stating that database locks cannot be prevented .

If you are fortunate enough to break up database operations in order to interact with only one table at a time (which is not always possible), you are forced to choose between low performance and the possibility of deadlocks. Choose your poison.

Answer 2

Just do not use those functions that may cause deadlocks. ON DELETE CASCADE can be rewritten in such a way as to force order and thus avoid deadlocks.

SELECT ... FOR UPDATE specifically designed so you can avoid locks - it allows you to select and lock a row so that you can maintain a consistent order for all threads.

You need to be careful how you use it, this can lead to a deadlock if you do not understand the blocking order of all your updates.