Re: Row locks, SKIP LOCKED, and transactions

On 12/17/19 8:12 AM, Steven Winfield wrote:
> Hi all,
>
> I'm seeing some unexpected behaviour with SELECT ... FOR UPDATE SKIP LOCKED, and having finding it tricky to boil it down to a simple repro case as there's almost certainly a race condition somewhere (more later). So I thought I would ask if what I'm doing is unsupported (or just plain wrong!), before expending more effort in reproducing it.
>
> I'm running v11.5, RHEL 7.7.
>
> I have two tables jobs and results:
> CREATE TABLE job (id integer PRIMARY KEY);
> CREATE TABLE result (id integer PRIMARY KEY);
> (obviously the real tables have more columns, but that's not too important here)
>
> Something populates the job table with IDs.
> A job is done if its id appears in the result table.
> I would like to have multiple worker processes working on jobs.
>
>
> I thought I could achieve this with each working doing the following:
>
> BEGIN;
>
> SELECT id
> FROM job
> WHERE NOT EXISTS (SELECT 1 FROM result WHERE result.id = job.id)
> LIMIT 1
> FOR UPDATE SKIP LOCKED;
>
> -- worker process does some work for the selected ID here
>
> INSERT INTO result (id) VALUES (the_id_from_above);
>
> COMMIT;
>
>
> However, even with just two worker processes, I quickly found that one worker process would be assigned a job id that had *very* recently been completed by the other worker.
>
> Some more potentially useful information:
> * The LockRows node of the plan for the SELECT query above doesn't receive any tuples until about a second after the query begins executing
> * If worker 2 begins querying for a new job id half a second before worker 1 commits then worker 2 will pick up the job id that worker 1 has just finished with.
> * I observe this even if I crank up the transaction isolation level to repeatable read and serializable.
>
>
> I'm wondering if row locks are not obeying the same transactional semantics as row data, as a potential explanation for the above behaviour is as follows (W1/2 = Worker 1/2):
>
> W1: BEGIN;
> W1: SELECT ...
> W1: (SELECT returns id=1. W1 now has job(id=1) locked.)
> W1: INSERT INTO result (id) VALUES (1)
>
> W2: BEGIN;
> W2: SELECT ...
>
> W1: COMMIT; job(id=1) is now unlocked.
>
> W2: (SELECT returns id=1: W1 had not committed when the SELECT started, so result(id=1) is not visible, but LockRows found that job(id=1) was not locked. W2 now has job(id=1) locked.)
>
>
> ...i.e. W2's SELECT could not see the row that W1 INSERTed (because W2's BEGIN occurs and W2's SELECT begins before W1's commit), but W2's SELECT *could* see the removal of W1's row lock.

Gotta believe it is this:

https://www.postgresql.org/docs/11/transaction-iso.html#XACT-READ-COMMITTED

"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands ..."

If I read correctly, transactions can see the effects of other
transactions that commit during their lifetime.

>
>
> Perhaps this is a misuse of the locking system, since I'm locking a row "FOR UPDATE" but not actually updating it, but as row locks are released at the end of a transaction (according to the docs) then my expectation was for the unlocking and the visibility of newly committed rows to be atomic.
> I've tried FOR NO KEY UPDATE too, without luck.
>
> If I'm doing something forbidden (and the docs say so) then I'd be grateful if someone could point that out!
>
> Best,
> Steven.
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com