Some database operations involve several steps that must succeed or fail together. A transaction is a group of operations treated as one logical unit of work.

A transaction example

Transferring $100 from account A to account B has two steps:

BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 100 WHERE ID = 'A';
UPDATE Account SET Balance = Balance + 100 WHERE ID = 'B';
COMMIT;

If the system crashed after the first update but before the second, $100 would vanish. A transaction ensures both happen or neither does. COMMIT makes changes permanent; ROLLBACK undoes everything since BEGIN.

ACID properties

Reliable transaction systems guarantee four properties:

  • Atomicity: the transaction is all-or-nothing. If any step fails, all earlier steps are rolled back.
  • Consistency: the database moves from one valid state to another, obeying all rules (keys, constraints, balances). A transfer cannot create or destroy money.
  • Isolation: concurrent transactions do not interfere; each behaves as if it ran alone.
  • Durability: once committed, the changes survive crashes and power failures, usually because they are written to a transaction log on disk.

Concurrency problems and control

When many users access data at once, interference can occur. A lost update happens if two users read the same balance, each change it, and the second write overwrites the first. Example: both read $500, one adds $100, the other subtracts $50; without control the final value could be $600 or $450 instead of $550.

Solutions:

  • Record locking: a transaction locks the data it uses; others must wait until it finishes. A shared lock allows reading, and an exclusive lock allows writing.
  • Deadlock: two transactions each hold a lock the other needs, so both wait forever. The system detects this and rolls one back, or prevents it by locking in a fixed order.
  • Timestamp ordering and optimistic methods let transactions proceed and check for conflicts at commit time.

Recovery

A DBMS keeps a transaction log and takes regular backups. After a crash it replays committed transactions and rolls back unfinished ones. Together with access rights, backups and logs support security and data integrity. Transaction processing is crucial for banking, airline booking and online shopping.

Check your understanding

What does atomicity guarantee?

  • All steps of a transaction happen or none do
  • Data is encrypted
  • Queries are fast
  • Tables are normalised

Answer: All steps of a transaction happen or none do. A failure triggers rollback.

Which ACID property ensures committed data survives a power cut?

  • Atomicity
  • Consistency
  • Isolation
  • Durability

Answer: Durability. The transaction log makes recovery possible.

What is a deadlock?

  • Two transactions each waiting for a lock the other holds
  • A deleted table
  • A full disk
  • A failed login

Answer: Two transactions each waiting for a lock the other holds. One must be rolled back.

What does ROLLBACK do?

  • Undoes all changes made in the current transaction
  • Commits the changes
  • Deletes the database
  • Locks the table

Answer: Undoes all changes made in the current transaction. The database returns to its earlier state.

PreviousNext