Question
Virtusa
AU
Last activity: 8 Feb 2017 9:48 EST
Getting deadlock on lock resources
Hi All,
Did anyone face this kind of issue before? We are seeing this kind of exceptions in the logs. We got around 100 - 150 exceptions at once. Below is the exceptions details -
Hi All,
Did anyone face this kind of issue before? We are seeing this kind of exceptions in the logs. We got around 100 - 150 exceptions at once. Below is the exceptions details -
2017-01-05 17:00:25,976 [ PegaRULES-Batch-1] [ STANDARD] [ ] [ PegaRULES:07.10] ( internal.access.DatabaseImpl) ERROR - Encountered problem when performing a Commit
com.pega.pegarules.pub.database.DatabaseException: Database-General Problem saving instance of class Log-RuleUsage-Details 1205 40001 Transaction (Process ID 657) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (B50848D1FD1BB898255C817FB6DDE1DDD)
SQL: MERGE INTO dbo.pr4_log_rule_usage_details t USING (VALUES (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)) AS inputs ("pzInsKey" , "pxAssemblyCacheKey" , "pxClassName" , "pxCreateDateTime" , "pxDetailIndex" , "pxInsName" , "pxObjClass" , "pxParentRuleUsageKey" , "pxSystemNode" , "pxUpdateDateTime" , "pxUseCount" , "pyLabel") ON (t.pzInsKey = inputs."pzInsKey") WHEN MATCHED THEN UPDATE SET pxAssemblyCacheKey = inputs."pxAssemblyCacheKey" , pxClassName = inputs."pxClassName" , pxCreateDateTime = inputs."pxCreateDateTime" , pxDetailIndex = inputs."pxDetailIndex" , pxInsName = inputs."pxInsName" , pxObjClass = inputs."pxObjClass" , pxParentRuleUsageKey = inputs."pxParentRuleUsageKey" , pxSystemNode = inputs."pxSystemNode" , pxUpdateDateTime = inputs."pxUpdateDateTime" , pxUseCount = inputs."pxUseCount" , pyLabel = inputs."pyLabel" , pxCommitDateTime = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (pzInsKey , pxCommitDateTime , pxAssemblyCacheKey , pxClassName , pxCreateDateTime , pxDetailIndex , pxInsName , pxObjClass , pxParentRuleUsageKey , pxSystemNode , pxUpdateDateTime , pxUseCount , pyLabel) VALUES (inputs."pzInsKey" , CURRENT_TIMESTAMP , inputs."pxAssemblyCacheKey" , inputs."pxClassName" , inputs."pxCreateDateTime" , inputs."pxDetailIndex" , inputs."pxInsName" , inputs."pxObjClass" , inputs."pxParentRuleUsageKey" , inputs."pxSystemNode" , inputs."pxUpdateDateTime" , inputs."pxUseCount" , inputs."pyLabel");
SQL Inserts: <LOG-RULEUSAGE-DETAILS E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT!0> <RULE-HTML-PARAGRAPH|EMBED-CONTROL-MODE-ACTIONSETS!PZACTIONSETLABEL;STREAM|IMS:Administrators!Prod!01.10.01$1830589c08e58ed6372d90c24c853b3b> <com.pegarules.generated.html_paragraph.ra_stream_pzactionsetlabel_cf98acc33973c9b6016e2bb3d324eaa6> <2017-01-05 17:00:03.375> <0> <E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT!0> <Log-RuleUsage-Details> <LOG-RULEUSAGE E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT> <e23452a54252f8de4b878b91aa81bd79> <2017-01-05 17:00:03.375> <3> <recent>
Caused by SQL Problems.
Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 657) 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:257)
at com.pega.pegarules.data.internal.access.Saver.addBatch(Saver.java:980)
at com.pega.pegarules.data.internal.access.Saver.saveInstance(Saver.java:796)
at com.pega.pegarules.data.internal.access.Committer.performOps(Committer.java:236)
at com.pega.pegarules.data.internal.access.DatabaseImpl.attemptToProcessUpdates(DatabaseImpl.java:2625)
at com.pega.pegarules.data.internal.access.DatabaseImpl.processUpdates(DatabaseImpl.java:2288)
at com.pega.pegarules.data.internal.access.DatabaseImpl.commit(DatabaseImpl.java:2027)
at com.pega.pegarules.monitor.internal.context.RuleUsageImpl.updateSnapshot(RuleUsageImpl.java:339)
at com.pega.pegarules.session.internal.mgmt.Executable.updateRuleUsageSnapshot(Executable.java:7867)
at com.pegarules.generated.activity.ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.step1_circum0(ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.java:177)
at com.pegarules.generated.activity.ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.perform(ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.java:69)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:719)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:600)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:885)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1177)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:926)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:825)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:618)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 657) was deadlocked on lock 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.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.executeUpdate(DatabasePreparedStatementImpl.java:471)
at com.pega.pegarules.data.internal.access.Saver.addBatch(Saver.java:956)
... 20 more
***Updated by moderator: Lochan to update Categories; added SR Details***
Accepted Solution
Pegasystems Inc.
IN
Adding resolution note of the SR for reference.
RESOLUTION
Here’s the explanation for the reported behavior:
- The SQLException looks to be very sporadic and observed only once in production.
- At the moment, user don’t have enough deadlock data captured at DB level to figure out the causes.
- As we have observed few known issues in these lines, we have couple of recommendations depending on the deadlock cause.
- If we detect any truncate queries on the same table from the deadlock data – We can try HFix-28369 which will prevent concurrent execution of systemcleaner agent on multiple nodes.
- When it’s not systemcleaner activity, we can try the below DSS settings to overcome the deadlock which usually occur due to the usage MERGE statement with batch updates using MSSQL server.
Owing Ruleset : Pega-Engine
Setting purpose : prconfig/classmap/usemergestatement/default
Value : false
Owing Ruleset : Pega-Engine
Setting purpose : prconfig/ database/batchupdates/default
Value : -1
- Considering the sporadic nature of the issue, user will wait for the issue to reoccur so as to capture write data. This will help us to take a call on which recommendation to apply.
Pegasystems Inc.
IN
Hi Satya,
Which is the PRPC version you are using?
Similar issue I have seen in 7.2 case, but it was for delete operation when the system cleaner agent is enabled on all the node. Refer :https://community.pega.com/support/support-articles/deadlock-table-pr4logruleusagedetails
Here I could see merge action is getting executed. We need to analyse it with full stack trace of pegarules and alert log.
Virtusa
AU
Thank you for response othos..
We are using Pega 7.1.7
This is the exception I have in the logs -
Thank you for response othos..
We are using Pega 7.1.7
This is the exception I have in the logs -
2017-01-05 17:00:25,977 [ PegaRULES-Batch-1] [ STANDARD] [ ] [ PegaRULES:07.10] (internal.context.RuleUsageImpl) ERROR - Error committing RuleUsage snapshot recent
com.pega.pegarules.pub.database.DatabaseException: Database-General Problem saving instance of class Log-RuleUsage-Details 1205 40001 Transaction (Process ID 657) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (B50848D1FD1BB898255C817FB6DDE1DDD)
SQL: MERGE INTO dbo.pr4_log_rule_usage_details t USING (VALUES (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)) AS inputs ("pzInsKey" , "pxAssemblyCacheKey" , "pxClassName" , "pxCreateDateTime" , "pxDetailIndex" , "pxInsName" , "pxObjClass" , "pxParentRuleUsageKey" , "pxSystemNode" , "pxUpdateDateTime" , "pxUseCount" , "pyLabel") ON (t.pzInsKey = inputs."pzInsKey") WHEN MATCHED THEN UPDATE SET pxAssemblyCacheKey = inputs."pxAssemblyCacheKey" , pxClassName = inputs."pxClassName" , pxCreateDateTime = inputs."pxCreateDateTime" , pxDetailIndex = inputs."pxDetailIndex" , pxInsName = inputs."pxInsName" , pxObjClass = inputs."pxObjClass" , pxParentRuleUsageKey = inputs."pxParentRuleUsageKey" , pxSystemNode = inputs."pxSystemNode" , pxUpdateDateTime = inputs."pxUpdateDateTime" , pxUseCount = inputs."pxUseCount" , pyLabel = inputs."pyLabel" , pxCommitDateTime = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (pzInsKey , pxCommitDateTime , pxAssemblyCacheKey , pxClassName , pxCreateDateTime , pxDetailIndex , pxInsName , pxObjClass , pxParentRuleUsageKey , pxSystemNode , pxUpdateDateTime , pxUseCount , pyLabel) VALUES (inputs."pzInsKey" , CURRENT_TIMESTAMP , inputs."pxAssemblyCacheKey" , inputs."pxClassName" , inputs."pxCreateDateTime" , inputs."pxDetailIndex" , inputs."pxInsName" , inputs."pxObjClass" , inputs."pxParentRuleUsageKey" , inputs."pxSystemNode" , inputs."pxUpdateDateTime" , inputs."pxUseCount" , inputs."pyLabel");
SQL Inserts: <LOG-RULEUSAGE-DETAILS E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT!0> <RULE-HTML-PARAGRAPH|EMBED-CONTROL-MODE-ACTIONSETS!PZACTIONSETLABEL;STREAM|IMS:Administrators!Prod!01.10.01$1830589c08e58ed6372d90c24c853b3b> <com.pegarules.generated.html_paragraph.ra_stream_pzactionsetlabel_cf98acc33973c9b6016e2bb3d324eaa6> <2017-01-05 17:00:03.375> <0> <E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT!0> <Log-RuleUsage-Details> <LOG-RULEUSAGE E23452A54252F8DE4B878B91AA81BD79!COM.PEGARULES.GENERATED.HTML_PARAGRAPH.RA_STREAM_PZACTIONSETLABEL_CF98ACC33973C9B6016E2BB3D324EAA6!RECENT> <e23452a54252f8de4b878b91aa81bd79> <2017-01-05 17:00:03.375> <3> <recent>
Caused by SQL Problems.
Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 657) 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:257)
at com.pega.pegarules.data.internal.access.Saver.addBatch(Saver.java:980)
at com.pega.pegarules.data.internal.access.Saver.saveInstance(Saver.java:796)
at com.pega.pegarules.data.internal.access.Committer.performOps(Committer.java:236)
at com.pega.pegarules.data.internal.access.DatabaseImpl.attemptToProcessUpdates(DatabaseImpl.java:2625)
at com.pega.pegarules.data.internal.access.DatabaseImpl.processUpdates(DatabaseImpl.java:2288)
at com.pega.pegarules.data.internal.access.DatabaseImpl.commit(DatabaseImpl.java:2027)
at com.pega.pegarules.monitor.internal.context.RuleUsageImpl.updateSnapshot(RuleUsageImpl.java:339)
at com.pega.pegarules.session.internal.mgmt.Executable.updateRuleUsageSnapshot(Executable.java:7867)
at com.pegarules.generated.activity.ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.step1_circum0(ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.java:177)
at com.pegarules.generated.activity.ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.perform(ra_action_ruleusagesnapshot_8acf7559930ab416e0609201cc19c6af.java:69)
at com.pega.pegarules.session.internal.mgmt.Executable.doActivity(Executable.java:3375)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.runActivity(QueueProcessor.java:719)
at com.pega.pegarules.session.internal.async.agent.QueueProcessor.execute(QueueProcessor.java:600)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:885)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.performTargetActionWithLock(PRSessionProviderImpl.java:1177)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:926)
at com.pega.pegarules.session.internal.PRSessionProviderImpl.doWithRequestorLocked(PRSessionProviderImpl.java:825)
at com.pega.pegarules.session.internal.async.BatchRequestorTask.run(BatchRequestorTask.java:618)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:991)
at com.pega.jsr166backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:532)
at java.lang.Thread.run(Thread.java:744)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 657) was deadlocked on lock 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.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.executeUpdate(DatabasePreparedStatementImpl.java:471)
at com.pega.pegarules.data.internal.access.Saver.addBatch(Saver.java:956)
... 20 more
Cheers,
Satya
Pegasystems Inc.
IN
Hi Satya,
We need to analyse this in detail.Please raise an SR with pegarules and alert.
Quick analyse tips on this:
- Firstly figure out the objects/code parts involved in the deadlock
- Go through the execution plan to identify the areas which are consuming more time and importantly resources
- Check the sequence in which the objects being accessed across the processes involved in the deadlock scenario. They should be accessed in the same order
- Create indexes wherever necessary
- Make sure that queries are optimally written so that the performance is at the best
Regards,
Sudhish OP
Pegasystems Inc.
IN
Hello!
Thank you for updating the SR number here! I have tagged your post above with this information. I have also added a pulse post on the SR, thus connecting the two for effective troubleshooting.
Regards,
Lochan | Community Moderator | Pegasystems Inc.
Accepted Solution
Pegasystems Inc.
IN
Adding resolution note of the SR for reference.
RESOLUTION
Here’s the explanation for the reported behavior:
- The SQLException looks to be very sporadic and observed only once in production.
- At the moment, user don’t have enough deadlock data captured at DB level to figure out the causes.
- As we have observed few known issues in these lines, we have couple of recommendations depending on the deadlock cause.
- If we detect any truncate queries on the same table from the deadlock data – We can try HFix-28369 which will prevent concurrent execution of systemcleaner agent on multiple nodes.
- When it’s not systemcleaner activity, we can try the below DSS settings to overcome the deadlock which usually occur due to the usage MERGE statement with batch updates using MSSQL server.
Owing Ruleset : Pega-Engine
Setting purpose : prconfig/classmap/usemergestatement/default
Value : false
Owing Ruleset : Pega-Engine
Setting purpose : prconfig/ database/batchupdates/default
Value : -1
- Considering the sporadic nature of the issue, user will wait for the issue to reoccur so as to capture write data. This will help us to take a call on which recommendation to apply.