Does INSERT INTO ... SELECT require lock (FOR UPDATE)-CodePudding

I am using PostgreSQL. Here is a simplified query that I have:

INSERT INTO TableA
SELECT values
FROM TableB 
JOIN tables
LEFT JOIN TableA
WHERE conditions
AND TableA.X IS NULL

It adds new record to TableA but only if it does not exist. When I run this query sequentially I get only one new record created. But what if multiple threads will run it in parallel? Is it possible that more that one record in TableA will be created?

Do I need to use SELECT ... FOR UPDATE to lock records in TableB?

CodePudding user response：

If two of these statements are running concurrently, none of the SELECTs can see the INSERT of the other (because neither transaction is committed yet). So they may insert the same row twice.

The best way to avoid that is to use INSERT ... ON CONFLICT with a unique or primary key constraint.