sql - 在 SQL Server 中，复合主键是否会增加死锁的可能性？

Question

我想知道，由于行锁定的增加，和/或由于持有锁的时间增加，在表上定义复合主键是否会增加更新表时发生死锁的机会一次多个线程？

感谢您的帮助。

Answer 1

如果您使用复合 PK 并并行插入大量数据，您可能会受到资源哈希冲突的影响。请参阅"The Curious Case of the Dubious Deadlock and the Not So Logical Lock"举一个现实世界的例子。

对于资源哈希冲突的解释，我将在"%%lockres%% collision probability magic marker: 16,777,215"中引用Remus Rusanu。 (推荐阅读):

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.