July 17, 2008 at 6:13 pm
Begin Transaction
While i < 100
Update tblTable
set Column = 'test'
where PrimaryKey = i
waitfor DELAY '00:01:00'
set i = i + 1
Loop
Commit Transaction
Go
Select * from tblTable where i = 12
Hi all,
Will above while loop causes deadlock?
Thanks
July 17, 2008 at 10:03 pm
it looks ok, I cant see why it would cause a deadlock, if it is look at the locking of your table.
T
July 18, 2008 at 12:10 am
Catcha (7/17/2008)
Will above while loop causes deadlock?
Not on its own. Deadlocks require two connections wanting locks that the other connection holds.
It is a really, really inefficient way to do an update though. Why the waitfor?
Are you trying to force a deadlock?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 12:14 am
Yes, I am trying to stimulate a dead lock.
Thanks I think I have enough info.
Derek
July 18, 2008 at 12:25 am
The following form of code will usually deadlock if run enough times.
Connection 1:
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
update Table1 SET SomeNumberField = SomeNumberField +1
WAITFOR DELAY '00:00:30
UPDATE Table2 SET SomeNumberField = SomeNumberField +1
COMMIT TRANSACTION
END
Connection 2:
WHILE (1=1)
BEGIN
BEGIN TRANSACTION
update Table2 SET SomeNumberField = SomeNumberField -1
WAITFOR DELAY '00:00:30
UPDATE Table1 SET SomeNumberField = SomeNumberField -1
COMMIT TRANSACTION
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply