Support Article

Declare index is causing concurrency issue

SA-37075

Summary



Declare index is having concurrency issue which is resulting in deadlocks in production system and index table is not getting updated.

Index table is still showing the old data. Causing poor performance and system slowness.

Most of time system Hangs.


Error Messages



2017-04-21 10:54:xxx [10.138.xxx:8444-9] [TABTHREAD1] [ ] [ iTrac:01.01.01] (WithErrorHandling.Work_.Action) FATAL itgsydsrv1009-vip4.macbank|adiaz3 - .pyCommitError: ** Problem clearing out old indexes for instance ENTMG-ITRAC-WORK-CASE-EXCEPTION EXE-703700, purpose ITracExceptionDetails: code: 1205 SQLState: 40001 Message: Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2017-04-21 10:5 INFO [stdout] (Dispatcher-Thread-93) 2017-04-21 10:54:52,721 [10.138xxx:8444-9] [TABTHREAD1] [ ] [ iTrac:01.01.01] ( internal.access.DatabaseImpl) ERROR itgsydsrv1009-vip4.macbank|10.xxx adiaz3 - Encountered problem when performing a Commit
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93) com.pega.pegarules.pub.database.DatabaseException: Database-General Problem clearing out old indexes for instance ENTMG-ITRAC-WORK-CASE-EXCEPTION EXE-703704, purpose ITracExceptionDetails 1205 40001 Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93) DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93) | SQL Code: 1205 | SQL State: 40001
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93)
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93) From: (H0817B750B0019C54C43C8F1CD3558A92:10.xx
2017-04-21 10:54:52,723 INFO [stdout] (Dispatcher-Thread-93) SQL: delete from data.pr_Index_iTrac_Exception where pxInsIndexedKey = ? and pxIndexPurpose = ?


Steps to Reproduce



1. Create a declare index.
2. Update source class properties.


Root Cause



Root cause is explained in the PDN link:

https://pdn.pega.com/support-articles/system-slow-all-users-database-deadlock-errors-logs

Resolution



The explanation for the reported behavior explained in bellow PDN link:

https://pdn.pega.com/support-articles/system-slow-all-users-database-deadlock-errors-logs


Published April 26, 2017 - Updated May 14, 2017

Have a question? Get answers now.

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