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

Question

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?

Answer 1

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.

Answer 2

There are few differences between autocommit mode and a READ ONLY READ COMMITTED transaction.

One is that with the transaction, you will get an error if you try to modify any data, for example by calling a function in a SELECT statement. That can serve as a way to make sure you really don't perform any data mpdifications.

Another reason could be that you want to set a parameter only for the duration of the transaction:

SET LOCAL work_mem = '1GB';

Such a setting is automatically reset as soon as the transaction is ended, which can be a useful device.

There may be other differences that don't come to my mind right now, but they are all subtle.

Answer 3

Let's look at these in pieces:

In a READ ONLY transaction, could you not just omit the transaction entirely and have no real difference in behaviour?

In the event of a single query against a database, you do not need to wrap the statement in a BEGIN / COMMIT block. If this is operating inside a stored procedure with multiple long-running queries where READ COMMITTED is not the default isolation level for a database, then a BEGIN / COMMIT can ensure the data being queried remains in a consistent state so that queries at the end of the operation do not conflict with the initial queries that may have been used to populate temporary tables.

You can't use SELECT ... FOR UPDATE in a read-only transaction ...

Correct, as this is illogical. If one is simply reading data, there is no UPDATE required. Using this as a means to lock records at a row-level for read operations would make no sense.

... but you can use LOCK [TABLE], which would last until unlocked or the transaction ends. So there's one difference.

As stated in the above answer, SELECT ... FOR UPDATE locks specific rows in a table. Using LOCK [TABLE] locks every row in the table.

Are there other differences?

There are several types of locks, each with their own set of use cases. As an overzealous use of locks can result in a great deal of contention within a database, seriously affecting system performance for the people using the system, it may be a good idea to review the official documentation to understand the differences and — more importantly — when certain types of locks may be necessary.

---

Speaking from experience, read-only activities rarely need any form of locking. Only in very specific situations does it make sense. Write operations for most user-created data should be wrapped in a transaction to ensure that an error does not result in orphaned records. Table locking is used only during very specific accounting-related operations. Your use case may wildly differ, so take this last bit with a grain of salt.