Does a serializable transaction around a single statement have any effect compared to an implicit transaction?

Question

I'm doing some performance auditing of our codebase and have noticed that we always run SQL statements within a serializable transaction. I've also noticed that many of these transactions only perform a single statement. Are there any circumstances where the effect of a statement will change based on whether or not it's running in a serializable transaction? The official PostgreSQL documentation (https://www.postgresql.org/docs/current/tutorial-transactions.html) states:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.

But it doesn't say anything about the isolation level used.

My hope is I can drop these transactions (avoiding at least two roundtrips for BEGIN and COMMIT, along with some other accidental complexity), but I want to avoid subtly changing the semantics of this code.

Answer 1

Yes, even a single SELECT statement that does not run under the SERIALIZABLE isolation level can cause an anomaly. The following example is taken from this excellent PostgreSQL Wiki article.

We have a single-row table control that determines the currently open batch of receipts:

CREATE TABLE control (
   deposit_no integer NOT NULL
);

INSERT INTO control VALUES (1);

Receipts are stored in this table:

CREATE TABLE receipt (
   receipt_no bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   deposit_no integer NOT NULL,
   payee text NOT NULL,
   amount numeric(15,2) NOT NULL
);

INSERT INTO receipt (deposit_no, payee, amount) VALUES
   ((SELECT deposit_no FROM control), 'Crosby', '100'),
   ((SELECT deposit_no FROM control), 'Stills', '200');
   ((SELECT deposit_no FROM control), 'Nash', '300');

Now, in session 1, we start a transaction and insert another receipt:

BEGIN ISOLATION LEVEL SERIALIZABLE;

INSERT INTO receipt (deposit_no, payee, amount) VALUES
   ((SELECT deposit_no FROM control), 'Young', '100');

That insert is not yet visible outside of session 1.

Then, in a different session 2, somebody closes the current batch of receipts and starts the next one:

BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE control SET deposit_no = deposit_no + 1;

COMMIT;

So far, there is no problem. If session 1 commits before someone peeks, everything is fine, and session 1 is logically before session 2.

But unfortunately session 2 runs a report on the batch it has just closed:

SELECT * FROM receipt WHERE deposit_no = 1;

This query doesn't see the receipt added by the still open session 1.

Now session 1 commits:

COMMIT;

Now we have an anomaly: session 1 must logically be after session 2, because it added a receipt to the batch that session 2 closed. But the report run in session 2 must be logically before session 1, because it does not see the row that session 1 added.

We don't get a serialization error, because the report in session 2 was not run in a SERIALIZABLE transaction. If that SELECT had run in a SERIALIZABLE transaction, session 1 would have got a serialization error on commit.

Answer 2

My hope is I can drop these transactions (avoiding at least two roundtrips for BEGIN and COMMIT, along with some other accidental complexity), but I want to avoid subtly changing the semantics of this code.

You should always exercise utmost caution when altering the code of an existing database application which you didn't design.

This kind of "performance audit" approach is almost certain to cause you grief, and it may introduce defects which are serious but occur in very unusual circumstances, or have very subtle corrupting effects which will not be noticed until the reliability of a large amount of data has been undermined.

I've also noticed that many of these transactions only perform a single statement. Are there any circumstances where the effect of a statement will change based on whether or not it's running in a serializable transaction?

All database statements are always covered by a transaction, whether "explicit" or "implicit".

In general, database engines operate in "auto-commit" mode, where a single statement not already covered by an explicit transaction, instead executes within an implicit transaction that automatically begins and then commits at the end of the statement.

Therefore, it isn't usually necessary to wrap a single statement with BEGIN...COMMIT. It is only necessary to have an explicit transaction with multi-statement transactions.

But it doesn't say anything about the isolation level used.

The "isolation level" is a concept different from, but related to, the "transaction".

The transaction defines the scope of statements which are a single logical unit of work, and therefore the scope of statements across which the database must guarantee "consistency".

The transaction also defines the scope of work which will be rolled back, either in the event of an error during the transaction, or in the event of an explicit rollback command.

The isolation level, meanwhile, determines how two (or more) transactions interact when they are executed concurrently.

In other words, the isolation level is irrelevant to an analysis of individual transactions, and it is also irrelevant to an analysis of multiple transactions executing at different times. It is only relevant to multiple transactions executing concurrently.

The whole topic defies simple summary, but SERIALIZABLE is the highest isolation level offered by a database engine. It is the isolation level that programmers invariably want in principle, and which minimises or eliminates the amount of global reasoning required to program a database application correctly.

Programmers downgrade from this isolation level only for compelling performance reasons, or by mistake. Mistake is probably the most common reason, and leads to applications where subtle defects in behaviour are possible under concurrent load.

The isolation level is set via the SET TRANSACTION ISOLATION LEVEL command or via an ambient setting, and is independent of whether you choose to use BEGIN...COMMIT to enclose a single statement or not.

There are many sources of information online that set up scenarios to describe the consequences of lower isolation levels in principle, but knowing these in principle does not get you very far in deciding whether it is acceptable to use a lower isolation level than one already set. Analysis of the database application itself would be a far harder task.

My advice would be not to meddle with the existing code - especially not sweeping changes across a codebase - unless performance profiling shows that existing performance is completely unacceptable for practical use.

Depending on your circumstances, doing so could change a mature, carefully-designed, and battle-tested application into one riddled with subtle faults that never previously existed - and not just the kind of faults that cause immediate crashes, but the kind which just silently corrupts data, or which causes a latent proneness to crash that only materialises perhaps months later when silently corrupted data finally reaches an algorithm that doesn't cope.