isolation levels

Oracle RDB uses a locking mechanism for controlling concurrency and enforcing logical and physical integrity of the database. The strategy for locking objects is as follows:

* Lock the object
* Perform the required work on the object
* Unlock the object at a later time, most likely at the end of the Transaction

Among lockable objects the following hierarchy is found:

* Database
* Table
* Page
* Row
* Index node

Oracle RDB implements a dedicated logic that chooses the appropriate object and adjusts the lock granularity. It selects a suitable lock object and level which are based on the operation being performed in a given context. All this is aimed at minimizing potential lock conflicts.

Note that the locking scope is normally associated with a transaction. The SET TRANSACTION syntax contains elements for controlling the LOCKING applied:

* READ/WRITE
* WAIT [x]/NOWAIT

Nevertheless Oracle RDB locking policy is rather strict. Lock conflicts are a matter of routine in a database that serves multiple users concurrently. A user can be a ‘Blocker’, while holding a locked resource required by others, or ‘Blocked By’, while waiting for a resource locked by others to be released or ‘Both’.

Circular lock conflicts can lead to deadlock situations where User A locks Resource 1 and waits for Resource 2, which is locked by User B who is waiting for Resource 1.

The Oracle RDB data source driver is aware of Oracle RDB potential locking conflicts and provides certain built-in relief measures:

* The default transaction WAIT parameter is 0. This translates to NOWAIT, which actually means that a default transaction initiated by the Oracle RDB data source will not be blocked. Instead it will inform of a lock conflict if one is encountered.
* The default proposed isolation level is READ COMITTED which reduces lock contention and increases the degree of concurrency. Note that this affects ‘pure’ transactional integrity, since data committed by others is visible in your transaction.
* The Oracle RDB data source maintains dual (multiple) connections to the database. This notion is based on the fact that when snapshots are enabled for a database (the default), READ ONLY transactions do not lock the rows they read. Data is read from the snapshot maintained by Oracle RDB. Therefore the Oracle RDB data source driver normally holds two connections to the database. The first, denoted as NAV in the data source driver’s log, is used for WRITE transactions, which are subject to LOCK conflicts. The second, denoted as NAVREAD, serves the READ transactions. This notion of duplicating connections is also extended to separating DDL and stored procedure locking activities from the main data source driver’s course, which is READ/WRITE operations.
* The Oracle RDB data source provides explicit control on elements that affect locking along the transaction. This is carried out by assigning appropriate values to configurable parameters, as described above. In particular, ISOLATION LEVEL can be set explicitly both for READ and WRITE transactions as follows:

– readCommitted: Allows your transaction to see all data committed by other transactions.

– repeatableRead: Guarantees that if you execute the same query again, your program receives the same rows it read the first time. However, you may also see rows inserted and committed by other transactions (known as Phantoms).

– serializable: Guarantees that the operations of concurrently executed transactions are not affected by any other transaction.

Serializable

from ask tom

The ANSI/ISO SQL92 standard adds to the concurrency control mix as well. In this standard, they define four levels of transaction isolation with different possible outcomes for the same transaction mixes. That is, the same work performed in the same fashion with the same inputs, may result in different answers given your isolation level. These isolation levels are defined in terms of three ‘phenomena’ that are either permitted or not at a given level. These phenomena are:

* Dirty read : The meaning of this is as bad as it sounds. You are permitted to read uncommitted ‘dirty’ data. This is the effect you would achieve by just opening an OS file someone else is writing, and reading whatever data happened to be there. Data integrity is compromised, foreign keys violated, unique constraints ignored.

* Non-repeatable read : This simply means that if you read a row at time T1, and attempt to re-read that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.

* Phantom read : This means that if you executed a query at time T1, and re-execute it at time T2, additional rows may have been added to the database, which affects your results. This differs from the non-repeatable read in that in this case, data you already read has not been changed but rather that more data satisfies your query criteria than before.

SQL92 takes these three phenomena and creates four isolation levels based on the existence, or lack thereof, of the above phenomena. They are:

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted Permitted Permitted Permitted
Read Committed   Permitted Permitted
Repeatable Read     Permitted
Serializable      

Oracle supports explicitly two of the above isolation levels as they are defined ? read committed and serializable. This doesn’t tell the whole story however. The SQL92 standard was attempting to set up isolation levels that would permit various degrees of consistency for queries performed in each level. Repeatable read is the isolation level they claim you must be at to get a read consistent result from a query. Read committed does not give you consistent results. Read uncommitted is the level to use to get non-blocking reads.

So, serializable gives you the ability to prevent “phantom reads” — a really really repeatable read if you will. To paraphrase:

A serializable transaction operates in an environment that makes it appear as if there are no other users modifying data in the database, the database will be “frozen” at the point in time your query began. Your transaction sees the database consistently, at a single point in time. Side effects (changes) made by other transactions are not visible to it, regardless of how long it has been running.
that is what is given beyond read committed. In a serializable transaction if you do the following:

t1       select count(*) from b -- returns "0"
t2                                                   insert into b values(1);
t3                                                   commit;
t4                                                   select count(*) from b --
                                                         returns "1"
t5       select count(*) from b -- returns "0"
t6       commit;
t7       select count(*) from b -- returns "1"

Using read committed, the query at time t5 would return 1, not 0. You get a “phantom read”


This is generally considered the most restrictive level of transaction isolation, but provides the highest degree of isolation. A serializable transaction operates in an environment that makes it appear as if there are no other users modifying data in the database, the database will be “frozen” at the point in time your query began. Your transaction sees the database consistently, at a single point in time. Side effects (changes) made by other transactions are not visible to it, regardless of how long it has been running. Serializable does not mean that all transactions executed by the users are the same as if they were executed one right after another in a serial fashion. It does not imply that there is some serial ordering of the transactions that would result in the same outcome. This last point is a frequently misunderstood concept and a small demonstration will clear it up. The following table represents two sessions performing work over time. The database tables A and B referenced in the example start out empty and are created as such:

tkyte@TKYTE816> create table a ( x int );
Table created.

tkyte@TKYTE816> create table b ( x int );
Table created.

Time    Session 1 Executes    Session 2 Executes
0:00    Alter session set isolation_level=serializable;
0:01                            Alter session set isolation_level=serializable;
0:02    Insert into a select count(*) from B;
0:03                            Insert into b select count(*) from A;
0:04    Commit;
0:05                            Commit;

Now, when this is all said and done – A and B will each have a row with the value of zero in it. If there was some “serial” ordering of the transactions – we could not possibly have both tables containing the value zero in them. If Session 1 executed before Session 2 ? then table B would have a count of 1. If Session 2 executed before Session 1 ? then table A would have a count of 1. Both tables however will have a count of ZERO. They just executed as if they were the only transaction in the database at that point in time. No matter how many times Session 1 queried table B – the count would be the count that was committed in the database at time 0:00. Likewise, no matter how many times Session 2 queries table A – it will be the same as it was at time 0:01.

So, what I was trying to demonstrate is what serializable means — in short:

A serializable transaction operates in an environment that makes it appear as if there are no other users modifying data in the database, the database will be “frozen” at the point in time your query began. Your transaction sees the database consistently, at a single point in time. Side effects (changes) made by other transactions are not visible to it, regardless of how long it has been running.

13 Data Concurrency and Consistency

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s