Parent Child - Deadlocks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Parent Child - Deadlocks

Author

Topic

Saar
Starting Member

5 Posts

Posted - 2008-06-25 : 09:24:33

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

I have tried uncopmmitted isolation level and others but no luck.

Any ideas will be appreciated.

Thanks,

Saar.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-06-25 : 09:49:52

If SQL's not rolling back one of them with a 1205 error, it's not a deadlock. It's just blocking.
I tried out your queries. I got blocking (as I expected), but no deadlocks.

An insert will cause an exclusive lock. While one procss has an exclusive lock, no other process can do anything on the locked resource. That's why your second sessions waits (blocked) intil the first is commited or rolled back.

Read uncommitted only applies to selects. Inserts have to take exclusive locks.

--
Gail Shaw

Saar
Starting Member

5 Posts

Posted - 2008-06-25 : 09:54:01

Thanks for coming back to me.

I do get a deadlock because if session 1 tries the insert into saar2 it will dead lock.

However, I have found that if the table I select from (sysobjects in our case) has 3 records it will work without issues.

Can you explain why?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-28 : 00:57:36

Did you trace it in profiler?

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-29 : 12:35:25

You can kill the SPID to come out of the Blocking Situation for now.
Check on your ISOLATION LEVELS. Also cna use WITH (Nolock) on SELECT Statements.
If Updates use WITH TABLOCKX(Exclusive Locks on Table)

Maninder

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-29 : 14:23:10

Kill process is not the solution in most case. Nolock hint rsults dirty read, ensure app allows that. Will tablock hint cause more blocking?

maninder
Posting Yak Master

100 Posts

Posted - 2008-06-29 : 16:17:58

Agreed.. but what cases? how can you Release the DEADLOCK Scenario (IF not ISOLATION LEVELS) - Killing a Process, will be sometimes the only available solution to Release the Process. Yes you have to TAKE Care and Study your SPID Process before taking the STEP.
Yes NOlock Does have Dirty Reads, that the reason they can be used on NON Essential and QUICK REPORTS or AD-HOC SELECTS.
TABLOCKX is a Scenario, that can be used to get RID of Unnecessary locking, when required to do a UPDATE or INSERT, if the table has Excessive Processing..

Maninder

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-29 : 20:53:04

In case of deadlock, sql will handle that by killing less cost process involved. So you don't have to kill anything. To solve deadlock, better to check logical order in the code than tablock. Snapshot isolation may help also.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-29 : 21:38:19

quote:
Originally posted by maninder

Agreed.. but what cases? how can you Release the DEADLOCK Scenario (IF not ISOLATION LEVELS) - Killing a Process, will be sometimes the only available solution to Release the Process. Yes you have to TAKE Care and Study your SPID Process before taking the STEP.
Yes NOlock Does have Dirty Reads, that the reason they can be used on NON Essential and QUICK REPORTS or AD-HOC SELECTS.
TABLOCKX is a Scenario, that can be used to get RID of Unnecessary locking, when required to do a UPDATE or INSERT, if the table has Excessive Processing..

Maninder

So you Resolve DeadLock by Killing the Spid. Like rmaio said , instead of killing you have to look for solution like accessing objects in same order,normalize you database.Don't just throw blanket statement coz everyone is watching over here.

Saar
Starting Member

5 Posts

Posted - 2008-06-30 : 04:42:54

I have tried isolation levels and nolock etc... No success!

The strange thing is that if I limit the select to return say 3 rows it works fine! The insert order is the same as per the example.

It creates an exclusive lock on the 1st table and when Ii insert into the child it tries to get a shared lock for the foreign key validation, but not sure why!.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-30 : 23:21:41

Again, did you ever trace it in profiler? It'll give you deadlock details.

Saar
Starting Member

5 Posts

Posted - 2008-07-01 : 06:10:48

Thanks.

Yes, I did trace and it and it seems to create an exclusive lock on the paretn table (Saar) and then a shared lock on the parent when inserting to the child (Saar2), I presume to check the constraint.
This causes the deadlock.

But how do we get around it?

buzzi
Starting Member

48 Posts

Posted - 2008-07-01 : 09:54:58

I think the only way to get arround is to put out the insert for the parent table out of the transaction, since you already have the key value assigned to a variable, this way you will not hold the locks on the parent table during your insert to the child table

Saar
Starting Member

5 Posts

Posted - 2008-07-01 : 11:05:12

Thanks, I thought about doing that but would not want to implement my own transaction management as I will have to delete the parent record if there was a failure for the child insert.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources