Issues with Snapshot Isolation Transaction Failures in Cross-Warehouse Ingestion

[nguyenhieubis]

I have a Workspace that includes:

• Lakehouse lh_bronze: Where I create views at the SQL endpoint of the lakehouse.
• Warehouse wh_silver: Where data from the views in lakehouse bronze is stored.
• Data Pipelines: I have multiple SQL statements running concurrently to load data from lakehouse bronze to warehouse silver using cross-warehouse ingestion.

The SQL statements are structured as follows:

BEGIN TRY
    DROP TABLE IF EXISTS [wh_silver].[staging].[table_A];
    CREATE TABLE [wh_silver].[staging].[table_A]
    AS
    SELECT * FROM [lh_bronze].[export].[v_table_A];
    
    BEGIN TRANSACTION
        DELETE tgt
        FROM [wh_silver].[dbo].[table_A] tgt
        INNER JOIN [wh_silver].[staging].[table_A] src
            ON tgt.pk_col = src.pk_col;
        
        INSERT INTO [wh_silver].[dbo].[table_A]
        SELECT * FROM [wh_silver].[staging].[table_A];
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
END CATCH;

I schedule these to run about 10 times a day and encounter approximately 2-4 error notifications daily with the following message:

Snapshot isolation transaction failed in database 'lh_bronze' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.
Statement ID: {D1533248-6EF4-4D8E-9BDB-C339B0083307} | Query hash: 0xFD91EA61D6EFE5A8 | Distributed request ID: {6D66C75B-BD4E-42DC-9B2C-628AF26F298A}
Statement ID: {2C3AA46C-A5AB-4554-BEA3-27347EB20256}

I have been searching for a solution on Google for the past month but have not been able to resolve the issue.

I would greatly appreciate your assistance in fixing this error and identifying the underlying problem.

[AndyDDC]

Hi, it could be that another process is modifying the table.  Can you read through this blog I wrote a while ago and see if any of it applies to your scenario?

https://www.serverlesssql.com/transaction-isolation-in-fabric-warehouses/

[v-cboorla-msft]

Hi @nguyenhieubis 

Thanks for using Microsoft Fabric Community.

This error can occur if you are querying metadata under snapshot isolation and there is a concurrent DDL statement that updates the metadata that is being accessed under snapshot isolation. SQL Server does not support versioning of metadata. For this reason, there are restrictions on what DDL operations can be performed within an explicit transaction running under snapshot isolation.

Please refer the below documentation for additional information.

Link 1 : MSSQLSERVER_3961 - SQL Server | Microsoft Learn

Link 2 : Snapshot isolation transaction fails when querying metadata - Microsoft Community Hub

I hope this information helps. Please do let us know if you have any further queries.

Thank you.

[nguyenhieubis]

Hi @v-cboorla-msft 

Thanks for your response.

I also referred to the two links you mentioned. However, after reviewing my SQL code, I can confirm that I am not using any DDL statements within the TRANSACTION block.

I am only running parallel SQL statements as described in the original problem.

Thank you.

[v-cboorla-msft]

Hi @nguyenhieubis 

Could you please confirm whether you are getting above error message on each execution. Or you are observing this error on few executions.

Thank you.

[nguyenhieubis]

For example: In one pipeline run, approximately 12 SQL statements are executed, and one of them will encounter this error. With 10 pipeline runs per day, this error occurs in about 2 runs.

[v-cboorla-msft]

Hi @nguyenhieubis 

Apologies for the inconvenience that you are facing here.

There might be a chance of an intermittent issue could you please follow the below steps that might help you.

Temporary Glitch: Clearing cookies and caches can sometimes resolve temporary glitches within the application that might be causing the issue.

Corrupted Data: In rare cases, corrupted data stored in the browser's cache related to Microsoft Fabric might be causing the issue. Clearing the cache removes this potentially problematic data.

Hard Refresh: A hard refresh bypasses the cached version of the webpage and forces the browser to download the latest version from the server. Press Ctrl+Shift+R (Windows) or Cmd+Shift+R (Mac).

Try Microsoft Edge: If you are currently using Chrome or Firefox, try switching to Microsoft Edge to see if the issue persists. Edge is the native browser for Microsoft products and might have better compatibility with Microsoft Fabric.

If the issue still persists, please do let us know. Glad to help.

I hope this information helps.

Thank you.

[v-cboorla-msft]

Hi @nguyenhieubis 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[v-cboorla-msft]

Hi @nguyenhieubis 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.