Transaction in SQL Server

A transaction in SQL Server is a sequential group of operations (such as queries or statements) that are performed as a single unit of work. These operations may involve reading, writing, updating, or deleting data in a database.

Transactions ensure data integrity by following the ACID properties.

ACID properties

Atomicity: Ensures that a transaction is treated as a single unit. Either all operations in the transaction are completed successfully, or none are applied, maintaining the "all-or-nothing" principle.
Consistency: Guarantees that a transaction transforms the database from one valid state to another, adhering to all defined rules, constraints, and relationships.
Isolation: Ensures that concurrent transactions do not interfere with each other, maintaining data integrity as if transactions were executed sequentially.
Durability: Ensures that once a transaction is committed, its changes are permanently recorded in the database, even in the event of a system failure.

Transaction Control

The following are the commands used to control transactions.

BEGIN TRANSACTION: Marks the start of a transaction. All subsequent operations will be part of this transaction until it is committed or rolled back.
COMMIT: Saves all the changes made during the transaction permanently to the database. Once committed, the changes cannot be undone.
ROLLBACK: Reverts all changes made during the transaction to their state at the start of the transaction, effectively canceling the transaction.
SAVEPOINT: Creates a checkpoint within a transaction. This allows rolling back a transaction to a specific point without undoing the entire transaction.
RELEASE SAVEPOINT: Deletes a previously defined SAVEPOINT. Once released, SAVEPOINT can no longer be used for rollback.
SET TRANSACTION: Configures a transaction with specific properties, such as setting it to read-only or read/write, or associating it with a specific rollback segment.

Types of Transactions

Implicit Transactions
- Automatically initiated by the database system when specific commands (e.g., INSERT, DELETE, UPDATE) are executed.
- The transaction remains active until explicitly committed or rolled back by the user.
Explicit Transactions
- Manually initiated and controlled by the user.
- Typically defined using BEGIN TRANSACTION, followed by COMMIT or ROLLBACK to either save or undo changes.
Autocommit Transactions
- The default transaction mode in most SQL systems.
- Each individual SQL statement is automatically committed if it executes successfully. No explicit commands are needed to commit or rollback.
Savepoints
- Checkpoints within a transaction that allow partial rollbacks.
- Useful for rolling back a specific part of a transaction without undoing the entire

Basic Transaction Syntax

Explicit Transaction Example

BEGIN TRANSACTION;

-- Deduct from one account
UPDATE EmpSalary_int
SET Salary = Salary - 100
WHERE EmpID = 1;

-- Add to another account
UPDATE EmpSalary_int
SET Salary = Salary + 100
WHERE EmpID = 2;

-- Commit the transaction
COMMIT;

Transaction Syntax

Using ROLLBACK

BEGIN TRANSACTION;

UPDATE products 
SET stock_quantity = stock_quantity - 10 
WHERE product_id = 5;

-- Simulating an error
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction failed and was rolled back.';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction completed successfully.';
END

Using ROLLBACK

Failed transaction

Failed transaction

Savepoints for Partial Rollbacks

BEGIN TRANSACTION;

-- Step 1
INSERT INTO orders (order_id, customer_id, order_date) 
VALUES (101, 1, GETDATE());

SAVE TRANSACTION SavePoint1;

-- Step 2
INSERT INTO order_details (order_id, product_id, quantity) 
VALUES (101, 2, 5);

-- Rollback to SavePoint1 if needed
ROLLBACK TRANSACTION SavePoint1;

-- Commit remaining operations
COMMIT;

Here, we can see in the second table that order_details data is not saved because we have set rollback savepoint1.

Partial Rollbacks

TRY...CATCH Example

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE EmpSalary_int 
    SET Salary = Salary - 100 
    WHERE EmpID = 1;

    UPDATE EmpSalary_int
    SET Salary = Salary + 'null' 
    WHERE EmpID= 2;

    COMMIT;
    PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'An error occurred. Transaction rolled back.';
END CATCH;

Catch

Failed transaction

Failed

Conclusion

In SQL, transactions are sequences of operations performed as a single logical unit of work, ensuring data consistency and integrity. A transaction follows the ACID properties: Atomicity (all-or-nothing execution), Consistency (ensures data validity), Isolation (independence of concurrent transactions), and Durability (changes persist after completion). Transactions are crucial for managing database operations reliably and are typically controlled with commands like BEGIN, COMMIT, and ROLLBACK.