Concurrent Transactions

1. Concurrent Transactions • Even when there is no “failure,”several transactions can interact to turn a consistent state into an inconsistent state.

2. Overview • Scheduler manages read/write requests from transactions; allows execution in an order that is “serializable,” i.e., it “looks like” the transactions executed one­at­a­time(serial)

3. Schedulers • A scheduler takes requests from transactions for reads and writes, and decides if it is “OK” to allow them to operate on DB or defer them until it is safe to do so. • Ideal: a scheduler forwards a request iffit cannot lead to inconsistency of DB

4. Timestamps • Unique value representing a time. • Example: clock time. • Example: serial counter.

5. Serializability Via Timestamps • Main idea:let things rumble along without any locking or scheduling (be optimistic) • As transactions read/write, check that what they are doing makes sense if the serial order was the same as the order in which transactions initiated. • Big gain if most transactions are read­only. • Every transaction T is given a timestamp TS(T) when it is initiated.

6. Serializability Via Timestamps (Continued) • Every DB element X has two timestamps: 1. RT (X) = highest timestamp of a transaction to read X. 2. WT (X) = highest timestamp of a transaction to write X to the DB. • Every DB element X has a bit C(X) indicating whether the most recent writer of X has committed. • Essential to avoid a “dirty read,” where a transaction reads data that was written by a transaction that later aborts.

7. What is Physically Unrealizable? • Read Too Late: Transaction T tries to read X, but TS(T) < WT(X). • T would read something that was written after T apparently finished.

8. But Wait if Data is Dirty? • T tries to read X, and TS(T)>WT(X), but C(X) = false. • T would be reading dirty data --- a risk we won't take.

9. What is Physically Unrealizable? • Write Too Late: Transaction T tries to write X, but RT(X)>TS(T)>WT(X). • Some other transaction read a value written earlier than T write, when it should have read what was written T.

10. What is Physically Unrealizable? • Write Too Late: Transaction T tries to write X, but RT(X)>TS(T)>WT(X). • When T tries to write X it finds RT(X)>TS(T). This means that X has already been read by some transaction that theoretically executed after T. • We also find TS(T)>WT(X), which means that no other transaction wrote into X a value that would have overwritten T’s value, negating T responsibility for the value of X. • This idea that writes can be skipped when a write with a later write-time is already in place, is called Thomas rule.

11. Abort/Update Decision • If the read is legal, perform it and change RT(X) if TS(T) is greater. (Or wait if C(X)=0) • If a write is legal, do nothing if WT(X)>TS(T). If WT(X)<TS(T), perform the write and change WT(X) to be TS(T). (Or wait if C(X)=0) • If illegal, rollback T = abort T and restart it with a new timestamp. • Legal = Physically Realizable. • Illegal = Physically Unrealizable.

12. Commitment • When a transaction finishes with no rollback, commit the transaction by changing all C(X) bits to true.

13. T2 aborts because it tries to write “at time 150” when another value of C was already read “at time 175.” • T3 is allowed to “write A,” but since there is already a later write of A, the DB is not affected.

14. Timestamps Versus Locks • Locking requires a lock table for currently locked items,while timestamping uses space for two timestamps in each DB element, locked or not. • Locking may cause transactions to wait;timestamping doesn't cause waiting, but may abort transactions. • Net effect: if most transactions are read­ only or few transactions interfere, then timestamping gives better throughput; otherwise not.