Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
in [PgSql]
|
Prev: [HACKERS] Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Next: [HACKERS] HS/SR Assert server crash
From: Tom Lane on 13 May 2010 17:39 Florian Pflug <fgp(a)phlo.org> writes: > All in all, I believe that SHARE and UPDATE row-level locks should be > changed to cause concurrent UPDATEs to fail with a serialization > error. I don't see an argument for doing that for FOR SHARE locks, and it already happens for FOR UPDATE (at least if the row actually gets updated). AFAICS this proposal mainly breaks things, in pursuit of an unnecessary and probably-impossible-anyway goal of making FK locking work with only user-level snapshots. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 13 May 2010 17:51 Florian Pflug <fgp(a)phlo.org> wrote: > All in all, I believe that SHARE and UPDATE row-level locks should > be changed to cause concurrent UPDATEs to fail with a > serialization error. I can come up with a patch that does that, > but I wanted to get some feedback on the idea before I put the > work in. Before you work on that, you might want to wait until you can review the work that I and Dan Ports (a Ph.D. candidate from MIT) have been doing on support for true serializable transactions. You don't need to use FOR SHARE or FOR UPDATE or any explicit locks as long as the concurrent transactions are SERIALIZABLE. We have it working, but have been holding off on discussion or patch submission at Tom's request -- he felt it would distract from the process of getting the release out. Whenever people are ready, I can submit a WIP patch. All issues discuss on this thread "Just Work" with the patch applied. There's a Wiki page and a public git repository related to this work, for anyone who is interested and not busy with release work. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Florian Pflug on 13 May 2010 18:52 On May 13, 2010, at 23:39 , Tom Lane wrote: > Florian Pflug <fgp(a)phlo.org> writes: >> All in all, I believe that SHARE and UPDATE row-level locks should be >> changed to cause concurrent UPDATEs to fail with a serialization >> error. > > I don't see an argument for doing that for FOR SHARE locks, and it > already happens for FOR UPDATE (at least if the row actually gets > updated). Yes, actually updating the row is a workaround. A prohibitively expensive one, though. The arguments are as stated a) SHARE or UPDATE locking a concurrently updated row *does* cause as serialization error, making the current behavior asymmetric b) Locking primitives usually ensure that once you obtain the lock you see the most recent version of the data. This is currently true for READ COMMITTED transactions but not for SERIALIZABLE ones, and pretty undesirable a behavior for a locking primitive. c) I fail to see how the current behavior is useful in the presence of SERIALIZABLE transactions. Currently, they could IMHO completely ignore FOR SHARE locks, without making any previously correct algorithm incorrect. plus a weaker one: d) Oracle does it for FOR UPDATE locks, and actually has an example of a FK trigger in PL/SQL in their docs. > AFAICS this proposal mainly breaks things, in pursuit of > an unnecessary and probably-impossible-anyway goal of making FK locking > work with only user-level snapshots. I don't see the breakage this'd cause. For READ COMMITTED transactions nothing changes. For SERIALIZABLE transactions the behavior of FOR UPDATE / FOR SHARE becomes much easier to grasp. In both cases a SHARE lock would then say "Only update this row if you have seen the locking transaction's changes". Why do you think that making FK locking work with only user-level snapshots is probably-impossible-anyway? With the proposed changes, simply FOR SHARE locking the parent row on INSERT/UPDATE of the child, plus checking for child rows on UPDATE/DELETE of the parent gives a 100% correct FK trigger. I do not have a formal proof for that last assertion, but I'm not aware of any counter-examples either. Would love to hear of any, though. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 13 May 2010 20:37 On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <fgp(a)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... -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Nicolas Barbier on 14 May 2010 04:40
2010/5/14 Greg Stark <gsstark(a)mit.edu>: > On Thu, May 13, 2010 at 10:25 PM, Florian Pflug <fgp(a)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(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |