postgresql - some questions about "select for update"?

Question

Here is Pseudo code using libpq.so;but it does not go as what I think.

transaction begin

re1 = [select ics_time from table1 where c1=c11, c2=c22, c3=c33, c4=c44 for update];

if(re1 satisfies the condition)
{
   re2 = [select id where c1=c11, c2=c22, c3=c33, c4=c44 for update];
   delete from table1 where id = re2;
   delete from table2 where id = re2;
   delete from table3 where id = re3;
   insert a new record into table1,table2,table3 with the c1,c2,c3,c4 as primary keys;
]
commit or rollback

Note that c1,c2,c3,c4 are all set as the primary key in the database, so it is only one row with these keys in the database.

What confuses me is as follows:

1. There are two "select for update" which will lock the same row. In this code, does the second SQL statement wait for the exclusive lock blocked by the first statement? But, the actual situation is that it does not happen.
2. Something occurs beyond my expectation. In the log, I see a large number of duplicate insert errors. In my opinion that the "select for update " locks the row with the unique for keys, two processes go serially. The insert operation goes after a delete. How can these duplicate insertation occur? Doesn't the "select for update" add an exclusive lock to the row, which blocks all other processes that want to lock the same row?

Answer 1

Regarding your first point: Locks are not held by the statement, locks are held by the surrounding transaction. Your pseudo-code seems to use one connections with one transaction which in turn uses several statements. So the second SELECT FOR UPDATE is not blocked by the first. Read the docs about locking for this:

[...]An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

Otherwise it would be very funny, if a transaction could block itself so easily.

Regarding your second point: I cannot answer this because a) your pseudo code is to pseudo for this problem and b) I don't understand what you mean by "processes" and the exact usecase.