Keep seeing deadlock on Log-RuleUsage-Details - why and what is the impact
Hi there,
In production logs, I keep seeing the error with the updating rules usage.... Can we do anything about this? e.g. Index needed? and what is the impact for the client?
Hi there,
In production logs, I keep seeing the error with the updating rules usage.... Can we do anything about this? e.g. Index needed? and what is the impact for the client?
2016-04-20 15:00:28,744 [ PegaRULES-Batch-3] [ 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 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. From: (BD9BB6F9DA5D71D538416E9F46A42EA18) 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: Caused by SQL Problems. Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 95) 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)
Regards
Seri
Message was edited by: Lochan to add Category
Accepted Solution
Pegasystems Inc.
AU
Hi,
Deadlock happens, and it could due to the DB performance (slow IOPs/disk?) or missing indexes. So I would take the SQL to the DBA and see if this the case.
Cheers
Seri
Pegasystems Inc.
US
Hi Seri,
- Can you paste the complete stack trace?
- Can the DBA look for a trace file generated at the deadlock timestamp? We have one SQL that was rolled back, the trace file will show SQL statements from both sessions that will help us analyze the deadlock
Pradeep
Pegasystems Inc.
AU
- Can you paste the complete stack trace?
2016-04-20 15:00:28,744 [ PegaRULES-Batch-3] [ 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 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (BD9BB6F9DA5D71D538416E9F46A42EA18)
- Can you paste the complete stack trace?
2016-04-20 15:00:28,744 [ PegaRULES-Batch-3] [ 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 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
From: (BD9BB6F9DA5D71D538416E9F46A42EA18)
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 25CA48D441C84CD71FE41E738FC9C286!COM.PEGARULES.GENERATED.FLOWACTION.RA_STREAM_SEARCHFORFAULTS_C5A6801751FEC9E8B73FB8D579921BC2!RECENT!0> <RULE-OBJ-FLOWACTION|TNSW-FW-FAULTMGMTFW-WORK!SEARCHFORFAULTS;STREAM|FMS:TMCUsers!Prod!01.11.01$8e84dbfa7f4d86bac8ee68e3c9ce70ad> <com.pegarules.generated.flowaction.ra_stream_searchforfaults_c5a6801751fec9e8b73fb8d579921bc2> <2016-04-20 15:00:13.462> <0> <25CA48D441C84CD71FE41E738FC9C286!COM.PEGARULES.GENERATED.FLOWACTION.RA_STREAM_SEARCHFORFAULTS_C5A6801751FEC9E8B73FB8D579921BC2!RECENT!0> <Log-RuleUsage-Details> <LOG-RULEUSAGE 25CA48D441C84CD71FE41E738FC9C286!COM.PEGARULES.GENERATED.FLOWACTION.RA_STREAM_SEARCHFORFAULTS_C5A6801751FEC9E8B73FB8D579921BC2!RECENT> <25ca48d441c84cd71fe41e738fc9c286> <2016-04-20 15:00:13.462> <2> <recent>
Caused by SQL Problems.
Problem #1, SQLState 40001, Error code 1205: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 95) 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 95) 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
Accepted Solution
Pegasystems Inc.
AU
Hi,
Deadlock happens, and it could due to the DB performance (slow IOPs/disk?) or missing indexes. So I would take the SQL to the DBA and see if this the case.
Cheers
Seri