讲解 HW3: Concurrency Control辅导 SQL语言

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?