2010/5/14 Greg Stark <gsst...@mit.edu>: > On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <f...@phlo.org> wrote: > >> 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 > > Can you give an actual realistic example -- ie, not doing a select for > update and then never updating the row or with an explanation of what > the programmer is attempting to accomplish with such an unusual > sequence? The rest of the post talks about FKs but I don't see any > here...
The link with FKs is as follows: * The example does not use a real FK, because the whole purpose is to do the same as FKs while not using the FK machinery. * The example uses only one table, because that is enough to illustrate the problem (see next items). * C1 locks a row, supposedly because it wants to create a reference to it in a non-mentioned table, and wants to prevent the row from being deleted under it. * C2 deletes that row (supposedly after it verified that there are no references to it; it would indeed not be able to see the reference that C1 created/would create), and C1 fails to detect that. * C2 also fails to detect the problem, because the lock that C1 held is being released after C1 commits, and C2 can happily go on deleting the row. * The end result is that the hypothetical reference is created, although the referent is gone. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
