What is SKIP LOCKED and how does it work?

SKIP LOCKED — is an option in SQL queries used when selecting data with row locking (for example, with FOR UPDATE). It allows skipping rows that are currently locked, without waiting for them to be released. This is useful in parallel processing scenarios where multiple transactions select and process data simultaneously, to avoid deadlocks and improve performance.

Example of usage in PostgreSQL:

SELECT * FROM tasks
WHERE status = 'new'
FOR UPDATE SKIP LOCKED;

In this example, the transaction acquires a lock on rows with tasks with the status "new", skipping those already locked by other transactions. Thus, different processes can process different tasks in parallel without waiting for each other.