CONCURRENCY CONTROL Spring 2015 Warning n n This

CONCURRENCY CONTROL Spring 2015

Warning n n This is a first draft I welcome your corrections

One common objective n Maintaining database in a consistent state ¨ Means here maintaining the integrity of the data n After a money transfer between two accounts, the amount debited from the fist account should be equal to the amount credited to the second account ¨Assuming no-fee transfer

Two different problems n n Handling outcomes of system failures: ¨ Server crashes, power failures, … ¨ Previous chapter Preventing inconsistencies resulting from concurrent queries/updates that interfere with each other ¨ This chapter

The problem n Assume that we want to process in parallel ¨ A transaction T 1 crediting a checking account by $2, 000 ¨ A transaction T 2 debiting the same account by $100

A correct schedule n n n T 1 reads the current balance ($500) T 1 computes the new balance ($2, 500) T 1 writes the new balance ($2, 500) to disk T 2 reads the current balance ($2, 500) T 2 computes the new balance ($2, 400) T 2 writes the new balance ($2, 400) to disk Serial execution of the two transactions

Another correct schedule n n n T 2 reads the current balance ($500) T 2 computes the new balance ($400) T 2 writes the new balance ($400) to disk T 1 reads the current balance ($400) T 1 computes the new balance ($2, 400) T 1 writes the new balance ($2, 400) to disk Another serial execution

An incorrect schedule n n n T 1 reads the current balance ($500) T 2 reads the current balance ($500) T 1 computes the new balance ($2, 500) T 1 writes the new balance ($2, 500) to disk T 2 computes the new balance ($400) T 2 writes the new balance ($400) to disk That's not right!

Another problem n Assume that we want to process in parallel ¨ A transaction T 1 crediting a checking account X by $2, 000 ¨ A transaction T 2 debiting a checking account Y by $100

A correct schedule n n n T 1 reads the current balance of X ($500) T 1 computes the new balance of X ($2, 500) T 1 writes the new balance of X ($2, 500) to disk T 2 reads the current balance of Y ($800) T 2 computes the new balance of Y ($700) T 2 writes the new balance of Y ($700) to disk Serial execution of the two transactions

Another correct schedule n n n T 1 reads the current balance of X ($500) T 2 reads the current balance of Y ($800) T 1 computes the new balance of X ($2, 500) T 1 writes the new balance of X ($2, 500) to disk T 2 computes the new balance of Y ($700) T 2 writes the new balance of Y ($700) to disk A correct concurrent schedule

Two different approaches (I) n n Most OS ensure the correctness of conflicting operations by forcing them to execute in mutual exclusion ¨ Through locks, semaphores, monitors Avoid conflicts but forces operations to execute in some serial order ¨ Acceptable for short critical sections ¨ Not acceptable for DB n Would slow down update rate

Two different approaches (II) n DBMS will ¨ let potentially conflicting transactions execute concurrently ¨ controlt heir actual schedule and guarantee that their outcome will be identical to some serial execution of these transactions n Serializability criterion

Notations n n As far as concurrency control is concerned, a transaction can be represented by the sequence of its read and write operations: ¨ r 1(A); w 1(A) means transaction T 1 reads A then updates A A transaction schedule S represents the sequence of the read/write operations of multiple transactions as they are executed ¨ r 1(A); r 2(A); w 1(A); w 2(B)

Serial schedules n A schedule is said to be serial if all its transactions execute ¨ One at a time ¨ Without any interleaving n All serial schedules are assumed to be correct Different orders of execution may produce different outcomes n

Example n n n Transaction T 1 reads the balance of an account Transaction T 2 withdraws $200 Two serial schedules ¨ S = r 1(A); r 2(A); w 2(A) ¨ S' = r 2(A); w 2(A); r 1(A) are both correct even though T 1 will return different values

Serializable schedules n An interleaved schedule is said to be serializable if its outcome is identical to some serial schedule for the same transactions n All serializable schedules are assumed to be correct

Example n Transaction T 1 reads the balance of an account Transaction T 2 withdraws $200 n S = r 1(A); r 2(A); w 2(A) is a serial schedule n S' = r 2(A); r 1(A); w 2(A) is interleaved but equivalent to S ¨ S' is serializable n

