What is a transaction?
A set of logical operations to access and update the various data items of a database.
Which two main issues must transaction processing deal with?
Failures of various kinds (hardware failures, system crashes) and concurrent execution of multiple transactions.
What does ACID stand for?
Atomicity, Consistency, Isolation, Durability.
Atomicity: what must the system ensure if a transfer fails after step 3?
That updates of a partially executed transaction are not reflected in the database ("All or nothing rule").
Abort vs commit.
Abort: changes made to the database are not visible. Commit: changes made are visible.
Consistency in the $50 transfer example.
The total is maintained: 200 + 100 = 300 before, 150 + 150 = 300 after — the transaction takes the database from one consistent state to another.
When does the durability requirement start to apply?
Once the user has been notified that the transaction has completed; its updates must then persist even if there are software or hardware failures.
What goes wrong without isolation in the transfer example, and how can isolation be ensured?
Another transaction reading A and B between steps 3 and 6 sees an inconsistent database (A + B less than it should be). Isolation can be ensured by running transactions serially.
Who is responsible for each ACID property?
Atomicity: Transaction Manager. Consistency: Application programmer. Isolation: Concurrency Control Manager. Durability: Recovery Manager.
List the five transaction states.
Active; partially committed; failed; aborted; committed.
Partially committed vs committed.
Partially committed: after the final statement has been executed. Committed: after successful completion.
What is the aborted state, and what are the two options after it?
The transaction has been rolled back and the database restored to its state prior to the start. Options: restart the transaction (only if no internal logical error) or kill it.
Two advantages of concurrent execution.
Increased processor and disk utilization (better transaction throughput); reduced average response time (short transactions need not wait behind long ones).
What are concurrency control schemes?
Mechanisms to achieve isolation: they control the interaction among concurrent transactions to prevent them from destroying the consistency of the database.
What is a schedule, and which two rules must it follow?
A sequence of instructions specifying the chronological order of the instructions of concurrent transactions. It must consist of all their instructions and preserve the order within each individual transaction.
What is the last statement of a transaction that completes, and of one that fails?
Commit for a successful transaction (assumed by default); abort for a failed one.
Is Schedule 3 serial? Is it acceptable?
It is not serial, but it is equivalent to Schedule 1 (and preserves A + B), so it is acceptable — it is conflict serializable (slide 20).
When is a schedule serializable?
When it is equivalent to a serial schedule (conflict serializability or view serializability).
The three conditions for two operations to conflict.
They belong to different transactions; they operate on the same data item; at least one of them is a write operation.
Define conflict equivalent and conflict serializable.
S and S' are conflict equivalent if S can be transformed into S' by a series of swaps of non-conflicting instructions. S is conflict serializable if it is conflict equivalent to a serial schedule.
Schedule: T3 read(Q); T4 write(Q); T3 write(Q). Why is it not conflict serializable?
T4's write(Q) lies between T3's read(Q) and write(Q); these conflict, so no swaps give either < T3, T4 > or < T4, T3 >.
When is an arc drawn from Ti to Tj in a precedence graph?
When the two transactions conflict, and Ti accessed the data item on which the conflict arose earlier.
What is the precedence-graph test for conflict serializability?
A schedule is conflict serializable if and only if its precedence graph is acyclic; the serializability order is a topological sorting of the graph.
How long does cycle detection take?
Order n² time (n = number of vertices); better algorithms take order n + e (e = number of edges).
How does a transaction begin and end in SQL?
It begins implicitly. It ends by commit work (commits and begins a new one) or rollback work (aborts the current transaction).