TL/DR: Transactions do not inherently prevent all race conditions. You still need locking, abort-and-retry handling, or other protective measures in all real-world database implementations. Transactions are not a secret sauce you can add to your queries to make them safe from all concurrency effects. Isolation What you’re getting at with your question is the I … Read more
While Erwin’s suggestion is possibly the simplest way to get correct behavior (so long as you retry your transaction if you get an exception with SQLSTATE of 40001), queuing applications by their nature tend to work better with requests blocking for a chance to take their turn at the queue than with the PostgreSQL implementation … Read more
Edit: I googled to check my answer: “Processing Data Queues in SQL Server with READPAST and UPDLOCK”. It’s been years since I read about and played with this solution. Original: If you use the READPAST hint, then locked rows are skipped. You’ve used ROWLOCK so you should avoid lock escalation. You also need UPDLOCK, as … Read more
A race condition occurs when two or more threads can access shared data and they try to change it at the same time. Because the thread scheduling algorithm can swap between threads at any time, you don’t know the order in which the threads will attempt to access the shared data. Therefore, the result of … Read more