[BPM 8.5.7]

I frequently encounter a deadlock in the LSW_USR_ATTR table. The problem once encountered affects the BPM portal which prevents users from login in order to take action on the tasks assigned to them.

Note: I have hidden the user ID value and replaced it by <user_id>

In the SystemOut file, I found the following error trace:

[8/30/20 14:26:15:114 AST] 0001dad7 UserOrgModule E CWLLG1162E: An exception occurred while setting the attribute Portal Mention Timestamp to value 2020-08-30T11:25:15.084Z for user <user_id>. Error: com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

[8/30/20 14:27:15:084 AST] 0001dad7 UserOrgModule E CWLLG1162E: An exception occurred while setting the attribute Portal Mention Timestamp to value 2020-08-30T11:25:15.084Z for user <user_id>. Error: com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

[8/30/20 14:46:21:040 AST] 0001d625 UserOrgModule E CWLLG1162E: An exception occurred while setting the attribute Portal Mention Timestamp to value 2020-08-30T11:45:04.167Z for user <user_id>. Error: com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update LSW_USR_ATTR set ATTR_VALUE = ?,ATTR_NUMBER_VALUE = ?,USER_ID = ?,USER_ATTR_DEF_VALUE_ID = ?,USER_ATTR_DEF_ID = ? where USER_ATTR_ID = ?]; nested exception is java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock

Hi aziz09 -

Most likely your BPM 8.5.7 is out-of-support, unless you obtained a service extension. That said, you don't really have the ability to seek a remedy on BPM 8.5.7

I'm actually not familiar with that problem occurring with any regularity on BPM 8.5.7, but we did see a couple issues in that area, in later releases. Unless you had a service extension, we wouldn't be able to do any troubleshooting/diagnosis even if you filed a support ticket.

Since BPM 8.6 will be out-of-service at the end of Sept, my suggestion would be to upgrade all the way to BAW 19.0.0.3

At that level, you would get the benefit of minimized LSW_LOCK issues (which is not your problem):

JR62095: UNNECESSARY LOCKING OCCURS DURING REST API CALLS

https://www.ibm.com/support/pages/apar/JR62095

You would also get the benefit of these other fixes in the user attribute area (which are available for BAW 18.0.0.2)

JR59724: YOU NOTICE HANGING THREADS WHEN CONCURRENTLY UPDATING A USER ATTRIBUTE

https://www.ibm.com/support/pages/apar/JR59724

JR60037 LOCK SITUATION CAN BE CAUSED UPON USER REPLICATION FROM PROCESS ADMIN CONSOLE

http://www-01.ibm.com/support/docview.wss?uid=swg1JR60037