I must be missing something about PostgreSQL and two phase commit with PREPARE TRANSACTION.
The following SQL :
BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
Gives the follwing locks :
4092 Private 329373 acc 15/53295 RowExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 Private 329369 acc 15/53295 RowExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 Private 328704 acc 15/53295 RowExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 Private 327169 acc 15/53295 RowExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 acc 15/53295 15/53295 ExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 Private 329377 acc 15/53295 RowExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
4092 acc 15/53295 ExclusiveLock Oui 2013-06-13 18:15:55+02 UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
As soon as the transaction is prepared :
PREPARE TRANSACTION 'TEST'
the lock are gone.
Because there is small delay that occurs between PREPARE and COMMIT, another query could get an older version of the record.
Is there a configuration setting to avoid this behavior or is it by design ?
Thanks in advance.
EDIT : I'm using PostgreSQL 9.2.2 on Windows x64 (PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit)
EDIT 2 : Following is the full test case :
Issue the following in new session :
BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE person.tcities set ctyname='L ABERGEMENT CLEMENCIAT TRANSACT' WHERE ctyid = 1
PREPARE TRANSACTION 'TEST';
Then in another new session :
SELECT * FROM person.tcities
You'll get the old version of the records.
I can't reproduce the described behaviour on PostgreSQL 9.2:
CREATE TABLE prep_test AS SELECT generate_series(1,10) AS x;
BEGIN;
UPDATE prep_test SET x = x*10;
PREPARE TRANSACTION 'test';
then in a second session:
UPDATE prep_test SET x = x*20;
blocks, as expected, until I either COMMIT PREPARED 'test' or ROLLBACK PREPARED 'test'
I also got the expected results when testing serializable isolation:
A# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
B# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
A# INSERT INTO prep_test(x) VALUES (42);
B# INSERT INTO prep_test(x) VALUES (43);
A# SELECT count(x) FROM prep_test;
B# SELECT count(x) FROM prep_test;
A# PREPARE TRANSACTION 'test';
B# COMMIT;
B fails with a serializability error, exactly as expected. The same happens if B tries to PREPARE TRANSACTION too.
After question updated with test case:
Your test case looks fine to me, ie it should behave exactly as described and execute without error.
PREPARE TRANSACTION isn't a commit. You can still ROLLBACK PREPARED. So PostgreSQL can't show you the changed rows until you do the final COMMIT PREPARED, otherwise you'd get a dirty read anomaly if you read the rows then did a ROLLBACK PREPARED.
You'd get the same result from your test caes if you didn't PREPARE TRANSACTION in the first session before you ran the second command. It has nothing to do with prepared transactions. You're just not seeing rows changed by an uncommitted transaction, which is perfectly normal.
If both transactions are SERIALIZABLE then it's still fine. Serializability requires that there be a valid ordering in which the concurrent transactions could have occurred serially to produce the same results. Here, that's obvious: The SELECT came first, then the UPDATE. The transactions are still occurring concurrently with each other when the SELECT occurs because a transaction prepared with PREPARE TRANSACTION is still open until it's committed or rolled back.