Select for Update behavior

Question

I have I good understanding of what happens in regard to the update of a row when SELECT FOR UPDATE is used and another SELECT/UPDATE takes place. But what happens when two requests take place with SELECT FOR UPDATE.

For example:

1. Thread A starts a transaction and does a a SELECT FOR UPDATE on a row and retrieves some info and begins an HTTP request that takes time. After the call has returned the transaction is committed and the session is closed.
2. Thread B, as A waits for the request, starts a new transaction and does a SELECT FOR UPDATE on the same row. Will it retrieve the information and proceed with the HTTP request on its on or will it wait for thread A to commit/do the update and then retrieve the data from the row.

I do not care what will happen once the request returns and the time to update the table comes. The second that goes for the update will either throw or update the last possible data on the row.

But will the actual HTTP request be done by both of them? In other words can in this scenario SELECT FOR UPDATE be used (abused) as a thread synchronization mechanism?

Answer 1

You are mixing up layers. PostgreSQL doesn't do HTTP. SELECT ... FOR UPDATE has nothing to do with HTTP.

Here's how it works:

• Session 1 does BEGIN
• Session 2 does BEGIN
• Session 1 does SELECT ... FOR UPDATE and gets one or more rows
• Session 2 does SELECT ... FOR UPDATE and matches one of the same rows, so it blocks, not returning anything, until...
• Session 1 does a COMMIT or ROLLBACK
• Session 2 gets the results from the earlier SELECT ... FOR UPDATE

In other words, lock duration is controlled by transaction boundaries. Where the transaction boundaries lie depends on your application and framework, stuff way above the database layer that you haven't identified in any way.

(Also, this has nothing to do with threads).