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.
| Author |
Topic |
|
Maracatu
Starting Member
13 Posts |
 Posted - 2004-06-10 : 12:52:41
|
| I have a large sproc which deletes a lot of stuff from the database within a transaction with transaction isolation level set at SERIALIZABLE. If the sproc falls over, for some reason all the locks taken out are left on. Anybody have any suggestions??I check @@SQLERROR after each delete and if an error has occurred I jump to an error handler which rolls back that transaction. However, certain errors (for example if I have a delete statement that refers to a non existant table) cause the sproc to exit immediately without going thru the handler. The result is that I get a "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing". Worse than that, all the locks taken out during the sproc are left on and not released.thanks,Maracatu |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-10 : 13:34:45
|
| Exactly...Don't change the Isolation level...it's dangerous...even bol says soAnd why are you refering to a table that doesn't exists?The a sevrity level where sql will just raise out...Nothing you can trap unless you check for existance first..what are you doing and why did you pick this method?Go to the home page and search on error handlingand go to bol and look at what the say about isolation levelsBrett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-10 : 13:36:29
|
| @@SQLERROR? Are we talking MS SQL Server? Or did you just mean @@ERROR?Tara |
|
|
|
Maracatu
Starting Member
13 Posts |
Posted - 2004-06-10 : 13:51:02
|
| Sorry - I meant @@ERROR - I store it in a variable called @SQLERROR...The reference to the table that doesn't exist was a bug in the code which has now been fixed - but If a similar problem arises in future than we'll still get the lock/transaction trouble.The reason I'm setting the transaction isolation level to serializable is because the sproc is part of a web content management system that has many users and many websites. This particular sproc drops a website and all its related data from the system completely so I wanted to be sure that no other db activity related to the site that's being dropped takes place whilst this is happening.Is there a better way of ensuring this?ThanksMaractu |
|
|
|
javamick
Starting Member
7 Posts |
Posted - 2004-06-11 : 15:41:23
|
| Have you tried using:SET XACT_ABORT ONI don't think it will help with getting to your handling code, but it might help with the rollback in bad error cases.--Micky McQuadewww.mcquade.com |
|
|
|
|
|
|
Transaction counts, errors and rollbacks