I have a sidekiq job that runs very frequently and each job updates a single MySQL database record using ActiveRecord in Rails:
HTTP request
-> RecordUpdateWorker(id, value)
-> Record.find(id).update(value: value)
It's causing deadlocks lock timeouts in the database. There seem to be no duplicate jobs, but the IDs are near to each other, so I can only guess that this is due to Gap Locks. Regardless, my idea for a solution is to "buffer" the jobs and perform them less frequently in batches. There is no need for them to be executed immediately, so for this use-case it wouldn't cause a problem.
HTTP Request
-> Add Record to Buffer
-> Occasionally run RecordUpdateWorker()
-> Record.where(id: ids_from_buffer).update_all(value: values_from_buffer)
# I'd have lots of flexibility to tune the transaction size, etc here
Is there any way to accomplish this with sidekiq or with a sidekiq add-on? I'm already using batches (Sidekiq Pro) in other places, but it doesn't seem like it would fit this use-case. One obvious solution would be to just use a custom Redis queue and a reoccurring job (or something like a until_and_while_executing lock in sidekiq-unique-jobs), but if there is a standard way to accomplish this, I'd rather use that.
I've already looked at the sidekiq docs, including pro and enterprise, and didn't find anything that quite matched this description.
Edit: Changed deadlocks to lock timeouts since I'm not sure it's a deadlock scenario.