Pipeline Calling Stored Proc; "Snapshot isolation transaction failed"

[AlanZ]

Greetings,

I have a pipeline that calls a stored procedure. The stored procedure is in a Fabric warehouse and creates dimensions / facts. It regularly, but not always (perhaps 75% of the time), returns an error:

" Sql error number: 3961. Error Message: Snapshot isolation transaction failed in database '<REMOVED>' 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."

Interestingly, the database referenced in the above as '<REMOVED>' exists in a Fabric lakehouse. The stored procedure selects from, but does not create/update/delete, objects in that database. Are we somehow getting issues where multiple simultaneous SELECT statements are hitting this error? 

Thank you for your help!

[v-cboorla-msft]

Hi @AlanZ 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience.

The error message you are encountering, SQL error number 3961, indicates a problem related to snapshot isolation in your database. 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. 

Change Isolation Level: Before querying metadata, change the snapshot isolation level to a non-snapshot isolation level such as read committed before querying metadata. This ensures consistent access to metadata.

For additional information please refer the below mentioned documents :

Link1 : MSSQLSERVER_3961.

Link2 : Snapshot isolation transaction fails when querying metadata.

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

Thank you.

[AlanZ]

Thanks, @v-cboorla-msft .

I'm not aware of any capacity within which I'm querying metadata. I'm beginning a transaction, running basic DML queries, and committing that transaction. Then doing it a bit more. I'm not where where the snapshot isolation / metadata issue is with this.

Link1 you provided indicate that the following would cause the issue. I'm not using any of these commands.

ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement

Further, it doesn't seem to me that Fabric supports this:

Further, according to this link, only one isolation level is supported within Fabric warehouses... snapshot. 

So I feel like I'm stuck!

[AlanZ]

@v-cboorla-msft , checking back in-- this issue broke last night's pipeline run, as well.

Would you suggest I submit a support ticket?

[v-cboorla-msft]

Hi @AlanZ 

Apologize for the inconvenience.

Please reach out to our support team to gain deeper insights and explore potential solutions. It's highly recommended that you reach out to our support team. Their expertise will be invaluable in suggesting the most appropriate approach.

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Thank you.

[AlanZ]

Thanks, @v-cboorla-msft . I've done so, 2404190040004509.