Reputation: 5071
Can I use row locks on rows that have not been created yet?
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...
Upvotes: 1
Views: 1085
Answers (2)
Reputation: 247445
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.
If you want to mask that error, you could write a simple PL/pgSQL function that catches the error. Or perhaps INSERT ... ON CONFLICT can solve the underlying problem.
Upvotes: 1
Reputation: 44305
An inserted row is automatically locked. But, how does this help you? What do you want to do with the locked rows?
Upvotes: 0
Related Questions
- Lock specific table rows to insert a new row
- Postgres row locking
- Is there a way to manually lock/unlock a table row in PostgreSQL that does not rely on transactions?
- Lock row, release later
- Lock all the rows in the PostgreSQL table
- Locking row that is being currently inserted to avoid race conditions when reading same
- Lock the newly inserted row [PostgreSQL]
- Postgres: is it possible to lock some rows for changing?
- Is there a way to generate some set of rows exactly once on demand in Postgres?
- How to specify explicit locking while creating a table in PostgreSQL