In SQL Server, do composite primary keys increase the chance of a deadlock?

Question

I was wondering if, either because of increased row locking, and/or because of increased time spent holding locks, having a composite primary key defined on a table would increase the chance of having a deadlock when a it is being updated my multiple threads at once?

Thank you for any help.

Answer 1

If you are using composite PKs and are inserting large amounts of data in parallel you might get hit by resource hash collision. See "The Curious Case of the Dubious Deadlock and the Not So Logical Lock" for a real world example.

And for the explanation to resource hash collision I'll cite Remus Rusanu in "%%lockres%% collision probability magic marker: 16,777,215" (recommended read):

The lock manager in SQL Server doesn’t know what it locks, it just locks ‘resources’ (basically strings). It is the job of higher level components like the the access methods of the storage engine to present the ‘resource’ to the lock manager and ask for the desired lock. When locking rows in a heap or a b-tree the storage engine will synthesize a ‘resource’ from the record identifier. Since these resources have a limited length, the storage engine has to reduce the effective length of a key to the maximum length is allowed to present to the lock manager, and that means that the record’s key will be reduced to 6 bytes. This is achieved by hashing the key into a 6 byte hash value.

[...]

On 6 bytes there are 281,474,976,710,656 distinct possible values. Its a pretty big number? Not that big actually. [...] So the SQL %%lockres%% hash will produce two records with same hash, with a 50% probability, out of the table, any table, of only 16,777,215 record.

Answer 2

Since the SQL Server’s Lock Manager uses a lockhash value (not the PK directly) I would conclude there is no difference in locking using a single column PK versus a composite PK.

Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency

Unlike some other database vendors, there is a logical component to SQL Server’s Lock Manager. SQL Server uses a lockhash value to represent a lock on the lock structure in the SQL Server Lock Manager instead of using the physical description to a row, page or a table. The lockhash value then is kept in memory.

I would conclude no greater chance of getting a hash collision of the lockhash value from a single column key versus a composite key. As indicated in the link with SQL 2008R2 the lockhash was significantly improved and specifically addressed composite keys.

Prior to 2008R2 lockhash was less than perfect for both single and composite keys.
It is a good practice to keep your PK short.

.NET KeyValuePair and Tuple do not generate a good hash.