>> 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