How can deadlocks occur if objects are accessed in the same order?

Question

On the MS Technet page on "Minimizing Deadlocks", it says that "deadlocks cannot be completely avoided". More specifically, it states that

If all concurrent transactions access objects in the same order, deadlocks are less likely to occur.

This implies that deadlocks could theoretically occur, even if all transactions access objects in the same order. Could someone give an example of how that would be possible?

Answer 1

Just to provide a simple code example further to Charles's answer

CREATE TABLE T(X INT)

Then run in two concurrent connections

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM T 

WAITFOR DELAY '00:00:10'

INSERT INTO T VALUES(1)

COMMIT

Deadlock.

The issue is that both connections can aquire shared locks on a resource but then block each other when they need to convert that to an exclusive lock.

Answer 2

One way is when using Isolation Level Serializable.

If you have a parent child relationship, as for example in an accounting database with customers, customer accounts, and individual financial debits and credits into those accounts, then:

you have a database transaction consisting of multiple balancing debit and credit entries into the child table, and an accompanying update of an account balance in the customer table.

At isolation level serializable, in order to prevent what are called phantom reads the DB engine places range locks on all records which the SQL statement has to read, (In this case, the credit and debit table). This ensures that no other transaction can insert new credits or debits into the table while this Tx is calculating the new customer balance. Any attempt to insert a new credit or debit is blocked by the lock.

Meanwhile, the first Tx is blocked as well, since the other insert Tx will have placed a lock on the Customer Table row, since it also needs to update the customer Balance. The locks aer not released until the entire Tx is committed, so both Transactions are blocking each other... Deadlock.