Concurrency Control Issue- "Optimistic Update Access Intent"

In one of our project we used EJB’s and It is not working when migrated from Websphere Server 5.1 to Websphere server 7.0.

It started throwing deadlock exceptions when multiple users clicks the save button at the same time.

This is happening because two sessions going to the same functions and the function is under Transaction Attribute “Required”, which means the method should be under transactional context.

If a calling method is under transactional context then this method follows the same transaction, if is not under transactional context than websphere container intiates a new transaction. Here the problem is not with the transactional attribute. The problem is with “Concurrency Control” Issue. Both sessions trying to update the same table then they are getting locks on the table and after updating the tables they have to load the case from the database. So both are trying to read the database and while reading they are locking the table’s again (which is not required to lock the table while reading—we wanted a dirty read). This is happening because by default websphere server uses “Pessimistic Update” Access Intent on all Entitiy Beans. Pessimistic Update locks the table even for reading also by issuing “SELECT…..FOR UPDATE” Statement.

Changing Access Intent from “Pessimistic Update” to “Optimistic Update” resolved the issue. In Optimistic Update it doesn’t lock on table for reading and it issues simple “SELECT” statement.

Finally the deadlock issue is Resolved. Finding the issue was tough than the solution.

Here goes log of Dead Lock stack Trace.

*** MODULE NAME:(JDBC Thin Client) 2010-06-24 13:09:17.861

*** SERVICE NAME:(SYS$USERS) 2010-06-24 13:09:17.861

*** SESSION ID:(901.1366) 2010-06-24 13:09:17.861

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00060012-0003e77d 475 901 X 401 2127 X

TX-0002000f-000521da 401 2127 X 475 901 X

session 901: DID 0001-01DB-00000026 session 2127: DID 0001-0191-0000001C

session 2127: DID 0001-0191-0000001C session 901: DID 0001-01DB-00000026

Rows waited on:

Session 2127: obj - rowid = 000139A5 - AAATmlAAQAAAacCAAf

(dictionary objn - 80293, file - 16, block - 108290, slot - 31)

Session 901: obj - rowid = 000139A5 - AAATmlAAQAAAbBDAA3

(dictionary objn - 80293, file - 16, block - 110659, slot - 55)

Information on the OTHER waiting sessions:

Session 2127:

pid=401 serial=1922 audsid=10835401 user: 7626/ASAPS_WEB

O/S info: user: wasadm, term: unknown, ospid: 1234, machine: surya7.dss.state.

va.us

program: JDBC Thin Client

application name: JDBC Thin Client, hash value=2546894660

Current SQL Statement:

SELECT T1.AS_UAI_QUESTION_ID, T1.QUESTION_TXT, T1.QUESTION_DESC, T1.UAI_LOCATOR, T1.QUESTION_ORDER, T1.LST_UPDT_ID, T1.LST_UPDT_DT FROM OASIS.TAS_UAI_QUESTI

ON T1 WHERE T1.AS_UAI_QUESTION_ID = :1 FOR UPDATE

End of information on OTHER waiting sessions.

Current SQL statement for this session:

SELECT T1.AS_UAI_QUESTION_ID, T1.QUESTION_TXT, T1.QUESTION_DESC, T1.UAI_LOCATOR, T1.QUESTION_ORDER, T1.LST_UPDT_ID, T1.LST_UPDT_DT FROM OASIS.TAS_UAI_QUESTION

T1 WHERE T1.AS_UAI_QUESTION_ID = :1 FOR UPDATE