Transactions in SQL Server
Part four of a four-part series of blogs

This blog examines how to use Transactions in Microsoft SQL Server to allow you to control when data changes are committed to the database. You'll also discover how you can rollback transactions, effectively giving you the chance to undo changes to your data.

  1. Transactions in SQL Server
  2. Errors within Transactions
  3. Nesting Transactions
  4. Transaction Savepoints (this blog)

This blog is part of a complete SQL Server Tutorial.

Posted by Andrew Gould on 11 October 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Transaction Savepoints

Normally, when you rollback a transaction SQL Server will undo everything that has happened since the transaction began.  You can, however, provide yourself with a way to rollback only part of a transaction using something called a savepoint.

Creating and Rolling Back to a Savepoint

You can create a savepoint at any stage after beginning a transaction, as shown in the code below:

BEGIN TRAN Tran1

INSERT INTO tblTest(TestName)

VALUES ('A')

SAVE TRAN Savepoint

INSERT INTO tblTest(TestName)

VALUES ('B')

ROLLBACK TRAN Savepoint

INSERT INTO tblTest(TestName)

VALUES ('C')

COMMIT TRAN Tran1

 

Once a savepoint has been created you can choose to rollback to that point at any stage up until you commit or rollback the entire transaction.  In the code above, anything that is done between SAVE TRAN and ROLLBACK TRAN will be rolled back, while anything that occurs before or after these lines will be committed.

If we examine the contents of tblTest after executing the code we would see the following values:

Results of rolling back to savepoint

The second value that was added to the table doesn't appear because we rolled back this section of the transaction.

 

Using Savepoints in Stored Procedures

If you begin a transaction in one procedure and then call another procedure the transaction remains open.  You can create a savepoint within the called procedure to allow you to rollback any actions that are performed within it.

The code below shows a simple procedure to add a new film to our database.  Within this procedure we call another one to assign the correct director to the film.

CREATE PROC spAddFilm(

@Title VARCHAR(MAX)

,@ReleaseDate DATETIME

,@Director VARCHAR(MAX))

AS

BEGIN

DECLARE @FilmID INT

DECLARE @DirectorID INT

BEGIN TRAN AddFilm

--Add a new record to the Film table

INSERT INTO tblFilm(FilmName, FilmReleaseDate)

VALUES (@Title, @ReleaseDate)

--Store the Id of the film just added

SET @FilmID = @@IDENTITY

--Call stored procedure to get the DirectorID

EXEC @DirectorID = spAssignDirector @Director

--Assign the DirectorID to the film added earlier

UPDATE tblFilm

SET FilmDirectorID = @DirectorID

WHERE FilmID = @FilmID

COMMIT TRAN AddFilm

END

The procedure that is called in the middle of this one is used to retrieve the Id number of the film's director.  The code for this is shown below:

CREATE PROC spAssignDirector(

@Director VARCHAR(MAX))

AS

BEGIN

DECLARE @ID INT

--Create a savepoint

SAVE TRAN AddDirector

--Insert the new director name into the table

INSERT INTO tblDirector(DirectorName)

VALUES (@DirectorName)

--If there is more than one director with the same name,

--Rollback to the savepoint

IF (SELECT COUNT(*) FROM tblDirector WHERE DirectorName=@Director) > 1

ROLLBACK TRAN AddDirector

--Store the ID of the director

SELECT @ID = DirectorID

FROM tblDirector

WHERE DirectorName = @Director

--Return the DirectorID to the calling procedure

RETURN @ID

END

This procedure creates a savepoint before it attempts to modify any data.  It then inserts a new record into the director table and checks to see if there is more than one director with the same name.  If that's true then the transaction is rolled back to the savepoint created at the start of this procedure, without affecting any actions that have taken place in the calling procedure.

This is a fairly inefficient way to achieve the result we want! We're using this method purely to demonstrate how savepoints work in stored procedures.

This blog has 0 threads Add post