Deadlock Troubleshooting, Part 2

re: Deadlock Troubleshooting, Part 2

bartduncan — Fri, 04 Dec 2009 00:44:34 GMT

@stephen_or_doug,

First, apologies for the delayed response. If there is an index on column [c1], then SQL will use a very selective index seek and only the rows where [c1]=@p1. But if there is no index that has [c1] as the leading column, SQL will need to scan the table or an index, and it will lock (and hold the lock on) every row that it scans.

USE tempdb;

DROP TABLE t1;

CREATE TABLE t1 (c1 int PRIMARY KEY, c2 int);

INSERT INTO t1 SELECT [object_id], [object_id] FROM master.sys.objects;

BEGIN TRAN;

SELECT OBJECT_ID ('t1')

SELECT COUNT(*) FROM t1 WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE c2 = 5;

EXEC sp_lock;

ROLLBACK;

BEGIN TRAN;

SELECT COUNT(*) FROM t1 WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE c1 = 5;

EXEC sp_lock;

ROLLBACK;

re: Deadlock Troubleshooting, Part 2

stephen_or_doug — Tue, 02 Jun 2009 16:20:58 GMT

(Sorry if I seem to have posted this twice - forgot to log in the first time!)

This is a really useful post. One day the documentation that ships with the SQL server product will be equally clear...

You mention the option of forcing one of the transactions to block early on the process, by doing something like:

SELECT @x = COUNT(*) FROM t1 WITH (HOLDLOCK, UPDLOCK) WHERE c1 = @p1

Would this in general:

a) lock a single set of rows (i.e. the ones where C1=@p1) in the index cidx?

b) effectively place a index wide lock?

If it is a), then the issue I have is that the two processes are independent, (different users logged into different clients!) so the runtime values of @p1 for the two procedures are more than likely to be completely different - in fact the values apply to different columns in the table, so there is no relationship between the parameters for the procedures at all.

In addition, the count(*) is likely to be expensive in some cases - what if there are millions of rows?. If the locking is an index wide lock, would this lock still be held, even if the value for @p1 was chosen such that there we no rows at all where c1=@p1?

These may be silly questions !

This is such black art stuff.

re: Deadlock Troubleshooting, Part 2

Sarabjeet singh Arors — Fri, 06 Mar 2009 02:56:11 GMT

Bart Sir, You are great, and I also worked for Microsoft in old willows building 10525.This was the great help for every Sql lover guys.

Thanks and Keep Posted!!

Bart Duncan's SQL Weblog : Deadlock Troubleshooting, Part 3

Bart Duncan's SQL Weblog : Deadlock Troubleshooting, Part 3 — Tue, 01 May 2007 22:08:13 GMT

PingBack from http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx