I was reading through Quick and Easy, Exactly-Once, Distributed Work Queues Using Serializable Transactions - DEV Community (excellent article, btw) because it was the closest thing I’d been able to find to my desired use-case. What I want is a bit different though, so I figured I’d ask if there was a different best-practice approach.
I have a table where each row corresponds to an item that can be acquired by an owner. Something like this:
CREATE TABLE items (
My workers need to be able to acquire an unowned item of a particular flavor and a NULL owner. It does this by writing in an owner id into the owner column. This is pretty easy to do with something like this:
UPDATE items SET owner = X WHERE owner IS NULL and flavor = Y LIMIT 1 RETURNING id;
An issue here is that it seems like this update ends up locking the entire range of eligible items not just the one that’s actually written to. I know because the order matters to the db here (even if it doesn’t to me) it makes sense that it wouldn’t want anything else inserting something before the row it’s going to return, although I am a bit surprised that it ends up locking everything after too.
The other issue is that workers doing updates in parallel are going to all be contending for the first eligible row, whereas I really don’t care which they pick. This is what makes it different from the message queue example, where getting the first one does matter. From looking around it seems like some other dbs have a SKIP LOCKED which would address this, but isn’t implemented in crdb right now.
Is there a best-practice way to approach this?