Intuition

$\sigma(\mathcal R) \rightarrow \sigma(\mathcal R \uplus \Delta \mathcal R)$

$\equiv$ $\sigma(\mathcal R)$ $\uplus$ $\sigma(\Delta \mathcal R)$

Set/Bag difference also commutes through selection

$\pi(\mathcal R) \rightarrow \pi(\mathcal R \uplus \Delta \mathcal R)$

$\equiv$ $\pi(\mathcal R)$ $\uplus$ $\pi(\Delta \mathcal R)$

Does this work under set semantics?

$\mathcal R_1 \uplus \mathcal R_2 \rightarrow \mathcal R_1 \uplus \Delta \mathcal R_1 \uplus \mathcal R_2 \uplus \Delta \mathcal R_2$

$\equiv$ $\mathcal R_1 \uplus \mathcal R_2$ $\uplus$ $\Delta \mathcal R_1 \uplus \Delta \mathcal R_2$

Transactions

A "batch" of operations that should execute together

START/BEGIN TRANSACTION

Start batching

COMMIT

Conclude the transaction and apply changes

ABORT/ROLLBACK

Undo all changes applied as part of the transaction

Atomicity

A transaction is either applied fully (COMMITed) or not at all (ABORTed).

Consistency

Constraints are enforced on the final state of the transaction (and the transaction is ABORTed if they fail).

Isolation

Two transactions running in parallel don't interfere with each other

Durability

Once the database returns from a COMMIT successfully, the data is safe from marmots

What does it mean for a transaction to be Isolated?

Alice and Bob submit transactions at the same time!

Option 1

Alice's transaction executes to completion.

Bob's transaction executes to completion.

Option 2

Bob's transaction executes to completion.

Alice's transaction executes to completion.

Schedule

A sequence of read and writes from one or more transactions to objects

Serial Schedule

A schedule with no interleaving

... but with an arbitrary transaction order

Serializable Schedule

A schedule that produces the same output as a serial schedule

Conflict Equivalence

Two schedules are conflict equivalent if there is a sequence of pairwise "flips" (of reads, or operations on different objects) that gets you from one schedule to the other.

Example 1

Example 1

Conflict equivalent to a serial schedule!

Example 2

Can't rewrite!

Conflict Serializability

A schedule is conflict serializable if it is conflict equivalent to a serial schedule.

How do we determine if a schedule is conflict-serializable?

Example 2

T2's write to B "happens before" T1's read

T1's write to A "happens before" T2's write

Cycle! No equivalent serial schedule!

An acyclic "Happens Before" or Dependency Graph is conflict serializable.

2-Phase Locking

Create one lock for each object.

Each transaction operates in two "phases".

Acquire Phase

Before accessing an object, the transaction must acquire the object's lock.

The transaction does not release locks.

Release Phase

A transaction can release locks

A transaction can never again access an object it doesn't have a lock for.

In practice, the release phase happens all at once at the end

All schedules created by 2PL are conflict-serializable

Not all conflict-serializable schedules can be created by 2PL

$2PL \subset CS$

Snapshot Isolation

Phase 1: Read

Transaction executes on a private copy of all accessed objects

Phase 2: Validate

Check if applying the transaction would break isolation

Phase 3: Write

Write the transaction's updates out to the main database

Validation Phase

Pick a serial order (e.g., the order in which transactions reach the validation phase)

Make sure the transaction's operations follow this order

T1 and T2 read the same object

OK!

T1 reads an object written by T2 (read-write)

Ok. (T1's validation phase started before T2's)

T2 reads an object written by T1 (write-read)

Ok if T1-Write finishes before T2-Read starts.

T1 and T2 write the same object (write-write)

Ok if T1-Write finishes before T2-Write starts.

All schedules created by Snapshot Isolation are conflict-serializable

... but Snapshot Isolation only checks for equivalence to ONE serial schedule. There might be a different, conflict-equivalent serial schedule.

$SI \subset CS$

Timestamp Concurrency Control

(Snapshot Isolation as seen "in practice")

Each object $A$ gets a read timestamp ($RTS(A)$) and a write timestamp ($WTS(A)$)

Each transaction $\mathcal T$ gets a timestamp ($TS(\mathcal T)$).

