Using Save Points with Transactions to Get Better

When we last conversed, we talked about the TRY/CATCH block and how it works with transactions. If you somehow missed that, you can read about it here – How about we TRY and CATCH some Transactions? We’ll wait for you…

All right, now that everyone’s back with us, we’ll talk more about everyone’s favorite – transactions. When they deal with transactions, most people only know how to begin one, then either commit it or roll it back. But there’s so much more you can do with a transaction!

This time I want to focus on savepoints for transactions. Yes, the same term you’ve been using in games for years can be used in the workplace!

A savepoint – like in games – is used to save you from yourself. It allows you to begin a mini-transaction and either tear everything down to the frame or you can save everything that you’ve been doing.

For example, if I am in the middle of a transaction in one procedure and decide to bounce out to a new procedure – should I really be able to commit everything in the transaction that is about to be changed from the new one or should I just be able to deal with what I’ve been working on in this new procedure? If you said “Only deal with what you’ve done in that procedure,” then you’re probably right! As always, there are edge cases that you wouldn’t want to do this during, but the majority of the time, this should work for you – as always, mileage will vary.

So, my friends, with that we can go into today’s snippet of code:

CREATE PROC dbo.FirstProc AS 
BEGIN
     DECLARE @ErrorMessage NVARCHAR(4000);  
     DECLARE @ErrorSeverity INT;  
     DECLARE @ErrorState INT;  
     
     BEGIN TRY
           BEGIN TRAN
           INSERT INTO dbo.Person4
           (FirstName, LastName)
           SELECT 'Fred', 'Flintstone'

           EXEC dbo.SecondProc
           COMMIT

     END TRY
     BEGIN CATCH
        SELECT @ErrorMessage = ERROR_MESSAGE();  
        SELECT @ErrorSeverity = ERROR_SEVERITY();  
        SELECT @ErrorState = ERROR_STATE();  
 
        RAISEERROR (@Message, @Severity, @State)
        IF @@TRANCOUNT > 0 ROLLBACK
     END CATCH
END

CREATE PROC dbo.SecondProc AS
BEGIN
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    DECLARE @TranCounter INT;  
    
    SET @TranCounter = @@TRANCOUNT;  
    IF @TranCounter > 0  
	SAVE TRAN ProcedureSave;  
    ELSE
	BEGIN TRAN;

    BEGIN TRY
        INSERT INTO dbo.Person4
        (FirstName, LastName)
        SELECT 'Barney', 'Rubble'

        IF @TranCounter = 0  
	    COMMIT 
				
    END TRY
    BEGIN CATCH
         SELECT @ErrorMessage = ERROR_MESSAGE();  
         SELECT @ErrorSeverity = ERROR_SEVERITY();  
         SELECT @ErrorState = ERROR_STATE();  
 
         RAISEERROR (@Message, @Severity, @State)

         IF @TranCounter = 0
	     ROLLBACK TRAN
	 ELSE
	     IF XACT_STATE() <> -1  
	     ROLLBACK TRAN ProcedureSave

    END CATCH
END

The first procedure, which I thoughtfully called dbo.FirstProc, calls the second procedure, shockingly called dbo.SecondProc, and the savepoint fun can begin!

In the first procedure, you’ll see that we inserted the Fred Flintstone record into our dbo.Person4 table. If that works, then we go into the second procedure and attempt to insert the Barney Rubble record. If that is inserted with no issues, we’ll commit everything in our transaction. If the Barney Rubble record has trouble being inserted, we’ll rollback Barney, but still keep Fred in the table.

Yes, you’ll notice from the stored procedures listed above that there is a semi-duplication of the catch block since you do have to catch all your errors – like good developers.

And it truly is simple as that. Just remember, if you flip to a new procedure in the middle of a transaction, then a savepoint could be your new best friend!

And with that, my friends, we come to the end of yet another post. Come join us next time when we talk even more about transactions. But until then, take a listen to a track from the Cyberpunk 2077 soundtrack – “No Save Point” from Yankee and The Brave (Run the Jewels).