Lesson 5: Transactions, Concurrency Control, and Recovery Basics

A transaction is a logical unit of work that should either complete properly or not take effect at all.

Example: during enrollment, the system may need to:

• add the student's subject record
• update available slots
• record assessment data

If the first two steps succeed but the third fails, the database may become inconsistent. Transactions prevent this by grouping related operations.

The classic properties of transactions are called ACID:

• Atomicity — all or nothing
• Consistency — valid state before and after
• Isolation — concurrent work should not interfere improperly
• Durability — committed work survives failures

For DBAs, transactions are essential because they connect application behavior to correctness, locking, logging, and recovery.

In real systems, many users act at the same time. This is called concurrency. Without proper control, one user's action can interfere with another's.

Common anomalies include:

Lost update — Two users read the same row, both modify it, and one update overwrites the other.

Dirty read — A transaction reads data written by another transaction that has not yet committed.

Non-repeatable read — A transaction reads the same row twice and gets different values because another transaction changed it in between.

Phantom read — A transaction reruns a query and finds new rows added by another transaction.

DBMSs manage these issues through locking, timestamps, or other concurrency methods, and expose different isolation levels. Higher isolation can increase correctness but may reduce concurrency and speed.

A DBA must understand that performance tuning is not only about speed. Sometimes the correct question is:

"What level of isolation does this business process really need?"