Defining equivalence n n We cannot depend on either ¨ The original values of the DB elements being modified ¨ Their new values We will use conflict-based serializability:

Conflicting operations n Two operations are said to be conflicting if ¨ They belong to two different transactions T 1 and T 2 ¨ They access the same DB element A ¨ At least one of them is a write

Examples n n The following pairs of operations conflict: ¨ r 1(A); w 2(A) ¨ w 1(A); r 2(A) ¨ w 1(A); w 2(A) The following pairs do not: ¨ r 1(A); r 2(A) ¨ w 1(A); r 2(B) ¨ w 1(A); w 2(B)

Important property n Changing the order of two conflicting operations is likely to affect the correctness of the schedule ¨ r 1(A); r 2(A); w 1(A); w 2(A) is not equivalent to r 1(A); w 1(A) r 2(A); w 2(A) n Changing the order of two non-conflicting operations will never affect the correctness of the schedule ¨ r 2(A); r 1(A); w 2(A) is equivalent to r 1(A); r 2(A); w 2(A)

Our serializability criterion n Two schedules S 1 and S 2 are said to be conflict equivalent if S 1 can be transformed into S 2 by a series of swaps on non-conflicting actions. ¨ G. -M. n A schedule S is said to be conflict serializable if it is conflict-equivalent to some serial schedule. ¨ G. -M.

Example n r 2(A); r 1(B); w 2(A); r 3(A); w 1(B); w 3(A); r 2(B); w 2(B) can be successively transformed into r 1(B); r 2(A); w 1(B); r 3(A); r 2(B); w 3(A); w 2(B) r 1(B); r 2(A); w 1(B); w 2(A); r 2(B); r 3(A); w 2(B); w 3(A) r 1(B); w 1(B); r 2(A); w 2(A); r 2(B); w 2(B); r 3(A); w 3(A) which corresponds to a serial execution of the transactions T 1, T 2 and T 3

Another example n The schedule r 1(A) r 2(A) w 1(A) w 2(A) cannot be transformed into either r 1(A) w 1(A) or r 1(A) w 1(A) r 2(A) w 2(A) and is not conflict serializable

Definition n n For a given schedule S, a transaction T 1 is said to be ahead of a transaction T 2 if there is an action A 1 in T 1 and an action A 2 such that ¨ A 1 appears ahead of A 2 in the schedule, ¨ Both A 1 and A 2 access the same element X of the DB ¨ At least one of them is a write We write T 1 <S T 2

Precedence graph n The precedence graph for a schedule S contains: ¨A node for each transaction Ti in S ¨ An arc from node Ti to node Tk if Ti <S Tk

Example n In the schedule r 2(A); r 1(B); w 2(A); r 3(A); w 1(B); w 3(A); r 2(B); w 2(B) we have ¨ T 1 <S T 2 because w 1(B); …; r 2(B); ¨ T 2 <S T 3 because w 2(A); …; r 3(A); 1 2 3

Another example n In the schedule r 1(A) r 2(A) w 1(A) w 2(A) we have ¨ T 2 <S T 1 ¨ T 1 <S T 2 1 2

Theorem n The necessary and sufficient condition for a schedule to be conflict-serializable is that its precedence graph has no cycles

Necessary condition n If schedule S contains a cycle, such as T 1 T 2 T 3 … Tn T 1, any equivalent serial schedule must have ¨ All actions of T 1 preceding all actions of T 2, ¨ All actions of T 2 preceding all actions of T 3 ¨… ¨ All actions of Tn preceding all actions of T 1, which is impossible

Sufficient condition n n Proof by induction Basis: ¨ Obvious if the schedule contains a single transaction

Sufficient condition n Induction step: ¨ Assume it is true for any schedule with n - 1 transactions ¨ Let S be a schedule with n transactions ¨ Since the precedence graph of S has no cycles, at least one of its nodes has no incoming arcs ¨ Take all actions of transaction associated with that node and start new schedule with them ¨ What remains of S has no cycles and n - 1 transactions

Pessimistic Concurrency Control

Pessimistic scheduling (I) n Uses locks Lock table Incoming transactions Transaction scheduler Serializable transaction schedule

