IB Computer Science Topic A3.3.6: How Transactions Maintain Data Integrity in a Database Notes

A Transaction Groups Statements So They Succeed or Fail Together

1. A transaction is a sequence of database operations treated as one indivisible unit of work.
2. Many real operations need more than one statement, and a bank transfer needs two: one account goes down and another goes up.
3. Run separately, a crash between the two statements destroys money that has left one account and never arrived at the other.
4. Grouping them into a transaction removes that possibility, and the four ACID properties are the guarantees that make it work.

Atomicity: The Transaction Happens Completely or Not at All

1. Atomicity means the database never shows a transaction half done.
2. If any statement in the group fails, every change made by the group is undone.
3. The database keeps a log of what changed, so it can reverse the earlier statements when a later one fails.

Consistency: Each Transaction Moves the Database Between Valid States

1. Consistency means every rule defined on the database still holds once the transaction ends.
2. Those rules include primary keys, foreign keys, NOT NULL columns, data types and any check constraint the designer added.
3. A transaction that would break one of them is rejected and rolled back rather than partly applied.
4. Consistency is only as strong as the constraints that were written, since the database can only enforce rules it has been told about.

Isolation: Concurrent Transactions Cannot See Each Other's Unfinished Work

1. Isolation means each transaction behaves as though it were the only one running, even when hundreds run at once.
2. The database achieves this by locking the rows a transaction is working on until it finishes.
3. Without it, two transactions reading the same value before either writes will each overwrite the other's work.

Durability: Once Committed, the Change Survives a Crash

1. Durability means a committed transaction is written to permanent storage and cannot be lost.
2. The database writes to its transaction log before reporting success, so a crash immediately afterwards is survivable.
3. On restart it replays the log and reapplies any committed change that had not reached the data files.
4. A power cut a second after a transfer confirms is therefore not able to reverse it.

TCL Provides the Three Commands That Control a Transaction

1. Transaction Control Language (TCL) is the part of SQL that starts, confirms and abandons transactions.
2. BEGIN TRANSACTION
   1. Marks the start, so every statement after it is held provisionally rather than applied.
   2. Nothing another user reads changes yet.
3. COMMIT
   1. Makes every change in the transaction permanent and visible to other users.
   2. It is the point at which durability takes effect, and it cannot be undone afterwards.
4. ROLLBACK
   1. Discards every change since the transaction began, returning the database to its earlier state.
   2. It is how atomicity is delivered in practice, and the database issues one itself if the connection drops.

A Bank Transfer Written Out in Full

1. The transaction opens, isolating the two account rows from other transactions.
2. Fifty is taken from account 1001 and the same fifty is added to account 2002.
3. If both statements succeed, COMMIT makes the pair permanent.
4. If either fails, ROLLBACK removes both and the balances are exactly as they were.

How the Four Properties Map Onto the Transfer

ACID Guarantees Are Paid For in Performance

1. What ACID buys
   1. Data that is always in a valid state, and safe recovery after a failure.
   2. Many users working at once without corrupting each other's changes.
2. What ACID costs
   1. Locking makes transactions wait for each other, which lowers throughput under heavy load.
   2. Writing to the transaction log before every commit is extra disk work on every change.
   3. Two transactions can each hold a lock the other is waiting for, which is the deadlock problem A1.3.5 describes.
3. For banking or ticketing the guarantees are worth the cost, while a system logging sensor readings may reasonably trade some of them for speed.

Recognising When a Scenario Needs a Transaction

1. Any operation needing two or more writes that must agree with each other is a transaction.
2. Booking a seat and taking the payment is one example, and moving stock between two warehouses is another.
3. A single UPDATE affecting one row does not need one, because it is already atomic on its own.