Robert Haas <robertmh...@gmail.com> wrote: > The example that I remember was related to SELECT FOR > UPDATE/SELECT FOR SHARE. The idea of those statements is that you > want to prevent the row from being updated or deleted until some > other concurrent action is complete; for example, in the case of a > foreign key, we'd like to prevent the referenced row from being > deleted or updated in the relevant columns until the inserting > transaction is committed. But it doesn't work, because when the > updating or deleting process gets done with the lock wait, they > are still using the same snapshot as before, and merrily do > exactly the the thing that the lock-wait was supposed to prevent. This issue is one which appears to be a problem for people trying to migrate from Oracle, where a write conflict would be generated. > If an actual UPDATE is used, it's safe (I think): anyone who was > going to UPDATE or DELETE the row will fail with some kind of > serialization error. Right; a write conflict. > But a SELECT FOR UPDATE that commits is treated more like an > UPDATE that rolls back: it's as if the lock never existed. > Someone (Florian?) proposed a patch to change this, but it seemed > problematic for reasons I no longer exactly remember. It had to do with only having one xmax and how that worked with subtransactions. Of course, besides the technical obstacles, such a semantic change could break existing code for PostgreSQL users. :-( > When using an actual foreign key, we work around this by taking a > new snapshot to cross-check that things haven't changed under us, > but user-level code can't do that. At READ COMMITTED, depending > on the situation, either the fact that we take new snapshots > pretty frequently or the EPQ machinery sometimes make things work > sensibly anyway, and at SERIALIZABLE, SSI prevents these kinds of > anomalies. But REPEATABLE READ has no protection. Well, personally I have a hard time calling READ COMMITTED behavior sensible. Consider this: -- connection 1 test=# create table t (id int not null primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE test=# insert into t select generate_series(1, 10); INSERT 0 10 -- connection 2 test=# begin; BEGIN test=# update t set id = id - 1; UPDATE 10 -- connection 1 test=# select * from t where id = (select min(id) from t) for update; [blocks] -- connection 2 test=# commit; COMMIT -- connection 1 [unblocks] id ---- (0 rows) -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
