PostgreSQL Tutorial: SKIP LOCKED

July 29, 2024

Summary: In this tutorial, you will learn how to use SKIP LOCKED for avoiding and resolving deadlocks in PostgreSQL.

Table of Contents

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.

PostgreSQL SKIP LOCKED example

If you need to SELECT from a table and protect those rows from being updated until your transaction has completed, you would specify FOR UPDATE, but if some rows are locked, you can specify SKIP LOCKED to tell it to simply ignore those rows and just perform the operation on whichever rows it can access.

Here’s an example of how to use “SELECT … FOR UPDATE SKIP LOCKED”:

In session 1:

BEGIN;

SELECT * FROM colours;
 id | name
----+-------
  1 | red
  2 | green
  3 | blue
(3 rows)

UPDATE colours SET name = 'scarlet' WHERE name = 'red';

In session 2:

BEGIN;

SELECT * FROM colours FOR UPDATE NOWAIT;
ERROR:  could not obtain lock on row in relation "colours"

SELECT * FROM colours FOR UPDATE SKIP LOCKED;
 id | name
----+-------
  2 | green
  3 | blue
(2 rows)

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.

Search

Categories

Tags