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).

CodePudding user response：

answer to the first question

The second transaction can still read the value that the first transaction has deleted, because the first transaction has not committed yet and reading data requires no row lock.

answer to the second question

The delete in the second transaction will be blocked by the row lock taken by the first transaction, and it will hang until the first transaction finishes.

To avoid race conditions like that, either perform the first step using

SELECT ... FOR UPDATE

or use a higher transaction isolation level than READ COMMITTED. The former will take a row lock and block the concurrent transaction early on, the latter will make one of the transactions fail.