I have multi threaded JEE application which run SELECT FOR UPDATE LIMIT 1; query on a Table with WHERE clause and Update row in each transaction, which create row-level write lock and do not block readers to read.
Is there any way to configure postgres, to stop readers to read rows with write lock?
On postgres 9.5+ there is the SKIP LOCKED option:
If you need to SELECT from a table and protect those rows from being updated until your transaction has completed, you would specify FOR UPDATE, but if some rows are locked, you can specify SKIP LOCKED to tell it to simply ignore those rows and just perform the operation on whichever rows it can access.
https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.5#SKIP_LOCKED
Here is an option for you if you are runnint Postgres <=9.4 where NOWAIT exists, but SKIP LOCKED. This demonstrates the used of CONTINUE and EXIT in a LOOP in order to identify the next available unlocked single row.
DO $$
DECLARE
r RECORD;
... -- your variables
BEGIN
FOR r IN
SELECT some_id
FROM some_table
WHERE ... -- your conditions
ORDER BY ... -- your ordering
LIMIT ... -- your limit
LOOP
BEGIN
SELECT ... -- your needed column(s)
INTO ... -- your defined variable(s)
FROM some_table -- the same table
WHERE some_id = r.some_id -- only check this one record
FOR UPDATE NOWAIT; -- don't wait for parallel transactions
EXIT;
EXCEPTION WHEN lock_not_available THEN
CONTINUE;
END;
END LOOP;
... -- do something based on variables, or move this before the EXIT above
END;
$$ LANGUAGE plpgsql;
