SQL Transactions

SQL transactions are a fundamental concept in relational database management systems. They allow a sequence of operations to be treated as a single, atomic unit of work. This ensures data integrity and consistency, especially in scenarios involving multiple, interdependent data modifications.

What is a Transaction?

A transaction is a logical unit of work that comprises one or more SQL statements. These statements are executed sequentially. The core principle of a transaction is that either all the operations within it are successfully completed, or none of them are. This is often referred to as the "all or nothing" principle.

ACID Properties

Transactions are governed by the ACID properties to guarantee reliability:

Atomicity: Ensures that all operations within a transaction are completed successfully, or the transaction is rolled back entirely, leaving the database in its original state.
Consistency: Guarantees that a transaction brings the database from one valid state to another. It ensures that any data written to the database is valid according to all predefined rules, including constraints, cascades, triggers, and any combination thereof.
Isolation: Dictates that concurrent transactions do not interfere with each other. Each transaction appears to be executing on its own. This prevents issues like dirty reads, non-repeatable reads, and phantom reads.
Durability: Guarantees that once a transaction has been committed, its changes are permanent and will survive subsequent system failures, such as power outages or crashes.

Transaction Control Language (TCL)

SQL provides specific commands to manage transactions:

BEGIN TRANSACTION or START TRANSACTION: Initiates a new transaction.
COMMIT: Saves all changes made during the current transaction to the database.
ROLLBACK: Discards all changes made during the current transaction and restores the database to its state before the transaction began.
SAVEPOINT: Creates a point within a transaction to which you can later roll back.

Example: Transferring Funds

Consider a common scenario: transferring money between two bank accounts. This operation involves two steps: debiting one account and crediting another. Both must succeed or fail together.


-- Assume two accounts: AccountA and AccountB
-- Transfer $100 from AccountA to AccountB

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 'AccountA';

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 'AccountB';

-- If both updates are successful, commit the transaction
COMMIT;

-- If an error occurs during either update, rollback:
-- ROLLBACK;

Isolation Levels

Databases offer different isolation levels to manage the trade-off between data consistency and concurrency. Common levels include:

READ UNCOMMITTED: Allows transactions to read uncommitted data from other transactions (prone to dirty reads).
READ COMMITTED: Ensures that transactions only read data that has been committed.
REPEATABLE READ: Guarantees that if a transaction reads a row multiple times, it will see the same data each time, preventing non-repeatable reads.
SERIALIZABLE: The highest level, ensuring that concurrent transactions execute as if they were executed serially, preventing all concurrency anomalies.

The default isolation level often varies between database systems.

Best Practices

Keep transactions as short as possible to minimize locking and improve concurrency.
Handle potential errors and implement appropriate ROLLBACK logic.
Use SAVEPOINTs for complex operations that might require partial rollback.
Understand and configure appropriate isolation levels based on application requirements.