Deadlocks workaround?

[Fredrik M?ller]

Hi All,

I have read about deadlocks here on Google and I was surprised to read
that an update and a select on the same table could get into a
deadlock because of the table's index. The update and the select
access the index in opposite orders, thereby causing the deadlock.
This sounds to me as a bug in SQL Server!

My question is: Could you avoid this by reading the table with a
'select * from X(updlock)' before updating it? I mean: Would this
result in the update transaction setting a lock on the index rows
before accessing the data rows?

Merry Christmas!
/Fredrik Möller

[Erland Sommarskog]

louis nguyen (lo************@hotmail.com) writes:

In the example you posted, I typically use a "set transaction" option.
My understanding is that this would prevent all shared locks. What
is your opinion (pros/cons) of this? Thanks, Louis.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
INSERT tbl (id, ...) VALUES (@id, ...)
COMMIT TRANSACTION

This is very likely to cause deadlocks. The isolation level does not
affect the ability to get shared locks. It only affects what you can
see if you issue the same statement later in the query.

Try this:

CREATE TABLE tbl (id int NOT NULL)
go
DECLARE @id int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl
WAITFOR DELAY '00:00:10'
INSERT tbl (id) VALUES (@id)
COMMIT TRANSACTION

First create the table, then run the batch from two windows. You will
get a deadlock. Add "WITH (UPDLOCK)" after the table, and both
batches will succeed.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[louis nguyen]

Hi Erland,

Wow. I learned my lesson. Thanks, Louis.

[Fredrik M?ller]

Hi Erland and Louis,
The example you provided was very enlightening in showing the
difference between UPDLOCK and HOLDLOCK/SERIALIZABLE. Thank you!
As to the locking of index and data rows I might come back later with
an example illustrating the problem.
Regards
Fredrik