Watch Named, Nested Transactions

Gail Shaw finishes her outstanding series on transactions:

The error was thrown by the ROLLBACK statement. As such, the transaction is still open, the locks are held and the transaction log space can’t be reused. Unless the application that called this was checking for open transactions, that transaction could potentially be left open for quite some amount of time, causing blocking and/or the transaction log to grow.

It’s not just that someone in the future might call the code from another stored proc within a transaction, it’s also that it might be that the code is called from an application which started a transaction. Or called from SSIS which started a transaction. It’s very hard to ensure that code is never called from within an existing transaction

Read the whole thing.

Related Posts

Troubleshooting SQL Server Error 18456 State 73

Thomas Rushton reproduces an error state in SQL Server: A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?” Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes […]

Read More

Benefits Of Explicit Transactions

Kendra Little talks about explicit transactions and when they’re useful for single-statement operations: If you do not enable implicit transactions, and you don’t start an explicit transaction, you are in the default “autocommit” mode. This mode means that individual statements are automatically committed or rolled back as whole units. You can’t end up in a […]

Read More

Categories

December 2015
MTWTFSS
« Nov Jan »
 123456
78910111213
14151617181920
21222324252627
28293031