Isolation Level in Oracle

We need to have an understanding of different Isolation Levels in Oracle;
Why and what are isolation levels:
With different isolation levels in oracle, the same work performed in the same fashion with the same inputs may result in different answers. The ANSI/ISO SQL standard defines 4 levels of transaction isolation, with different possible outcomes for the same transaction scenario. -
Read uncommitted:
Read committed
Serializable,
Read-only.
Application designers, application developers, and database administrators can choose appropriate isolation levels for different transactions, depending on the application and workload. I will be taking through each isolation level basing on an example how it works and how is it implemented.
Lets get into the details of Read Uncommitted and what exactly do I mean by Read UnCommitted.
This isolation level allows dirty reads. This means – you are permitted to read uncommitted, or dirty, data. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored. Oracle Database doesn't use dirty reads, nor does it even allow them. The basic goal of a READ UNCOMMITTED isolation level is to provide a standards-based definition that allows for nonblocking reads. As you've seen, Oracle Database provides for nonblocking reads by default.
We shall look at an example of the transaction, whose isolation level has been set to Read Uncommitted:
Lets take a simple bank transaction scenario:

Row number	Account Number	Account Name	Account Balance
1	111	Paul	1000$
2	222	Jeff	2000$
3	333	Tom	3000$
4	444	Harry	4000$

Consider a transaction from account 111 to 444 an amount of 500$ from 111 to 444 which has not been committed yet and a select query which is accessing all the rows in the table.

Row number	Account Number	Account Name	Account Balance
1	111	Paul	(1000 ) changed to 500$
2	222	Jeff	2000$
3	333	Tom	3000$
4	444	Harry	(4000) changed

In this scenario, even if the transaction has not been committed yet, the select query sees the values: 500$ in 111 account and 4500$ in the 444 account – which means it is reading dirty data – uncommitted data. . Therefore this not only does it return the wrong answer, but also it returns a total that never existed in the table. In a multiuser database, a dirty read can be a dangerous feature.

Now, lets take a look at the Read Committed Isolation level:
This is the default transaction isolation level.
If we consider a scenario of a simple transaction and a query simultaneously, the query sees only the data that was committed before the query began (and not the transaction). It will completely avoid the dirty reads. But, even this level of isolation has a short coming when it is having multiple transactions at the same time – reason is that Oracle does not prevent other transactions from modifying the data while other transaction is still in a processing state (uncommitted). So, if we consider 2 things simultaneously on the same data – a query and a transaction. Assume transaction hit the DB first - being in the process (uncommitted). It will lock the corresponding rows until the transaction is committed. If a query hits the DB in the process, it would not be able to access the DB until the user commits the changes. But, the bad news here is that the query waiting to read the value of that particular row would actually read the wrong answer, meaning the uncommitted, previous values that were present before the transaction has began. Thus, a transaction that executes a given query twice can experience both nonrepeatable read and phantoms depending on the other transactions in process.
Here is the same example, where we consider another select query during the transaction. Once the transaction (transfer from 111 to 444 ) begins, the rows 111 and 444 are locked until the commit has been performed. So the query( SELECT Account Balance from TABLE1 ) is in wait state till the transaction commits. At some point the transaction commits and account balances in 111 and 444 are changed to 500 and 4500 respectively unlocking the rows. But, we do not get the appropriate result for the query ( SELECT Account Balance from TABLE1 ) - It will still see
Account Balance
1000
2000$
3000$
4000

Instead of actual correct values:
Account Balance
500
2000$
3000$
4500$



Lets take a look at the Serializable transaction.

This is the highest level of Isolation level that can be achieved and the most safest(but at the cost of performance). The transactions or queries that try to access the DB while the Serializable transaction is in process, would be able to get the correct values at any time. In this level of isolation, the rows involved in the transaction are blocked and any queries hitting the DB in the process would block also be blocked.
Serializable isolation is suitable for environments:
• With large databases and short transactions that update only a few rows
• Where the chance that two concurrent transactions will modify the same rows is relatively low.
• Where relatively long-running transactions are primarily read-only



I am considering the same example and scenario. A transaction in process and a query concurrently trying to read the same rows involved in the transaction. This isolation permits such concurrent transactions.
Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:
Cannot serialize access for this transaction.
When a serializable transaction fails with the "Cannot serialize access" error, the application can take any of several actions:
• Commit the work executed to that point
• Execute additional (but different) statements (perhaps after rolling back to a savepoint established earlier in the transaction)
• Roll back the entire transaction.
We need to understand that the name suggests, they will be executed in a serial(sequential model) . These kind of transactions do not experience nonrepeatable reads or phantoms
Let's consider an example:
These levels are defined in terms of three phenomena that are either permitted or not at a given isolation level:
• Dirty read: The meaning of this term is as bad as it sounds. You're permitted to read uncommitted, or dirty, data. You can achieve this effect by just opening an OS file that someone else is writing and reading whatever data happens to be there. Data integrity is compromised, foreign keys are violated, and unique constraints are ignored.
• Nonrepeatable read: This simply means that if you read a row at time T1 and try to reread 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 execute a query at time T1 and re-execute it at time T2, additional rows may have been added to the database, which may affect your results. This differs from a nonrepeatable read in that with a phantom read, data you already read hasn't been changed, but instead, more data satisfies your query criteria than before.
You can set the isolation level of a transaction by using one of these statements at the beginning of a transaction:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET TRANSACTION ISOLATION LEVEL READ ONLY;