ROLLBACK at Deadlock - 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 2000 Forums

SQL Server Administration (2000)

ROLLBACK at Deadlock

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-04-02 : 15:33:01

Folks:

I have this SP which is run by multiple users and sometimes it detects a deadlock. My question is and what I want is to ROLLBACK EVERYTHING whenever a deadlock occurs. Will my this query ROLLBACK EVErything or just TABLE 2 if the deadlock happens at TABLE2?

BEGIN TRAN S1
UPDATE TABLE 1
SET column Name

IF @@error <> 0
ROLLBACK TRAN S1

UPDATE TABLE 2
SET column Name

IF @@error <> 0
ROLLBACK TRAN S1

UPDATE TABLE 3
SET column Name

IF @@error <> 0
ROLLBACK TRAN S1

COMMIT TRAN S1

=======

Thanks !

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-02 : 16:39:01

The way your code is written if an error occurs on the 1st statement then your transaction will rollback but later you try to commit the transaction. You'll get an error for trying to commit a non-existant transaction - plus it's not the behavior you want (all or nothing).

Another problem is that a deadlock won't be caught by @@error.

Something like this is closer to what you need:


set xact_abort on
begin tran

	update table1
	if @@error <> 0 goto onError

	update table2
	if @@error <> 0 goto onError

commit tran
return 0

onError:
if @@trancount > 0 rollback tran
return -1

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-02 : 16:42:31

If you are getting deadlocks often then perhaps you've got a problem in your code. If you want some advice post the real SP

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources