Introduction to SQL Transactions

In SQL, a transaction is a logical unit of work that contains one or more SQL statements, which are executed as a single unit. The main idea behind SQL transactions is that they allow you to group multiple operations, ensuring that they are either fully completed or not executed at all, thereby maintaining database integrity.

SQL transactions are vital in maintaining ACID properties: Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably and consistently, even in the event of system failures or unexpected interruptions.

In this article, we’ll explore SQL transactions in detail, explain ACID properties, and discuss common transaction management commands: COMMIT, ROLLBACK, and SAVEPOINT with practical examples.

What Are ACID Properties?

ACID stands for:

Atomicity

A transaction is an atomic unit of work, which means it is indivisible. Either all operations in the transaction are completed successfully, or none of them are applied. If one part of the transaction fails, the entire transaction is rolled back, leaving the database in a consistent state.

Consistency

A transaction ensures that the database moves from one consistent state to another. It enforces all rules, constraints, and triggers that maintain data integrity.

Isolation

Isolation ensures that the operations of a transaction are not visible to other transactions until the transaction is complete. This prevents transactions from interfering with each other, ensuring that intermediate states are not visible.

Durability

Once a transaction has been committed, its changes are permanent, even if the system crashes afterward. The data changes are written to durable storage (e.g., disk) and will survive any failure.

Transaction Handling in SQL

A transaction is typically defined using the following SQL commands:

Example of Transaction Handling

Let’s consider a banking system where we want to transfer money between two accounts.

We will perform the following operations in a single transaction:

-- Start a transaction
BEGIN TRANSACTION;

-- Deduct 500 from Account A
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

-- Add 500 to Account B
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

-- Commit the transaction, making the changes permanent
COMMIT;

Result: If both UPDATE statements execute successfully, the changes are committed, and both accounts will be updated.

Using COMMIT, ROLLBACK, and SAVEPOINT

Let’s explore each of these transaction commands with examples.

1. COMMIT

The COMMIT command is used to permanently save all changes made during the transaction. Once a COMMIT is executed, the changes are written to the database, and the transaction is complete.

Example:

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

-- Commit the changes, making them permanent
COMMIT;

Result: The transaction is successfully completed, and the database reflects the updated balances for both accounts.

2. ROLLBACK

The ROLLBACK command is used to undo all changes made during the transaction. If an error occurs or if you decide not to proceed with the changes, you can use ROLLBACK to revert the database to its state before the transaction started.

Example:

Let’s say there’s an error while transferring money, and we need to cancel the entire transaction.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

-- Simulate an error (e.g., insufficient funds in Account A)
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;  -- Non-existent account

-- Since an error occurred, we rollback the transaction
ROLLBACK;

Result: The ROLLBACK ensures that no changes are made to any account, and the balances remain unchanged.

3. SAVEPOINT

The SAVEPOINT command allows you to set a point within a transaction to which you can roll back without rolling back the entire transaction. This is useful if you want to undo part of a transaction but keep the other parts intact.

Example:

Let’s say we want to deduct money from two accounts, but if something goes wrong in the second update, we want to roll back only that update and not the entire transaction.

BEGIN TRANSACTION;

-- Deduct 500 from Account A
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

-- Create a SAVEPOINT after the first update
SAVEPOINT before_transfer;

-- Try to update Account B
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

-- Simulate an error
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 3;  -- Non-existent account

-- Rollback to the SAVEPOINT (undo the second update but keep the first one)
ROLLBACK TO SAVEPOINT before_transfer;

-- Commit the transaction
COMMIT;

Result:

Transaction Isolation Levels

Transaction isolation defines how the operations in one transaction are isolated from operations in other concurrent transactions. SQL provides several isolation levels, each providing a different level of concurrency and consistency.

1. Read Uncommitted

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Conclusion

Understanding SQL transactions and the ACID properties is crucial for ensuring the integrity and reliability of your database operations. By using transaction management commands like COMMIT, ROLLBACK, and SAVEPOINT, you can effectively manage changes, recover from errors, and implement sophisticated business logic within your database.

Each transaction isolation level offers a balance between consistency and concurrency, allowing you to fine-tune your database to meet the needs of your application. Understanding and utilizing transactions properly will help ensure that your database behaves predictably, even under heavy load or complex scenarios.