PostgreSQL: Dealing with Queues and Locks
When we’re working with multiple services or workers consuming the same queue table, we’ll probably have problems with one worker locking a row and blocking another. Or, even worse, both processing the same row. PostgreSQL has the solution! (Ok, to be fair, SQL Server has it too WITH (UPDLOCK, ROWLOCK, READPAST), but let’s keep our focus on PostgreSQL, which I’m using this week) Concurrent workers can skip rows that are already locked and claim the next available job ...