How to resolve SQL Server deadlocks - once changing order and shortening queries is exhausted?

Question

i have two hypothetical queries:

UPDATE BankAccounts SET HomePhone = '[phone]' 
WHERE AccountNumber = 14400000619

and

SELECT * FROM BankAccounts 
WHERE HomePhone = '555-1212'

on a hypothetical table with no extra indexes:

CREATE TABLE BankAccounts 
( 
   AccountNumber bigint NOT NULL PRIMARY KEY CLUSTERED,
   FirstName nvarchar(50) NOT NULL,
   MiddleName nvarchar(50) NULL,
   LastName nvarchar(50) NOT NULL,
   HomePhone varchar(50) NULL,
   IsClosed tinyint DEFAULT 0
)

and everything will be great. If i add an index on HomePhone:

CREATE INDEX IX_BankAccounts_HomePhone ON BankAccounts 
( HomePhone)

Now my SELECT statement can be a deadlock victim:

Tranasction (Process ID 169) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The common suggestions are to:

• access tables in the same order
• keep transactions as short as possible

Except in this case:

• i am access the (one) table in the same order (1 choose 1 is 1)
• the transactions are a single statement; i cannot get any shorter than that

What's the long-term solution to eliminate deadlocks like this?

i was considering changing my transaction isolation level to READ UNCOMMITTED (i.e. eliminating integrity), but because i'm actually dealing with a financial system i'm hesitant to allow a customer to withdraw his entire balance twice.

The only other solution i can find comes from KB Article 83252:

SQL Server technical bulletin - How to resolve a deadlock

...deadlocks cannot be avoided. That is why the front-end application should be designed to handle deadlocks.

In a well designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction.

Which i guess is saying: "can't win; don't try"

Anything else?

Gaw

Answer 1

can't win; don't try

That's right. You're seeing probably a read-write deadlock (w/o the deadlock graph is only speculation on our part). The reverse order access occurs exactly because you're doing the right thing (ie. you do have the right index). Even if you access different keys (look up one phone, update another) you are still playing a game of probabilities due to hash collisions.

You can either accept deadlocks or resort to snapshot isolation model.

Showing

Answer 2

If you replace the SELECT * with a column list (or consider all columns) and then add them to the index as INCLUDEd columns, then the SELECT query will not need to query the clustered index to complete. The SELECT can then always run to completion.

If you don't want to do that, then rather than changing the whole isolation level, I'd consider whether a locking hint on the SELECT is appropriate. Whether the appropriate hint is NOLOCK or READPAST is something you'd need to carefully consider (In an ideal world, there would be a way to specify READPAST but gain information on whether any skipping of rows actually occurred).

Of course, SNAPSHOT isolation might also be considered (if there are no locks, there are no deadlocks).

---

(A glutton for punishment might also consider using TABLOCKX on the SELECT statement. It'll prevent the deadlock and ensure you read a row if it exists, at a massive penalty for concurrency)

Sascha

In large Warehouse applications I've "resolved" some nasty deadlock issues by serializing the offending actions. Each of those actions try to lock the same singular record. I suppose this is equivalent to your TABLOCKX? – Firenze

@VincentVancalbergh - yes, it's effectively a local way of asking for serialization. – Sascha