2005/11/27

Transaction isolation levels in Oracle Database

There's an interesting article about transaction isolation levels in the last issue (November/December 2005) of Oracle Magazine - On Transaction Isolation Levels by Tom Kyte.

Being a SQL Server guy who has to use NOLOCK all the time, I was surprised to learn that there is no READ UNCOMITTED isolation level in Oracle Database. And here's the explanation how it is solved:
When the query I'm executing gets to the block containing the locked row (row 342,023) at the bottom of the table, it will notice that the data in it has changed since execution began. To provide a consistent, or correct, answer, Oracle Database will create a copy of the block containing this row as it existed when the query began. That is, it will read a value of $100, which is the value that existed when the query began. Effectively, Oracle Database takes a detour around the modified data—it reads around it, reconstructing it from the undo (also known as a rollback) segment. A consistent and correct answer comes back without waiting for the transaction to commit.
Nice.

No comments:

Post a Comment