3 ms·

You can reproduce the problem in postgresql with the following: create table counters(counter int); insert into counters(counter) values(1); BEGI

You can reproduce the problem in postgresql with the following: create table counters(counter int); insert into counters(counter) values(1); BEGIN TRANSACTION ISOLATION LEVEL serializable; select sum(counter) from counters; /* this will return 1 */ /* insert sum into counters. wait until committing next transaction before executing the insert */ insert into counters(counter) values(1); COMMIT; /* this transaction should commit before doing the insert in the above transaction and after the above transaction has calculated the sum */ BEGIN TRANSACTION ISOLATION LEVEL serializable; insert into counters(counter) values(10); COMMIT; both transactions commit and the final table looks like: 1, 10, 1 which is possible if the first transaction committed first, and then the second transaction committed which is different from the order the transactions committed in by the wall clock. so it is possible for another client to see the table as: [1] (the initial state) [1, 10] (after the second transaction committed) [1, 1, 10] (after the first transaction committed) which is a sequence of states which should not be possible. if you see [1], [1, 10] then you should see [1, 10, 11] as the last state. hence it violates external consistency.

karmakaze 8y ago
The query results [1, 1, 10] and [1, 10, 1] are equivalent and any perception of difference irrelevant.
- benmmurphy 8y ago
  I agree the order in the table is irrelevant. However, the ordering observed by another entity doing 3 different read-only transactions is relevant. The third party does query 1 and sees [1]. This is a result of the initial state. The third party does query 2 and sees [1, 10]. This is a result of the second transaction committing. The second transaction just inserts '10' so [1,10] makes sense. The third party does query 3 and sees [1, 1, 10]. This is a result of the first transaction committing but postgresql pretending it committed before the second transaction. The first transaction inserts the sum of what was in the table. If the first transaction committed first then inserting '1' is allowed in the serial order. It doesn't break the second transaction because the second transaction didn't read any rows. In order to let the transaction commit postgresql pretends that it actually committed first There is no serial order the transactions could have committed in that would let the third party see these results. However, this allowed in postgresql because postgresql doesn't prevent anomalies that can only be observed across multiple transactions.

karmakaze 8y ago

The query results [1, 1, 10] and [1, 10, 1] are equivalent and any perception of difference irrelevant.

benmmurphy 8y ago
I agree the order in the table is irrelevant. However, the ordering observed by another entity doing 3 different read-only transactions is relevant. The third party does query 1 and sees [1]. This is a result of the initial state. The third party does query 2 and sees [1, 10]. This is a result of the second transaction committing. The second transaction just inserts '10' so [1,10] makes sense. The third party does query 3 and sees [1, 1, 10]. This is a result of the first transaction committing but postgresql pretending it committed before the second transaction. The first transaction inserts the sum of what was in the table. If the first transaction committed first then inserting '1' is allowed in the serial order. It doesn't break the second transaction because the second transaction didn't read any rows. In order to let the transaction commit postgresql pretends that it actually committed first There is no serial order the transactions could have committed in that would let the third party see these results. However, this allowed in postgresql because postgresql doesn't prevent anomalies that can only be observed across multiple transactions.