Deadlock & Resolving Deadlocks with “SKIP LOCKED” in PostgreSQL
Deadlocks are a common challenge for database-driven applications, including those that use PostgreSQL. In this blog post, we will discuss strategies for avoiding and resolving deadlocks in your PostgreSQL database.
Transaction
Transactions are the fundamental building blocks of database-driven applications. When a transaction starts, it can read and modify data in the database. When it modifies data, it obtains a lock on the resources such as rows or tables that it is changing. This lock prevents other transactions from modifying the same resources at the same time, ensuring that changes to the data are made in a consistent and predictable manner.
However, if two transactions attempt to access or modify the same resource simultaneously, they can end up in a deadlock situation, where neither transaction can proceed until the other releases its locks.
Best Practices
To avoid deadlocks, it’s important to follow some best practices when designing and implementing transactions:
- Avoid locking resources for long periods: Long transactions and long-running locks can increase the likelihood of deadlocks. Try to keep transactions short and avoid holding locks for extended periods.
- Optimise your queries: The queries of a transaction should be optimal and try to perform operations on required rows only. This reduces the rows being locked by the transaction and allows other transactions to access these rows.
- Ensure updates occur in a consistent order: Make sure that updates to related resources occur in a consistent order.
- Avoid explicit locks and table-level locks: As much as possible, avoid using explicit locks or table-level locks, as a table-level lock restricts any actions on an entire table and prevents any other transaction from performing any queries on it.
Resolve Deadlock
If you encounter a deadlock, the first step in resolving it is to analyse the situation and identify the transactions involved, the resources they are contending for, and the sequence of events that led to the deadlock. You can use the PostgreSQL log files or query the pg_stat_activity system view to gather this information. You can use this information to identify the queries and resources involved in the deadlock.
Once you’ve identified the queries and resources involved, you can examine them to determine if there are any optimisations that can be made to reduce the likelihood of future deadlocks based on best practices.
Use of “Select … FOR UPDATE SKIP LOCKED”
One useful syntax is “SELECT … FOR UPDATE SKIP LOCKED.” This syntax is particularly useful in situations where multiple transactions are trying to update the same set of rows simultaneously. It locks the selected rows but skips over any rows already locked by other transactions, thereby reducing the likelihood of deadlocks.
Here’s an example of how to use “SELECT … FOR UPDATE SKIP LOCKED”:
BEGIN -- the select query skips any row that's currently locked by another transaction.
SELECT * FROM employees WHERE id > 100 FOR UPDATE SKIP LOCKED; -- the update query only updates the records locked by the above query.
UPDATE employees SET name = 'New Name' WHERE id > 100;
COMMIT;
When using the SKIP LOCKED option with SELECT … FOR UPDATE, there are a few things to keep in mind.
- SKIP LOCKED should be used only in situations where multiple transactions may attempt to lock the same rows simultaneously, and where it’s acceptable for some transactions to skip over locked rows.
- Skipping locked rows can potentially lead to data inconsistencies if not used carefully. Make sure that your application logic can handle situations where certain rows are skipped over.
Alternatively, we can use the NOWAIT option with SELECT.. FOR UPDATE. With the NOWAIT option, if a selected row cannot be locked immediately, the statement will immediately report an error instead of waiting. It requires proper error handling logic in the application.
Summary
By implementing best practices when designing and implementing transactions and monitoring your application’s performance, you can avoid and resolve deadlocks in your PostgreSQL database. While using techniques such as “SELECT … FOR UPDATE SKIP LOCKED” be sure to use it carefully and test your application thoroughly.
Resources
Deadlock: https://www.postgresql.org/docs/15/explicit-locking.html#LOCKING-DEADLOCKS
Explicit Locking: https://www.postgresql.org/docs/15/explicit-locking.html
Also the PostgreSQL documentation on ‘Concurrency Control’ is a valuable resource for understanding managing concurrent access to DB.
You can find the documentation at https://www.postgresql.org/docs/15/mvcc-intro.html
