Deadlock help on single table

I am experiencing a large volume of deadlocks with a stored procedure I have
written. The stored procedure only ever accesses one table. First it
performs a couple of selects to check if a record exists or not and if it
doesn't then it tries to insert it.
Its on the insert that I get the deadlock. Out of 23 concurrent processes,
20 of them get deadlocked.
Each stored procedure runs under the serializable isolation setting (they
have to due to the system that invokes them).
I didn't realise that you could get deadlocks on the same table - I thought
it could only occur between tables.
There are a few indexes on the table which I think could be the cause for
the deadlocks.
Any tips on how to reduce deadlocking in such a scenario?
I was thinking that I could "lock" the record that I want to create first
before actually inserting it, then perform the selects and then the insert.
But I don't know how I can "lock" a record that does not exist.
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]Could you please post the procedure definition?
ML
http://milambda.blogspot.com/|||>>>>>>> This is the table:
CREATE TABLE [dbo].[DNX_CIPFile] (
[Filename] [char] (32) NOT NULL ,
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[SequenceNumber] [int] NOT NULL ,
[LoadStartTime] [datetime] NOT NULL ,
[Type] [char] (32) NOT NULL ,
[LoadEndTime] [datetime] NULL ,
[Records] [int] NOT NULL ,
[Inserts] [int] NOT NULL ,
[Updates] [int] NOT NULL ,
[Batches] [int] NOT NULL
)

CREATE UNIQUE INDEX UX_DNX_CIPFile_1 ON [dbo].[DNX_CIPFile] ([Filename])
CREATE UNIQUE INDEX UX_DNX_CIPFile_2 ON [dbo].[DNX_CIPFile] ([Type],
[SequenceNumber] desc)
ALTER TABLE [dbo].[DNX_CIPFile] ADD
CONSTRAINT [PK_DNX_CIPFile] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
create procedure DNX_LoadDailyCIPFiles_CommenceLoad
@.FileName char ( 32 ),
@.Sequence int,
@.Type char ( 32 )
as
-- see if a record already exists.
if exists (select [Id] from DNX_CIPFile where [Filename] = @.FileName)
begin
RETURN
end
declare @.SequenceNumber int
select @.SequenceNumber = max(SequenceNumber) from DNX_CIPFile where [Type] =
@.Type
-- ensure if file type not exist
if @.SequenceNumber is NULL set @.SequenceNumber = 0
--ensure that it is next sequence number
if @.SequenceNumber + 1 <> @.Sequence
begin
RETURN
end
-- file does not exist and is the next in the sequence
insert into
DNX_CIPFile
(
[Filename] ,
[SequenceNumber] ,
[LoadStartTime] ,
[Type] ,
[LoadEndTime] ,
[Records] ,
[Inserts] ,
[Updates] ,
[Batches]
)
values
(
@.FileName,
@.Sequence,
getdate ( ),
@.Type,
null,
0,
0,
0,
0
)
McGy
[url]http://mcgy.blogspot.com[/url]
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A2FC6E36-C013-441D-ACD3-758C917AEA67@.microsoft.com...
> Could you please post the procedure definition?
>
> ML
> --
> http://milambda.blogspot.com/|||Try adding the WITH(UPDLOCK) table hint when reading from the table - this
will enable other processes to access the same row(s) without causing a
deadlock.
I.e.:
select @.SequenceNumber = max(SequenceNumber)
from DNX_CIPFile with(updlock, rangelock, holdlock)
where ([Type] = @.Type)
rangelock - locks the entire range of rows corresponding to the criteria;
holdlock - keeps "intruders" out for the duration of the process (also try
without it).
And most importantly: test, re-test, double-test and then test again.
ML
http://milambda.blogspot.com/|||Cheers mate. I will try that out.
McGy
[url]http://mcgy.blogspot.com[/url]
"ML" <ML@.discussions.microsoft.com> wrote in message
news:829AAF22-D55D-4575-9FD2-014FAA1015D8@.microsoft.com...
> Try adding the WITH(UPDLOCK) table hint when reading from the table - this
> will enable other processes to access the same row(s) without causing a
> deadlock.
> I.e.:
> select @.SequenceNumber = max(SequenceNumber)
> from DNX_CIPFile with(updlock, rangelock, holdlock)
> where ([Type] = @.Type)
> rangelock - locks the entire range of rows corresponding to the criteria;
> holdlock - keeps "intruders" out for the duration of the process (also try
> without it).
> And most importantly: test, re-test, double-test and then test again.
>
> ML
> --
> http://milambda.blogspot.com/|||Check out Alejandro's suggestion - it looks even better.
Also think again about the reasons behind the need for serializable
isolation level - I think the default (read committed) isolation level would
suffice.
ML
http://milambda.blogspot.com/|||Try,
-- file does not exist and is the next in the sequence
insert into
DNX_CIPFile
(
[Filename] ,
[SequenceNumber] ,
[LoadStartTime] ,
[Type] ,
[LoadEndTime] ,
[Records] ,
[Inserts] ,
[Updates] ,
[Batches]
)
select
@.FileName,
@.Sequence,
getdate ( ),
@.Type,
null,
0,
0,
0,
0
where
not exists(select [Id] from DNX_CIPFile where [Filename] = @.FileName)
and
(select isnull(max(SequenceNumber), 0) + 1 from DNX_CIPFile where [Type]
= @.Type) = @.Sequence
go
AMB
"McGy" wrote:

