CODEWITHSUNDEEP
postgresqltransactionslocking
oldhomemovie
oldhomemovie

Reputation: 15129

Concurrent access to rows: making locked rows "invisible"

TL;DR. I'd like to make locked rows invisible for subsequent queries, until the lock is released.

I'm working on an application that relies on concurrent access to data.

I have data that I need to process, and thousands of SQL queries that need to run over that same set of data. On the application level I serialize all SQL queries into a single big queue, and execute them in transactions, one by one.

I would like to, however, remove this artificial "queue" bottleneck I made on the application level, relying fully on DB's mix of built-in concurrency primitives & cursor based data access.

For this I need to make sure two concurrent SQL queries never fetch the same set rows, e.g. former query locks rows and later query ignores locked rows.

I'd like the following picture to work (example is simplified by intention; consider each SELECT to run in a transaction, using cursor-based access).

SELECT * FROM my_table ORDER BY id LIMIT 5; -- returns rows with IDs 1, 2, 3, 4, 5
SELECT * FROM my_table ORDER BY id LIMIT 5; -- returns rows with IDs 6, 7, 8, 9, 10

My question is: is this even possible to do? Or am I asking too much from DB?

Upvotes: 0

Views: 58

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246413

With PostgreSQL 9.5 or later, you can do this within a transaction:

SELECT * FROM my_table
ORDER BY id
LIMIT 5
FOR UPDATE SKIP LOCKED;

Upvotes: 2

Related Questions