"Albe Laurenz" <laurenz.a...@wien.gv.at> 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. No blocking occurs. Now T1 updates its row. 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. 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. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers