search

HangfireIO / Hangfire

An easy way to perform background job processing in .NET and .NET Core applications. No Windows Service or separate process required
https://www.hangfire.io
Other
9.43k stars 1.71k forks source link

InsertToList() performance #2391

Open neildodson opened 7 months ago

neildodson commented 7 months ago

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: https://github.com/HangfireIO/Hangfire/blob/d1f9bbe9be8f1b0028b607a94608703b6cd88e54/src/Hangfire.SqlServer/SqlServerWriteOnlyTransaction.cs#L367

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 commented 7 months ago

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 commented 7 months ago

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...

https://github.com/HangfireIO/Hangfire/blob/d1f9bbe9be8f1b0028b607a94608703b6cd88e54/src/Hangfire.SqlServer/SqlServerWriteOnlyTransaction.cs#L618

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?