Isolation level for two statements on the same transaction vs. single statement

Question

I have the following table:

DROP TABLE IF EXISTS event;
CREATE TABLE event(
    kind VARCHAR NOT NULL,
    num  INTEGER NOT NULL
);
ALTER TABLE event ADD PRIMARY KEY (kind, num);

The idea is that I want to use the num column to maintain separate increment counters for each kind of event. So num is like a dedicated auto-increment sequence for each different kind.

Assuming multiple clients/threads writing events (potentially of the same kind) into that table concurrently, is there any difference in terms of the required level for transaction isolation between: (a) executing the following block:

BEGIN TRANSACTION;
DO
$do$
DECLARE
    nextNum INTEGER;
BEGIN    
    SELECT COALESCE(MAX(num),-1)+1 FROM event WHERE kind='A' INTO nextNum;
    INSERT INTO event(kind, num) VALUES('A', nextNum);
END;
$do$;
COMMIT;

... and (b) combining the select and insert into a single statement:

INSERT INTO event(kind, num)
(SELECT 'A', COALESCE(MAX(num),-1)+1 FROM event WHERE kind='A');

From some tests I run it seems that in both cases I need the serializable transaction isolation level. What's more, even with the serializable transactions isolation level, my code has to be prepared to retry due to the following error in highly concurrency situations:

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.

In other words merging the select into the insert does not seem to confer any benefit in terms of atomicity or allow any lower/more lenient transaction isolation level to be set. Am I missing anything?

(This question is broadly related in that it asks for a PostgreSQL pattern to facilitate the generation of multiple sequences inside a table. So just to be clear I am not asking for the right pattern to do that sort of thing; I just want to understand if the block of two statements is in any way different than a single merged INSERT/SELECT statement).

I believe you are gaining nothing by going from the default isolation level (read committed) to serializable. You can wrap the whole thing in a begin try/catch and retry if it fails — benjamin moskovits, Mar 13 '15 at 15:25
@benjaminmoskovits I confirm after more testing that not only does the simpler `read committed` isolation level also work but that the `serializable` isolation level resulted in fact in many more retries being actually needed. — Marcus Junius Brutus, Mar 13 '15 at 19:59
Thanks. By the way if test by doing your code without a commit and in another window try this: SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks and you will see all the locks currently in affect. — benjamin moskovits, Mar 13 '15 at 20:24

score 1 · Accepted Answer · edited May 23 '17 at 12:28

The problem with the task is possible concurrent write access. Merging SELECT and INSERT into one statement reduces the time frame for possible conflicts to a minimum and is the superior approach in any case. The potential for conflicts is still there. And yes, serializable transaction isolation is one possible (if expensive) solution.

Typically (but that's not what you are asking), the best solution is not to try what you are trying. Gapless sequential numbers are a pain in databases with concurrent write access. If possible, use a serial column instead, which gives you unique ascending numbers - with possible gaps. You can eliminate gaps later or dynamically with in a VIEW. Details:

Serial numbers per group of rows for compound key

Aside: you don't need parentheses around the SELECT:

INSERT INTO event(kind, num)
SELECT 'A', COALESCE(MAX(num) + 1, 0) FROM event WHERE kind='A';

I appreciated the insight about "gapless sequences" being a bad idea as none of the answers in the question I linked to had volunteered that perspective. Will definitely go that way on my next system. — Marcus Junius Brutus, Mar 13 '15 at 20:19

Isolation level for two statements on the same transaction vs. single statement

1 Answers1