Kevin Grittner wrote: > > maybe I misunderstood something. > > > > Consider a function > > "makehighlander(personid integer) RETURNS void" > > defined like this: > > > > SELECT ishighlander INTO b FROM scots WHERE id=personid; > > IF b THEN > > RETURN; /* no need to do anything */ > > END IF; > > UPDATE scots SET ishighlander=TRUE WHERE id=personid; > > SELECT count(*) INTO n FROM scots WHERE ishighlander; > > IF (n > 1) THEN > > RAISE EXCEPTION 'There can be only one'; > > END IF; > > > > If we assume that "ishighlander" is false for all records in > > the beginning, and there are two calls to the function with > > two personid's of records *in different pages*, then there cannot be > > any conflicts since all (write and intention) locks taken by each of > > these calls should only affect the one page that contains the one > > record that is updated and then found in the subsequent SELECT. > > > > Yet if the two execute concurrently and the two first SELECTs are > > executed before the two UPDATEs, then both functions have a snapshot > > so that the final SELECT statements will return 1 and both functions > > will succeed, leaving the table with two highlanders. > > I do think you misunderstood. If there are two concurrent executions > and each reads one row, there will be an SIREAD lock for each of those > rows. As an example, let's say that one of them (T0) updates its row > and does its count, finds everything looks fine, and commits. In > reading the row the other transaction (T1) modified it sets the > T0.outConflict flag to true and the T1.inConflict flag to true.
Where does T0 read the row that T1 modified? > No > blocking occurs. Now T1 updates its row. Wait a minute, I am confused. I thought T1 had already modified the row before T0 committed? Or is "modify" not the update? > Still no problem, because > if it committed there, there would still be a sequence of transactions > (T0 followed by T1) which would be consistent with the results; but it > selects rows which include the one modified by T0, which causes > T0.inConflict and T1.outConflict to be set to true. Where does T1 select rows that were modified by T0? It selects only one row, the one it modified itself, right? > These would both > be pivots in an unsafe pattern of updates. No mystery which one needs > to be rolled back -- T0 has already committed; so T1 is rolled back > with a serialization failure (probably indicating that it is an unsafe > update versus an update conflict or a deadlock, which are two other > forms of serialization failure). Assuming that the software > recognizes the serialization failure code and retries, it now finds > that there is already a highlander and fails for real. You see, there must be something fundamental I am getting wrong. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers