Hangfire on sql server 2014 xp_userlock

question
1

we have a production instance of hangfire (1.5.3) running the xp_userlock stored proc constantly. We are not passing that much load through it, approx 6-10 jobs/day.

Any help would be appreciated.
Sql Server 2014 and Hangfire version 1.5.3

2

We are facing blocking issue due to xp_userlock while using Batch feature of Hangfire Pro. We are passing 10k jobs/day.

Any help would be appreciated.
SQL Server 2016 and Hangfire pro 2.1

3

We are using SQL 2016. We are having by far the highest waits for locks caused by xp_userlock. Is there a way to remove/reduce these waits?

4

xp_userlock is causing 50% of overall server waits for SQL Server. Maybe myself and builders of hangfire can work together to find a solution. I am good at databases (SQL Server) but my knowledge of hangfire is limited edition :slight_smile:

5

Hi Baig,

To avoid xp_userlock, Please try to reducing the worker count. We have 60 (Workers) X 3 (Machine) = 180 total worker in our prod env. when batch starts to enqueue jobs (1 million) contention increase on SQL server and causing xp_userlock so we reduce down the workers to 5 (worker) X 3 (machine) = 15 total worker (currently on prod) are sufficient to our load. We have 1 million Job every 15 mins.

6

Guys, try to upgrade to the latest version, Hangfire 1.6.17. It contains the latest fixes, including the modified lock implementation for SQL Server. Please read the release notes for details.

7

We upgrade the Hangfire 1.6.17 but still facing xp_userlock issue. Please suggest.
Thanks - YS

8

At least they don’t eat SQL Server’s worker threads now, because of reduced wait times. @yogi, what resources are blocked? If they relate to Set, Hash and List tables, then it is currently by design – there’s no other deadlock-free technique, except a global lock, available to modify those tables with the current table structure.

As a part of 1.7.0 migration I’ve modified the underlying structure, so it will be possible to modify those tables without a global lock by applying a more granular locking to the concrete resources only, since all the data that belongs to the same key now reside close to each other.