transactions

Code template for transactions

Code template for correctly handling transactions in SQL Server:

SET XACT_ABORT ON;

BEGIN TRY
        BEGIN TRANSACTION
                -- Your script here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        IF XACT_STATE() != 0  
                ROLLBACK TRANSACTION

        -- Re-throw error
        EXEC ThrowError
END CATCH
GO

Sample of ThrowError function:

CREATE PROCEDURE ThrowError
AS
BEGIN
   DECLARE
     @ErrorNumber INT
     , @ErrorMessage NVARCHAR(4000)
     , @ErrorSeverity INT
     , @ErrorState INT
     , @ErrorProcedure NVARCHAR(128)
     , @ErrorLine INT
     
   SELECT @ErrorNumber = ERROR_NUMBER()    
        , @ErrorMessage = ERROR_MESSAGE()
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
        , @ErrorProcedure = ERROR_PROCEDURE()
        , @ErrorLine = ERROR_LINE()

    SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
   
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )
END
GO

Isolation levels: dirty reads, non-repeatable reads, phantom reads

An image saying everything about transaction isolation levels in SQL Server:

  Description Dirty reads Non-repeatable reads Phantom reads
Read Uncommited (S) locks are not acquired by SELECT statements.
You can also achieve this degree of isolation on a query basis using the NOLOCK locking hint:
SELECT * FROM dbo.TheTable WITH(NOLOCK);
Yes Yes Yes
Read Commited (S) locks are requested by the SELECT statements, but (S) locks are acquired only while data is read (not until the end of the transaction). No Yes Yes
Repeatable Read Shared locks are retained by the SELECT statements until the end of the transaction which completely prevents others from updating any data covered by the lock. No No Yes
Serializable Instead of acquiring a lock only on the row to be accessed, the Serializable isolation level acquires a range lock on the row and the next row in the order of the data set requested.
This prevents update and the addition of rows by other transactions in the data set operated on by the first transaction.
No No No
Subscribe to RSS - transactions