Mastering Deadlock Resolution in PostgreSQL: Unleash the Power of SKIP LOCKED

Vineet Stewart Lasrado

@VineetLasrado

• The Challenge of Concurrency: In modern database systems, multiple transactions often try to access and modify the same data simultaneously.
• Locking Mechanisms: Databases employ locking mechanisms to ensure data consistency and prevent race conditions.
• Traditional Locking Issues: While essential, traditional locking can lead to:
• Blocking: One transaction waits for another to release a lock.
• Deadlocks: Two or more transactions are blocked indefinitely, waiting for each other.
• Performance Bottlenecks: High contention can significantly reduce throughput.
• Introducing UPDATE SKIP LOCKED
• Data Retrieval (SELECT)
• Modification (UPDATE)

@VineetLasrado

​

Introduction

Behavior with SELECT:

• When a SELECT ... FOR UPDATE SKIP LOCKED statement is executed, the database attempts to acquire an exclusive lock on the rows matching the WHERE clause.
• If a matching row is already locked by another transaction, instead of waiting, the database skips that row and moves on to the next eligible row.
• The SELECT statement will only return and lock the rows it successfully acquires a lock on.

Behavior with UPDATE:

​

• The transaction performing the UPDATE SKIP LOCKED will only operate on the rows it successfully acquires a lock on.

Key Advantage: Reduces blocking and potential deadlocks in high-concurrency scenarios for both reading and writing operations.

​

@VineetLasrado

​

Behavior

-- For SELECT statements:�SELECT column1, column2

FROM table_name�WHERE condition�FOR UPDATE SKIP LOCKED [LIMIT count];�

�-- For UPDATE statements:�UPDATE table_name�SET column1 = value1,� column2 = value2,� ...�WHERE condition�SKIP LOCKED [LIMIT count];

​

@VineetLasrado

​

Syntax

• Consider a system where multiple worker processes are responsible for picking up and processing tasks from a shared task queue. Each task is represented as a row in a database table with a status (e.g., 'PENDING', 'PROCESSING', 'COMPLETED'). Workers need to find and claim pending tasks for processing.
• Traditional Approach (without SKIP LOCKED)
• If multiple workers execute this simultaneously, they might all try to lock the same pending task, leading to one or more workers being blocked until the initial lock is released. This blocking reduces the efficiency of task distribution.
• SELECT SKIP LOCKED Solution.
• When multiple workers execute this, each will attempt to find a pending task. If a task is already locked by another worker, it will be skipped, and the worker will move on to the next available pending task.

@VineetLasrado

​

Use Case of SELECT SKIP LOCK

CREATE TABLE tasks (� id SERIAL PRIMARY KEY,� description TEXT,� status VARCHAR(20) DEFAULT 'PENDING',� priority INTEGER DEFAULT 1�);��INSERT INTO tasks (description, priority) VALUES�('Process high priority email', 1),�('Generate daily reports', 2),�('Update user profiles', 2),�('Analyze website traffic', 3),�('Backup database', 3);

​

Prepare Two Database Sessions: Open two separate database client connections to your PostgreSQL database (e.g., using psql in separate terminal windows or two query editor tabs in a database management tool like pgAdmin).

​

@VineetLasrado

​

Demonstration

Terminal Session 1: In the first database session, execute the following commands to start a transaction and select and lock a pending task:

BEGIN;

​

SELECT id, description

FROM tasks

WHERE status = 'PENDING'

ORDER BY priority

LIMIT 1

FOR UPDATE;

-- Note the 'id' of the locked task that is returned (e.g., 1).

Terminal Session 2: In the second database session, within a new transaction, try to select and lock a pending task using the same query without SKIP LOCKED:

BEGIN;

�SELECT id, description

FROM tasks

WHERE status = 'PENDING'

ORDER BY priority

LIMIT 1

FOR UPDATE;

​

Observation: You will likely observe that the query in Session 2 will hang, waiting for the lock held by the transaction in Session 1 to be released. This demonstrates the blocking behavior that can occur when multiple transactions try to lock the same or overlapping sets of rows without SKIP LOCKED.

Session 1: In the first session, commit the transaction to release the lock:

COMMIT;

​

Observation: Once the transaction in Session 1 is committed, the query in Session 2 will likely proceed, selecting and locking a pending task.

​

@VineetLasrado

​

Demonstration - Part 1 (Without SKIP LOCKED - SELECT FOR UPDATE)

​

Reset the tables: UPDATE tasks SET status = 'PENDING';

Terminal Session 1: In the first session, execute the SELECT ... FOR UPDATE SKIP LOCKED statement:

BEGIN;�SELECT id, description �FROM tasks �WHERE status = 'PENDING' �ORDER BY priority LIMIT 1 �FOR UPDATE SKIP LOCKED;�-- Note the 'id' of the selected and locked task.��SELECT * FROM tasks �WHERE id = (SELECT id FROM tasks WHERE status = 'PENDING' ORDER BY priority LIMIT 1 FOR UPDATE SKIP LOCKED);�-- DO NOT COMMIT this transaction yet.

Terminal Session 2: In the second database session, execute the same SELECT ... FOR UPDATE SKIP LOCKED statement

BEGIN;�SELECT id, description FROM tasks �WHERE status = 'PENDING' �ORDER BY priority LIMIT 1 �FOR UPDATE SKIP LOCKED;�-- Note the 'id' of the selected and locked task in this session. It will likely be a different task if one was successfully locked in Session 1.��SELECT * FROM tasks �WHERE id = (SELECT id FROM tasks WHERE status = 'PENDING' ORDER BY priority LIMIT 1 FOR UPDATE SKIP LOCKED);�COMMIT;

​

@VineetLasrado

​

Demonstration - Part 2 (With SKIP LOCKED - SELECT FOR UPDATE SKIP LOCKED)

​

Terminal Session 1: Now, commit the transaction in the first session:

COMMIT;

​

Observation: You should observe that both sessions were able to successfully select and lock different pending tasks without blocking each other. The SKIP LOCKED clause in each session allowed it to bypass any rows that were already locked by the other session, enabling concurrent access to available tasks.

​

@VineetLasrado

​

Demonstration - Part 2 (With SKIP LOCKED - SELECT FOR UPDATE SKIP LOCKED)

​

• High Concurrency Scenarios
• Work Queue Implementations
• Real-time Processing

​

• Improved Concurrency: By allowing transactions to skip locked rows, the overall level of concurrency in the system is increased, as more transactions can proceed simultaneously.
• Increased Throughput: More work can be processed in a given unit of time because transactions spend less time waiting for locks to be released.
• Reduced Latency: The response time for individual transactions can be significantly reduced as they are less likely to be blocked by other long-running operations.
• Simplified Application Logic: In some cases, using SKIP LOCKED can simplify the application logic required to handle concurrent access, reducing the need for complex retry mechanisms or manual lock management.
• More Efficient Resource Utilization: Worker processes or database connections spend less time idle waiting for locks and more time actively processing data, leading to better utilization of system resources.

​

@VineetLasrado

​

When to Use SKIP LOCKED?

Benefits of SKIP LOCKED

Here

SCAN

@hackersmang