Question about Microsoft SQL Server 2000, deletion, and lo..

All,

I've been racking my brains trying to solve a deadlocking issue I've
encountered and trying to find the simplest way of stating my problem
so that others can help - and I think I've figured out the best way to
ask. I have it all narrowed down to 2 questions:

1. If I have a database with only ONE table (no foreign keys) and NON-
CLUSTERED indexes on that table, is it possible for me to get in a
deadlock situation when 2 (or more) threads are deleting multiple rows
within their transactions (no other selects or updates)?

2. If I have a database with only ONE table (no foreign keys) and
CLUSTERED indexes on that table, is it possible for me to get in a
deadlock situation where 2 (or more) threads are deleting multiple
rows within their transactions (no other selects or updates)?

I'm all a-tingle awaiting your answer. Many thanks!

-john

On Jan 11, 4:21 pm, theRat wrote:
> All,
>
> I've been racking my brains trying to solve a deadlocking issue I've
> encountered and trying to find the simplest way of stating my problem
> so that others can help - and I think I've figured out the best way to
> ask. I have it all narrowed down to 2 questions:
>
> 1. If I have a database with only ONE table (no foreign keys) and NON-
> CLUSTERED indexes on that table, is it possible for me to get in a
> deadlock situation when 2 (or more) threads are deleting multiple rows
> within their transactions (no other selects or updates)?
>
> 2. If I have a database with only ONE table (no foreign keys) and
> CLUSTERED indexes on that table, is it possible for me to get in a
> deadlock situation where 2 (or more) threads are deleting multiple
> rows within their transactions (no other selects or updates)?
>
> I'm all a-tingle awaiting your answer. Many thanks!
>
> -john

Yes, it is possible, and it is very easy to reproduce. See for
yourself:
-- set up data
CREATE TABLE a(i INT, j INT)
GO
SET NOCOUNT ON
GO
DECLARE @i INT, @j INT
SET @i = 0
WHILE(@i < 100000) BEGIN
SELECT @i = @i + 1, @j = rand()*100000
INSERT a(i,j) VALUES(@i, @j)
END
GO
CREATE NONCLUSTERED INDEX a_i ON a(i)
GO
CREATE NONCLUSTERED INDEX a_j ON a(j)
GO

--from one connection, run this:

DECLARE @i INT
SET NOCOUNT ON
SET @i = 0
WHILE(@i < 100000) BEGIN
SELECT @i = @i + 1
DELETE FROM a WHERE i = @i
END
GO

-- from another connection, run this:
DECLARE @j INT
SET NOCOUNT ON
SET @j = 0
WHILE(@j < 100000) BEGIN
SELECT @j = @j + 1
DELETE FROM a WHERE j = @j
END
GO

Msg 1205, Level 13, State 45, Line 6
Transaction (Process ID 52) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.


in my experience 100K is enough to reproduce a deadlock on my server.
You might want to play with that value in your enviropnment.