HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is there a use for a READ COMMITTED READ ONLY transaction?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
committedreadfortransactionthereonlyuse

Problem

A READ COMMITTED (the default isolation level) transaction will acquire a new snapshot for each query. In a READ ONLY transaction, could you not just omit the transaction entirely and have no real difference in behaviour?

You can't use SELECT ... FOR UPDATE in a read-only transaction, but you can use LOCK [TABLE], which would last until unlocked or the transaction ends. So there's one difference. Are there other differences?

Solution

Let's suppose some queries use now() or one of these functions:

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME(precision)
CURRENT_TIMESTAMP(precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME(precision)
LOCALTIMESTAMP(precision)

As said in the doc:

These SQL-standard functions all return values based on the start time
of the current transaction

So when a query like select * from table where somefield < now() is part of a long-running transaction in read-committed mode, the result obtained can be significantly different from the results of the same query run outside of a transaction at the same exact point in time.

This is just one example, but it seems sufficient to refute the hypothesis that isolated read-only statements are not to be distinguished from statements in a read-committed read-only transaction.

Context

StackExchange Database Administrators Q#292610, answer score: 4

Revisions (0)

No revisions yet.