Contents
What does Postgres do when there is a lock?
Postgres will recognise this situation after a second or so and will cancel one of the transactions to let the other one finish. When this happen, you should take a look at your application to see if you can make your transactions always follow the same order.
Is it possible to deadlock optimistic locking in SQL?
Typically, the user will make separate calls to retrieve the item and to save it, which may even be handled by different instances of your application in a cluster. Some sources claim that, when using optimistic locking, deadlocks are not possible because it never issues any database-level locks.
What to do when Postgres blocks a query?
By setting lock_timeout, the DDL command will fail if it ends up waiting for a lock, and thus blocking queries for more than 2 seconds. The downside is that your ALTER TABLE might not succeed, but you can try again later. You may want to query pg_stat_activity to see if there are long-running queries before starting the DDL command.
Are there any drawbacks to using pessimistic locking?
A drawback of pessimistic locking is that you miss the flexibility of optimistic locking: when using pessimistic locking, everything needs to happen inside a single database transaction. This makes it less applicable to situations where you need to wait for user input or an expensive operation between retrieving and saving an object.
When to use DDL to block SELECT queries?
When you can have long-running SELECT queries on a table, don’t do this: By setting lock_timeout, the DDL command will fail if it ends up waiting for a lock, and thus blocking queries for more than 2 seconds. The downside is that your ALTER TABLE might not succeed, but you can try again later.
What happens when transaction B tries to acquire a lock?
If a transaction B tries to acquire a lock that is already held by transaction A with a conflicting lock level, then transaction B will wait in the lock queue.