Advantage of SQL_TXN_SERIALIZABLE over SQL_TXN_REPEATABLE_READ in DB2 and C++

Question

Hi,

We are facing a problem in our application. We have two instances of our monitoring application. The application behaviour is as follows:

Step 1. Monitor the ftp folder in a loop
Step 2. If files are present, insert the file details to DB for the all files
Step 3. Read the file details from the DB and process it
Step 4. Once the file is selected from DB change the status to start processing so that no other process should process it.

Here we have two monitor process, two instance of same program (monitor --instance 1 && monitor --instance 2)

Here at some particular time, both process monitor1 and monitor2 reads the same data from the DB and process. Because of this, the same file is processed twice.

It is due to the delay in step 3 and step 4. Monitor1 does step 3 and before it does step 4, monitor2 also does step3 so that it was not aware that already monitor1 got the record.

Our DB is db2 and we are using SQL_TXN_READ_UNCOMMITTED isolation level in step3. I found from the IBM site that SQL_TXN_REPEATABLE_READ or SQL_TXN_SERIALIZABLE are solutions for this as this will prevent dirty read.

Which is the best option to use in our situation. I read from the net that SQL_TXN_SERIALIZABLE will slow down the database access.

If anybody has faced this problem in real time, could you share the solution.

Any thoughts/suggestions are well appreciated.

Thanks, Mathew Liju

Answer 1

You probably want to use SQL_TXN_READ_COMMITTED, not SQL_TXN_REPEATABLE_READ or SQL_TXN_SERIALIZABLE, as it offers better concurrency than the other two methods.

See the DB2 documentation on isolation levels, keeping in mind the following mapping:

   CLI Name                    DB2 Isolation Level
   ------------------------    -------------------
   SQL_TXN_READ_UNCOMMITTED    Uncommitted Read
   SQL_TXN_READ_COMMITTED      Cursor Stability
   SQL_TXN_REPEATABLE_READ     Read Stability
   SQL_TXN_SERIALIZABLE        Repeatable Read

Since it sounds like you're setting a flag when one of the workers starts working on a file, talk to your DBA to find out if the DB2_EVALUNCOMMITTED registry variable has been enabled, as this may also help prevent the two threads from waiting on each other.