If you’ve landed on this post, you probably know what you are looking for, so here is how to skip rows locked in another transaction (PostgreSQL):

1BEGIN;
2
3SELECT * FROM urls
4FOR UPDATE
5SKIP LOCKED
6LIMIT 100;
7
8END;

The key to achieve such skip, is to first ensure that the rows to be queried will actually be locked, hence the FOR UPDATE clause, and then the SKIP LOCKED clause to prevent the query from waiting for the RDBMS to release the locks that another query might have acquired.

With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped. Skipping locked rows provides an inconsistent view of the data, so this is not suitable for general purpose work, but can be used to avoid lock contention with multiple consumers accessing a queue-like table - PostgreSQL Documentation: The Locking Clause

How was this useful to me?

I am working on a monitoring tool architected with the Leader-follower model where the service acting as the leader, and the service acting as a worker needs to be capable of working with multiple replicas of themselves. The leader needs to query data on an interval basis and publish events/messages to a queue to be consumed by the workers.

Due to the potential volume of data, and to prevent having a single point of failure I needed to foresee having multiple leaders running in a replicated fashion, which without proper concurrency control would have led to issues such as multiple leaders accessing the same rows for the same purpose.

That’s it for this post, thank you.