DB Deadlocks in WEM

Hello, in WEM we are using our custom approval/publishing Workflow, which uses out-of-the-box WEm activity "Approve" within it. We have been seeing DB Deadlocks around that activity for a long time (7-8 years) but after we upgraded from WEM 8.1 to 10.5 about two months ago (in late September 2015) they have increased 2-3 fold. We also see Deadlock error message popup in UI when users save content.

When the error happens in the workflow we need to rerun the it in order to republish content. We get workflow escalation emails like this:

Error in activity "Approve" (id=7b51462e584ff67c00000150a256567ef35a) in process "CCG Approve and Publish Workflow - 2015.10.26 14:42:12.Publish" (id=7b51462e584ff67c00000150a256567ef337).

The exception has the following:

at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256) at weblogic.work.ExecuteThread.run(ExecuteThread.java:221) Caused by: com.vignette.as.client.exception.ApplicationException: 10/26/2015 14:42:26:936, Error 25 (DBError), Message 026-150-0106: Error during write of ManagedObject data: [Vignette Corp.][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 418) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at com.vignette.as.client.exception.ApplicationException.getOne(ApplicationException.java:169) at com.vignette.as.server.persist.ManagedObjectPersistor.throwSQLExceptionWrite(ManagedObjectPersistor.java:3606) at com.vignette.as.server.persist.ManagedObjectPersistor.updateObjectData(ManagedObjectPersistor.java:643) at com.vignette.as.server.persist.ManagedObjectPersistor.commitImpl(ManagedObjectPersistor.java:329) at com.vignette.as.server.persist.ManagedObjectPersistor.fullCommit(ManagedObjectPersistor.java:260) at com.vignette.as.server.persist.ManagedObjectPersistor.commit(ManagedObjectPersistor.java:155) at

We also noticed that it happens on content types which use Relator-type widgets, which thus use other tables. For example one of our CTD's called "CCG Procedure" has Related Items relationship as well as Sections one. They both use their own tables.

We are seeing that when "commit" in "Approve" task happens it seems that WEM is trying to delete those items before adding them back:

executeUpdate [delete from WF_CCG_PROCEDURE_SECTION where (id=? and procedure_id=? )], PARAM[1=5e327fc2a8bba410VgnVCM100000dbe95b0a____ 2=fc3b43ad671a7410VgnVCM100000dbe95b0a____]

Looks like this creates a lot of load and hence deadlocks.

We are also seeing timeout-type exceptions, which look to be related to this.

Does anyone have experience with those?

Thank you,

Vlad

Find more posts tagged with

Comments

Joseph_Mares

Hi Vlad,

I think the best way forward would be to raise a support ticket, if that has not already been done.

I noticed that there was an older ticket dating back to May 2013 dealing with the same issue which was not followed up further due to an environment refresh.

The issue you are experiencing as documented here would require more investigative work considering the past of this issue in your environment.

Regards

Jos Mares

Vladislav_Polonskiy

After over a month of fighting it looks like we were able to resolve the issue. In DB (SQL Server 2008) we converted fields for our CTD attributes from "varchar" to "nvarchar", which sped up transaction speeds from 100ms to less than 1ms and that eliminated Deadlocks. Because of holiday week the load is at about 40% so we'll see what happens under regular load next week but the hopes are high.