Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

SQL query deadlock when pzPreGetChildKeys is run

SA-680

Summary



Performance issues and SQL query deadlock is observed when OOTB pzPreGetChildKeys is run.

Error Messages



2014-07-21 15:14:40,002 [ WebContainer : 0] [TABTHREAD6] [ you_accessgroup:01.03.01] (l.access.RDBPageResultPackager) ERROR |127.0.0.1 your_operator- There was a database problem when performing an RDBExecute
com.pega.pegarules.pub.database.DatabaseException: There was a database problem when performing an RDBExecute: code: 1205 SQLState: 40001 Message: Transaction (Process ID 249) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (HA776372521D794FBA8BEC775D51BEA0A:127.0.0.1)
SQL: select pxObjClass as "pxObjClass", pxCoverInsKey as "pxCoverInsKey", pzInsKey as "pzInsKey", pyStatusWork as "pyStatusWork" from pc_work where pxCoverInsKey = ? UNION ALL select pxObjClass as "pxObjClass", pxCoverInsKey as "pxCoverInsKey", pzInsKey as "pzInsKey", pyStatusWork as "pyStatusWork" from pc_Work_ProcessEmail where pxCoverInsKey = ? UNION ALL select pxObjClass as "pxObjClass", pxCoverInsKey as "pxCoverInsKey", pzInsKey as "pzInsKey", pyStatusWork as "pyStatusWork" from pc_work_P2 where pxCoverInsKey = ? UNION ALL select pxObjClass as "pxObjClass", pxCoverInsKey as "pxCoverInsKey", pzInsKey as "pzInsKey", pyStatusWork as "pyStatusWork" from pc_JH_Life_Calc_Work where pxCoverInsKey = ? UNION ALL select pxObjClass as "pxObjClass", pxCoverInsKey as "pxCoverInsKey", pzInsKey as "pzInsKey", pyStatusWork as "pyStatusWork" from PC_WORK_DSM_BATCH where pxCoverInsKey = ?
SQL Inserts: <ABC-XYZ-CLAIMS-WORK INV-52167> <ABC-XYZ-CLAIMS-WORK INV-52167> <ABC-XYZ-CLAIMS-WORK INV-52167> <ABC-XYZ-CLAIMS-WORK INV-52167> <ABC-XYZ-CLAIMS-WORK INV-52167>
Caused by SQL Problems.
Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 249) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:167)
at com.pega.pegarules.data.internal.access.RDBPageResultPackager.handleResultSet(RDBPageResultPackager.java:643)
at com.pega.pegarules.data.internal.access.RDBPageResultPackager.packageResults(RDBPageResultPackager.java:302)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6010)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6452)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6413)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6394)
at com.pegarules.generated.activity.ra_action_pzgetchildkeys_7d8e45a331a4ee7df7afbcd389722685.step1_circum0(ra_action_pzgetchildkeys_7d8e45a331a4ee7df7afbcd389722685.java:251)
at com.pegarules.generated.activity.ra_action_pzgetchildkeys_7d8e45a331a4ee7df7afbcd389722685.perform(ra_action_pzgetchildkeys_7d8e45a331a4ee7df7afbcd389722685.java:72)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3040)
at com.pegarules.generated.activity.ra_action_pyloadmycasesnested_423ea85cce92981cdc7829650bdb071c.step3_circum0Caused by:
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 249) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer$FetchBufferTokenHandler.onEOF(SQLServerResultSet.java:4783)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4849)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1062)
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:3120)
at com.pega.pegarules.data.internal.access.DatabaseResultSet.next(DatabaseResultSet.java:451)
at com.pega.pegarules.data.internal.access.ResultPageIteratorImpl.<init>(ResultPageIteratorImpl.java:357)
at com.pega.pegarules.data.internal.access.RDBPageResultPackager.handleResultSet(RDBPageResultPackager.java:550)
... 137 more

Steps to Reproduce



1. Create a parent case with child.
2. Open the parent case in review mode in Case Manager portal.


Root Cause



The root cause of this problem is a defect in Pegasystems’ code/rules.

The activtiy pzPreGetChildKeys makes a very big query which is causing performance impact in users system. The query generated is having many UNION clauses on many tables which are actually not needed.

The expectation is that the query should run only on the immediate application or the BUILT on applications when trying to fetch the case details. However, the query is run on all the Implementations and Frameworks which this PRPC system has.
The query is generated in the activity pzPreGetChildKeys. It is run on all the tables that are derived from Work-.
 


Resolution



Apply HFix-9892.

Published November 24, 2015 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us