T‑SQL Transactions

Understanding Transactions in T‑SQL

A transaction is a logical unit of work that must be either fully completed or fully rolled back. In SQL Server, transactions guarantee the ACID properties – Atomicity, Consistency, Isolation, and Durability.

• Atomicity: All statements succeed or none are applied.
• Consistency: Data moves from one valid state to another.
• Isolation: Concurrent transactions do not interfere.
• Durability: Once committed, changes persist even after a crash.

Basic Transaction Syntax

BEGIN TRANSACTION;
    -- Your DML statements here
    INSERT INTO dbo.Customers (Name, Email) VALUES ('Alice', 'alice@example.com');

    -- If everything is OK
    COMMIT TRANSACTION;
    -- If something goes wrong
    -- ROLLBACK TRANSACTION;

Advanced Scenarios

BEGIN TRANSACTION OuterTran;
    INSERT INTO Orders (OrderDate) VALUES (GETDATE());

    SAVEPOINT InnerSave;
    BEGIN TRY
        INSERT INTO OrderDetails (OrderID, ProductID, Qty) VALUES (1, 101, 5);
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION OuterTran;
    END CATCH;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT * FROM dbo.Inventory WITH (UPDLOCK) WHERE ProductID = 101;
COMMIT;

Try It Yourself

Select a scenario and see a simulated result.

Select a scenario and click Run.