(note that these can be logical timestamps like sequence numbers)

(also note that real DBs don't use read timestamps... which creates problems)

View Serializability

View Equivalence

Two schedules are view-equivalent when you can transform one into the other by reordering any pair of operations that...

• operate on different objects
• OR, are both reads
• OR, are both writes to the same object, but ONLY IF the object is later overwritten by another write.

View Serializability

A schedule is view serializable if it is view-equivalent to some serial schedule

Timestamp concurrency control is guaranteed to produce view-serializable schedules.

View Serializability

On the happens-before graph, throw away edges created by "hidden" write-write conflicts.

If the resulting graph is acyclic, the schedule is view serializable

By definition there are view serializable schedules that are not conflict serializable

$CS \subset VS$

$2PL, SI \subset CS \subset VS \subset S$

Buffer memory is limited...

What if we need to page out some pages modified by a live transaction?

Write-Ahead Logging (WAL)

1. "Log" the transaction's actions
2. Wait for the log to be safely on-disk
3. Write the transaction's effects to disk

Idea: Periodically mark down the index of the earliest log entry still needed

WAL Recovery

1. Scan backwards through the log to find the checkpoint.
2. Scan forward starting with the checkpointed 'first log entry' to find all transactions with a COMMIT entry.
3. Replay log entries starting with the checkpointed 'first log entry', ignoring non-COMMITed transactions.

Undo-Logging

Log

Idea: Record the page's previous value.

Recovering after a crash

1. Rebuild in-memory state (Analyze)
2. Rebuild buffer manager (Redo)
3. Abort uncommitted transactions (Undo)

ARIES Recovery

Stop thinking about relations as collections of records, and instead think of them as collections of facts

The fact $R(1, 2)$ is true.

The fact $R(2, 1)$ is false (or unknown).

A table contains all facts that are provably true.

$$Q(A) :-~~ R(A, B), S(B, C)$$

• there is some $B$ and $C$ for which...
• the fact $R(A, B)$ is true, and...
• the fact $S(B, C)$ is true.

$\forall A : \big( \exists B, C : R(A, B) \wedge S(B, C) \big) \rightarrow Q(A)$

$$Q(A) :-~~ R(A, B), S(B, C)$$ $$Q(A) :-~~ R(A, B), R(B, C)$$

Treat multiple rules as a disjunction.
($Q(A)$ is true if any rule is satisfied)

As powerful as Set-RA

Projection

$Q := \pi_A(R)$

$Q(A) :-~~ R(A, \ldots)$

Union

$Q := R \cup S$

$Q(\ldots) :-~~ R(\ldots)$

$Q(\ldots) :-~~ S(\ldots)$

Join

$Q := R \bowtie S$

$Q(\ldots) :-~~ R(\ldots), S(\ldots)$

Selection (Equality)

$Q := \sigma_{R.A = R.B}(R)$

$Q(A) :-~~ R(A, A)$

Selection (Equality')

$Q := \sigma_{R.A = 1}(R)$

$Q(B) :-~~ R(1, B)$

Selection (Other)

$Q := \sigma_{A > B}(R)$

$Q(A,B) :-~~ R(A, B), [[ A > B ]]$

Relations are Sets of Facts. We can have a relation consisting of all pairs $A, B$ where $A$ is bigger.

How does the input data relate to a query output.

Types of Provenance

Why Provenance (Lineage)

What's the smallest fragment of my input needed to produce some row

Why-Not Provenance

What's the least I can add to my input to get a desired row

How Provenance

An execution trace of the result; How were the tuples combined?

Where Provenance

Which cell(s) was a given output value taken from

Taint

Was the output affected by any "tainted" input cell/row

Idea: Arithmetic models how a tuple was derived pretty well.

$[[ R(a, b) ]] \rightarrow \texttt{constant}$

$[[R(a, b) \cup S(a, b)]] \rightarrow [[R(a, b)]] \oplus [[S(a, b)]]$

$[[R(a, b) \times S(c, d)]] \rightarrow [[R(a, b)]] \otimes [[S(c, d)]]$

$[[\pi_a R(a, b)]] \rightarrow \sum_b [[R(a, b)]]]]$