Transaction and SAVEPOINT

Transaction is a very important part of SQL developer. In this article I am taking about the SAVEPOINT of MS SQL Server and how we use the power of save point. Hope it will be interesting.

When we are working with T-SQL statement we are using the TRANSACTION that protects all my T-SQL statements within it.

Generally it contains BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN.

The block of transaction is mentioned bellow.

BEGIN TRY

   BEGIN TRAN

     <... T-SQL to Execute...>

   COMMIT TRAN

END TRY

BEGIN CATCH

   ROLLBACK TRAN 

END CATCH

So anything between BEGIN TRAN and COMMIT TRAN goes wrong (means error occurs) it directly go to the CATCH portion and ROLL BACK the transaction. So all the T-SQL statements must be executed properly,  to get the COMMIT TRAN.

Think about the situation where error is not define and came frequently and we are executing some long T-SQL statement. In this situation we don't want complete rollback of all the transaction but want to rollback only the error portion T-SQL executions.

In this type of situation we must use the SAVE POINT options.

We can use savepoints in rolling back portions of transactions to predefined locations. A T-SQL savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

Keep in mind that SQL updates and rollbacks generally are expensive operations. So savepoints are useful only in situations where errors are unlikely and checking the validity of an update beforehand is relatively costly.

An example of savepoint is mentioned bellow.

SET NOCOUNT OFF;

GO

USE AdventureWorks2008R2;

GO

CREATE TABLE InvCtrl

    (WhrhousID      int,

    PartNmbr      int,

    QtyInStk      int,

    ReordrPt      int,

    CONSTRAINT InvPK PRIMARY KEY

    (WhrhousID, PartNmbr),

    CONSTRAINT QtyStkCheck CHECK (QtyInStk > 0) );

GO

CREATE PROCEDURE OrderStock

    @WhrhousID int,

    @PartNmbr int,

    @OrderQty int

AS

    DECLARE @ErrorVar int;

    SAVE TRANSACTION StkOrdTrn;  ß Point 1 [ Prepare the Savepoint]

    UPDATE InvCtrl SET QtyInStk = QtyInStk - @OrderQty

        WHERE WhrhousID = @WhrhousID

        AND PartNmbr = @PartNmbr;

    SELECT @ErrorVar = @@error;

    IF (@ErrorVar = 547)

    BEGIN

        ROLLBACK TRANSACTION StkOrdTrn; ß Point 2 [Rollbacking the Savepoint]

        RETURN (SELECT QtyInStk

                FROM InvCtrl

                WHERE WhrhousID = @WhrhousID

                AND PartNmbr = @PartNmbr);

    END

    ELSE

        RETURN 0;

GO

Whenever we roll back a transaction to a savepoint, it must proceed to completion or be canceled altogether. Therefore a COMMIT or a complete ROLLBACK should always follow a rollback to savepoint, because the resources used during the transaction (namely the SQL locks) are held until the completion of the transaction. When part of a transaction rolls back to a savepoint, resources continue to be held until either the completion of the transaction or a rollback of the complete transaction. In other words, even after rollback to a midpoint, the transaction is considered open and must be closed by either committing work or rolling back the entire transaction.

Hope that the article is quite informative and thanking you to provide your valuable time on it.