Convergence of operations is a feature of all modern databases. This means that one operation can modify the data while the other reads the same data, or two operations read the same data. This entails some complications related to data consistency and readability.
For example – one operation reads data (SELECT1), the number of rows read is 4. Another session after SELECT1 updates the same lines (UPDATE1), and adds another (INSERT1) and validates. Repeating the SELECT1 operation will read the new row and the changed rows. The result of the same operation will be different in subsequent executions. This can lead to undesired situations, such as when we want to double-query to discard the same report in two different file formats.
When reading a new line we are dealing with phantom readings, when we read the changed form of the lines we are dealing with readings which are impossible to repeat.
What you will see will be the SELECT1 operation will depend on the level of transaction isolation.
In Oracle, we have three transaction isolation levels.
The fourth known from other databases – READ UNCOMMITTED is not supported by Oracle.
Available in Oracle:
READ COMMITTED – This mode is the default for Oracle. In this mode, every query sees only the changes that were approved before the query started. If the query is re-executed, the result will include any possible and approved changes, including those deleted and added, after the first (or during) the change request.
SERIALIZABLE – at this level, only changes approved before transaction start and those executed within a given transaction (INSERT, UPDATE, DELETE) will be visible. If we repeat the same SELECTA, we will not be dealing with repetitive or phantom operations.
READ ONLY – Only changes approved before transaction start are visible. No change is possible in a session that has this isolation level enabled.
Enabling individual insulation modes
Alter session set isolation_level = read committed;
Alter system set isolation_level = read committed;
Alter session set isolation_level = serializable;
Alter system set isolation_level = serializable;
Alter session set isolation_level = readonly;
Alter system set isolation_level = readonly;
Mode of operation of the individual insulation levels in the examples
READ COMMITTED – default
In one session I check the data status in the REGIONS table in the HR diagram:
In a separate session, I also check the status, add a new row, and update one existing one. However, I do not approve the transaction:
Then from the same session, I check the data status in the REGIONS table:
The session that changed and added the data sees changes – as made by yourself. Previous session
can not see the change:
The line updated by the second session with region_id = 2 remains locked until it does not approve, or it will not roll back. My first session is waiting for the release of the resource:
Only the second session was approved:
Disables the lock on the line and allows you to make changes to the first session, and also makes the first session see the new row added by the second session. She would see and change the poem, but she just overwritten the changes made by session number two.
As you can see, twice doing the same SELECT in the meantime, while another session is changing, will see different results.
I now turn on the SERIALIZABLE mode, and I get the contents of the REGIONS table.
Now from a separate session I insert a new row and make changes to the existing one. Both operations are approved.
The first session does not see approved changes because it has a SERIALIZABLE isolation level. You will not see these changes as long as you remain in that mode.
Only after leaving SERIALIZABLE because of the COMMIT command (I could also go into READ COMMAND mode with the standard session command: Alter session set isolation_level = read committed) the first session sees changes and new lines introduced by session two.
Now a trap. I run two sessions, one with SQL Developer, the other with SQL * Plus. In the first run SERIALIZABLE mode:
In the second, I make some changes to the COUNTRIES table, and insert a new line into it, and commit the transaction.
Now, from the SQL Developer session, I am returning the COUNTRIES table and ….. I see the changes. What happened? Can SERIALIZABLE mode not work? Nothing happened. I see the changes because earlier in this session I did not ask the COUNTRIES table. If I did this before the changes from the SQL * Plus session, I would not see any changes.