Fabric GraphQL mutation deadlocks

[mikeburek1]

For the Fabric GraphQL connecting to a Fabric warehouse, the data reading makes sense.

But when doing many data updates or inserts, isn't it very susceptible to deadlocks if many mutation requests come in at the same time since the Fabric Warehouse only uses snapshot isolation? Or if another process updates the table when the GraphQL mutation is also trying to update the same table?

[rajendraongole1 · Accepted solution]

Hi @mikeburek1 - Snapshot Isolation is designed to prevent readers from blocking writers and vice versa, which makes it a strong choice for read-heavy environments. However, when multiple mutation requests (i.e., updates or inserts) come in at the same time, it could lead to contention.

While snapshot isolation avoids deadlocks between reads and writes, writes can still conflict with each other. If multiple processes or GraphQL mutations attempt to update the same row at the same time, these updates could result in write conflicts, leading to potential failures or retries.

Mutation operations in GraphQL work well for simple, independent updates but might face problems in environments with high concurrency, especially when the same row or dataset is being accessed by multiple users or processes.

Write Conflicts: If two mutations attempt to update the same row simultaneously, one of the operations might fail and would need to be retried. In the absence of proper locking mechanisms (since snapshot isolation doesn't lock rows for reads), these conflicts may happen frequently during bulk operations or highly concurrent writes.

Deadlocks in Snapshot Isolation are less common compared to other isolation levels, but write-write conflicts can still lead to contention where processes may need to retry their operations.

Re: Snapshot isolation transaction aborted due to ... - Microsoft Fabric Community

Fabric Data Warehouse stored proc fails: Snapshot ... - Microsoft Fabric Community

[mikeburek1]

Thanks! This is what I was thinking would happen.

I'll have to discuss this situation with the GraphQL API end user to decide how much they need mutations. Or if they can be scheduled. I don't want a mutation to cancel an ETL job that is updating the underlying table.

And, of course, API usagage is supposed to be programmed to retry on certain failures. So I'll need to make sure GraphQL API end users for our Fabric Warehouse tables know they could get a deadlock error that is likely just due to timing, and they should retry.