Deadlock on simple update related to clustered index

I am encountering a deadlock situation, with two threads
running two updates to the same table, if that table has a
clustered index on it that is NOT the primary key, and the
where clause of the UPDATE statement is on the primary
key. Very strange... Here is the test case:
create table x
(id int not null,
lastModified datetime not null,
altkey varchar(3) not null)
GO
CREATE UNIQUE CLUSTERED INDEX IAK_X ON x (altKey)
GO
alter table x add constraint pk_x primary key(id)
GO
insert into x
values (1, getdate(), 'XXX');
Then, from 2 ISQL threads, ran the following code...
begin tran
waitfor time '15:06' -- ensure they start at same time
update x
set lastModified = getdate()
where id = 1;
waitfor delay '000:00:10' -- wait to ensure lock occurs
update x
set lastModified = getdate()
where id = 1;
commit tran;
When I monitor the locks, the following happens:
Thread A successfully performs Update 1
Thread B is blocked as A has Exclusive lock on the row.
Thread B, however, successfully gets a KEY UPDATE lock on
PK_X
Thread A, when it tries to perform Update 2, is blocked
trying to get a KEY UPDATE lock on PK_X, causing the
deadlock.
Even if I then drop the clustered index with
DROP INDEX x.iak_x;
the problem remains.
However, if I NEVER create the clustered index. The
threads both work fine and never encounter a deadlock.
Neither transaction attempts to get a KEY UPDATE lock on
the primary key index PK_X.
We try to use clustered indexes on fields other than the
ID # (one-up number, but NOT an IDENTITY column in this
case), because we encountered deadlocks on Inserts in the
past.
Any help would be greatly appreciated.On Thu, 24 Jul 2003 14:02:58 -0700, "Corey" <chorton1@.austin.rr.com>
wrote:
>Any help would be greatly appreciated.
That's pretty humorous.
I've never gotten into the detailed guts of SQLServer like some around
here, but it sounds to me like you've hit a SQLServer bug. It looks
like SQLServer locks the data record in thread A, but allows thread B
to begin and get a lock on an index entry while waiting for the data,
and voila, deadlock. If that is the case, I'd call it a bug.
I assume it works correctly if you have the PK on id?
J.sql