Transaction Isolation Level in ODI

Transaction Isolation Level in ODI:

Hi Friends,

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 (11.1.1.9) procedure, it has 6 options as shown below.

ODI_ISOLATION_1
Transaction Isolation Level in ODI

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

ODI_ISOLATION_12c
Transaction Isolation Level in ODI

 

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.
NONE: none

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.

ODI_ISOLATION_NOT_SUPPORTED
Transaction Isolation Level in ODI

 

Also there is a justification for READ_UNCOMMITED as Oracle never permits dirty reads. Here is the note from Oracle documentation.

ODI_ISOLATION_0

 

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.

ODI_ISOLATION_5
Transaction Isolation Level in ODI
ODI_ISOLATION_6
Transaction Isolation Level in ODI

 

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.

ODI_ISOLATION_3

ODI_ISOLATION_4
Transaction Isolation Level in ODI

 

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.

ODI_ISOLATION_7
Transaction Isolation Level in ODI

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.

 

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

2 Comments

  1. Well explained. Thanks.
    Further, I was trying in session parallelism where this transactional setting seems to be blocking the parallelism. Any Idea, what changes are required for same?

Leave a Reply

Your email address will not be published.


*