CODEWITHSUNDEEP
sqldatabaseoracle-databasetransactionsrdbms
Mr.Eddart
Mr.Eddart

Reputation: 10273

Transaction isolation level for SELECTs

Given a transaction running on a Relational Database, firing a series of SELECTS.

I am supposing that if in the middle of this transaction, any other transaction commits some UPDATE or INSERT against the database, this new data is VISIBLE for the rest of the remaining selects in the former transaction. Is this assumption correct?

I mean, I am supposing that the transaction is not isolated for reading (it always reads the last state of the Database even if it changes in the meanwhile), but only for the writings, is it?

If this depends on the the transactional policy of each RDBMS, what is the policy of Oracle?

Upvotes: 4

Views: 300

Answers (2)

Mr.Eddart
Mr.Eddart

Reputation: 10273

Copy&Pasting the answer given as comment to the question:

The default isolation level in Oracle is "read committed" (you "see" changes committed by other transactions, even if they were committed after your transaction started). Oracle also allows you to set the isolation level to "serializable" (you only "see" changes that had been committed by other transactions at the time your transaction started) or "read only" (like "serializable", except that it does not allow to INSERT, UPDATE, or DELETE). For all the details, see link.

Upvotes: 0

DCookie
DCookie

Reputation: 43533

Your assumption is correct, at least for Oracle.

Oracle guarantees the consistency of a read performed at a given moment. Once the read is initiated, it doesn't matter if other transactions change the data being selected - Oracle guarantees the data is what was in the database at the start of the read. If it can't honor that guarantee, you get an "ORA-01555 snapshot too old" error. However, subsequent selects may not get the same answer.

In order to provide read isolation/repeatable reads, you have to give up some concurrency, because you have to lock the table against updates. Oracle chose to be highly concurrent - readers don't block.

If you're just looking for data at a given point in time, Oracle does provide flashback queries.

Upvotes: 4

Related Questions