Xp_userlock showing in SQL Server for version 1.7

question
1

I have recently upgrade to the latest version of Hangfire 1.7, after following the guide to get everything upgraded from the 1.6 and published it. I seem to getting an issue within SQL Server 2016, whereby I get a xp_userlock for every worker I have declared.

What I would like to know is, is anyone else suffering from issue once upgraded or could i be doing something wrong which is causing this to happen?

I am currently unable to run Hangfire as it will lock up the server for all other tasks even when not related to Hangfire database.

2

Could you tell me more about your environment, e.g. is it a dedicated instance or cloud one. If dedicated, did you change the “Maximum worker threads” setting? If you are running in cloud, could you tell me what pricing plan do you use?

SELECT max_workers_count
FROM sys.dm_os_sys_info
3

I believe we are using a cloud based environment from Azure, unfortunately due to the nature of the business I don’t know the pricing plan that is used.

However the server specs is 16 core Windows Server 2012 with 32GB of RAM, we have SQL Server SQL Analysis Server and a Web Server running from the same machine, after running you code to get the max worker count it shows as 704, I don’t believe this figure to be modified.

Also for you information below in the configuration we have for Hangfire. Which we use a dashboard and two instances of Hangfires servers one for job from users and one for internal jobs.

For the dashboard here is the global config settings we use:

        public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero,
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true
                });
            
            app.UseHangfireDashboard("", new DashboardOptions
            {
                Authorization = new[] { new HangfireAuthorizationFilter() }
            });
        }

Fore the jobs coming for users we have the following config settings

        public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero,
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true,
                    EnableHeavyMigrations = true
                });

            var options = new BackgroundJobServerOptions
            {
                Queues = new[] { "critical", "high", "default", "low" },
                SchedulePollingInterval = new TimeSpan(0, 0, 30),
                ServerCheckInterval = new TimeSpan(0, 0, 30),
                ServerTimeout = new TimeSpan(0, 5, 0),
                WorkerCount = 2,
                StopTimeout = TimeSpan.FromSeconds(10)
            };

            app.UseHangfireServer(options);
        }

        protected void Application_Start(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Start();
        }

        protected void Application_End(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Stop();
        }

For the internal jobs server we have the following settings

 public void Configuration(IAppBuilder app)
        {
            GlobalConfiguration.Configuration
                .SetDataCompatibilityLevel(CompatibilityLevel.Version_170)
                .UseRecommendedSerializerSettings()
                .UseSqlServerStorage("Hangfire", new Hangfire.SqlServer.SqlServerStorageOptions
                {
                    CommandBatchMaxTimeout = TimeSpan.FromMinutes(5),
                    QueuePollInterval = TimeSpan.Zero
                    SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                    UseRecommendedIsolationLevel = true,
                    UsePageLocksOnDequeue = true,
                    DisableGlobalLocks = true,
                    EnableHeavyMigrations = true
                });

            var options = new BackgroundJobServerOptions
            {
                Queues = new[] { "critical_io", "high_io", "default_io", "low_io" },
                SchedulePollingInterval = new TimeSpan(0, 0, 30),
                ServerCheckInterval = new TimeSpan(0, 0, 30),
                ServerTimeout = new TimeSpan(0, 5, 0),
                WorkerCount = Environment.ProcessorCount * 5,
                StopTimeout = TimeSpan.FromSeconds(10)
            };

            app.UseHangfireServer(options);
        }

        protected void Application_Start(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Start();
        }

        protected void Application_End(object sender, EventArgs e)
        {
            HangfireBootstrapper.Instance.Stop();
        }

Then in the active expensive queries within SSMS shows as follows:
image

Hope this helps.

4

Does it really blocks every other task in your database or it’s just a thought? What number of workers do you have in total?

5

I believe it does block every other query, it says they are suspended when looking at the process monitor in SSMS and nothing else seems to run or it runs very slowly.

These xp_userlock are all associated with the Hangfire database we have setup, and there appears to be one for every worker, we have a total of 82 workers 2 for CPU bound process(user) and 80 for IO bound process (internal).

However the jobs passed through Hangfire run just fine with these locks in place.

6

Any update on this issue? It looks like we are having exactly the same problems.

7

Hangfire processes produce a lot of xp_userlock even though there are only a couple of jobs per day.

8

I update Hangfire to the latest version (1.7.7). Still have the problem. Any suggestion? I have 2 machines and each of them only has 1 worker

9

This is intended, it’s the behaviour of hangfire when you have a low or zero timespan poll interval. Rather then every worker polling sql server for jobs, every worker aquires a lock for the queues they handle, then the worker with the lock polls for jobs.