Follow

Troubleshooting deadlocks in Execution

Symptoms

You receive this error when executing your project or package:

Transaction (Process ID nn) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Quick Troubleshooting

1. Are you running packages on concurrent schedules ? i.e. more than one packages running at the same time, attempting to work on the same object?

2. Right-click on project name => Performance Recommendations => Find (look for any 'index fix' suggestions)

Cause

Deadlocks happen when SQL server is attempting to execute Table1 and Table2, however, Table1 is waiting on Table2 to finish and Table2 is waiting on Table 1.  So essentially, it is an issue with dependencies. While TimeXtender typically handles dependencies automatically, there are some scenarios with custom data where this may not happen.  For example, using custom views or custom scripts where the software is unable to trace the lineage.

Resolution

There are a few ways you can resolve the above scenario:

  1. Address the Symptom: Enable retries on the Execution Package. This will simply retry the deadlocked table and should succeed. 
  2. Address the Cause: Configure dependencies on the table (right-click table > Advanced > Object Dependencies). Select the tables that should be executed prior to the selected table. This will ensure this table never executes simultaneously with the deadlock object. 

mceclip0.png

Find Unused Fields

mceclip1.png

If a field listed as “unused” is actually being used (through a custom script/view etc.), consider making a custom object dependency to explicitly mark its use.

Use the “Object Dependencies” feature to define an order between the tables.  Check to see if this resolves the error.

Is there a Circular Reference?

In older versions of the TimeXtender, circular reference detection wouldn't work properly in some circumstances.  This would allow a careless user to make bad circular references; it also allowed careful users to make functioning circular references. 

In newer versions, circular reference detection works properly, and will not allow you to deploy any object that contain such a reference.  This means that certain objects may not deploy properly after upgrading.

Look for Circular References in Relations and lookups, when upgrading from older versions Troubleshooting Upgrade Issues

Views and parameters

Check to see if the tables getting deadlocked are based on views and those views have not been parametrized.  Due to this, they have also not been mapped to their source tables.

 Parameter Guide

mceclip2.png

When you have set up parameters, you can map the tables in the above menu. This will make the view part of the linage, avoiding simultaneous executions.

mceclip3.png

In general, make sure everything is related and is known by the object which uses it.

References

Troubleshooting concurrency issues in ODX data sources

Execution Details SQL Query - The SQL script in this article queries meta-data in repository to return detailed execution information.

Was this article helpful?
0 out of 0 found this helpful

0 Comments

Article is closed for comments.