使用 Laravel 队列时如何避免作业数据库表锁定问题?

How to avoid jobs DB table locks issue when using Laravel queues?(使用 Laravel 队列时如何避免作业数据库表锁定问题?)

问题描述

我使用的是 Laravel 5.1.

I'm using Laravel 5.1.

队列用于多个系统之间的数据获取/同步.

The queues are used for data fetching/syncing between several systems.

我使用数据库驱动，3个artisan queue:work --daemon"进程一直在运行.

I use the database driver, 3 "artisan queue:work --daemon" processes are running all the time.

作业由系统用户和调度程序 (cron) 分派.三个队列用于确定作业的优先级.

The jobs are dispatched both by system users and scheduler (cron). Three queues are used to prioritize the jobs.

一切似乎都很好 - 工作表充满了记录，系统会处理它们并删除已完成的记录.

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]: 序列化失败: 1213 Deadlock found when试图获得锁定；尝试重新启动事务

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

和

'RuntimeException' 带有消息 'Can't swap PDO instance while while inside交易.'

'RuntimeException' with message 'Can't swap PDO instance while within transaction.'

和

SQLSTATE[HY000]:一般错误:1205 超出锁定等待超时；尝试重启事务

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

我还没有尝试使用其他队列驱动程序.不过，我真的很想继续使用数据库.引擎是InnoDB，jobs表有默认的结构和索引.

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.

有没有办法解决这个问题?你有什么想法?

Is there a way to solve this issue? What are your thoughts?

值得一提的是，我在作业类中调用了 DB::reconnect()，因为队列工作器作为守护进程运行.

It might be worth mentioning that I call the DB::reconnect() inside my job classes since the queue workers are running as daemons.

作业是使用 DispatchesJobs 特性调度的，正如人们所期望的那样.我不会以任何其他方式干扰队列算法.

The jobs are dispatched using DispatchesJobs trait as one would expect. I don't interfere with queues algorithm in any other way.

推荐答案

这可能不是答案，而是一些信息.

This may not be the answer but some info.

在使用 SELECT ... FOR UPDATE 语句时，您可能会观察到锁争用(死锁等).

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

select … for update where x <= y

它的范围扫描与 <=数据库锁定所有行 <= y，包括任何间隙所以如果你有这样的 y 行:1, 3, 5它甚至锁定索引中 1 和 3 之间的空白空间它叫做间隙锁定

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

可以看到这个命令的问题:

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

最后一行

如果您的事务中有很多间隙锁会影响并发性和性能，您可以通过两种不同的方式禁用它们:

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/

这篇关于使用 Laravel 队列时如何避免作业数据库表锁定问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！