Symptoms
You receive this error when executing your Prepare Instance or execution 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 package running at the same time, attempting to work on the same object?
2. Right-click on the Prepare instance Advanced > Performance Recommendations > Find (look for any 'index fix' suggestions)
Cause
Deadlocks happen when the 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.
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 contains 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 if 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 lineage, avoiding simultaneous executions.
In general, make sure everything is related and is known by the object which uses it.
