June 25, 2008 at 8:16 am
Hello,
I have a dead lock issue, but i can not understand why or how to overcome it.
Consider the following tables:
-- BEGIN CODE
create table saar (a int identity(1,1) primary key nonclustered, b int)
go
create table saar2 (a int not null foreign key references saar(a), b int not null)
go
alter table saar2 add primary key (a,b)
-- END CODE
saar is the parent and saar2 is the child.
Now when I want to insert into these tables in one transaction I have the following (in an SP):
-- BEGIN CODE
begin tran
declare @id int
insert into saar(b) values(1)
set @id = scope_identity()
insert into saar2 (a,b) select @id,id from sysobjects -- sysobjects is just for the example!
commit
-- END CODE
All is good, but when I have concurrent users running this I get dead locks.
I have found out that if I open one session and run the following:
-- BEGIN CODE
begin tran
declare @id int
insert into saar(b) values(1)
-- END CODE
Then open a new (2nd) session and run:
-- BEGIN CODE
begin tran
declare @id int
insert into saar(b) values(1)
set @id = scope_identity()
insert into saar2 (a,b) select @id,id from sysobjects -- sysobjects is just for the example!
-- END CODE
It will hang until I commit or rollback session one. If in session 1 I run:
-- BEGIN CODE
set @id = scope_identity()
insert into saar2 (a,b) select @id,id from sysobjects -- sysobjects is just for the example!
-- END CODE
while session 2 is hanging it will cause a deadlock. It seems that it tries to create a shared lock on the parent table (saar).
Furthermore, if I limilt the results returned by sysobjects (top 3 for example) it will work without blocking.
I have tried uncopmmitted isolation level and others but no luck.
Any ideas will be appreciated.
Thanks,
Saar.
June 25, 2008 at 11:02 am
You might need to commit the insert into saar before you begin the insert into saar2, for this to work.
begin transaction
insert into saar(b) select 1
commit
begin transaction
insert into saar2 select scope_identity(), 1
commit
It might be holding a table lock on saar because of the uncommited insert.
The way to find out is to run a trace for deadlocks, and find what kind of lock is being taken and by what.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 3:16 pm
Thanks, but this is causing an issue as I do not want the first insert to complete if the 2nd fails (for example).
I have found that if the 2nd insert into Saar2 is limited to only several records (select top 3... for example), it works fine.
Very strange!
June 30, 2008 at 8:23 am
Not strange at all if it's going beyond row-locks into page/extent/table locks. That's why I said trace the deadlocks (Books Online says how to do this and what options you have on it), find what kind of locks they are taking, and you'll probably find the cause of the whole thing pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 4:17 am
Each insert into the parent table (Saar) causes two locks (per session): Row lock (RID) and Index (key), so we have 2 RID and 2 KEY locks all granted.
Now when the 2nd session tries to insert into the child table (Saar2) it creates a shared KEY lock on the parent table (Saar), this is causing the deadlock.
any ideas how to get around this?
July 1, 2008 at 8:58 am
You might take a look at isolation levels in Books Online. Might find a solution in there. I don't know one off the top of my head.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 9:02 am
Thank you. Had a look before posting the original question but it did not help (even tried read uncomitted...).
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply