Anomaly detection can be pretty complex because the database is unaware of all the reads and writes that are part of a transaction until it is committed. The application may execute multiple statements within a transaction where one statement determines what runs next. If the database knew all the reads and writes in advance, it would be easier to serialize access by constructing a conflict graph and ordering the operations. However, anomaly detection must log enough information to detect future conflicts. Those are locks or intents.

In some situations, the database knows no write will conflict with what was previously read, such as in read-only transactions. Although this is not explicitly stated in traditional SQL isolation levels, the read-only transaction mode is essential in MVCC databases, crucial for OLAP and reporting transactions.

Setting a transaction as read-only is advisable when dealing with analytics or reporting. This type of transaction allows you to query a consistent virtual snapshot of the database from a read-time set at the beginning of the transaction or earlier if you are reading from asynchronous replicas, without all the overhead of serializable conflict detection. In monolithic databases, this is the only activity that can be scaled out, and the read-time depends on the last Write-Ahead Log (WAL) record applied.

With Distributed SQL databases like YugabyteDB, you can optimize the efficiency of querying by reading from the Raft followers if they are closer and provide a lower latency than reading from the Raft leader. This eliminates any need to check the current intents in the provisional records and allows for a read-time typically 15 seconds before the start of the transaction. By doing this, you can further reduce latency and enhance the performance of your database queries in Distributed SQL environments.

It is a best practice to define transactions as read-only in databases when you know that it will not perform any write operations. During the design phase, it is important to identify read-only use cases, such as in the Command Query Responsibility Segregation (CQRS) pattern. When deploying, the read-only services should have a dedicated connection pool set as read-only.

Oracle Database defines Read Only as an isolation level. This sets a serializable isolation level, but restricts it from allowing any writes.

When a transaction is set to read-only mode, it does not always set serializable mode. In PostgreSQL, when you use begin transaction isolation level read committed read only;, the read time is reset for each statement. However, if you use begin transaction isolation level serializable read only;, all queries in the transaction will have the same read time, which is the start of the transaction. This guarantees a consistent multi-statement report without anomalies. Additionally, the begin transaction isolation level serializable read only deferrable; allows the transaction to wait for a read time guaranteed without conflict. This reduces the overhead of conflict resolution and ensures that you will never receive a serialization error. However, it may increase the latency if there are long-running transactions as it has to wait for them to be completed.

If you are using YugabyteDB, which is distributed PostgreSQL, it is recommended to use serializable read only deferrable for reporting on OLTP databases. This command waits for the maximum clock skew in addition to the end of concurrent transactions so that no serializable errors occur. Besides that, such a report probably accept a read time from a few seconds before the start of the transaction which will be optimized with set yb_read_from_followers to on;

Here are the characteristics of Serializable Read Only Deferrable isolation level in YugabyteDB

• Read time: the start of the transaction
• Possible anomalies: none, all reads appear as of the same point in time
• Performance: no overhead, but available only when no writes are involved. Reduces the latency when reads from followers are enabled.
• Development constraint: set the transaction read-only, no need for a retry logic when deferrable.