Rails/Postgres - What type of DB lock do I need?

Question

I have a PendingEmail table which I push many records to for emails I want to send.

I then have multiple Que workers which process my app's jobs. One of said jobs is my SendEmailJob.

The purpose of this job is to check PendingEmail, pull the latest 500 ordered by priority, make a batch request to my 3rd party email provider, wait for array response of all 500 responses, then delete the successful items and mark the failed records' error column. The single job will continue in this fashion until the records returned from the DB are 0, and the job will exit/destroy.

The issues are:

• It's critical only one SendEmailJob processes email at one time.
• I need to check the database every second if a current SendEmailJob isn't running. If it is running, then there's no issue as that job will get to it in ~3 seconds.
• If a table is locked (however that may be), my app/other workers MUST still be able to INSERT, as other parts of my app need to add emails to the table. I mainly just need to restrict SELECT I think.
• All this needs to be FAST. Part of the reason I did it this way is for performance as I'm sending millions of email in a short timespan.
• Currently my jobs are initiated with a clock process (Clockwork), so it would add this job every 1 second.

What I'm thinking...

• Que already uses advisory locks and other PG mechanisms. I'd rather not attempt to mess with that table trying to prevent adding more than one job in the first place. Instead, I think it's ok that potentially many SendEmailJob could be running at once, as long as they abort early if there is a lock in place.
• Apparently there are some Rails ways to do this but I assume I will need to execute code directly to PG to initiate some sort of lock in each job, but before doing that it checks if there already is one lock, and if there is it aborts)

I just don't know which type of lock to choose, whether to do it in Rails or in the database directly. There are so many of them with such subtle differences (I'm using PG). Any insight would be greatly appreciated!

Answer 1

Answer: I needed an advisory lock.