Pessimistic scheduling (II) n n n Scheduler will guarantee the serializability of the schedules it issues by enforcing sufficient conditions These conditions will be enforced through locking Outcome will be schedules that satisfy conflict serializability

Locking n Two operations ¨ li(A): transaction Ti requests and gets an exclusive lock on element A ¨ ui(A): transaction Ti releases its exclusive lock on element A

First rule: Well-formed transactions n n A transaction T can only access an element A of a DB if it has obtained a lock for A and not released it All transactions will eventually release all their locks Ti: … li(A) … ri(A) …wi(A) … ui(A). . .

Second rule: Legal scheduler n Scheduler will not grant a lock to a transaction Tk for a DB entity A if another transaction Ti holds a lock on A Ti: … li(A) … ri(A) …wi(A) … ui(A). . . x lk(A) must wait

Locking is not enough n First transaction: l 1(A) r 1(A) u 1(A) … … … l 1(A) w 1(A) u 1(A) n Second transaction: l 2(A) r 2(A) u 2(A) … l 2(A) w 2(A) u 2(A) T 1 and T 2 read same old value of A then enter a race condition when they update it

Two-phase locking n n Requires all transactions to acquire all their locks before they release any Go through two phases ¨ Acquiring their locks ¨ Releasing them Locks owned by transaction Time

Claim: 2 PL produces serializable schedules n Geometric argument: Locks owned by T 2 Locks owned by T 1 Time

More formally n Any legal schedule that only contains consistent two-phase locked transactions is serializable ¨ To prove it we will show we can convert S = T 1, T 2, . . . , Tn into an equivalent serial schedule S'

Proof (I) n n Basis: ¨ True for n = 1 Induction step: ¨ Let Ti be transaction with first unlock action, say ui(X), in entire schedule ¨ One way to start constructing S' would be to move all actions of Ti ahead of the actions of all other transactions

Proof ¨ Consider an arbitrary action of Ti, say wi(Y) ¨ The sole condition that would prevent us from moving wi(Y) to the beginning of S' would the existence of a conflicting operation wk(Y) in some transaction Tk ¨ This would assume the existence in the schedule of a sequence of action containing lk(Y). . . wk(Y). . . Uk(Y). . . li(Y). . . wi(Y). . . ui(Y). . . which is contrary to our assumption.

2 PL is enough n First transaction: l 1(A) r 1(A) … w 1(A) u 1(A) n Second transaction: l 2(A) r 2(A) … w 2(A) u 2(A) T 2 must now wait Schedule is serial and thus serializable

Shared and exclusive locks n n Shared locks: ¨ sli(A) allows transaction Ti to access element A in read-only mode ¨ Can be shared with other transactions requiring the same mode of access Exclusive locks: ¨ xli(A) grants to transaction Ti the exclusive right to access element A n Required for write access

First rule: Well-formed transactions n n A transaction T can only ¨ Perform a read action ri(A) on an element A of a DB if it has obtained a shared or an exclusive lock for A and not released it ¨ Perform a write action wi(A) on an element A of a DB if it has obtained an exclusive lock for A and not released it All transactions will eventually release all their locks

Second rule: Legal scheduler n Scheduler will not grant: ¨ A shared lock to a transaction Tk for a DB entity A if another transaction Ti holds a exclusive lock on A ¨ An exclusive lock to a transaction Tk for a DB entity A if another transaction Ti holds a a lock on A (either shared or exclusive)

Third rule: Two-phase locking n Requires all transactions to acquire all their locks before they release any The option of downgrading a lock and go from an exclusive lock xli(A) to a shared lock sli(A) is not offered

Interesting property n A transaction Ti holding a shared lock sli(A) on an element A knows that the value of this element will not change as long as it holds that lock

Compatibility matrix Lock requested Lock granted S X S Yes No X No No

Next problem: Deadlocks n First transaction: sl 1(A) r 1(A) … xl 1(A) w 1(A) Both denied u 1(A) n Second transaction: sl 2(A) r 2(A) … xl 2(A) w 2(A) u 2(A)

Two solutions (I) n All or nothing lock allocation ¨ A transaction will either get all the locks it needs or none of them ¨ Deadlock free ¨ Limits concurrency Locks acquired by T 1

