InsertToList() performance

[neildodson]

Version: 1.8.10
Storage: Hangfire.SqlServer
ConfigurationLogic: any
Custom Filters: none

Description:
When inserting to a list, an exclusive lock is taken on all existing rows with the same key by means of selecting them:

Hangfire/src/Hangfire.SqlServer/SqlServerWriteOnlyTransaction.cs

Line 367 in d1f9bbe

select [Key] from [{_storage.SchemaName}].List with (xlock, forceseek)

I was a bit confused about why this happens - as a shared application lock has already been taken on the key before this Sql statement runs...
The reason I ask is that when the list for a give key becomes long, all the previous rows are selected, meaning an insert at position 1 million requires selecting the previous 999,999 rows - and that doesn't perform well..

[neildodson]

just counting them seems to take the lock as well, btw...

select count([key]) from hangfire.list with (xlock, forceseek)
where [Key] = @key;

but it still doesn't scale up

[neildodson]

Hmm, what I assumed initially isn't right - in the default case (where DisableGlobalLocks is true and UseFineGrainedLocks is false) in fact application locks aren't taken for any type of storage update...

Hangfire/src/Hangfire.SqlServer/SqlServerWriteOnlyTransaction.cs

Line 618 in d1f9bbe

if (!_storage.Options.DisableGlobalLocks || _storage.Options.UseFineGrainedLocks)

I guess that's because the SQL server locking semantics are adequate for all these cases, and the (xlock) hints are needed to make that true (right?).. But why is it necessary to lock all the list predecessors before inserting?