HW3: Concurrency Control
The following questions focus on isolation and concurrency control. They use the notations for schedules seen in class
Please ensure that the programming calculation process (query) for each question is shown (if essential) and reasonably explained. Make sure to mark clearly which explanation refers to which question.
Q1. Is the following schedule conflict-serializable?
R1(A) W2(A) W3(A) R2(B) W3(C) R2(C) C1 C2 C3
True/ False
Q2. Are the following two schedules view-equivalent?
R1(A) W2(B) R1(B) W2(C) R1(A) W3(D) C1 C2 C3
R1(B) W2(B) W2(C) R1(A) R1(A) W3(D) C1 C2 C3
True/ False
Q3. Is the following schedule recoverable?
W1(A) W1(C) R2(B) R2(A) W2(D) C2 C1
True/ False
Q4. Could the following schedule have been generated by strict two-phase locking?
R1(A) W2(C) R1(C) W1(B) W2(D) C2 C1
True/ False
Q5. Does the following schedule belong to the class of schedules avoiding cascading aborts?
R1(A) W3(B) R2(A) R2(B) W1(C) W2(D) C1 C2 C3
True/ False
Q6. Is the following schedule final-state serializable?
R1(A) W2(A) R3(A) W3(A) R2(B) W2(B) W1(C) C1 C2 C3
True/ False
Q7. Could the following schedule have been generated via two-phase locking?
R1(A) W2(B) W3(C) R3(B) R1(C) W2(A) C2 C3 C1
True/ False
Q8. Which serial schedule is conflict-equivalent to the following schedule?
R1(A) W3(D) W2(A) W2(C) W3(E) R3(C) C1 C2 C3
Order the following transactions below to represent the equivalent serial order:
l Transaction 3
l Transaction 2
l Transaction 1
Q9. We try to execute the schedule below with two transactions, using plain two-phase locking:
W1(A) W2(B) W1(B) W2(A) C1 C2
How many of the two transactions will likely execute?
A. 0
B. 1
C. 2
Q10. Consider a table storing orders, defined as:
CREATE TABLE Orders(OrderID int, customer text);
To insert new orders, we use a query of the form
INSERT INTO Orders Values ((SELECT COUNT(*) FROM Orders), <CustomerID>);
(Here, <CustomerID> is a placeholder and represents a customer ID). We reason that the order ID should be unique as it uses the number of orders already present (which increases after each insertion). We choose isolation level "Serializable" (according to SQL-92). Yet, to our surprise, the system tries to insert orders with the same ID multiple times. Which of the following concurrency control protocol (discussed in class) was likely used?
A. Two-phase locking
B. Optimistic Concurrency Control
C. Timestamp-Based Concurrency Control
D. Snapshot Isolation
Q11. (process here) Please provide justifications for your answers above. Make sure to clearly associate justifications with specific questions.
Q1.
Q2.
Q3.
Q4.
Q5.
Q6.
Q7.
Q8.
Q9.
Q10.