Transactions

What
Transactions are units of work done by the database. Transactions may contain multiple queries.

ACID Properties

 * A - Atomicity. All the actions in a transaction happens, or none happen at all
 * C - Consistency. If the transaction is consistent, and the database started in a consistent state, the database will still be consistent after the transaction commits.
 * I - Isolation. The effects of transactions are independent of one another
 * D - Durability. If a transaction commits, its effects are permanent

Atomic Property
Atomicity states that all actions in a transaction must happen, or none will happen. There are a few ways to implement this but one is:
 * Keep track of a log of actions so that the DBMS can undo the effects of aborted transactions.

Consistency Property
Along with the idea mentioned above, the consistency property also deals with integrity constraints such as primary keys and such defined by the CREATE TABLE statement. If any ICs fail, the transaction will ? be rolled back.

Isolation Property
Isolation states that all transactions should run as if they were alone. Therefore, this also amounts to concurrency. The DBMS achieves this property by interleaving read and write calls of various transactions. There are two general approaches
 * Pessimistic - Don't allow problems to arise in the first place
 * Optimistic - Assume conflicts are rare and deal with them after they happen

Durability Property
The DBMS must be able to recover from a crash when they inevitably happen. There are generally two types of crashes There are three steps to recovery in the ARIES recovery, and most other systems
 * System Crash - short term memory lost but disk is OK.
 * Disk Crash - needs technologies like RAID to help us back up the data in cases like these
 * Analysis - Scan the log forward from the most recent checkpoint to find all the active transactions and dirty pages in the buffer pool
 * Redo - Redo all the dirty pages in the buffer pool by writing them out to disk
 * Undo - Undo all the effects of the active transactions by returning to their before values as stored in the logs