Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle - Mailing list pgsql-hackers

Hi

After the recent discussion about the impossibility of efficiently implementing FK-like constraint triggers in PL/PGSQL
thatwork correctly under SERIALIZABLe transactions, I've compared our behavior to that of Oracle. As it turns out, a
slightdifference in Oracle's behavior makes those FK constraint triggers which on postgres are only correct in READ
COMMITTEDmode fully correct in SERIALIZABLE mode also. 

1. Summary of the previous discussion

The built-in FK constraint trigger looks for rows visible under either the transaction's snapshot *or* a freshly taken
MVCCsnapshot when checking for child-table rows that'd prevent an UPDATE or DELETE of a row in the parent table. This
isnecessary even though the parent row is SHARE-locked on INSERTs/UPDATEs to the child table, and would also be
necessaryif it was UPDATE-locked. The following series of commands illustrates why 

C1: BEGIN
C1: SELECT * FROM t WHERE id = 1 FOR UPDATE
C2: BEGIN
C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
C2: SELECT * FROM t -- Take snapshot before C1 commits
C1: COMMIT
C2: DELETE FROM t WHERE id = 1
C2: COMMIT

Since C1 commits before C2 does DELETE, C2 is entirely unaffected by C1's UPDATE-lock. C2 has no way of detecting
possibledependent rows that C1 might have inserted, since C1 is invisible to C2. 

Note that if you swap the SELECT .. FOR UPDATE and the DELETE commands, the SELECT .. FOR UPDATE will cause a
serializationerror! 

2. The behavior or Oracle

Oracle treats a "FOR UPDATE" lock much like an actual UPDATE when checking for serialization conflicts. This causes the
DELETEin the example above to raise a serialization error, and hence prevents the failure case for FK constraint
triggerseven without a recheck under a current snapshot. 

One can think of a FOR UPDATE lock as a kind of read barrier on Oracle - it prevents other transactions from messing
withthe row that don't consider the locking transaction to be visible. 

3. Conclusio

While it might seem strange at first for a lock to affect other transactions even after the locking transaction has
ended,it actually makes sense when viewed as a kind of write barrier. It is very common for locking primitives to use
barrierinstructions to ensure that one lock holder sees all changes done by the previous owner. Raising a serialization
errorin the example above is the transactional equivalent of such a barrier instruction in the case of SERIALIZABLE
transactions- since updating the transaction's snapshot is obviously not an option, the remaining alternative is to
restartthe whole transaction under a current snapshot. This is exactly what raising a serialization error accomplishes. 

Also, while Oracle's behavior has obvious use-cases (e.g. FK-like constraints), I failed to come up with a case where
postgres'current behavior is useful. When would you want a (SERIALIZABLE) transaction to wait for a lock, but then
continueas if the lock had never existed? What is the point of waiting then in the first place? 

All in all, I believe that SHARE and UPDATE row-level locks should be changed to cause concurrent UPDATEs to fail with
aserialization error. I can come up with a patch that does that, but I wanted to get some feedback on the idea before I
putthe work in. 

best regards,
Florian Pflug