0

I want a certain behavior for transactions in Postgresql, but I can’t tell if it is possible. I would describe this behavior as an “Optimistic Read Lock”, but that does not seem to be the right term for it.

Here is the behavior I want:

  1. My transaction starts. Its transaction isolation level is “repeatable read” or “serializable”.
  2. In the transaction I read row A.
  3. In the transaction I do some more reading and writing, but I never update row A.
  4. I try to commit the transaction.

Now I need the transaction to fail, if row A was changed by some other transaction during the lifetime of my transaction. If row A was unchanged, I want want the transaction to commit.

Is there a way to do this in Postgresql?

My alternative approach would be to update row A with some random value within my transaction, in order to force a write conflict. However that would mean a lot of unnecessary conflicts, because without forcing conflicts, row A would be rarely updated, but often read.

2
  • Why do you think you need that? All that matters is the logical order of transactions. You are safe with SERIALIZABLE. Nov 7, 2023 at 14:08
  • I have a system that writes events into a table. Due to concurrency these events might be inserted out of order. At the end of a transaction that processes these events, I want to make sure that no new relevant events were written (possibly out of order) while these events were being processed. Otherwise it would be possible to “skip” events. Allowing the out-of-order inserts is currently considered a necessity for performance reasons. I did some experiments using “Serializable”, but in my experiments it did not cause a rollback if data was inserted out of order.
    – Labs
    Nov 7, 2023 at 14:25

1 Answer 1

0

You are probably talking about a situation like this:

CREATE TABLE events (
   event_id bigint PRIMARY KEY,
   ts timestamp with time zone NOT NULL
);

CREATE TABLE summary (
   summary_id bigint PRIMARY KEY,
   processed_until timestamp with time zone NOT NULL
);

INSERT INTO events VALUES
   (1, '2023-11-07 12:00:00+00'),
   (2, '2023-11-07 12:10:00+00');

Now transaction 1 summarizes the events:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT max(ts) FROM events;

          max           
════════════════════════
 2023-11-07 12:10:00+00
(1 row)

INSERT INTO summary VALUES (100, '2023-11-07 12:10:00+00');

Before transaction 1 can commit, transaction 2 inserts an older row:

BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO events VALUES (3, '2023-11-07 12:05:00+00');

COMMIT;

And there will be no error when transaction 1 commits.


A possible solution is for transaction 2 to read the summary table after it inserts the new row. Let's look how that works:

Transaction 1:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT max(ts) FROM events;

          max           
════════════════════════
 2023-11-07 12:10:00+00
(1 row)

INSERT INTO summary VALUES (100, '2023-11-07 12:10:00+00');

Transaction 2:

BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO events VALUES (3, '2023-11-07 12:05:00+00');

-- check if everything is fine
SELECT summary_id FROM summary WHERE processed_until >= '2023-11-07 12:05:00+00';

 summary_id 
════════════
(0 rows)

COMMIT;

Now when transaction 1 commits:

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.

Since transaction 2 looked at summary and did not see the summary row, it ran logically before transaction 1. But since transaction 1 did not see its results, it ran logically before transaction 2. This contradiction causes the serialization error.

If transaction 2 finds a summary row, it is in a tight spot, and it should rollback and discard the new value or try to fix the summary. If that is not an option, then you will have to truly serialize operations with big locks, which is not very appealing.

1
  • Thank you for this answer. That is a very interesting approach. I will give it a shot, and see if I can get it to work with our system.
    – Labs
    Nov 7, 2023 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.