Two solutions (II) n Abort one of the transactions involved in a deadlock ¨ Will undo all its actions ¨ Just as if transaction did not happen ¨ Will not appear on the schedule

Update locks n Useful when a transaction wants to read an element A and reserve the right to update it later ¨ uli(A) n Can be granted if another transaction Tk has a shared lock slk(A) on DB element A n Once granted prevents any other transaction Tk to obtain any lock on DB element A

Update locks (II) n Main advantage is reducing deadlocks ¨ Requires preventing transactions from upgrading their shared locks.

Compatibility matrix n A transaction can get an update lock for an entity for which another transaction has a shared lock but the reverse is not true Lock requested Lock granted S U X Yes No No No X No No No

We avoid a deadlock n First transaction: ul 1(A) r 1(A) xl 1(A) w 1(A) u 1(A) n Second transaction: ul 2(A) r 2(A) xl 2(A) w 2(A) u 2(A) T 2 must now wait Schedule is serial and thus serializable

Increment locks n n Most DB updates involve adding or subtracting some quantity to a value in the DB ¨ Debit/credit operations ¨ Updating inventory New INCi( A, increment) "locks" ¨ Consist of an atomic sequence of : n READ(A, t) t = t + increment WRITE(A, t) OUTPUT(A);

Properties n Increment locks ¨ Commute among themselves ¨ Are incompatible with any other locks

Locking schedulers n n Cannot trust transactions to lock/release resources Must be done by the scheduler ¨ Two-step scheduler n First step is to add lock requests to transactions n Second step is to produce and execute a legal schedule

A two-step scheduler Transactions Scheduler step I Lock table Transactions + locks Scheduler step II Legal schedule DB

Inserting locks (I) n Simple case: ¨ One type of locks ¨ Just add a lock request before any attempt to access a DB element that is not already locked by the transaction

Inserting locks (II) n General case: ¨ Multiple locks n S/X or S/X/U ¨ Just add the appropriate lock request before any attempt to access a DB element that is not already locked by the transaction n S if transaction will not modify the element n X if transaction will modify the element n U if transaction could modify the element

Inserting locks (III) ¨ Lock manager must look ahead to find out whether the transaction will/will not modify a given entity n Easy for interactive SQL requests n Harder for embedded SQL ¨Must know the logic of the program

The lock table n Hash table: ¨ Each entry contains all information for a given element A n Organized as a linked list Element A lock status

A lock table entry Object: A Max mode: U Waiting: Y Trans. list: Trans. Lock W? Next TLink T 1 S no T 2 U no T 3 X yes In our example. the highest lock is the update lock held by T 2

Explanations (I) n Header contains ¨ Element identifier A ¨ Highest lock held on A (for faster decisions) ¨ Flag telling whethere are transactions that wait for the lock (Y/N) ¨ Link to list of all transactions that n Have requested a log on A n Hold a lock on A

Explanations (II) n Each transaction entry in the linked list contains ¨ Transaction identifier ¨ Lock obtained/requested by transaction ¨ Flag telling whether the transactions is (Y/N) ¨ Link to next transaction waiting for a lock/holding a lock on element A ¨ Link to other lock entries for the transaction n Simplifies unlocks at the end of the transaction

Handling lock requests n Assume transaction T requests a lock on entity A ¨ If A has no lock entry n A lock entry is created, the lock is granted and a transaction entry for T is created ¨ Else if the request is compatible with the highest lock already granted on A n The lock is granted and a transaction entry for T is created ¨ Else n T is put on the wait list, the wait flag is set to Yes and a transaction entry for T is created.

Handling lock releases n n n Delete first the transaction entry for the transaction that released its lock Update the lock status If wait flag is Yes ¨ Grant one or more locks to the transactions that were waiting ¨ We can give priority to the transactions that n Waited the longest (First-come-first-served) n Request shared locks n Request upgrade locks

Locks with multiple granularity n n Want to be able to grant locks at different levels of the database ¨ Whole relation n Block containing several tuple n Single tuple Very attractive for read-only transactions

The DB hierarchy Database Relation Block Tuple … Relation … Tuple … Block Tuple … … Tuple …

