Deadlock help on single table

24 Mar 2006 3:27 PM

McGeeky

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.

--
McGeeky
http://mcgeeky.blogspot.com

24 Mar 2006 3:44 PM

ML

Could you please post the procedure definition?

ML

---
http://milambda.blogspot.com/

24 Mar 2006 3:50 PM

McGeeky

>>>>>>> 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
)

>>>>>>> These are the indexes:

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]

>>>>>>> This is the procedure:

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
)

--
McGeeky
http://mcgeeky.blogspot.com

Show quote

"ML" <M*@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/

24 Mar 2006 4:12 PM

ML

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/

24 Mar 2006 5:09 PM

McGeeky

Cheers mate. I will try that out.

--
McGeeky
http://mcgeeky.blogspot.com

Show quote

"ML" <M*@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/

26 Mar 2006 10:46 PM

ML

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/

26 Mar 2006 10:48 PM

Alejandro Mesa

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

Show quote

"McGeeky" wrote:

> >>>>>>> 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
> )
>
>
> >>>>>>> These are the indexes:
>
> 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]
>
>
> >>>>>>> This is the procedure:
>
> 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
> )
>
>
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>
> "ML" <M*@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/
>
>
>

26 Mar 2006 10:52 PM

ML

Three steps make one giant leap? :)

Nice one.

ML

---
http://milambda.blogspot.com/

27 Mar 2006 1:47 PM

McGeeky

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.

--
McGeeky
http://mcgeeky.blogspot.com

Show quote

"Alejandro Mesa" <AlejandroM***@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
>
> "McGeeky" wrote:
>
> > >>>>>>> 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
> > )
> >
> >
> > >>>>>>> These are the indexes:
> >
> > 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]
> >
> >
> > >>>>>>> This is the procedure:
> >
> > 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
> > )
> >
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> > "ML" <M*@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/
> >
> >
> >

27 Mar 2006 3:09 PM

ML

Just remember this newsgroup . :)

ML

---
http://milambda.blogspot.com/