Lesson 6: Transactions and Concurrency Control

A transaction is a sequence of database operations (inserts, updates, deletes) that must all succeed as a unit. The ACID properties ensure transaction reliability: Atomicity (all or nothing – if any part fails, the whole transaction rolls back), Consistency (the database moves from one valid state to another, respecting all rules), Isolation (transactions do not interfere – interim changes are not visible to others until commit), and Durability (once committed, changes survive system crashes). For example, transferring ₱1000 from Account A to B involves two updates; ACID ensures that either both updates happen or neither does, so money is never lost or created. In SQL, transactions are demarcated by BEGIN/COMMIT/ROLLBACK (e.g. BEGIN TRANSACTION; UPDATE ...; COMMIT;).

When multiple users access the database, concurrency issues can arise (like lost updates, dirty reads). DBMSs use locking and isolation levels to prevent these anomalies. Basic isolation levels include:

• Read Uncommitted: Lowest level, transactions see others' uncommitted changes (allows dirty reads).
• Read Committed: A transaction only sees data committed by others (prevents dirty reads).
• Repeatable Read: Ensures that if you read the same row twice in one transaction, you get the same value (prevents non-repeatable reads).
• Serializable: Highest isolation, transactions are fully isolated (no anomalies).

In practice, the PostgreSQL/MySQL default is usually Read Committed. Exams may describe scenarios like two students trying to register at the same time; discuss how locks prevent duplicate seats.