Warning locks (I) n Handling locks at multiple levels create a problem ¨ Cannot grant a shared lock on a relation if another transaction has an exclusive lock on one of the blocks or the tuples in that relation ¨ Cannot grant an exclusive lock on a block if another transaction has any lock on one of tuples in that block

The problem n Handling locks at multiple levels create a problem ¨ Cannot grant a shared lock on a relation if another transaction has an exclusive lock on one of the blocks or the tuples in that relation ¨ Cannot grant an exclusive lock on a block if another transaction has any lock on one of tuples in that block

Warning locks n n n Exist at the block or the relation level Warn that another transaction has a lock on some sub entity of the relation or the block Two types ¨ IS warns that another transaction has a shared lock on some sub entity ¨ IX warns that another transaction has an exclusive lock on some sub entity

Compatibility issues (I) n n Cannot grant an exclusive lock on a relation/block if that entity already has an warning lock (either IS or IX) Cannot grant a shared lock on a relation/block if that entity already has an exclusive warning lock (IX only)

Compatibility issues (II) n n n Cannot grant an exclusive warning lock on a relation/block if that entity already has any lock (either S or X) Cannot grant a shared warning lock on a relation/block if that entity already has an exclusive lock (X only) Intention locks never interfere with each other

Compatibility matrix Lock requested Lock granted IS IX S X Yes Yes No No Yes No No

Example Database Relation R IX Relation S IS Block IS Tuple X Tuple S Tuple Block Tuple

Handling deletions/insertions (I) n Problems when transactions create sub elements of a locked element: ¨ Relation R contains customers' checking accounts for a bank ¨ Transaction T has a read lock on the whole relation to compute the total values of all checking accounts ¨ Transaction U creates a new account n Not guaranteed to appear in result of transaction T

Handling deletions/insertions (II) n n n These missing accounts are called phantom accounts Similar problem when accounts are deleted ¨ Deleted account could appear in result of transaction T Solution is to require insertions and deletions to acquire an exclusive lock on the whole relation

Tree-based locking n n Assume we want to access a relation that is indexed by a B+-tree Will have to lock the tree while searching it Locked

The problem n Two-phase locking prevents transactions from releasing any lock while we are still acquiring new ones ¨ Cannot release lock on root of tree until they have acquired all their locks ¨ Tree root will remain locked for too long ¨ Prevent other transactions from accessing the relation through the index

The solution (I) n Start with any node X (root in the example) X is locked Y Z

The solution (II) n Can now lock any child of X X is locked Y is locked Z

The solution (III) n Can release lock on X as soon as we know we will never modify X Y is locked Z

The solution (IV) n Can now lock any child of Y X Y is locked Z is locked

The solution (V) n Can release lock on Y as soon as we know we will never modify Y X Y Z is locked

A problem n Can transaction T get a new lock for X (or Y)? X Y Z is locked

Answer n NO, another transaction could have acquired a lock on a subtree of X after T released the lock X Y Z is locked W is locked by another transaction that followed T

Rules 1. First lock by transaction T may be on any item 2. After that, T can lock any item Q if and only it holds a lock on parent(Q) 3. Any item may be unlocked at any time 4. T cannot relock any item Q on which it has released a lock even if it holds a lock on parent(Q)

Replacing two-phase locking n Third rule violates two-phase locking ¨ It allows transactions to release locks on tree nodes ¨ Only prevents them for reacquiring a lock

Correctness proof n SKIPPED NOT ON THE QUIZ

Optimistic Concurrency Control

Example n n Pessimistic concurrency control ¨ Assumes that serializability must be enforced ¨ Prevent the occurrence of non serializable schedules through locking Optimistic concurrency control ¨ Assumes that most transactions will cause no serializability conflicts ¨ Detect and abort offending transactions

Advantages and disadvantages n Optimistic concurrency control ¨ Works very well when conflicts are infrequent n Avoids locking overhead n Aborts few transactions ¨ Works less well when conflicts more frequent n Preventing becomes then cheaper than undoing

An analogy n Should we put traffic lights at each intersection? ¨ Would prevent collisions ¨ Would create delays for motorists n Cities use multiple approaches ¨ Nothing ¨ Stop signs ¨ Traffic lights ¨ Overpasses and underpasses

Timestamps

