DB2 Dirty Read

[karephul]

We have an outbound process which uses MDB (Message Driven Bean). Each MDB after completing updates the table

UPDATE PROCESS_RUN
SET COMPLETED = COMPLETED + 1
WHERE PROCESS_RUN_ID = ?

Logically, if there are 10 threads (MDB's) trying to update at the same time, DB2 should not allow dirty reads while updating and result should be fine. But, I have noticed that the COMPLETED batches is less that what I expect. Our DBA told us that DB2 allows dirty read so, used Select for update instead of UPDATE. Select for update have its own performance issues and I do not want to go with that (nether our DBA suggests that).

IS there any way, I can avoid dirty reads in DB2 ? Some property / suggestions?

I will appreciate you expeditious response.
Regards,

 

[Farab]

I'm not familiar with MDB, but had similar problems when the connection to DB2 is via JDBC. You need to ensure that the JDBC connection has a Transaction Isolation Level/Mode set to something like TRANSACTION_READ_COMMITTED, since the default is normally a setting that allows dirty reads.