SA-40961

Summary

Deadlock exceptions on "pr_sys_queue_ftsindexer" table.

Error Messages

2017-05-12 02:30:19,826 [l-threads-683576153)] [ STANDARD] [ ] [ CLMS:01.01.01] ( async.agent.QueueManagerImpl) ERROR - Problem with browse of System-Queue-FTSIncrementalIndexer
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 1205 SQLState: 40001 Message: Transaction (Process ID 140) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (B5853577FDC077B2CAE6DF639376D7B5D)
SQL: {call PEGA_DATA.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
SQL Inserts: <PEGA_DATA.pr_sys_queue_ftsindexer> <System-Queue-FTSIncrementalIndexer> <Pega-SearchEngine:FTSIncrementalIndexer> <Scheduled> <aa482fc2cdeb37af075f9879cd121cb9> <> <20170512T073019.482 GMT> <true> <> <(OUTPUT PARAMETER)>

Caused by SQL Problems.
Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 140) was deadlocked on lock 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:262)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6283)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6676)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6632)

Steps to Reproduce

Not applicable

Root Cause

pxCreateDateTime filter is present in the stored proc 'sppr_sys_reservequeueitem_b.sql'.

Resolution

Perform the following local change. Use the updated stored procedure sppr_sys_reservequeueitem_b.sql, with changes to remove pxCreateDateTime filter in the stored proc.