How it works (I) n Attach to each incoming transaction a unique sequential timestamp ¨ Proxy for the order of the transaction in an equivalent serial schedule ¨ Time stamp can be generated n By the system clock ¨If clock ticks are frequent enough n By a counter inside the scheduler ¨Logical clock

How it works (II) n Attach to each element X of DB two timestamps and a commit bit ¨ RT(X): timestamp of last transaction that read X ¨ WT(X): timestamp of last transaction that updated X ¨ C(X): 1 if last transaction that updated X has committed and 0 otherwise

How it works (III) n Monitor all read/update operations by transactions and verify that they correspond to a physically realizable serial schedule ¨ T 4 reads X and finds RT(X) = 3 then updates it and finds RT(X) = 4 and WT(X) = 3 n Very nice as long as X is committed ¨ T 5 reads X and finds RT(X) = 4 then wants to update it but finds WT(X) = 6 n T 6 cannot happen before T 5

How it works (IV) ¨ T 7 reads X and finds RT(X) = 5 then try to update it and finds RT(X) = 8 and WT(X) = 3 to n T 8 cannot happen before T 7

An old problem revisited n n Assume that we want to process in parallel ¨ A transaction T 1 crediting a checking account A by $2, 000 ¨ A transaction T 2 debiting the same account A by $100 Initial properties of account are ¨ balance is $500 ¨ RT(A) = WT(A) = 0 ¨ C(A) = 1

A correct schedule n n n T 1 reads the current balance ($500) ¨ RT(A) goes from 0 to 1, WT(A) = 0, T 1 writes the new balance ($2, 500) to disk ¨ RT(A) =1, WT(A) goes from 0 to 1, C(A) goes from 1 to 0 T 1 commits ¨ C(A) goes back to 1 T 2 reads the current balance ($2, 500) ¨ RT(A) goes from 1 to 2, WT(A) = 1 T 2 writes the new balance ($2, 400) to disk ¨ RT(A) = 2, WT(A) goes from 1 to 2, C(A) goes from 1 to 0

An incorrect schedule n T 1 reads the current balance ($500) ¨ RT(A) goes from 0 to 1, WT(A) = 0, n T 2 reads the current balance ($500) ¨ RT(A) goes from 1 to 2, WT(A) = 0 n T 1 tries to write the new balance ($2, 500) to disk ¨ Cannot do it because RT(A) = 2 ¨ Will ABORT T 1 and restart it as T 3

Another incorrect schedule n n T 2 reads the current balance ($500) ¨ RT(A) goes from 0 to 2, WT(A) = 0 T 2 writes the new balance ($2, 400) to disk ¨ RT(A) = 2, WT(A) goes from 0 to 2, C(A) goes from 1 to 0 T 2 commits ¨ C(A) goes back to 1 T 1 tries to read the current balance ($500) ¨ Cannot do it because WT(A) = 2 ¨ T 2 cannot happen before T 1

A last problem n T 1 reads the current balance ($500) ¨ RT(A) goes from 0 to 1, WT(A) = 0, n T 1 writes the new balance ($2, 500) to disk ¨ RT(A) =1, WT(A) goes from 0 to 1, C(A) goes from 1 to 0 n T 2 tries to read the current balance ($2, 500) ¨ Cannot do it now ¨ Must wait until T 1 commits and C(A) = 1

The rules n When scheduler processes a request from a transaction T, it can ¨ Grant the request ¨ Abort T and restart it with a new timestamp n Rollback ¨ Delay T and later decide to either grant the request or abort and restart the transaction

The rules for read requests n When scheduler receives r. T(A) for element X ¨ If TS(T) ≥ WT(X) and C(X) = 1 n It grants the request n New value of RT(X) is max(TS(T), RT(X)) ¨ If TS(T) ≥ WT(X) and C(X) = 0 n It delays the request ¨ If TS(T) < WT(X) and C(X) = 0 n It rolls back the request ¨Current schedule is not serializable

The rules for write requests n When scheduler receives w. T(A) for element X ¨ If TS(T) ≥ RT(X) and TS(T) ≥ WT(X) n It grants the request n Will set WT(X) to TS(T) and C(T) to 0 ¨ If TS(T) ≥ RT(X) but TS(T) ≥ WT(X) and C(X) = 1 n It cancels the request and let the transaction proceed n The write was overwritten ¨ If TS(T) ≥ RT(X) but TS(T) ≥ WT(X) and C(X) = 0 n It delays the request ¨ If TS(T) < RT(X) n It rolls back the request

