Transactions ​

When executing SQL statements, certain business requirements dictate that a series of operations must either all succeed or none at all. For example, in a transfer operation:

-- Transfer 100 from account id=1 to account id=2
-- Step 1: Deduct 100 from account A with id=1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Step 2: Add 100 to account B with id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Both SQL statements must execute completely, meaning if the first statement succeeds but the second fails, all changes must be rolled back.

This feature of treating multiple statements as a single operation is called a database transaction. Transactions ensure that all operations within their scope either succeed or fail together. If a transaction fails, it is as if none of the SQL statements were executed, leaving the database unchanged.

Transactions possess the ACID properties:

• A: Atomicity – All SQL statements are executed as atomic units; they either all execute or none do.
• C: Consistency – After the transaction, all data states are consistent; if account A is deducted by 100, account B must be increased by 100.
• I: Isolation – Changes made by concurrent transactions must be isolated from each other.
• D: Durability – Once a transaction is completed, its changes are permanently stored.

For single SQL statements, the database system automatically treats them as transactions, known as implicit transactions.

To manually execute multiple SQL statements as a transaction, use BEGIN to start a transaction and COMMIT to finalize it. This is called an explicit transaction. For the transfer operation above:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Multiple SQL statements must use explicit transactions to be executed as a unit.

COMMIT saves all changes made within the transaction. If the COMMIT fails, the entire transaction fails.

Sometimes, you may want to intentionally fail a transaction. In such cases, you can use ROLLBACK to revert all changes:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

The database system manages transactions; you simply utilize them according to your business logic.

Isolation Levels ​

When two transactions operate concurrently on the same record, inconsistencies may arise, leading to issues like dirty reads, non-repeatable reads, and phantom reads. Database systems provide isolation levels to help select the appropriate level of isolation for transactions, mitigating data inconsistency problems.

The SQL standard defines four isolation levels, each corresponding to potential inconsistency scenarios:

We will discuss the data consistency issues associated with these four isolation levels in turn.

Summary ​

Database transactions possess ACID properties, ensuring that multiple SQL statements are executed entirely or not at all.