Shaw On Transactions, Part 2

Gail Shaw has part 2 of her transactions series up:

Again, exactly the desired behaviour. The changes made in the outer procedure were committed, the changes in the inner procedure, the procedure where the error was thrown, were rolled back.

Used correctly, savepoints can be a powerful mechanism for managing transactions in SQL Server. Unfortunately they’re not well known and as such their use can also make code much harder for later developers to debug.

I’ve used conditional transactions fairly regularly (procedures can have calling parent procedures, or sometimes can be called on their own), but never savepoints.

Related Posts

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

Phantom Reads

Arun Sirpal sees not-quite-there-yet transactions: With Halloween around the corner what better topic to discuss than phantom reads. A phantom read occurs when rows have been inserted after a read operation and becomes visible in a follow-up read operation within the same transaction. I will show you what this looks like with an example. Please […]

Read More


November 2015
« Jan Dec »