Transaction Processing:

1. Transaction Processing: September 27, 2005

2. Database Access • For TP, represent database as a collection of named items. • Read(X) - read database item X into local variable named X • Write(X) - write local variable X’s value into database item X • Update(X) - dangerous! May or may not be atomic! • Granularity - the size of the data item. • Large grain -- whole table, set of records, … • Small grain -- … single record, set of fields within a record, single field.

3. Read(X) • Find address of sector containing X • Copy sector to buffer in main memory (unless already present) • Copy item X from buffer into local variable X.

4. Write(X) • Find address of sector containing X • Copy that sector into buffer in memory • Copy local variable X into correct location in buffer. • Write buffer to disk.

5. ReadSet, WriteSet • ReadSet -- the set of all items the transaction reads. • WriteSet -- the set of all items the transaction writes.

6. Example transactions (Elmasri, et al) T1 T2 read(x); read(x); x -= n; x += m; write(x); write(x); read(y); y += n; write(y); readSet(T1) = { x, y } readSet(T2) = {x} writeSet(T1) = { x, y } writeSet(T2) = {x}

7. Lost Update T1 (transfer) T2 (deposit) read(x); //1 read(x); //3 x -= n; //2 x += m; //4 write(x); //5 write(x); //7 read(y); //6 y += n; //8 write(y); //9 /* at //7, X has incorrect value because T1’s update is overwritten. */

8. Temporary Update (Dirty Read) T1 (transfer) T2 (deposit) read(x); // 1 read(x); // 4 x -= n; //2 x += m; // 5 write(x); //3 write(x); // 6 read(y); // 7 y += n; write(y); /* at // 4, reading uncommitted data at // 7, suppose T1 fails, then T1 must change x back; but T2 has already used, and written bad data!

9. Incorrect summary (aggregate) T1 (transfer) T3 read(x); // 4 sum = 0; // 1 x -= n; // 5 read(a); // 2 write(x); // 6 sum += a; // 3 read(y); // 11 . . . y += n; // 12 read(x); // 7 write(y); // 13 sum += x; // 8 read(y); // 9 sum += y; // 10 /* at // 7, T3 reads x after n was subtracted, and reads y BEFORE n was added ==> sum is off by n. */

10. Unrepeatable read A transaction reads X two times. Between the two reads, a different transaction changes the value of X. (Violates Isolation)

11. System log (aka journal) • System maintains a log that tracks all transaction operations affecting data values. • Log used to recover from failure • Log kept on disk, so not affected by many failure types. • Log is periodically backed up to archival (tape) storage to allow recovery from disk failure.

12. Log records • <start, T#> // T with system generated number // T#, has started execution. • <write, T#, X, oldValue, newValue> // T# has // changed value of DB item X. • <read, T#, x> // T# has read value of item X. • <commit, T#> // T has completed; its effect can be // committed. • <abort, T#> // T# has been aborted.

13. Rollbacks • Practically speaking, <read, T#, X> , is not used for rollbacks. • Also, some recovery protocols do not require the newValue field of the write record. • ALL changes to the data happen through transactions. • Undo effects of write operations by tracing backward and restoring with oldValue’s. • Sometimes, may need to redo (then we need those newValue’s)

14. Commit Point • All operations accessing data have executed successfully AND the appropriate log records (<write … > mostly) have been recorded in the log (the log written to disk). • After the commit point, the transaction is committed. The system then writes the <commit, T> record

15. Rollback (undo) versus redo • A transaction that has written <start> (and <write>) but no <commit> to the log may have to be rolled back to undo the effects of the writes. • A transaction that has written <commit> may have to have their changes to the data redone, by redoing the <writes> in the log.

16. Where is the log ? • If the log is kept on disk only, then there will be multiple disk writes of the same log file sector. • It is more efficient to write the log file buffer only when it fills up (just one write to disk). • When the system crashes, only the log entries on the disk are used for recovery! So (to improve recovery), before a transaction reaches commit point, the part of the log not yet written to disk is force-written.

17. Schedule, History • For n transactions, a schedule is the ordered list of operations on the data. • Order of ops of a single transaction must be maintained. • Order from > 1 transaction may be inteleaved. • r1(x) means T1 reads x. • W2(y) means T2 writes y • a means abort • c means commit

18. Schedule for lost update T1 (transfer) T2 (deposit) read(x); //1 read(x); //3 x -= n; //2 x += m; //4 write(x); //5 write(x); //7 read(y); //6 y += n; //8 write(y); //9 SlostUpdate = r1(x), r2(x), w1(x), r1(y), w2(x), w1(y)

19. Give the schedule for dirty read incorrect summary

20. Conflicting operations • Two operations in a schedule conflict if: • They belong to different transactions • They access the same data element • At least one is a write

21. S, is a complete schedule of n transactions if • The operations in S are exactly those in T1, … Tn, including commit or abort • For any pair of ops from the same Ti, their order in S is the same as their order in Ti • For any two conflicting ops, one of the two must occur before the other in the schedule. • I.e., for nonconflicting ops, a partial order is sufficient.

22. S is recoverable if • Once a transaction is committed, it is never necessary to rollback T. • S is recoverable if no transaction T in S commits until all transaction T’ that have written an item that T read have committed.

23. S: r1(x), r2(x), w1(x), r1(y), w2(x), c2, w1(y), c1 • S is recoverable, even though it suffers from lost update.

24. Sb: r1(x), w1(x), r2(x), r1(y), w2(x), c2, a1 • Not recoverable: T2 reads x from T1, then t2 commits before T1 commits. • Sc: r1(x), w1(x), r2(x), r1(y), w2(x), w1(y), c1, c2 • Recoverable • Sd: r1(x), w1(x), r2(x), r1(y), w2(x), w1(y), a1, a2 • recoverable

25. Make three transactions, one deposits(commits), one withdraws(commits), one transfers (aborts). • Give a recoverable schedule. • Give a nonrecoverable schedule.