Currently I am using Postgres' advisory locks, specifically pg_advisory_xact_lock, to lock IDs before I create rows with them in a transaction, so that they cannot be created in another transaction. The IDs are provided externally, so this can (and does) happen.

I am aware of row level locks, but my understanding is that they only work on rows with IDs that exist already. Is that correct? Or can I use row level locks on IDs which don't exist yet? If that makes sense...

CodePudding user response：

The normal way to do this is to put a unique or primary key constraint on the column. Then one of the inserting transactions will receive an error that it can handle.

CodePudding user response：

An inserted row is automatically locked. But, how does this help you? What do you want to do with the locked rows?