Postgres Repeatable Read vs Selecting rows that are changed by another ongoing transaction

Question

Let's say I have a set of select statements that query important field values in a for-loop. The goal is to make sure that the rows are not updated by any other transaction so that this set of selects doesn't result in data that is out of date.

In theory, it seems that setting the transaction level to repeatable read should solve the problem. In this case, we can begin the transaction in the first select statement and then reuse the same transaction in this loop to make sure that updates are blocked until this transaction is committed.

Is there anything I am missing? Probably, there are some other ways to be sure that stale rows are not selected.

UPDATE: a bit more details

I have a series of queries like select name from some_table where id = $id_param and this $id_param is set in a for-loop.

I am worried, however, that this name field might be changed by another concurrent operation for some row or even get deleted. This would result in corrupted states for the final object.

It seems that based on the comment below, pessimistic locking could be the way to go i.e. using ...FOR UPDATE, but I am not sure.

• Laurenz Albe: Reading a row will never block a concurrent update, no matter what the isolation level is. You have to use SELECT ... FOR NO KEY UPDATE for that. But that means that you have to keep a transaction open, which should never happen. What are you trying to achieve?
• Don Draper: @LaurenzAlbe, just added more details in the OP
• Laurenz Albe: Yes, you'd have to lock. But why is it a problem if somebody modifies or deletes such a row?
• J.D.: Yea locking is what you want. But I think the better question is why are you querying the same table multiple times in a loop? It would be more efficient and essentially solve your problem to query the table once to get all the rows that your loop is pulling, in a single query execution.
• user1822: I too don't see the need for a loop. Sounds as if this could be achieved with a join (maybe with the help of some custom functions).

Answer 1

Whatever it is, you're doing it the wrong way.

Look for a way where all the updates can be done using a single update command.

you might need to use a temporary table and update from it.

UPDATE table_name SET { column_name = { expression | DEFAULT } [, ...] FROM other_table WHERE condition