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

When the Transaction Begins

Josh Darnell points out that transactions don’t really begin with BEGIN TRAN: Of course, no one reads the documentation. And even if they do, they certainly don’t continue on to the 4th paragraph of the “General Remarks” section. That’s like going to the second page of Google search results. If one did trudge on through the […]

Read More

SQL Server Isolation Levels

Pamela M. takes us through a humorous journey covering transaction isolation levels in SQL Server: READ COMMITTED:  One step up (and the default for SQL Server).    A query in the current transaction can’t read data modified by someone else that hasn’t yet committed.  No dirty reads.  BUT….data could be changed by others between statements in […]

Read More

Categories

November 2015
MTWTFSS
« Jan Dec »
 1
2345678
9101112131415
16171819202122
23242526272829
30