The following bug has been logged on the website:
Bug reference: 15153
Logged by: Pablo Arquer
Email address: pdearquer@gmail.com
PostgreSQL version: 9.4.15
Operating system: Debian Jessie
Description:
Hi,
First, thanks for all the effort you put in PostgreSQL, it's a fantastic
piece of software.
Please consider this concurrent access example:
conn1=> \set AUTOCOMMIT off
conn1=> START TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
conn1=> UPDATE MyTable SET number = 123 WHERE id = 1;
conn2=> \set AUTOCOMMIT off
conn2=> START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
conn2=> SELECT number FROM MyTable WHERE id = 1 FOR UPDATE;
conn2 blocks waiting for conn1
conn1=> COMMIT;
conn2 issues a "could not serialize access due to concurrent update" (40001)
error
This isn't a bug, but the expected behaviour
(https://www.postgresql.org/docs/9.4/static/transaction-iso.html). However,
as the SELECT FOR UPDATE statement is the first of the transaction and there
is nothing which needs to be read repeatably, it is possible to run it
safely after conn2 commits. That is, the transaction internal start can be
delayed to the beginning of the first statement and this first statement
executed following the rules of the READ UNCOMMITTED level.
This approach would make it more efficient (saving unecessary retries) and I
think it'd be safe for all cases.
Hope it helps,
Pablo