Support Article
Problem with browse of System-Queue-FTSIncrementalIndexer
SA-86419
Summary
Multiple deadlocks occur when executing the sppr_sys_reservequeueitem_b stored procedure.
The user is running SQL Server 2014 (12.0.6293.0).
Error Messages
Problem with browse of System-Queue-FTSIncrementalIndexer][STACK][com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 1205 SQLState: 40001 Message: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<CR>DatabaseException caused by prior exception: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.<CR> | SQL Code: 1205 | SQL State: 40001<CR><CR><CR>From: (BGDXDUKBEKK5JBRUPRMXO0N5X83DIX3X6) <CR><CR> SQL: {call pega720_data.sppr_sys_reservequeueitem_b ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }<CR><CR><CR>Caused by SQL Problems.<CR><CR>Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment.
Since the Read_Committed_Snapshot is Off, the transactions may be performing dirty reads of uncommitted data. SQL statements should not read data that is modified or committed by other transactions.
Resolution
Perform the following local-change:
To prevent transactions from dirty reads, modify the the database (with the help of a Database Administrator (DBA)) as below:
ALTER DATABASE <pegadb> SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE <pegadb> SET ALLOW_SNAPSHOT_ISOLATION ON;
Published October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.