>> Another approach to the atomic visibility problem is to control >> snapshot acquisition timing and commit timing (plus using REPEATABLE >> READ). In the REPEATABLE READ transaction isolation level, PostgreSQL >> assigns a snapshot at the time when the first command is executed in a >> transaction. If we could prevent any commit while any transaction is >> acquiring snapshot, and we could prevent any snapshot acquisition while >> committing, visibility inconsistency which Amit explained can be >> avoided. >> > > I think the problem mentioned above can occur with this as well or if > I am missing something then can you explain in further detail how it > won't create problem in the scenario I have used above?
So the problem you mentioned above is like this? (S1/S2 denotes transactions (sessions), N1/N2 is the postgreSQL servers). Since S1 already committed on N1, S2 sees the row on N1. However S2 does not see the row on N2 since S1 has not committed on N2 yet. S1/N1: DROP TABLE t1; DROP TABLE S1/N1: CREATE TABLE t1(i int); CREATE TABLE S1/N2: DROP TABLE t1; DROP TABLE S1/N2: CREATE TABLE t1(i int); CREATE TABLE S1/N1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN S1/N2: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN S2/N1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN S1/N1: INSERT INTO t1 VALUES (1); INSERT 0 1 S1/N2: INSERT INTO t1 VALUES (1); INSERT 0 1 S1/N1: PREPARE TRANSACTION 's1n1'; PREPARE TRANSACTION S1/N2: PREPARE TRANSACTION 's1n2'; PREPARE TRANSACTION S2/N1: PREPARE TRANSACTION 's2n1'; PREPARE TRANSACTION S1/N1: COMMIT PREPARED 's1n1'; COMMIT PREPARED S2/N1: SELECT * FROM t1; -- see the row i --- 1 (1 row) S2/N2: SELECT * FROM t1; -- doesn't see the row i --- (0 rows) S1/N2: COMMIT PREPARED 's1n2'; COMMIT PREPARED S2/N1: COMMIT PREPARED 's2n1'; COMMIT PREPARED Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
