We are using EJB with stateless session beans and entity beans (Bean Managed Persistence)
in IBM Websphere application server with the database as DB2.



We are facing the following problem as a result of concurrent transactions.



Following is the detailed information about the same:

1. Platform : IBM WebSphere 5.0, IBM DB2 7.1

2. Architecture/Technology Used : MVC Architecture with Stateless Session Beans and Bean Managed Persistence (BMP) Entity Beans.

3. Problem Description :
When two transactions try to read and update a record in the database CONCURRENTLY, the updates due to one transaction are being overwritten by the other. As both the transactions are concurrent, both read the same data, modify it separately and then update the same in the database.

For e.g.:

If transaction T1 and T2, both read the same value (lets say 100) from the same record of the database.

1. T1 adds 100 to the original value and commits it i.e. the new value in the database now becomes 200.
2. Similarly, T2 adds 200 to the value which it read and makes it to 100 + 200 = 300 and commits it to database.

So, the final value of the Qty in database is 300, whereas it should have been 400 (200 + 200).

Please note that both these are exactly concurrent transactions with a difference of only some milliseconds.



Solutions already tried out:



I). Adding a FOR UPDATE clause while loading the data through ejbLoad( ) method, while loading the data.
- NOT WORKING.


II). Changing the Isolation Level of entity bean that manages the update of the above table, to SERIALIZABLE (the strictest isolation level, which should ideally lock the record and not allow any other transaction, even to read the data). - NOT WORKING.


III). Changing the Resource Reference Isolation Level to TRANSACTION_SERIALIZABLE from TRANSACTION_READ_COMMITTED. This property is present under the References tab of the Deployment Descriptor file for the above entity bean in WSAD 5.0. - NOT WORKING.


IV). Changing the WebSphere Access Intent for 2.x entities to "wsPessimisticUpdate-Exclusive", which therotically is supposed to exclusively lock the record and not allow for any access to the record even for reading. This property is present under the "Access / Access Intent for Entities 2.x" tab of the Deployment Descriptor file for the above entity bean in WSAD 5.0. - NOT WORKING.


I feel that this is a typical problem which should be handled by EJB.


If you have any suggestions or any information as to how this problem can be resolved, then please let us know at the earliest.