CODEWITHSUNDEEP
sqlpostgresql
Zanko
Zanko

Reputation: 4694

Lock the newly inserted row [PostgreSQL]

Is there a way to lock a recently inserted row so that other transaction do will not see it while my current transaction is still going on?

Upvotes: 5

Views: 1951

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325131

You don't have to. In fact, you can't have the opposite behaviour. There's no way to make your newly inserted row visible until your transaction commits.

Despite the fact that it's not visible to concurrent SELECT, it can still affect concurrent INSERT (or UPDATEs) though. Specifically, if you try to insert the same value into a unique index in two different transactions, one will block until the other commits or rolls back. Then it'll decide if it needs to raise a unique violation error, or if it can continue. So while you cannot directly see uncommitted data, sometimes you can see its side effects.

Upvotes: 7

Related Questions