Why the deadlock in this two Stored Procedure

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

General SQL Server Forums

New to SQL Server Programming

Why the deadlock in this two Stored Procedure

Author

Topic

anilinsqlteam
Starting Member

9 Posts

Posted - 2006-10-12 : 20:47:44

I have two store rpcedure as shown bellow,
When I run first dt_deadlock2 and then dt_deadlock1 deadlock happend and dt_deadlock1 is discarded by SQL server giving the deadlock message. What is the reason for it ?

CREATE PROCEDURE [agcdb].[dt_deadlock2] AS
BEGIN TRAN
UPDATE t1 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:10'
Select * from t1
COMMIT
GO

CREATE PROCEDURE [agcdb].[dt_deadlock1] AS
BEGIN TRAN
UPDATE t1 SET i = 11 WHERE i = 1
COMMIT
GO

Regards
Anil

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 22:30:48

Homework question?

The first proc has uncommitted rows and the secomd proc needs to read those rows.

CODO ERGO SUM

anilinsqlteam
Starting Member

9 Posts

Posted - 2006-10-12 : 22:44:27

Sorry but please help me as I am totaly new to database.

You told that the firs procedure has uncommited rows
But both procedure updating different row.
Thing is that if the select statement in the first procedure is not there then there is no deadlock, but if its there then only dead lock occure.

Thanks for reply.

Regards
Anil

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-12 : 23:07:15

More than likely, the select causes it to escalate the row locks to a table lock.

CODO ERGO SUM

anilinsqlteam
Starting Member

9 Posts

Posted - 2006-10-13 : 00:26:00

BEGIN TRAN
UPDATE t1 SET i = 99 WHERE i = 9
WAITFOR DELAY '00:00:10'
UPDATE t1 SET i = 33 WHERE i = 3
COMMIT

this statmenet is also giving deadlock. Though the two rows are different from the row that is going to be updated by the second procedure.

how can I specify a update statement to be only row lock not a table lock.

Regards
Anil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 00:56:41

If you wait more than 10 seconds to run the second SP (dt_deadlock1), the code will work.
Think about that.

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-13 : 06:42:02

I never tried an experiment like this actually but even though it sounds like homework it's an interesting question. I belive that if you put a clustered index on the column "i" it will not deadlock but to be honest I'm not sure. I'm gonna try it though...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-13 : 06:55:30

Yup, it worked after adding an index...didn't seem to matter if it was clustered or not.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources