CSE-4/562 Spring 2019

Transactions

March 27, 2019

Textbook: Ch. 18.1-18.2, 19.1

The running theme

If X and Y are equivalent and Y is better,
then replace all Xs with Ys

Today's focus: Updates

SQL DDL


            INSERT INTO Trees (location, species) 
              VALUES ('123 A Street', 'pin oak');
            
            DELETE FROM Trees WHERE date > now();
            
            UPDATE Trees SET species = 'pin oak' 
              WHERE species = 'pinoak';

What is "Correct"?

: My command gets executed fully, or not at all.
: The final state of the data is "sane".
: No concurrency glitches.
: If things break, my data is still safe.

My command gets executed fully, or not at all.

#	...	species
1	...	pin oak
2	...	pinoak
3	...	pinoak
4	...	pin oak
...


            UPDATE Trees SET species = 'pin oak' 
              WHERE species = 'pinoak';

Null Pointer Exception!

T-Rex Attack!

User-abort!

#	...	species
1	...	pin oak
2	...	pin oak
3	...	pinoak
4	...	pin oak
...

My command gets executed fully, or not at all.

The final state of the data is "sane".


      Bob.Balance -= 20
      Alice.Balance += 20

Bob's balance shouldn't drop below $0

Constraints

Primary Key / Unique: Unique column value for every row.
Foreign Key: Referenced value must exist.
Domain: Restrictions on allowable values
(e.g., Balance >= 0 or NOT NULL)
Check: Any boolean-valued SQL expression

No concurrency glitches.

If Alice and Bob both update the data at the same time,
nothing should break.

If things break, my data is still safe.

Once a write is confirmed, it should survive a power outage, crash, or marmot attack.

Atomicity: My command gets executed fully, or not at all.
Consistency: The final state of the data is "sane".
Isolation: No concurrency glitches.
Durability: If things break, my data is still safe.

Each use case may require different properties

Relational DBMSes (e.g., Oracle, DB2, Postgres): Full ACID (more or less)
Caches (e.g., Redis, Memcached): No need for Durability
Key-Value Stores (e.g., Riak, Aerospike, LevelDB): Only trivial Atomicity required

What if I want correctness over more than one operation?

Transactions


      START TRANSACTION
      UPDATE Ledger SET Balance = Balance - 20
        WHERE name = 'Bob' 
      UPDATE Ledger SET Balance = Balance + 20
        WHERE name = 'Alice' 
      COMMIT

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

A little abstraction...

Object

An object is a...

... a database "thing"

... a table
... a record
... a page
... a column

Each database is different,
so let's treat objects abstractly for now.

Observation: If two clients modify different objects, they can't possibly interfere with each other.

... but I'm getting ahead of myself

Transaction

A sequence of reads from and writes to objects.

... followed by a COMMIT or ABORT

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.

Example


                      def T1:
                            A = A + 100
                            B = B - 100


                      def T2: 
                            A = A * 1.06
                            B = B * 1.06

Example

Time	T1	T2
↓	`A = A + 100`
↓	`B = B - 100`
↓		`A = A * 1.06`
↓		`B = B * 1.06`

A gets an extra $6

Also acceptable

Time	T1	T2
↓		`A = A * 1.06`
↓		`B = B * 1.06`
↓	`A = A + 100`
↓	`B = B - 100`

B gets an extra $6

Not Acceptable

Time	T1	T2
↓	`A = A + 100`
↓		`A = A * 1.06`
↓		`B = B * 1.06`
↓	`B = B - 100`

A and B both get an extra $6

Idea: Don't allow updates in parallel!

Observation Blocking transactions is sloooow

Less Bad Idea: Create/Enforce the illusion that we're not allowing updates in parallel.

A Schedule

A sequence of Reads and Writes

Time	T1	T2
↓		`R(A)`
↓		`W(A) A = A * 1.06`
↓		`R(B)`
↓		`W(B) B = B + 1/06`
↓	`R(A)`
↓	`W(A) A = A + 100`
↓	`R(B)`
↓	`W(B) B = B - 100`

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

Question: How do we ensure that transactions are only ever executed with serializable schedules?

Problem: We can't know if a schedule will be serializable until the transaction is done

Concurrency Strategies

Locking (Pessimistic): Block transaction execution before it risks non-serializable behavior
Snapshot Isolation (Optimistic): Execute each transaction on a snapshot of the database and abort it if a serializable merge is impossible.
Timestamp Concurrency Control (Optimistic): Annotate each object with timestamps and abort transactions that trigger non-serializable behavior.

Can we convince ourselves that these concurrency strategies are guaranteed to produce serializable schedules?

Observation 1: Reads can never interfere with reads

You can reverse the order of two reads without changing the serializability of a schedule.

Observation 2: Operations on different objects can't interfere with each other.

You can reverse the order of two operations on different objects without changing the serializability of a 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

Time	T1	T2
\|		`W(B)`
\|	`R(B)`	↑
\|	↓	`W(A)`
↓	`W(A)`

Example 1

Time	T1	T2
\|		`W(B)`
\|		`W(A)`
\|	`R(B)`
↓	`W(A)`

Conflict equivalent to a serial schedule!

Example 2

Time	T1	T2
\|		`W(B)`
\|	`R(B)`
\|	`W(A)`
↓		`W(A)`

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?

Observation: We can't reorder Write/Write (or Read/Write) operations on the same object.

Two transactions accessing the same object create a partial order on the serial schedule

Example 1

Time	T1	T2
\|		`W(B)`
\|	`R(B)`
\|		`W(A)`
↓	`W(A)`

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

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

No cycles in the partial order!

Example 2

Time	T1	T2
\|		`W(B)`
\|	`R(B)`
\|	`W(A)`
↓		`W(A)`

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.

Forcing Acyclicity

Locking: Block the second transaction to access an object until the first is completely done.
Snapshot Isolation: Declare a serial order and abort transactions when they create reverse edges
Timestamp Concurrency Control*: Declare a serial order and abort transactions when they create reverse edges