patternsqlMinor
Is there a use for a READ COMMITTED READ ONLY transaction?
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?
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
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
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.
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.