Ever since DB2 was introduced we have been fighting against deadlocks and timeouts An important step in the right direction was taken when TYPE 2 indexes became available in DB2 version 4. There still exist many situations where deadlocks or timeouts occur in the applications using DB2. In this tip I will give a small contribution to the elimination of timeouts when performing INSERTs on tables with unique indexes.

The tip is only valid if two or more programs simultaniously tries to insert a row with the same unique row on the same table. This of course limits the situations where this tip may be relevant. I have used it to remove a very irritating timeout in a parallel execution of the same program where it turned out that two of the parallel executing jobs tried to insert the same row in the same table. One job had to wait while the other job continued to execute without committing for a longer period than the timeout limit. Normal solution to this kind of problem is to raise the timeout frequency, but skewed data and elapse time requirements made this solution inefficient.

I came up with the following idea:

SELECT key_value
INTO :host-key-value
FROM problem_table
WHERE key_value = :host-key-value
WITH UR
;
IF SQLCODE = 100
THEN
  INSERT INTO problem_table ...

I simply coded a SELECT before the INSERT using a "dirty read" to check the existance of a row with the same unique key on the table. The trick is that WITH UR makes it possible for DB2 to see uncommited data. The program was now able to discover whether it would end in a timeout situation or not. The good thing about this solution is that the program will not wait at all, but the bad thing is that extra DB2 resources are needed for the in normal situations redundant SELECT statement. There is still a tiny risk of a timeout if the parallel executing program gets control and performs the INSERT during the IF statement.

You may also end up i a situation where the program executing the INSERT performs a ROLLBACK while the other parallel executing program thinks that the row is inserted. In this situation it may be very bad that we assume that the row was inserted. Therefore you must carefully consider using this tip.