Correct Use of Transactions in SQL Server

Correct use of transactions in SQL Server

Add a try/catch block, if the transaction succeeds it will commit the changes, if the transaction fails the transaction is rolled back:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH

When to use Transactions in SQL Server

You use transactions when the set of database operations you are making needs to be atomic.

That is - they all need to succeed or fail. Nothing in between.

Transactions are to be used to ensure that the database is always in a consistent state.

In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.

Try/Catch blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.

Using Transactions - Best practices

You can use the following template for your stored procedures:

SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY

        BEGIN TRANSACTION;
        -- CODE BLOCK GOES HERE
        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH 

       IF @@TRANCOUNT > 0
       BEGIN
          ROLLBACK TRANSACTION
       END;

       -- GET ERRORS DETAILS OR THROW ERROR

    END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

More details:

XACT_ABORT - specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error;
if you need information about the error (ERROR_MESSAGE, ERROR_LINE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE)

This is a general technique for working with transactions. I would recommend the following articles of Erland Sommarskog:

Error and Transaction Handling in SQL Server
Part One – Jumpstart Error Handling
Error and Transaction Handling in SQL Server
Part Two – Commands and Mechanisms

Best way to work with transactions in MS SQL Server Management Studio

The easisest thing to do is to wrap your code in a transaction, and then execute each batch of T-SQL code line by line.

For example,

Begin Transaction

         -Do some T-SQL queries here.

Rollback transaction -- OR commit transaction

If you want to incorporate error handling you can do so by using a TRY...CATCH BLOCK. Should an error occur you can then rollback the tranasction within the catch block.

For example:

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

See the following link for more details.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

Hope this helps but please let me know if you need more details.

Proper way to use a transaction around multiple inserts or updates

If you put SET XACT_ABORT ON before you start transaction, in case of an error, rollback will be issued automatically.

SET XACT_ABORT ON

begin transaction

INSERT INTO TableA (id) VALUES (1)
INSERT INTO TableB (id) VALUES (1)
UPDATE TableC SET id=1 WHERE id=2

commit transaction

If you want to do rollback yourself, use try .. catch block.

begin transaction

begin try

  INSERT INTO TableA (id) VALUES (1)
  INSERT INTO TableB (id) VALUES (1)
  UPDATE TableC SET id=1 WHERE id=2

  commit transaction

end try

begin catch
  raiserror('Message here', 16, 1)
  rollback transaction
end catch

How exactly transactions work in Microsoft SQL Server

In fact a transaction is the state of the session. Nothing else. So you can be in a transaction sate or not. When you execute a SQL query (which is different from "command") there is an implicite transaction that encloses the query. When you are inside the trigger code, the transaction state is alive, and you can rollback it or commit. If any of this command (COMMIT or ROLLBACK) is executed in the trigger code, you will raise an execption... But the transacation state no more exists, until you execute any query.

Now if you have several trigger, there is a precedence in the serial execution of the trigger that you can manage with the procedure sp_settriggerorder.

Having multiple triggers for the same action is not recommanded, except when you have to execute them in a specific order. As an example I give in my courses, I limit to three the number of trigger for the same action, with this peculiar precedence :

trigger for set based constraint
trigger for operational process (client application)
trigger for tracking

Use transactions for select statements?

In a highly concurrent application it could (theoretically) happen that data you've read in the first select is modified before the other selects are executed.

If that is a situation that could occur in your application you should use a transaction to wrap your selects. Make sure you pick the correct isolation level though, not all transaction types guarantee consistent reads.

Update :
You may also find this article on concurrent update/insert solutions (aka upsert) interesting. It puts several common methods of upsert to the test to see what method actually guarantees data is not modified between a select and the next statement. The results are, well, shocking I'd say.

T-SQL Transaction syntax

You can use a syntax like below. Note that ths syntax also takes care of nested transaction when a another SP with similar structure is called from inside the begin try block

BEGIN TRAN

BEGIN TRY

    UPDATE SomeTable
    SET SomeColumnValue = 123
    WHERE Id = 123456

    DELETE FROM SomeOtherTable
    WHERE Id = 789

    INSERT INTO ThirdTable
    (Column1, Column2)
    VALUE
    ('Hello World', 1234567)  

    COMMIT TRAN

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
    ROLLBACK TRAN;

    INSERT INTO LogError (
                        --ErrorID
                        objectName
                        ,ErrorCode
                        ,ErrorDescription
                        ,ErrorGenerationTime            
                             )
    SELECT  
                        -- autogenerated
                        OBJECT_NAME(@@PROCID)
                        ,ERROR_NUMBER() AS ErrorCode
                        ,'Error of Severity: ' + CAST (ERROR_SEVERITY() AS VARCHAR (4))
                         +' and State: ' + CAST (ERROR_STATE() AS VARCHAR (8))
                         +' occured in Line: ' + CAST (ERROR_LINE() AS VARCHAR (10))
                         +' with following Message: ' + ERROR_MESSAGE() AS ErrorColumnDescription
                        ,GETDATE()
END CATCH

Correct Use of Transactions in SQL Server