Transaction Isolation Level in ODI:
Today I am going to demonstrate the Transaction Isolation in ODI. While working with temporary tables in SQL Server I got some surprising result and that led me to write this post. In this post I will be explaining the behavior in Oracle 11g and in the coming post will write on SQL Server 2000. And regarding the ODI versions, there is not much difference between ODI 11g and 12c. If you see the drop down in 11g (126.96.36.199) procedure, it has 6 options as shown below.
where as in 12c (12.2.1) the display options has changed slightly. As per documentation NONE is now deprecated.
NONE: Deprecated in 12.1.2 release with no direct replacement and will be removed soon, existing values will be changed to null
DEFAULT: default mode will be used (i.e. DB or driver specific)
READ_UNCOMMITED: The transaction can read data not committed by another transaction.
READ_COMMITED: The transaction can only read data committed by other transactions (in general, this is the default mode of many data servers.
REPEATABLE_READ: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified and committed by another transaction in the meantime.
SERIALIZABLE: The transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified, deleted, or created and committed by another transaction in the meantime.
At this moment Oracle only supports READ_COMMITED,SERIALIZABLE.
NONE, DEFAULT and READ_UNCOMMITED is not supported. If you will try with any of these options, you will see below error.
Also there is a justification for READ_UNCOMMITED as Oracle never permits dirty reads. Here is the note from Oracle documentation.
So we will be looking at READ_COMMITED, SERIALIZABLE. The READ_COMMITED is the default one and hence it will always read the commited data from a table. That means even if you have different transactions, It will still reads the data commited by other transaction.
I have created below procedure for this demonstration. I guess I dont need to give explanation as this is quite straight forward. Notice the difference between step 20 and 50. Both are having different transaction and in step 50 the isolation level is READ_COMMITED. Even if you won’t select READ_COMMITED, this will be taken as default.
In the above screenshot I am creating a table TESTISOLATION and inserting a value as 1 using transaction zero. The commit option is set to “COMMIT”. Now in the next step, I am selecting the value with another transaction and inserting it to a table called TESTISOLATION1.
If you will try to execute the procedure you can see value inserted in TESTISOLATION1 table and of-course this is expected.
Now lets see the SERIALIZABLE one.
As I have already mentioned that in case of SERIALIZABLE, transaction is certain to read the same information if it executes the same SQL query several times, even if the rows have been modified, deleted, or created and committed by another transaction in the meantime. So lets try to read the data in one transaction, then delete in another transaction followed by a commit. Then again read it in the previous transaction. If we can still see it then that will justify the above statement.
If you see the above screenshot, in step 20 I am trying to insert “1” to the TESTSIOLATION table using Transaction 0 with COMMIT.
In step 30, I am selecting the value using Transaction 1 and Isolation Level as SERIALIZABLE. This step should keep the values in that session without being affected by other sessions.
In step 40, I am deleting the value using Transaction 0 with COMMIT. This means now there is nothing in the table as I am commiting the DML operation.
In step 50, I am again selecting the values using Transaction 1 and Isolation Level as SERIALIZABLE ( See Command on Source tab). Well this is quite interesting here. Even if we have deleted the rows in previous step the value is still available in this transaction. If you run this procedure you can see no row in TESTSIOLATION and one row in TESTSIOLATION1.
Ok so far so good. Lets make it bit more interesting. In the first procedure set the COMMIT option “NO COMMIT” for all the steps and execute the procedure. Can you tell me which table will have rows????
Think again before looking down.
In this case you can see rows for TESTSIOLATION only. There will be nothing in TESTSIOLATION1. This is because in step number 2o, we have inserted a row without commit. Now in step number 30, we have a different transaction than step 20. That means this step will not be selecting any records and hence nothing to insert. When the procedure completes without any error, ODI will always fire a commit and this will result a row in the TESTSIOLATION table.
If the procedure will fail at the end step (Uncheck ignore error) then everything will be rolled back. Thats it. If you are not aware of this feature then dont forget to check my previous post.
Thats it for today!! Lets me know what you think.