Atomic UPDATE .. SELECT in Postgres

by

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 of SERIALIZABLE transactions, which allows higher concurrency and is somewhat more “optimistic” about the chances of collision.

The example query in the question, as it stands, in the default READ COMMITTED transaction isolation level would allow two (or more) concurrent connections to both “claim” the same row from the queue. What will happen is this:

  • T1 starts and gets as far as locking the row in the UPDATE phase.
  • T2 overlaps T1 in execution time and attempts to update that row. It blocks pending the COMMIT or ROLLBACK of T1.
  • T1 commits, having successfully “claimed” the row.
  • T2 tries to update the row, finds that T1 already has, looks for the new version of the row, finds that it still satisfies the selection criteria (which is just that id matches), and also “claims” the row.

It can be modified to work correctly (if you are using a version of PostgreSQL which allows the FOR UPDATE clause in a subquery). Just add FOR UPDATE to the end of the subquery which selects the id, and this will happen:

  • T1 starts and now locks the row before selecting the id.
  • T2 overlaps T1 in execution time and blocks while trying to select an id, pending the COMMIT or ROLLBACK of T1.
  • T1 commits, having successfully “claimed” the row.
  • By the time T2 is able to read the row to see the id, it sees that it has been claimed, so it finds the next available id.

At the REPEATABLE READ or SERIALIZABLE transaction isolation level, the write conflict would throw an error, which you could catch and determine was a serialization failure based on the SQLSTATE, and retry.

If you generally want SERIALIZABLE transactions but you want to avoid retries in the queuing area, you might be able to accomplish that by using an advisory lock.

More Related Contents:

  1. What is a race condition?
  2. Can we have race conditions in a single-thread program?
  3. What is the meaning of the term “thread-safe”?
  4. What is the difference between concurrency, parallelism and asynchronous methods?
  5. What is a mutex?
  6. Is this an idiomatic worker thread pool in Go?
  7. JavaFX2: Can I pause a background Task / Service?
  8. What is a semaphore?
  9. What will be used for data exchange between threads are executing on one Core with HT?
  10. How do I share a mutable object between threads using Arc?
  11. Which would be better for concurrent tasks on node.js? Fibers? Web-workers? or Threads?
  12. What is a deadlock?
  13. Number of threads used by Go runtime
  14. How to manage db connections on server?
  15. Are “data races” and “race condition” actually the same thing in context of concurrent programming
  16. What’s the difference between Thread start() and Runnable run()
  17. Concurrency: Atomic and volatile in C++11 memory model
  18. How to avoid Not on FX application thread; currentThread = JavaFX Application Thread error?
  19. What is the Re-entrant lock and concept in general?
  20. What are the advantages of using an ExecutorService?
  21. Simple Thread Sample Delphi
  22. ThreadPoolExecutor Block When Queue Is Full?
  23. Why does Java not see the updated value from another thread?
  24. How JavaFX application thread works?
  25. Is non-blocking I/O really faster than multi-threaded blocking I/O? How?
  26. What is adaptive spinning w.r.t lock acquisition?
  27. Python 2.7: streaming HTTP server supporting multiple connections on one port
  28. How can I wrap a method so that I can kill its execution if it exceeds a specified timeout?
  29. How do I run parallel threads of computation on a partitioned array?
  30. Atomically increment two integers with CAS

Leave a Comment