SqlServer locking for database queue driver

[fideloper]

Hi!

It looks like SqlServer's equivalent to SKIP LOCKED is READPAST - use cmd-f and search for readpast on that page (and there's more explanation on it here).

READPAST only works on the default, usual isolation level. However, the use of ->lock() in Laravel for SqlServer adds the HOLDLOCK locking hint, which appears to not compatible with the use of READPAST.

Due to how the SqlServer grammar works, it looks like we can successfully lock the jobs table in SqlServer using something like:

$job = $this->database->table($this->table)
    ->lock('with(rowlock, updlock, readpast)') // note the lack of holdlock
    ->where('queue', $this->getQueue($queue)) // and so on...

To get that locking string, we can change the implementation of getLockForPopping to this:

protected function getLockForPopping()
{
    $databaseEngine = $this->database->getPdo()->getAttribute(PDO::ATTR_DRIVER_NAME);
    $databaseVersion = $this->database->getPdo()->getAttribute(PDO::ATTR_SERVER_VERSION);

    if ($databaseEngine == 'mysql' && ! strpos($databaseVersion, 'MariaDB') && version_compare($databaseVersion, '8.0.1', '>=') ||
        $databaseEngine == 'pgsql' && version_compare($databaseVersion, '9.5', '>=')) {
        return 'FOR UPDATE SKIP LOCKED';
    }

    if ($databaseEngine == 'sqlsrv') {
        return 'with(rowlock, updlock, readpast)';
    }

    return true;
}

However I could use help testing this out.

@themsaid do you have a setup around still to create all the jobs and workers you used to test the original implementation? If not, I can try to set something up as I have Windows servers with SqlServer available to use in our AWS account.

[themsaid]

If I remember correctly, I pushed some jobs initially in a loop but also had a cron running that keeps dispatching jobs. That way when the workers run, we will have inserts, updates, and deletions at the same time

Without the SKIP LOCKED flag I started seeing deadlocks almost as soon as workers started. With the flag. I was able to process all jobs without any deadlocks.

[fideloper]

Without any modification to the SqlServer database queue driver, I can't get any deadlocks to surface.

50 workers and a while loop endlessly creating jobs got up to about 200k jobs before I stopped the worker).

Everything seems to be going smoothly.

So, while I guess theoretically there could be an issue, it seems that this Just Works™ to a pretty good degree out of the box for SqlServer.

I'm not an expert on SqlServer to explain this, however :(

[fideloper]

OK, I've tested with the default locking and with the usage of READPAST and I can't get SqlServer to deadlock nor run a job twice.

So, no modification is needed from my point of view.

I tested with a job that runs thisin the handle() method:

        \DB::table('HS_Jobs_Usage')->insert([
            'job_id' => $this->job->getJobId(),
        ]);
        sleep(mt_rand(5,20));

That table migration:

Schema::create('HS_Jobs_Usage', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('job_id');
 });

Query to see if one is run more than once:

select job_id, count(job_id) as times_ran 
    from HS_Jobs_Usage 
    group by job_id 
    order by times_ran desc;

No jobs were run more than once and I never once got a query exception about deadlock timeouts.

50 works, ~200k jobs or so (via a while loop that continued to add jobs to the queue).