Adapted from Ex 6.6.1: Consider the two programs that operate on the two relations:
Product (maker, model, type)
PC (model, speed, ram, hd, price)
T1: Given a model number, delete the tuple for that model from both PC and Product.
T2: Given a model number, decrease the price of that model PC by $100.
Q1) Sketch the resulting transactions in terms of BEGIN TXN, COMMIT, ABORT, READ(tuple), WRITE(tuple). Assume reads and writes operate on tuples of a table. You may use control structures like ‘for’ or ‘while’ loops where necessary.
Q2) Modify your programs to include locking operations: XLOCK(tuple) for requesting exclusive locks, SLOCK(tuple) for requesting shared locks, and RALL for releasing all locks (strict 2PL).
Q3) Give one possible schedule for concurrent execution of T1 and T2 with the locking operations under a strict 2PL scheduler. You should unroll any loops and eliminate any branching by assuming that the input to T1 and T2 are model number 2, and the contents of the tables are:
PC Product
------------------------ ----------------
| model | price | . . .| | model | . . .|
------------------------ ----------------
pc1 | 1 | 300 | . . .| pr1 | 1 | . . .|
pc2 | 2 | 400 | . . .| pr2 | 2 | . . .|
------------------------ ----------------
Row IDs are denoted on the left side of each table.
Q4) Design a logging system to ensure atomicity and support crash recovery. What would the sequence of log entries for your schedule in Q3 look like ?
Q5) Show how to recover from a crash at some point in the sequence of log entries.