How to avoid jobs DB table locks issue when using Laravel queues?

This may not be the answer but some info.

When using SELECT ... FOR UPDATE statements, you may observe lock contention(dead locks etc..).

select … for update where x <= y

its that range scan with <=the database locks all rows <= y, including any gapsso if you have rows with y like this: 1, 3, 5it locks even the empty space between 1 and 3 in the indexits called gap locking

can see the issue with this command:

SHOW ENGINE INNODB STATUS;---TRANSACTION 72C, ACTIVE 755 sec4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandboxTABLE LOCK table test.t trx id 72C lock mode IXRECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode XRECORD LOCKS space id 19 page no 3 n bits 80 index GEN_CLUST_INDEX of table test.t trx id 72C lock_mode X locks rec but not gapRECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X locks gap before rec

last line

If you have lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways:

1- Change the ISOLATION level to READ COMMITTED. In this isolation level, it is normal and expected that query results can change during a transaction, so there is no need to create locks to prevent that from happening.2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking.

https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/

I'm writing queue management system on Laravel, I have multiple jobs that has multiple users whom I should send emails. I'm running many workers with supervisor and to avoid multiple email sending to same user I wrote this code. Hope it will help somebody with this problem

DB::transaction(function () use ($job) {            if (!count($job->jobUsers()->sharedLock()->get())) { // to share reading ability btw multiple workers                Log::info('There is no user in this job');                $job->status = Job::STATUS_FINISHED;                $job->save();                return;            }            foreach ($job->jobUsers as $jobUser) {                Log::info($jobUser->user_id);                JobUser::where('job_id', $jobUser->job_id)                    ->where('user_id', $jobUser->user_id)                    ->lockForUpdate()  // exclusive lock                    ->update(['status' => JobUser::STATUS_SENT]);            }        });