Can you explain the purpose of using the SKIP LOCKED option when working with SQL queries?

The SKIP LOCKED option in SQL is used when working with row locks in transactions to skip already locked records and avoid waiting for their release. This is especially useful in systems with parallel task processing, such as implementing task queues in a database.

When multiple processes try to access the same rows simultaneously for processing, SKIP LOCKED allows one process to skip rows that are already locked by another process and only process available ones. This increases performance and prevents deadlocks.

Example of usage in PostgreSQL:

BEGIN;
SELECT * FROM tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;
-- process the task
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;

Here, the query selects one task with the status 'pending', locking it for the current transaction, but skips those already locked by other transactions.