Skip to main content
To homepage To homepage

I have been running into a problem where my execution package keeps failing due to a deadlock between an index rebuild and a select statement on sys.tables. 

TimeXtender is running both process' and one eventually gets killed and fails the entire job. 

in subsequent executions it has always failed on an index rebuild, but not the same index. 

Has anyone run into this issue or have any solutions? 

here's the query.

SELECT

SCHEMA_NAME(tbl.schema_id) AS  Schema],

tbl.name AS  Name],

tbl.object_id AS  ID],

CAST(

case

    when tbl.is_ms_shipped = 1 then 1

    when (

        select

            major_id

        from

            sys.extended_properties

        where

            major_id = tbl.object_id and

            minor_id = 0 and

            class = 1 and

            name = N'microsoft_database_tools_support')

        is not null then 1

    else 0

end         

             AS bit) AS ÂIsSystemObject]

FROM

sys.tables AS tbl

ORDER BY

 Schema] ASC,ÂName] ASC

 

Hi Josiah, 

Can you provide some more information about this issue:

  • Where is the error occurring; Business Unit, ODX, Data Warehouse?
  • What step? Transfer or Data Cleansing?
  • What is the error text? 

This is occurring in the Data Warehouse - DSA Layer on the Data Cleansing Step. 

below is the error text: 

Execute DSA dbo.PAT_ENC_VisitCancel_Ins Data Cleansing Rules:Error:Failed
-Execute DSA dbo.PAT_ENC_VisitCancel_Ins Data Cleansing Rules 'Failed'
Error during Data Processing for Table: dbo.PAT_ENC_VisitCancel_Ins at: DSA
Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Details:
SQL Server: ' AM-DEEP-SQL-PLS'
SQL Procedure: ''
SQL Line Number: 1
SQL Error Number: 1205
Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Module: .Net SqlClient Data Provider
System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at Microsoft.SqlServer.Management.Smo.DataProvider.ReadInternal()
at Microsoft.SqlServer.Management.Smo.DataProvider.Read()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitObjectsFromEnumResults(Urn levelFilter, IDataReader reader, Boolean forScripting, List`1 urnList, Int32 startLeafIdx, Boolean skipServer)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable`1 extraFields)
at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.Refresh(Boolean refreshChildObjects)
at TimeXtender.Sql.Engine.SqlEngineBase.GetTable(String tableName, String schemaName)
at TimeXtender.Sql.Engine.SqlEngineBase.DropForeignKeyRelations(String schema, String tableName, Guid tablePrimary)
at TimeXtender.DataManager.StepDataCleansingExecute.DoAbstractStep(StepSetup stepSetup)

Error during Data Processing for Table: dbo.PAT_ENC_VisitCancel_Ins at: DSA
System.Exception

No child steps

Hi Josiah, 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's an issue with dependencies. While, TX typically handles dependencies automatically, there are some scenarios with custom data where this doesn't happen. 

So there are a few ways you can resolve this for future runs:

  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. 

Hope that helps!

I also initially thought the deadlock was due to a dependency problem between ETL process' on the same table.

However, when analyzing the deadlock hierarchy, the victim of the deadlock is the above select from sys.table query which was conflicting with an index rebuild in the DSA Layer. 

the select statement was chosen as the deadlock victim and When SQL Server killed the process our execution package then failed.

attached is the image from the captured deadlock. 

 

Hi Josiah, 


Here is another tip you may try:


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

I see this happen frequently as well. Many internal TX operations acquire heavy locks. Row count logging for example will lock sys.tables and by doing so block all other deployments and executions.

We had similar problems both in ODX loads and execution packages. We pinned it down to execution plans in SQL Server that was not updated.


The solution for us for now is to schedule synchronize task regularly. It seems to have prevented most of the deadlocks.

@Martin Larsson can you explain the synchronization task? I have not heard of that before, but we have also been using 19.x.x version until very recently. 

We are using the ODX server (not Business Units) where you can schedule both Data load and synchronize tasks. Have a look hear:

 

 

Reply


Terms and ConditionsCookie settings