Getting locking errors when running some TSQL transformations in Synapse SQL pool

Question

Any idea about this error : Msg 3960, Level 16, State 117, Line 596
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Table_357be330476747d180e4d8d1e8c89097_43' directly or indirectly in database 'Distribution_43' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Completion time: 2022-12-02T15:34:20.8345772-05:00

Answer 1

Hello @Batta, Gaurav ,

Welcome to the MS Q&A platform.

Are you using any table hints in your query?

As per the error message, the snapshot isolation conflicts with the update lock and throws this error.

Could you please share the query that you are using?

A similar thread has been discussed here.

If you have any table hints and foreign keys, please remove them and try again.

Answer 2

It looks like one of the distribution node is getting conflict with another. When we run our updates, it looks like distribution node(s) are spinning up dataset temporarily in table 'dbo.Table_357be330476747d180e4d8d1e8c89097_43' internally and creating locking contention on it. This object is table 'dbo.Table_357be330476747d180e4d8d1e8c89097_43' all internal to Synapse SQL Pool's distribution nodes I guess.

Thanks,
Gaurav