'Concurrent transactions : reading data being modified

Given this SQL transactional function (knowing "PK" means "Primary Key"):

1) READ a value from given PK (if exists)
2) DELETE row of given PK (if exists)
3) INSERT row for same PK

Questions

In a Postgres server, if two instances of that transactional function are ran concurrently for the same PK, what happens internally?

  • Say transaction 1 (t1) has just finished executing step 2, when transaction 2 (t2) reaches step 1: will t2 read the value that was deleted by t1 because t1 hasn't committed yet?
  • And then if t1 is still in between step 2 and 3, and t2 executes step 2: is a rollback initiated for t2?

Follow-up

If there are indeed concurrency problems, how exactly can such a function be made to work properly without resorting to table-locks?

It seems to me that a row-lock would be great, but my understanding is that they do not prevent reads from happening so the second function would still potentially read an erroneous value (for example, if the entry was deleted and not yet reinserted, then it would assume it shouldn't count it, when it fact it should instead be waiting on the new insertion).



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source