The CDM Utils created SPROC (DataLakeToSynapse_InsertIntoControlTableForCopy) used to insert new table records in the control table, is causing a table (not row) lock when attempting to do the insert. It does this because it is using a WHERE NOT EXISTS clause in the WHERE clause of the Insert.
The table lock is resulting in excessive wait times and causes the new record inserts to fail. This is issue is compounded because the function app needs to loop through all tables- so essentially hundred of queries checking if a row exists and effectively locking the table.
The stored procedure that does the insert into the control table is created by the CDM Utils function app. If the stored procedure is dropped or altered the, function app will automatically recreate it. Even if the function app web service is stopped, and the SPROC is recreated/altered, the function app will drop and recreate it with the original code at some point. So currently there is no way to modify the SPROC code except by modifying the function app- which is something I would like to avoid. The SPROC is called by the function app directly, so recreating it with another name would have no benefit.
An alternative approach is needed to avoid table locking such as:
IF (SELECT COUNT([TableName]) FROM [dbo].[DataLakeToSynapse_ControlTableForCopy]
WHERE [TableName]=@TableName)=0**
BEGIN
INSERT INTO [dbo].[DataLakeToSynapse_ControlTableForCopy](TableName, DataLocation,FileFormat, CDCDataLocation, MetadataLocation)
SELECT * FROM
(SELECT @TableName, @DataLocation, @FileFormat, @CDCDataLocation, @MetadataLocation) as i (TableName, DataLocation, FileFormat, CDCDataLocation, MetadataLocation)
I am requesting assistance in the form of a change to the function app, or suggestions on how to avoid the issue.
The CDM Utils created SPROC (DataLakeToSynapse_InsertIntoControlTableForCopy) used to insert new table records in the control table, is causing a table (not row) lock when attempting to do the insert. It does this because it is using a WHERE NOT EXISTS clause in the WHERE clause of the Insert.
The table lock is resulting in excessive wait times and causes the new record inserts to fail. This is issue is compounded because the function app needs to loop through all tables- so essentially hundred of queries checking if a row exists and effectively locking the table.
The stored procedure that does the insert into the control table is created by the CDM Utils function app. If the stored procedure is dropped or altered the, function app will automatically recreate it. Even if the function app web service is stopped, and the SPROC is recreated/altered, the function app will drop and recreate it with the original code at some point. So currently there is no way to modify the SPROC code except by modifying the function app- which is something I would like to avoid. The SPROC is called by the function app directly, so recreating it with another name would have no benefit.
An alternative approach is needed to avoid table locking such as:
IF (SELECT COUNT([TableName]) FROM [dbo].[DataLakeToSynapse_ControlTableForCopy] WHERE [TableName]=@TableName)=0**
BEGIN
INSERT INTO [dbo].[DataLakeToSynapse_ControlTableForCopy](TableName, DataLocation,FileFormat, CDCDataLocation, MetadataLocation) SELECT * FROM (SELECT @TableName, @DataLocation, @FileFormat, @CDCDataLocation, @MetadataLocation) as i (TableName, DataLocation, FileFormat, CDCDataLocation, MetadataLocation)
I am requesting assistance in the form of a change to the function app, or suggestions on how to avoid the issue.