Deadlock Delivery issues in Campaign Classic

[RRafiki]

Adobe Campaign Classic, servers on-prem 

We get daily deadlock errors. Our solution runs five workflows every 10 mins; we have two big batches of workflows that run at 10 am and 2 pm.  We get, on average, one deadlock failure a day at all times of the day - there is no pattern to what time the deadlock will occur.

ET Abandoned App T1

Workflow

01/10/2023 2:57:49 AM notification WKF-560044 Error while sending notification with delivery template 'PDM519752'. Please refer to the delivery log ID 232454346 for more information (object associated with the workflow task).

01/10/2023 2:57:49 AM notification DLV-490118 Error during preparation. Please refer to the delivery action journal '232454346' for more information.

Delivery

01/10/2023 2:57:49 AM DLV-490006 Error while adding 0 message(s) out of 1 targeted.

01/10/2023 2:57:49 AM XSV-350023 Unable to save document of type 'Delivery URL tracking (nms:trackingUrl)'.

01/10/2023 2:57:49 AM ODB-240000 ODBC error: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Transaction (Process ID 85) was deadlocked on lock | communication buffer resources with another process and has been chosen as the

deadlock victim. Rerun the transaction. SQLState: 40001

Appreciate the help/insight on this issue

[Shubham_Goyal__ · Accepted solution]

Hi @RRafiki ,

TBH, understanding a deadlock is always a tricky business.  

Deadlock usually occurs from the DB level so I wonder if the adobe campaign will have any role to play here.

From logs I see you have an MS SQL server and to remediate the deadlock situation your DBA needs to capture these deadlocks, if there is a pattern or common statement that is causing this deadlock then the priority of this statement can be set as 'Low' or at last change the Isolation level of your SQL server.

Following are some articles you can refer to with your DBA:

https://www.sqlshack.com/what-is-a-sql-server-deadlock/

https://blog.quest.com/the-anatomy-of-sql-server-deadlocks-and-the-best-ways-to-avoid-them/

Br,

Shubham

[RRafiki]

Thanks, Shubham

[akshaaga]

@RRafiki -

Based on the example you shared from one of the deadlocks, there might be multiple simultaneous updates running that's competing for the same resources (i.e. the one has a lock on what the other update requires, and vice versa).

Most of these deadlock occurrences would be from the same updates/or statements.

Deadlock most likely occurs because of multiple workflow servers running at the same time and trying to process the same records in the database at the same time.

To look at resolving the matter permanently, the statements that are running simultaneously will need to be looked at and maybe run at different intervals instead.

Have you consulted any DBA yet, and what are the findings?

[RRafiki]

Thanks 

[Sukrity_Wadhwa]

Hi @RRafiki,

Did the shared insights helped you in resolving your issue? In case they were, then kindly choose the one that helped you the most as the 'Correct Reply'.

Thanks!