Bug #5296
[SCP ID :##2655##] : Urgent : Deadlock db M2U Production
| Status: | Closed - End of life cycle | Start date: | April 29, 2015 | |
|---|---|---|---|---|
| Priority: | Normal | Due date: | ||
| Assignee: |  Zahir Abd Latif | % Done: | 100% | |
| Category: | - | Spent time: | 3.00 hours | |
| Target version: | - |
Description
Hi,
Kindly attend below request:
Need your help to check there's a case for db deadlock at 28 april 2015, and this happen almost everyday.
the case is happen in this url : /ib101/ibLoginSecurityQuestion.do.
Node Details
State: Error Detected: Database Exceptions RIB
Method: executeUpdate()
Class: com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement
Argument: update dbo.IB_USER set ACCOUNT_ID=?, USER_ALIAS=?, CHANNEL_ID=?, PASSWORD_ERROR_COUNT=?, USER_STATE=?, LAST_LOGIN_DATE=?, MODIF_TIME=?, CREATION_DATE=?, LAST_CHANGE_PASSWORD_DATE=?, TAC_TRNX_ID=?, TAC_GENERATED_DATE=? where USER_ID=?
Return: [com.ibm.db2.jcc.am.SqlTransactionRollbackException: The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.63.123
at com.ibm.db2.jcc.am.fd.a(fd.java:666) at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.ho.c(ho.java:2644) at com.ibm.db2.jcc.t4.cb.t(cb.java:982)
at com.ibm.db2.jcc.t4.cb.l(cb.java:405) at com.ibm.db2.jcc.t4.cb.a(cb.java:62)
at com.ibm.db2.jcc.t4.q.a(q.java:50)
at com.ibm.db2.jcc.t4.tb.b(tb.java:220) at com.ibm.db2.jcc.am.io.lc(io.java:3318)
at com.ibm.db2.jcc.am.io.b(io.java:4275)
at com.ibm.db2.jcc.am.io.dc(io.java:759) at com.ibm.db2.jcc.am.io.executeUpdate(io.java:742)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1185)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:802)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.ja
Agent: M2U_Application_Server[DCAPPM2U01Cell01-member01]@dcappm2u01:17563766
API: JDBC
Thread Name: WebContainer : 17 <-703016724>
Elapsed Time [ms]: 2906.05
History
#1
Updated by Zahir Abd Latif over 9 years ago
- Status changed from New - Begin Life Cycle to Development / Work In Progress
Dear Gabby,
Kindly assist to update the current status in Redmine.
Thanks.
#2
Updated by Rayvandy Gabbytian over 9 years ago
- Tracker changed from Support to Bug
- Status changed from Development / Work In Progress to Pending Customer Feedback
- Assignee changed from Rayvandy Gabbytian to Zahir Abd Latif
Dear Zahir and all,
Based on the email informed to BII IT team and the rest. We have provided the fix at 29th April 2015. Kindly proceed to close this case.
Thank you.
Trace email to BII IT Team as follows:
Dear Pak Wahyu,
We have done some tweaking at services that handles RIB Authentication.
Kindly take note that during user login, system will always update certain user information such as:
- last login date.and certain info occasionally such as:
- number of password error count (incase of wrong login).
- user status (locked, active, dormant, etc).
- reset user status to normal, reset number of password error counter, set last change password date (upon change password & force change password during login).Currently, these activity involves at least 2 tables that store user authentication related information and user profile information.
Also there is another process called update user DOB that runs every minute in websphere. This may lock the 'user profile table' during update process.
Since the size of info held by ‘user profile table' is rather big; also the number of user may increase along the time, plus the frequency of user login increases during certain hour, it may cause lock between one process and another; affecting the same table.Thus, what we could do for now is by eliminating the process of 'user profile’ update during login, as there is no data change involved for this table during above activities. So that the db deadlock possibility can be reduced. The tweak has been installed at this URL: http://10.226.16.74:9080/bii-rib-sit/common/Login.do.
#3
Updated by Zahir Abd Latif over 9 years ago
- Status changed from Pending Customer Feedback to Closed - End of life cycle
- % Done changed from 0 to 100
Issue already closed in SCP.