Two Pods, One Job Table: A Safe Postgres Queue with SKIP LOCKED in Spring Boot
It's 10:20 AM on a Monday and support has forwarded three screenshots of the same "Your invoice is ready" email, received three times by one customer. The dashboards look fine. CPU is low, the error rate is zero. Your logs show this: 10:15:00.112 [scheduling-1] INFO c.a.n.NotificationWorker - picked 50 jobs (pod notif-7c9d-a) 10:15:00.115 [scheduling-1] INFO c.a.n.NotificationWorker - picked 50…
On a Monday morning, a support representative sent the same "Your invoice is ready" email to a customer three times. The system's dashboards showed normal performance, with low CPU usage and zero error rate. However, the logs revealed that the NotificationWorker was picking the same 50 jobs three times from the notification_job table, despite there being only one replica of the NotificationJob table.
The issue stemmed from the use of plain SELECT queries to fetch jobs without specifying the lock mode. PostgreSQL's default READ COMMITTED isolation level allows multiple pods to read the same set of pending jobs without locking them. As a result, all three pods ended up processing the same 50 jobs, leading to duplicate emails being sent.
To address this, the article suggests using a FOR UPDATE SKIP LOCKED clause in combination with a short transaction. This approach allows the pods to claim a set of jobs in a short transaction, process them, and then update the status of the processed jobs as DONE in a separate statement. By using SKIP LOCKED, the pods can avoid lock contention and process jobs in parallel without worrying about locking the same rows.
The article also provides a code example that demonstrates how to implement this pattern using Spring Data JPA. By setting the status column to RUNNING in the same transaction as selecting the jobs, the pods can ensure that each job is processed only once, even when running in multiple JVMs simultaneously. The key points to remember are:
1. Use FOR UPDATE SKIP LOCKED in a short transaction to claim a set of jobs without locking them permanently.
2. Perform the status update (e.g., setting status to DONE) in a separate statement after processing the jobs.
3. Keep the transaction duration short to release the row locks and allow other pods to process the remaining jobs.
By following this approach, the NotificationWorker can efficiently handle the job queue using a PostgreSQL table without the need for an external message broker.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
