The following bug has been logged on the website:
Bug reference: 6608
Logged by: Duncan Burke
Email address: [email protected]
PostgreSQL version: 9.1.3
Operating system: gentoo
Description:
I found that running a SELECT FOR UPDATE query in a CTE does not block
simultaneous transactions from running the same query. i.e it appears to not
be obtaining an exclusive row lock as expected.
CREATE TABLE foo (
x int PRIMARY KEY,
y int
);
INSERT INTO foo VALUES (0,0);
CREATE FUNCTION lock_0(int) returns int as $$
WITH locked as (
SELECT 1 FROM foo
WHERE x = $1
FOR UPDATE)
SELECT 1
$$ LANGUAGE SQL;
CREATE FUNCTION lock_1(int) returns int as $$
WITH locked as (
UPDATE FOO
SET y = y
WHERE x = $1)
SELECT 1
$$ LANGUAGE SQL;
--run in two simultaneous transactions, lock_0 does not block
BEGIN;
SELECT lock_0(0);
COMMIT;
--run in two simultaneous transactions, lock_1 blocks
BEGIN;
SELECT lock_1(0);
COMMIT;
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
