Posgres Concurrency Notes: 2

A repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.If it were to get into such a case, repeatable read transaction will be rolled back with the message

ERROR:  could not serialize access due to concurrent update

The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database.

Setup:
CREATE SCHEMA test;
CREATE TABLE test.purchase (id bigint not null);
Process A: BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;
Process A: INSERT INTO test.purchase(ID) VALUES (10);
Process A: Select sum(ID) From test.purchase where ID between 1 and 100;
Process B: BEGIN TRANSACTION ISOLATION LEVEL Repeatable Read;
Process B: INSERT INTO test.purchase(ID) VALUES (10);
Process B: Select sum(ID) From test.purchase where ID between 1 and 100;
Process B: COMMIT;
Process A: COMMIT;
Process A: COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store