Transactions in t-sql

Question

Transactions in t-sql

steal

SSC Eights!

Points: 827
More actions
July 21, 2009 at 7:18 am

#223717

I am maintaining some stored proc code that is wrapped in a transaction but includes no ROLLBACK. Does this make sense? What happens in this case when there is an error?
BEGIN TRAN
BEGIN
Code here ..........
END
COMMIT TRAN
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 1

I think this will depend on a number of factors, but it is certainly bad practice.

After an error I'd expect to exit from this stored procedure with a warning message saying the transaction counts differed between entering and leaving it (SQL generates this - can't remember the number)

After that I think it depends how it is handled by whatever called it - but there is certainly a theoretical possibility that the transaction will remain open.

Tim

.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Doing that the transaction remains open, holding locks, until the connection is closed, at which point the transaction will rollback. It is bad practice, and it's got some subtle side effects.

Say that proc runs, doesn't commit. Means there's an uncommitted transaction. Now, without closing the connection, the app goes and runs a whole bunch other data modifications, begins transactions and commits them, but doesn't check the tran count (a transaction isn't actually committed until @@Trancount reaches 0)

Then, after doing a bunch of data modifications that the app assumed were committed, it closes the connection. SQL issues a rollback because there's an open uncommitted transaction and rolls back all the other data modifications that were assumed to be committed.

That's a bug that would be exceedingly hard to find.

http://sqlinthewild.co.za/index.php/2008/05/20/common-t-sql-mistakes/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steal SSC Eights! Points: 827 More actions · Answer 3

Thanks for the great input. I will make it a point to refactor this code as I come across it.

VaibhavChoubey@SQL Ten Centuries Points: 1033 More actions · Answer 4

Just Check IF @@Trancount =0 ROLLBACK your transaction.

Also if you are using SQL 2005 why not use a TRY catch in your procedure.:-)