ISO supports following isolation levels
read uncommitted
read committed
repeatable read
serializable
Oracle supports following
read committed
serializable
However oracle's serializable is neither repeatable, nor serializable according to ISO specifications.
Oracle serializable provides the same data as of when the start of the transaction irrespective of any committed changes (yes oracle allows data modification even if you specify serializable) not even the read rows are locked. Only thing is the modifications are hide from you so that you think the access is serializable (cheating?)
Consider the following two cases which require serializable and repeatable (according to ISO). Scenario 1 (requires Serializable)
Table emp contain Columns empid and Role.
There is one script which check if there is any employee with President, if none, they choose one employee randomly and set the role as President.
Assume this script runs concurrently.
| time | Operation of Thread 1 | Operation of Thread 2 |
| 1 | select empid from emp where role = 'President' | |
| 2 | select empid from emp where role = 'President' | |
| 3 | Choose a emp randomly | |
| 4 | Choose a emp randomly | |
| 5 | update emp set role = 'President' where empid = 3 | |
| 6 | update emp set role = 'President' where empid = 5 | |
| 7 | Commit | |
| 8 | Commit |
Now there are two employees with Role as President is was indented
Scenario 2 (requires Repeatable Read)
Emp contains empid, role and backup
There are two scrips
Script 1 - If there are no Vice Presidents, make the backup person as Vice President
Script 2 - Change the backup person (and hence change the vice president also)
If these two scripts run concurrently it will lead to existence of Vice presidents
| time | Operation of thread1 | Operation of thread2 |
| 1 | select empid from emp where role = 'Vice President' | |
| 2 | backup, oldemp = select backup, empid from emp where role ='President' | |
| 3 | backup = select backup from emp where role = 'President' | |
| 4 | update emp set role = 'Vice President' where empid = backup | |
| 5 | update emp set role = null where empid = backup | |
| 6 | update emp set backup = 11 where empid = oldemp | |
| 7 | update emp set role = 'Vice President' where empid = 11 | |
| 8 | commit | |
| 9 | Commit |
|
Now we have old backup as well as new backup having Role as Vice President which was not indented
References:
1) http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html
2) http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21cnsis.htm
1 comment:
that's nice to know.
thanks!
Post a Comment