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

Question

I'm using 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.

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]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

and

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

and

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

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?

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

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

Answer 1

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 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

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/