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:
- Address the Symptom: Enable retries on the Execution Package. This will simply retry the deadlocked table and should succeed.
- 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.
Find Unused Fields
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.
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.
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.
0 Comments