On 10.10.2012 22:42, Andres Freund wrote: > On Wednesday, October 10, 2012 10:21:51 PM Tomas Vondra wrote: >> Hi, >> >> I've just noticed a change of LOCK command behavior between 9.1 and 9.2, >> and I'm not sure whether this is expected or not. >> >> Let's use a very simple table >> >> CREATE TABLE x (id INT); >> >> Say there are two sessions - A and B, where A performs some operations >> on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g. >> it might be a pg_bulkload that acquires such locks, and we need to do >> that explicitly on one or two places). >> >> Session B is attempting to read the data, but is blocked and waits. On >> 9.1 it sees the commited data (which is what we need) but on 9.2 it sees >> only data commited at the time of the lock attemt. >> >> Example: >> >> A: BEGIN; >> A: LOCK x IN ACCESS EXCLUSIVE MODE; >> A: INSERT INTO x VALUES (100); >> B: SELECT * FROM x; >> A: COMMIT; >> >> Now on 9.1, B receives the value "100" while on 9.2 it gets no rows. >> >> Is this expected? I suspect the snapshot is read at different time or >> something, but I've checked release notes but I haven't seen anything >> relevant. >> >> Without getting the commited version of data, the locking is somehow >> pointless for us (unless using a different lock, not the table itself). > That sounds like youre using different isolation levels in 9.1 and 9.2. Is > that > possible? I.e. your 9.1 test uses read committed, and 9.2 uses repeatable > read > or serializable.
Nope, it's 'read commited' on both. I haven't touched this, but I've verified it to be sure. ============ 9.1 ============ $ psql testdb psql (9.1.6) Type "help" for help. testdb=# show server_version; server_version ---------------- 9.1.6 (1 row) testdb=# show transaction_isolation ; transaction_isolation ----------------------- read committed (1 row) ============ 9.2 ============ $ psql testdb psql (9.2.0) Type "help" for help. testdb=# show server_version; server_version ---------------- 9.2.0 (1 row) testdb=# show transaction_isolation testdb-# ; transaction_isolation ----------------------- read committed (1 row) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
