使用 Laravel 队列时如何避免作业数据库表锁定问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32475853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid jobs DB table locks issue when using Laravel queues?
提问by MaGnetas
I'm using Laravel 5.1.
我正在使用 Laravel 5.1。
The queues are used for data fetching/syncing between several systems.
队列用于多个系统之间的数据获取/同步。
I use the database driver, 3 "artisan queue:work --daemon" processes are running all the time.
我使用数据库驱动程序，3 个“工匠队列：工作 --daemon”进程一直在运行。
The jobs are dispatched both by system users and scheduler (cron). Three queues are used to prioritize the jobs.
作业由系统用户和调度程序 (cron) 分派。三个队列用于确定作业的优先级。
Everything seems to be working just fine - the jobs table gets filled with records, the system takes care of them and removes the ones that are done.
一切似乎都工作得很好 - 工作表充满了记录，系统会处理它们并删除已完成的记录。
However after some time locking issues are starting to interfere:
然而，一段时间后锁定问题开始干扰：
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
SQLSTATE[40001]：序列化失败：1213 尝试获取锁时发现死锁；尝试重新启动事务
and
和
'RuntimeException' with message 'Can't swap PDO instance while within transaction.'
'RuntimeException' 和消息'不能在事务内交换 PDO 实例。
and
和
SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
SQLSTATE[HY000]：一般错误：1205 超出锁定等待超时；尝试重新启动事务
I haven't tried using another queue driver yet. I'd really like to stay with database though. The engine is InnoDB, the jobs table has default structure and indexes.
我还没有尝试使用另一个队列驱动程序。不过，我真的很想继续使用数据库。引擎是 InnoDB，jobs 表有默认的结构和索引。
Is there a way to solve this issue? What are your thoughts?
有没有办法解决这个问题？你怎么看？
It might be worth mentioning that I call the DB::reconnect()inside my job classes since the queue workers are running as daemons.
值得一提的是，DB::reconnect()由于队列工作器作为守护程序运行，因此我将内部称为我的工作类。
The jobs are dispatched using DispatchesJobstrait as one would expect. I don't interfere with queues algorithm in any other way.
DispatchesJobs正如人们所期望的那样，使用trait调度作业。我不会以任何其他方式干扰队列算法。
回答by guy_fawkes
This may not be the answer but some info.
这可能不是答案，而是一些信息。
When using SELECT ... FOR UPDATEstatements, you may observe lock contention(dead locks etc..).
使用SELECT ... FOR UPDATE语句时，您可能会观察到锁争用（死锁等）。
select … for update where x <= y
its that range scan with <= the database locks all rows <= y, including any gaps so if you have rows with y like this: 1, 3, 5 it locks even the empty space between 1 and 3 in the index its called gap locking
它的范围扫描 <= 数据库锁定所有行 <= y，包括任何间隙，所以如果你有这样的 y 行：1, 3, 5 它甚至锁定索引中 1 和 3 之间的空白空间，称为间隙锁定
can see the issue with this command:
可以看到这个命令的问题：
SHOW ENGINE INNODB STATUS;
---TRANSACTION 72C, ACTIVE 755 sec
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox
TABLE LOCK table test.t trx id 72C lock mode IX
RECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X
RECORD 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 gap
RECORD 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.
回答by Mikayel Margaryan
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
我正在 Laravel 上编写队列管理系统，我有多个作业有多个用户，我应该向他们发送电子邮件。我正在与主管一起运行许多工作人员，并避免向我编写此代码的同一用户发送多封电子邮件。希望它能帮助解决这个问题的人
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]);
}
});
回答by Douglas.Sesar
You could switch to MyISAM and that would remove the transaction errors; but at the same time you would lose a lot of great functionality and reliability that comes with innoDB.
您可以切换到 MyISAM，这将消除事务错误；但与此同时，您将失去 innoDB 带来的许多强大功能和可靠性。
*This is not an option if the tables have foreign keys and you are relying on things such as cascading deletes and updates that are only found in innoDB
*如果表具有外键并且您依赖于仅在 innoDB 中找到的级联删除和更新等内容，则这不是一个选项
