If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?
1 Answer
From the documentation:
The update and delete operators in a parallel query execution plan are executed serially, but the WHERE clause of an UPDATE or a DELETE statement may be executed in parallel. The actual data changes are then serially applied to the database.
As to your second question,
does it guarantee a communication buffer resource deadlock
Any deadlock is predicated on the query / queries being run and how they are structured and executed, SQL Server does not inherently guarantee any deadlocks on its own.
-
Interesting. Thank you for the link to the documentation, I was not able to find that earlier. However, I wonder why this type of deadlock does not occur more frequently, particularly in Data Warehouses that process updates to dimension records? Sep 15, 2020 at 18:17