Upgrading Inventory Server from 6.6.1 to 6.6.2 failed with a communication deadlock.

2021-05-26 12:01:16,727 [InstallationWorker] ERROR SnowSoftware.Update.ProductUpdate [(null)] - The exception was: 

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 72) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Changing inv.DataSoftware.AppId type to bigint

• Inventory Server 6.6.1, upgrading to 6.6.2+.
• This issue affects environments with larger databases.

Communication Buffer resources" are exchangeEvents described further in https://blogs.msdn.microsoft.com/bartd/2008/09/24/todays-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks/ and are used for combining results of parallel queries.

A key paragraph from the link is the following: 

An "exchangeEvent" resource indicates the presence of parallelism operators in a query plan. The idea is that the work for an operation like a large scan, sort, or join is divided up so that it can be executed on multiple child threads. There are "producer" threads that do the grunt work and feed sets of rows to "consumers". Intra-query parallel requires signaling between these worker threads: the consumers may have to wait on producers to hand them more data, and the producers may have to wait for consumers to finish processing the last batch of data. Parallelism-related waits show up in SQL DMVs as CXPACKET or EXCHANGE wait types (note that the presence of these wait types is normal and simply indicates the presence of parallel query execution -- by themselves, these waits don't indicate that this type or any other type of deadlock is occurring).

The Snow Inventory (SI) 6.6.2 upgrade includes a change in the format of the ApplicationID and AppID fields from INT to BIGINT (SIT-967) which can involve updating several very large tables. In this case, SQL distributed the update to DataSoftware into 8 parallel threads which could be seen by looking in the SQL Server Activity Monitor.

In this case, running the SQL migration scripts directly in SQL Management Studio worked even though SQL still created 8 parallel child threads.

After completion of the SQL script, the customer was able to successfully run the Snow Inventory Server 6.6.2 upgrade via the Snow Update Service (SUS). The migration scripts check to see if the changes were already made so it skipped over the steps to reformat the ApplicationID and AppId fields. It was able to implement the remaining steps for the update, including the updating of the registry entry and the recording of the upgrade in the DbVersionHistory table.

In rare cases, multiple SQL threads running a single query can end up deadlocking with one another. Re-running the same query may or may not result in the deadlock. This can happen anytime the MaxDegreeOfParallelism setting is set to greater than 1. This issue for the 6.6.2 upgrade is likely to only occur with larger SQL databases.