Transactions and concurrency

Introduction

Moving 300 taka from Rahim to Nadia takes two UPDATEs. If the power fails between them, the money vanishes. A transaction groups statements so they happen all together or not at all.

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Rahim';
UPDATE accounts SET balance = balance + 300 WHERE name = 'Nadia';
COMMIT;      -- make every change permanent
-- or ROLLBACK; to undo every change since BEGIN

CREATE TABLE accounts (
  name TEXT PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES ('Rahim', 1000), ('Nadia', 500);

-- Move 300 from Rahim to Nadia: both changes, or neither
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Rahim';
UPDATE accounts SET balance = balance + 300 WHERE name = 'Nadia';
COMMIT;

-- A mistake inside a transaction can still be undone
BEGIN;
DELETE FROM accounts;
SELECT COUNT(*) AS accounts_left FROM accounts;
ROLLBACK;

SELECT * FROM accounts;

Outside BEGIN ... COMMIT, each statement is its own small transaction (autocommit).

Real systems change several rows at once and run many changes at the same time. Transactions make those changes safe, and they are why your bank balance is never left half updated.

How it works

ACID

A DBMS promises four things about transactions:

Problems when transactions overlap

A busy DBMS runs many transactions at once. Without isolation, these go wrong:

The SQL standard's isolation levels say which problems a transaction accepts in return for speed:

Serializable means the result is the same as if the transactions had run one after another, in some order. A schedule is conflict serializable when its precedence graph (an arrow T1 → T2 whenever T1 touches an item before T2 does in a conflicting way, with at least one of them a write) has no cycle.

Locks and logs

• Locks: a transaction takes a shared lock to read an item and an exclusive lock to write it; many can share, but an exclusive lock waits for everyone else. Two-phase locking (take all locks first, release them only at the end) guarantees serializable schedules. Two transactions waiting for each other's locks are a deadlock; the DBMS spots it and rolls one back.
• Logs: before changing the data, the DBMS writes what it is about to do to a log (write-ahead logging). After a crash it redoes committed work from the log and undoes unfinished work. That is how atomicity and durability survive a power cut.

Syntax

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE name = 'Rahim';
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 300 WHERE name = 'Nadia';
ROLLBACK TO after_debit;   -- undo only the second change
COMMIT;

Example

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  item TEXT,
  qty INTEGER
);

BEGIN;
INSERT INTO orders (item, qty) VALUES ('Pen', 10);
SAVEPOINT before_bags;
INSERT INTO orders (item, qty) VALUES ('Bag', 500);
-- 500 bags was a typing mistake: undo only that
ROLLBACK TO before_bags;
INSERT INTO orders (item, qty) VALUES ('Bag', 5);
RELEASE before_bags;
COMMIT;

SELECT * FROM orders;

A savepoint is a mark inside a transaction. ROLLBACK TO name undoes only the changes made after the mark; the transaction stays open, and earlier changes stay. RELEASE name removes the mark.

Line by line

1. BEGIN; starts a transaction, and the first order, 10 pens, is inserted.
2. SAVEPOINT before_bags; marks this point.
3. An order for 500 bags is inserted by mistake.
4. ROLLBACK TO before_bags; undoes only that insert. The transaction stays open, and the pen order stays.
5. The correct order, 5 bags, is inserted, RELEASE removes the mark, and COMMIT makes both orders permanent.
6. SELECT * FROM orders; shows the two orders.

Expected result

id  item  qty
--  ----  ---
1   Pen   10
2   Bag   5
(2 rows)

In the real world

Banks, ticket booking and stock control depend on transactions to keep totals right when thousands of people act at once. Write-ahead logs are also how databases recover after a power cut without losing committed work.

Common mistakes

• Forgetting to COMMIT, so the changes are lost when the connection closes.
• Holding a transaction open while waiting for a user, which blocks others.
• Assuming every DBMS uses serializable isolation by default: most use read committed.

Best practices

• Keep transactions short.
• Group exactly the statements that must succeed or fail together.
• Choose the isolation level a task really needs.

Summary

Transactions group statements so they succeed or fail together, with BEGIN, COMMIT, ROLLBACK and savepoints. ACID describes their guarantees, isolation levels balance safety against concurrency, and locks and logs make it work.

Mark lesson complete