Chapter 9: Transactions and Concurrency

Transactions

A transaction is a logical unit of work: a sequence of database operations performed as a single unit. Transactions are a fundamental feature of database systems, and they keep data consistent and intact. The SQL standard defines the concept, and all modern database management systems support it.

Transactions have several benefits, including:

• Atomicity: either all of a transaction’s operations complete successfully or none of them do. If a transaction fails, all of its changes are rolled back to the previous state, so the data stays consistent.
• Consistency: the database stays in a consistent state throughout the transaction. Any change made during a transaction must follow the rules defined by the database schema.
• Isolation: each transaction is isolated from the others and sees a consistent snapshot of the database.
• Durability: once a transaction is committed, its changes persist and survive system failures.

To run a transaction in SQL, you use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements. BEGIN TRANSACTION marks the start of a transaction, and COMMIT marks the end of a successful one. If something fails, ROLLBACK returns the transaction to its starting state.

Concurrency control

Concurrency control manages concurrent access to shared resources. In a database system, that means managing access by multiple users or applications at the same time. The main goal is to keep data consistent and intact while that access is going on.

Concurrency control techniques fall into two categories, pessimistic and optimistic. Pessimistic concurrency control locks resources to prevent concurrent access. Optimistic concurrency control allows concurrent access and detects conflicts after they occur.

In SQL, transactions ensure consistency and integrity, and concurrency control techniques manage concurrent access. Database systems use several concurrency control mechanisms, including locking, timestamp ordering, and multiversion concurrency control (MVCC).

Locking is a pessimistic technique in which a transaction acquires locks on resources to prevent concurrent access. SQL provides several types of locks, including shared locks and exclusive locks.

Timestamp ordering assigns a timestamp to each transaction when it starts and uses those timestamps to settle conflicts: if an operation would break the timestamp order, its transaction is rolled back and restarted. It’s a technique inside some database engines, not something you write in SQL. (The TIMESTAMP data type only stores dates and times, and SET TRANSACTION sets options such as the isolation level.)

MVCC keeps multiple versions of a data item so that it can be accessed concurrently. SQL databases that support MVCC use snapshot isolation, where each transaction sees a consistent snapshot of the database.

Here is an example of how concurrency control keeps the data in a database consistent.

A bank has a database with two tables, accounts and transactions. The accounts table has the columns account_number, balance, and customer_name. The transactions table has the columns transaction_id, account_number, transaction_type, and amount.

Suppose two transactions run at the same time.

Transaction 1 transfers $100 from account A to account B:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_number = A;
UPDATE accounts SET balance = balance + 100 WHERE account_number = B;
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (A, 'debit', 100);
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (B, 'credit', 100);

COMMIT;

Transaction 2 deposits $200 into account A:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance + 200 WHERE account_number = A;
INSERT INTO transactions (account_number, transaction_type, amount) VALUES (A, 'credit', 200);

COMMIT;

Without concurrency control, the two transactions could interfere with each other and leave the database in an inconsistent state. Say transaction 1 runs its first UPDATE statement to subtract $100 from account A’s balance, and before it commits, transaction 2 runs its UPDATE statement to add $200 to account A’s balance. The final balance of account A will then be wrong.

A concurrency control mechanism such as locking prevents this. When transaction 1 starts, the database system can acquire an exclusive lock on account A’s row and another on account B’s row. No other transaction can modify either row until transaction 1 is committed or rolled back.

A database system that supports MVCC can use snapshot isolation instead, which allows concurrent access and still keeps the data consistent. Each transaction sees a consistent snapshot of the database as it was when the transaction started, and changes that other transactions make after that point are not visible to it. In this example, transaction 1 and transaction 2 would both see the initial balance of account A, even if transaction 1 modifies the balance before transaction 2 finishes.

Snapshots only cover reads, though. When both transactions update the same row, as they do with account A here, the database still has to stop one change from overwriting the other. Systems that use MVCC, such as PostgreSQL and MySQL’s InnoDB engine, also lock each row a transaction updates, so the second UPDATE on account A waits until the first transaction commits or rolls back. Depending on the isolation level, the waiting transaction then either applies its change to the newly committed balance or fails with an error and has to be retried.

Locking and MVCC each have pros and cons, and which one to use depends on the specific requirements of the application.