Обсуждение: BUG #17017: Two versions of the same row of records are returned in one query
BUG #17017: Two versions of the same row of records are returned in one query
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 17017 Logged by: 刘沛源 Email address: lpy.henu@gmail.com PostgreSQL version: 9.6.21 Operating system: CentOS Linux release 7.4.1708 (Core) Description: Schema and Initial data: Create Table t(a int primary key, b int); Insert into t values(1,2); Insert into t values(2,3); Operation: There are two sessions executing at the same time. [Time0, SessonA] > Begin; > set transaction isolation level repeatable read; > Select * from t where a=1; [Time1, SessonB] > Begin; > set transaction isolation level read committed; > Delete from t where a=2; > Commit; [Time2, SessonA] > Insert into t values(2,4); > Select * from t where a=2; Here, we expect PostgreSQL Server to return a row: 2 3 However, it returns two rows: 2 4 2 3
PG Bug reporting form <noreply@postgresql.org> writes: > Schema and Initial data: > Create Table t(a int primary key, b int); > Insert into t values(1,2); > Insert into t values(2,3); > [Time0, SessonA] >> Begin; >> set transaction isolation level repeatable read; >> Select * from t where a=1; > [Time1, SessonB] >> Begin; >> set transaction isolation level read committed; >> Delete from t where a=2; >> Commit; > [Time2, SessonA] >> Insert into t values(2,4); >> Select * from t where a=2; > Here, we expect PostgreSQL Server to return a row: > 2 3 > However, it returns two rows: > 2 4 > 2 3 In repeatable read mode, this doesn't surprise me. Session A surely must return the (2,3) row, since it isn't supposed to "see" the results of Session B's commit yet. And it would be mighty surprising for it not to see its own insertion, so you get (2,4) as well. In serializable mode, I'd expect this situation to throw a serialization error, and it does (but you must run BOTH transactions in serializable mode; there are not guarantees with a mix of serializable and non-serializable transactions). So I think the answer is "if you care about this, use serializable mode". Repeatable read mode meets the requirements of the SQL spec, but it doesn't guarantee no anomalies. regards, tom lane