SQL92 REPEATABLE-READ – Does It Preclude Write Skew (A5B)?

isolation-level

REPEATABLE READ

In SQL92's definition, REPEATABLE READ is defined by serveral conditions.

P1 ("Dirty read"):

1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

P2 ("Non-repeatable read"):

2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

Atomicity and no updates will be lost:

The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.

Table 9, which defined REPEATABLE READ must preclude P1 & P2:

Table 9, "SQL-transaction isolation levels and the three phenomena" specifies the phenomena that are possible and not possible for a given isolation level.

So in SQL92's definition of REPEATABLE READ, it must preclude P1, P2, and support for atomicity, no update lost.

A5B (Write Skew)

On the other side, A5B (Write Skew) is defined in A Critique of ANSI SQL Isolation Levels:

Suppose T1 reads x and y, which are consistent with C(), and then a T2 reads x and y, writes x,and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated. In terms of histories:

r1[x]…r2[y]…w1[y]…w2[x]…(c1 and c2 occur)

(Write Skew)

Is REPEATABLE READ preclude A5B (Write Skew) ?

In the later paper, it claims REPEATABLE READ will preclude A5B (Write Skew) in Table 4. Isolation Types Characterized by Possible Anomalies Allowed., and I am not conviced.

Any thought?

Best Answer

Does REPEATABLE READ preclude Write Skew?

Yes - if you buy into the Critique's view that the ANSI definitions were based on locking behaviours, for example:

Remark 6. The locking isolation levels of Table 2 and the phenomenological definitions of Table 3 are equivalent. Put another way, PO, PI, P2, and P3 are disguised redefinitions of Locking behavior.

From that viewpoint, the history:

r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

...is prevented when long-duration read shared locks are taken, because w1[y] is blocked by the lock taken by r2[y]. Similarly, w2[x] would be blocked by the lock taken by r1[x]. Therefore, this history cannot arise.

No - if you literally interpret the ANSI definitions. These are widely seen to be incomplete, so this outcome is not really all that surprising.

Related Question