ISOLATION levels


ISOLATION will used to determine how much time the program bound to the package can be isolated from the effect of other programs execution. In other words, how the program execution is isolated for accessing the database which is accessed by the other programs concurrently.

Database can be accessed by the multiple users at a time (Concurrency). The Isolation level is the way to maintain the concurrency for the multiple users by setting the levels of access to the database. ISOLATION is the BIND parameter which will determine the page lock duration.

CS	------	Cursor Stability.
RR	------	Repeatable Read.
UR	------	Uncommitted Read. 
	OR 
DR	------	Dirty Read.

Cursor Stability -


The Cursor stability isolation level is the most preferred isolation level among from all the isolation levels. It acquires the addressability of a record and locks one page of record. Lock is going to be released after the transaction is done.

It will lock only one row that currently referenced by the Cursor. It doesn’t mean that where the current pointer of the cursor lies. The row lock will remain until the next FETCH will be executed.

The other transactions can run concurrently and no depend on the current transaction. The other transactions can’t able to perform any operations like update or delete on the row currently locked. If no Isolation level defined, then the Cursor stability will take it as default.

Repeatable Read -


Repeatable read is the most dangerous and not suggested to use among all the isolation levels. If this isolation level is used, every row that is referenced by the current transaction will be locked and other transactions restricted to access the data. It acquires the addressability of a record and locks one page of information. (1 page = 4000 bytes of data)

If this isolation level used, the data retrieved will be same for the transaction even it is for the same transaction.All other transactions restricted to perform insert, delete and update on the data which is hold by the repeatable read.

Uncommitted read/Dirty read -


Uncommitted read was the least restrictive isolation level among from all. If this isolation level is used, the rows retrieved will be locked only when the transaction tries to modify it or other transactions tries to delete it or tries to delete the table. In other cases, the isolation level is used for read-only access the database.