How to avoid database deadlocks?

sql select cascading-deletes database-deadlocks
Gili picture Gili · Jan 3, 2013 · Viewed 7.2k times · Source

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.

Answer

Hogan picture Hogan · Jan 15, 2013

Just don't use those features which can cause deadlocks. ON DELETE CASCADE can be re-written in a way that forces an order and thus avoids deadlocks.

SELECT ... FOR UPDATE is specifically designed to allow you to avoid locks -- it lets you select and lock a row so you can keep a consistent order on all threads.

You do have to be careful how you use it, it could cause a deadlock if you don't understand the locking order of all your updates.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL SELECT WHERE field contains words

I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More