Transaction in SQL Server

Transaction:

 A transaction is a logical, atomic unit of work that contains one or more SQL statements.

Transaction groups SQL statements so that they are either all committed (which means they are applied to the database), or all rolled back (which means they are undone from the database).

Property of Transaction:

ACID: (atomicity, consistency, isolation, durability)

In computer science, ACID is a set of properties that guarantee that database transactions are processed reliably

Atomicity: Modification on the data in the database either fails or succeeds. The beginning of such a modification starts with a transaction and ends when a transaction finishes (either by a commit or a rollback).

Consistency: The transaction takes the database from one consistent state to another consistent state.

Isolation: The effect of a transaction is not visible to other transactions until the transaction is committed. 

Durability: Changes made by committed transactions are permanent.

Type of Transaction:

1.    Auto commit mode : On any DML operation, commit fires implicitly.(e.g. SQL Server)

2.    Implicit mode: commit is must before logout. (e.g. Oracle 9i & 10g)

3.    Explicit Mode: User will apply commit as per his requirement.

Example 1:

BEGIN TRANSACTION;

BEGIN TRY

    DELETE FROM TB1                   --DELETED ALL ROW

    SELECT * FROM TB1                 --NO ROW HERE

    DELETE FROM TB1 WHERE ID = 'ABC'; --HERE IS ERROR ('ABC' is not integer value)

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; --ROLL BACK HERE

        SELECT * FROM TB1    --IT WILL GIVE ALL ROW

END CATCH;

IF @@TRANCOUNT > 0

    COMMIT TRANSACTION;

GO