zlacker

>>hyzyla+(OP)
You really don't need anything fancy to implement a queue using SQL. You need a table with a primary id and a "status" field. An "expired" field can be used instead of the "status". We used the latter because it allows easy retries.

1. SELECT item_id WHERE expire = 0. If this is empty, no items are available.

2. UPDATE SET expire = some_future_time WHERE item_id = $selected_item_id AND expire = 0. Then check whether UPDATE affected any rows. If it did, item_id is yours. If not, loop. If the database has a sane optimizer it'll note at most one document needs locking as the primary id is given.

All this needs is a very weak property: document level atomic UPDATE which can return whether it changed anything. (How weak? MongoDB could do that in 2009.)

Source code at https://git.drupalcode.org/project/drupal/-/blob/9.2.x/core/... (We cooked this up for Drupal in 2009 but I am reasonably sure we didn't invent anything new.)

Of course, this is not the fastest job queue there is but it is quite often good enough.

>>_ugfj+z2
Oh please stop using databases as queues. I spent a disproportionate amount of time in the last 20 years undoing that decision. It doesn’t scale at all well.

>>hughrr+gc
Look at SKIP LOCKED in PostgreSQL and MySQL.

>>ngrill+Kc
It’s not that. It usually ends up in the same storage engine as the main business functionality which leads to IO contention. The some sick and twisted bastard does a join to it from somewhere abhorrent which means it requires some serious heavy lifting to fix it.

This is unfortunately the status quo in the slightly less ideal universe than “seemed like a good idea at the time”

Use SQS or RabbitMQ or something.

Edit: Also if you’re using something SQL for queues you’re going to have to build out your monitoring stack yourself based on your implementation rather than suck up a metrics endpoint with queue stats or pull from cloudwatch.

>>hughrr+ud
So use a separate database.

Is contention with the application your only objection? That’s pretty weak.

What’s the real compelling reason not to use a database? You haven’t said.

>>flefto+Bj
Contention, architectural separation, you have to build your own monitoring stack, not transactional with concerns outside the database without introducing distributed transactions and risk, no routing or distribution capability, you have to build it yourself, point in time message durability is somewhat dubious depending on your acknowledgement process which of course you had to invent yourself as well.

Not to mention numerous database administrators crying into their cups of coffee.

Enough?

Like I said 20 years of people fucking this up is my experience.

>>hughrr+Zk
Meh. All your objections are hand wavey and not factual.

Databases actually work fine as a queue but emotionally you don’t like it. That’s fine it’s just not real strong objections.

What you have not said is “it physically does not work”, and that’s because it does work fine.

>>flefto+Ml
I’m not saying it doesn’t work. I’m saying you’re shooting your toes off down the line.