Will INSERT ON CONFLICT DO NOTHING lock the row in case of conflict?

Question

In read-commited isolation level:

If I understood correctly, in case of no pre-existing rows that would result in conflict, two concurrent transactions with INSERT ... ON CONFLICT DO NOTHING - which would conflict between them - will have the following behaviour:

• One of the transaction will insert the row.
• The second transaction will wait for the first transaction to commit and then do nothing (or insert in case of rollback of the first transaction).

Will the row be locked in the second transaction?

Also, I guess the behaviour of the second transaction would be exactly the same than the case where we already have a row (commited), and we do a conflicting INSERT ... ON CONFLICT DO NOTHING?

Answer 1

An INSERT ... ON CONFLICT DO NOTHING won't lock the row if it detects a conflict. You can easily tell that because the xmax of the row will be unchanged after the operation.

The operation will have the same effect regardless of whether the conflicting row is old or just being inserted concurrently.