Search code examples
databasetransactionsacid

Is it correct to say that data reading operations need not run inside transactions?

Say that a method only reads data from a database and does not write to it. Is it always the case that such methods don't need to run within a transaction?

Solution

  • No. If you don't read at a specific isolation level you might not get enough guarantees. For example rows might disappear or new rows might appear.

    This is true even for a single statement:

    select * from Tab
except select * from Tab

    This query can actually return rows in case of concurrent modifications because it scans the table twice.

    SQL Server: There is an easy way to get fast, nonblocking, nonlocking, consistent reads: Enable snapshot isolation and read in a snapshot transaction. AFAIK Oracle has this capability as well. Postgres too.