SQL Transactions

A transaction in SQL refers to a set of SQL operations that are executed as a single unit. A transaction is designed to ensure data integrity and consistency, meaning that all changes within a transaction are either fully applied or fully rolled back if something goes wrong. Transactions are commonly used to group multiple operations, such as inserting, updating, or deleting data, into one atomic block of work.

ACID Properties

Transactions are guided by the ACID properties, which ensure that they are processed reliably:

1. Atomicity: A transaction is atomic, meaning it is treated as a single unit of work. Either all operations within the transaction are committed (saved) to the database, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and no changes are made.

2. Consistency: A transaction ensures that the database moves from one valid state to another. It maintains the integrity constraints of the database, such as foreign keys and unique constraints. After a transaction is committed, the database should remain in a consistent state.

3. Isolation: Each transaction is isolated from other transactions. This means that the operations of one transaction should not be visible to others until it is committed. SQL engines often support different isolation levels to control the degree of isolation, such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

4. Durability: Once a transaction is committed, its changes are permanent and will survive any system failures. The database guarantees that committed data will not be lost, even in the event of crashes.

Transaction Lifecycle

A typical transaction in SQL goes through the following lifecycle:

1. Start Transaction: The transaction begins with the BEGIN statement or an implicit start depending on the database.

2. Execute SQL Statements: Various SQL statements (e.g., INSERT, UPDATE, DELETE) are executed within the transaction.

3. Commit: If all operations within the transaction are successful and you want to save the changes, you use the COMMIT statement. This makes all changes made in the transaction permanent.

4. Rollback: If something goes wrong or an error occurs, you can use the ROLLBACK statement to undo all changes made during the transaction. The database reverts to its state before the transaction started.

Example of SQL Transaction

Here’s an example of how a transaction works:

BEGIN;

-- Step 1: Insert a new record into the "users" table
INSERT INTO users (username, email) VALUES ('johndoe', 'john@example.com');

-- Step 2: Update another record in the "accounts" table
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- Step 3: If both operations are successful, commit the transaction
COMMIT;

If an error occurs at any step, you can use the following:

ROLLBACK;

This will undo all changes made so far in the transaction.

Isolation Levels

SQL provides different isolation levels to define the visibility of changes made by one transaction to others. These isolation levels control how transactions interact with each other in terms of reading and writing data:

1. Read Uncommitted: Transactions can read uncommitted changes from other transactions (can lead to dirty reads).
2. Read Committed: Transactions can only read committed data, but changes made by a transaction are visible only after it commits.
3. Repeatable Read: Ensures that if a transaction reads a value, it will see the same value throughout the entire transaction, preventing non-repeatable reads.
4. Serializable: The highest isolation level, where transactions are executed in such a way that it is as if they were executed serially (one after the other), preventing phenomena like phantom reads.

Conclusion

SQL transactions are essential for ensuring the consistency, integrity, and reliability of database operations. They help manage operations in an atomic way, meaning they either complete fully or not at all, maintaining the integrity of the data. Understanding how to use transactions, along with ACID properties and isolation levels, is crucial for working with SQL databases in a robust way.

Interview angle

• “Explain ACID.” - atomicity (all or nothing), consistency (constraints hold across the transaction boundary), isolation (concurrent transactions don’t interfere in ways the level forbids), durability (committed data survives a crash).
• “Which isolation level is the default, and what does it allow?” - READ COMMITTED in Postgres, REPEATABLE READ in MySQL/InnoDB. READ COMMITTED still permits non-repeatable reads and phantoms, so two reads in one transaction can differ.
• “How do you prevent a lost update?” - SELECT ... FOR UPDATE for pessimistic locking, or a version column checked on write for optimistic concurrency. Read-modify-write without either loses one of two concurrent updates silently.
• “How long should a transaction be?” - as short as possible. Long transactions hold locks, block DDL, and in Postgres prevent vacuum from cleaning dead tuples. Never hold one open across an external API call.