Skip to main content

Starting on July 13, 2024, our data loads started having issues.  We did not have any patching or updates or changes that I can find. Prior to this they were running successfully since deployment for over 6 months.

 

The data load itself has a status of executing with errors.  Each data source shows as Executing, Failed, Success or Submitted.  Most were showing as Submitted.  The BI server errors looked like they were deadlocking them selves.  I put sequential IDs and sequences on all of the data sources and now most show as Success. If I run a data source by itself, then there are no issues.

 

Any help or ideas would be great.

 

Here is the IFS screen examples:
 

 

Here is the SSIS log for the DIM_Account:

Fetch Data Load Run Data Source Info:Error: Executing the query "EXEC [TABULAR_CREATE_AND_FETCH_DATA_SOURCE_RUN_INF..." failed with the following error: "Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

Set to Error:Error: Executing the query "EXEC [dbo].[TABULAR_DATA_LOAD_RUN_ITEM_INFO_MODIFY..." failed with the following error: "Transaction (Process ID 73) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. 

Hi @FR_Cust_IFS ,

Like you mentioned this was running fine for 6 months and suddenly these deadlocks are appearing.

I think the best thing to do is to run an environment setup again. It will recreate all the framework related tables in the SQL server side. Transaction data won’t be affected with this action.

When it comes to data load or any other action, the framework works as below.
All the information related to run the data load SSIS package is transferred to SQL server and stored in these FW related tables. With time there is a possibility to have some corrupted data in these tables and then the requests may fail with deadlocks or some error.
After an environment setup since the FW tables gets reset this error should go away.

Best regards,

Subash

​@subashfestus 
Are there things in the framework tables that can be checked first? If we can run the same code for one object successfully but fails when running concurrently, this doesn’t point to data corruption.

If the environment setup is to be run again, are there steps that can be skipped? I would hate to overwrite the metadata that supports the already incrementally loaded data.


 

When dealing with the same exact errors ​@Frank H  started this post about and going through all the tables and methods used in the failing jobs I found no corrupted tables, data, or packages but did notice some simple changes that can probably help this from happening:

-Consistent update order, so we don’t have the classic A-B-B-A deadlock scenario.

-UPDATE operations could have UPDLOCK and ROWLOCK hints to use UPDATE locks and avoid Shared Locks and deadlock escalation.

-READ operations could have NOLOCK hints so their Shared Lock doesn’t block concurrent UPDATE locks.



Any and all help is greatly appreciated
 

Hi ​@PhillBronson,

Doing the env setup will reset the incremental load related metadata as well. Unfortunately there is no mechanism to select which fw tables to be recreated.
A workaround should be to directly DELETE data of selected tables in the SQL server DB. 

TABULAR_REQUEST, TABULAR_DATA_LOAD_RUN_ITEM, TABULAR_DATA_LOAD_RUN cleaning these 3 tables might be helpful in this case.


We did some improvements to the logic to minimize the deadlocks by moving some of the logic in SQL server side to Oracle side. In our internal automatic test environments we saw that deadlock occurring has become almost zero.

 

Thanks and regards,

Subash

Terms & ConditionsCookie settingsAccessibility statement