FOR UPDATE NOWAIT doesn't work

Question

There are two queries:

1.

BEGIN;
SELECT * FROM "Users"
WHERE "Users"."Name" = 'user0'
FOR UPDATE NOWAIT ;

select pg_sleep(30);

END;

2.

BEGIN;
UPDATE "Users"
SET "Respect" = 2
WHERE "Users"."Name" = 'user0';
END;

As I use NOWAIT I expect that the second query will return error but it doesn't work for me. The second query just hangs and waits for the first one. What did I miss? I tried on 9.2-9.4 Postgres.

Answer 1

NOWAIT affects the statement it's in. It doesn't affect other statements that want to wait rather than reporting an error.

To have your second query immediately raise an error if it can't lock rows, you'll need to rewrite it to use select ... for update nowait, then update.... The update statement alone doesn't have a nowait option.

BEGIN transaction;
select "Respect" from "Users" where "Name" = 'user0' for update nowait;

UPDATE "Users"
SET "Respect" = 2
WHERE "Users"."Name" = 'user0';
END;