Explanation n If TS(T) ≥ RT(X) but TS(T) ≥ WT(X) and C(X) = 1 ¨ A more recent transaction has overwritten X ¨ We have a write conflict between the two transactions ¨ We solve it by doing nothing ¨ Same outcome as having done the write beforehand having the value overwrittenby the more recent transaction

Multiversion timestamps n n DB maintain old versions of the DB ¨ In reality, just some old blocks with their old timestamps Old transactions that have been delayed can read these old values when TS(T) < WT(X) as long as TS(T) ≥ WT(Xold)

New rules n n n When a new write w. T(X) occurs, we create a new version of X, Xt with t = TS(T) When a read r. T(X) occurs the scheduler finds the version Xt of X such that ¨ t ≤ TS(T) ¨ There is no version Xt' such that t < t' ≤ TS(T) Write times are associated with versions and never change n Think of immutable files n Here versions are immutable

New rules n n Read times are also associated with versions ¨ Will be used to reject writes whose time is less than the read times of previous versions When a version Xt has a timestamp t such that no active transaction has a timestamp TS(T) lower than t, we can delete all versions of X with timestamps lower than t

Combining locks and timestamps n Several commercial DBMSs combine locks and timestamps: ¨ Red/write transactions use two-phase locking n To minimize the number of aborted transactions ¨ Read-only transactions use multiversion timestamping n Will never be aborted

Concurrency control through validation

Key idea n n Let transaction proceed but without writing anything even to I/O buffers Decide whether the transaction could have happened with all reads and writes equivalent to those that would all have taken place exactly at the same time ¨ If yes commit the transaction and update the database ¨ If no, rollback

The three phases of a transaction n Read: ¨ Transaction T reads from DB all he entities in its read set and computes in its private address space the results it needs to write Validate: ¨ Scheduler compares the read and write sets of T with those of of other transactions and decides either to commit it or to rollback it Write: ¨ T updates the DB

Data structures (I) n For each transaction "of interest" T ¨ RS(T): the read set of T ¨ WS(T): the write set of T ¨ START(T): the time at which T has started ¨ VAL(T): the time at which T was validated ¨ FIN(T): the time at which T has completed all its updates to the DB

Data structures (II) n At the global level ¨ START, the set of transactions that have started but have not yet validated ¨ VAL, the set of transactions that have been validated but have not yet finished updating the DB ¨ FIN, the set of transactions that have completed all their updates to the DB

Data structures (III) n n Transactions get automatically removed ¨ From the START set when they get validated ¨ From the valid set when they finish all their DB updates Can remove a transaction T from FIN as soon as FIN(T) < START(U) for all active transactions U

First validation rule n n Must rollback transactions that have read potentially stale data ¨ Could have been modified by another transaction after the read Say there is a transaction U such that ¨ RS(T) WS(U) ¨ FIN(U) > START(T)

Second validation rule n n Must rollback transactions that will write something that could be overwritten by an "earlier" transaction Say there is a transaction U such that ¨ WS(T) WS(U) ¨ FIN(U) > START(T)

Example n n T 1 starts, reads element X then computes a new value for X T 2 starts, reads same element T 1 validates Cannot validate T 2

Example n n n T 1 starts, reads element X then computes a new value for X T 1 validates T 2 starts T 1 finishes T 2 reads X Cannot validate T 2 because we do not know when the read took place

Example n n n T 1 starts, reads element X then computes a new value for X T 1 validates T 1 finishes T 2 starts, reads same element X Can validate T 2

Example n n T 1 starts, reads element X then computes a new value for X T 1 validates T 2 starts, computes a new value for X Can validate T 2 because it could be overwritten

Example n n n T 1 starts, reads element X then computes a new value for X T 1 validates T 2 starts T 1 finishes T 2 computes a new value for X Cannot validate T 2 because we do not know the timing of the write

Example n n n T 1 starts, reads element X then computes a new value for X T 1 validates T 1 finishes T 2 starts, computes a new value for X Can validate T 2