Yet Another Locking Question

Hi folks,

I am would like to know how Oracle implements the ANSI locking levels. To my knowledge, the ANSI locking levels are

Isolation Level 0	Dirty Reads
Isolation Level 1	Read committed(No Dirty Reads)
Isolation Level 2	Repeatable Reads
Isolation Level 3	No "Phantom Reads"

For example, a repeatable read is when a transaction executes a select and reads rows; later it executes the same select and receives the same rows, unchanged. A "phantom read" is when a transaction executes a select and reads rows; later it executes the same select and receives the same rows unchanged, but also the same number of rows. In other words no rows have been inserted into or deleted from the solution set of the select query within the same transaction.

Sybase implements the locking levels as Isolation Level 0 allowing dirty reads. Isolation level 1 preventing dirty reads. Isolation level 3 which prevents dirty reads, requires repeatable reads, and prevents "phantom reads". Sybase calls Isolation level 3 "select with hold lock".

Informix implements locking levels as Isolation level 0 allowing dirty reads. Isolation level 1 requiring committed reads. Something between isolation level 1 and 3 called cursor stability which locks fetched rows of a cursor and prevents dirty reads. Finally, Isolation level 3 as read committed and repeatable reads which I assume also prevents "phantom reads" as well.

I have read many Oracle books, but have not been able to get s good description of locking. I am also wondering about roll-back segments. If a transaction is reading a row from a roll-back segment that another transaction is updating or possible deleting, isn't that a dirty read?

Thanks in advance for your assistance.

Ron Frank Received on Sun Oct 12 1997 - 00:00:00 CDT