> CREATE TABLE [dbo].[DNX_CIPFile] (
> [Filename] [char] (32) NOT NULL ,
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [SequenceNumber] [int] NOT NULL ,
> [LoadStartTime] [datetime] NOT NULL ,
> [Type] [char] (32) NOT NULL ,
> [LoadEndTime] [datetime] NULL ,
> [Records] [int] NOT NULL ,
> [Inserts] [int] NOT NULL ,
> [Updates] [int] NOT NULL ,
> [Batches] [int] NOT NULL
> )
>
> CREATE UNIQUE INDEX UX_DNX_CIPFile_1 ON [dbo].[DNX_CIPFile] ([Filename])
> CREATE UNIQUE INDEX UX_DNX_CIPFile_2 ON [dbo].[DNX_CIPFile] ([Type],
> [SequenceNumber] desc)
> ALTER TABLE [dbo].[DNX_CIPFile] ADD
> CONSTRAINT [PK_DNX_CIPFile] PRIMARY KEY NONCLUSTERED
> (
> [Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
>
> create procedure DNX_LoadDailyCIPFiles_CommenceLoad
> @.FileName char ( 32 ),
> @.Sequence int,
> @.Type char ( 32 )
> as
> -- see if a record already exists.
> if exists (select [Id] from DNX_CIPFile where [Filename] = @.FileName)
> begin
> RETURN
> end
> declare @.SequenceNumber int
> select @.SequenceNumber = max(SequenceNumber) from DNX_CIPFile where [Type] =
> @.Type
> -- ensure if file type not exist
> if @.SequenceNumber is NULL set @.SequenceNumber = 0
> --ensure that it is next sequence number
> if @.SequenceNumber + 1 <> @.Sequence
> begin
> RETURN
> end
> -- file does not exist and is the next in the sequence
> insert into
> DNX_CIPFile
> (
> [Filename] ,
> [SequenceNumber] ,
> [LoadStartTime] ,
> [Type] ,
> [LoadEndTime] ,
> [Records] ,
> [Inserts] ,
> [Updates] ,
> [Batches]
> )
> values
> (
> @.FileName,
> @.Sequence,
> getdate ( ),
> @.Type,
> null,
> 0,
> 0,
> 0,
> 0
> )
>
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:A2FC6E36-C013-441D-ACD3-758C917AEA67@.microsoft.com...
>
>|||Three steps make one giant leap? :)
Nice one.
ML
http://milambda.blogspot.com/|||Hi. Thanks for the suggestions and the rewritten query below. Unfortunately
I cannot rewrite as suggested below because I need to check specifically for
the existence of the record and the max sequence number because I return
values to the calling program (I removed that bit of script from the SQL).
However, I have since tried using the with (updlock) on the first sql
statement and it improved the situation no end. No more deadlocks!! I ran
some load tests to check for problems and all seemed to go through fine.
I have to use serializable as the calling program (BizTalk Server) specifies
as such and it can't be changed.
Thanks again.
McGy
[url]http://mcgy.blogspot.com[/url]
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:232C19F5-FB40-4DC1-A0B7-0D3F1C065757@.microsoft.com...
> Try,
> -- file does not exist and is the next in the sequence
> insert into
> DNX_CIPFile
> (
> [Filename] ,
> [SequenceNumber] ,
> [LoadStartTime] ,
> [Type] ,
> [LoadEndTime] ,
> [Records] ,
> [Inserts] ,
> [Updates] ,
> [Batches]
> )
> select
> @.FileName,
> @.Sequence,
> getdate ( ),
> @.Type,
> null,
> 0,
> 0,
> 0,
> 0
> where
> not exists(select [Id] from DNX_CIPFile where [Filename] = @.FileName)
> and
> (select isnull(max(SequenceNumber), 0) + 1 from DNX_CIPFile where
[Type]
> = @.Type) = @.Sequence
> go
>
> AMB
> "McGy" wrote:
>
[Type] =|||Just remember this newsgroup . :)
ML
http://milambda.blogspot.com/