'Why can "select * from table* cause a transaction to be non-serializable (postgres)?

i run two transactions with isolation level serializable in parallel. these contain the same statements: select * from table; insert into table values ... ;

(i took the exact case from this video https://youtu.be/4EajrPgJAk0?t=1472 at 24:34) i can reproduce the same error as in the video, but removing the select statement makes the inserts pass. if i remove the inserts, the selects pass.

now my question(s):

  • why does the select cause the transactions to fail? it's just a select, it performs no insert or update. the updates alone do not fail. logically, this makes no sense to me.

  • according to an assignment i saw, it's apparently possible to reproduce an error in mode serializable, make the transactions pass with "read committed", and all that with a single statement per transactions. my understanding after watching the video above doesn't allow this. waht obvious thing am i misunderstanding?

sqlpostgresql


Solution 1:[1]

As described in detail in the documentation[1], postgres will determine if data modified by one transaction is accessed by the other. In the case you remove the selects that will not be the case, presumably.

[1] https://www.postgresql.org/docs/12/transaction-iso.html#XACT-SERIALIZABLE

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 w08r

Related Questions