Re: [GENERAL] Locking & concurrency - best practices

> You should be able to do "select for update" on both parent and child
> records and get the effect you desire.
>

I don't think that will work. Let me demonstrate: (this is simplified, but sufficient to make my point)

• Connection 1 -- begin trans;

select * from parent_tbl
where id=1 for update;

select count(*) into myvar
from data_tbl where fk=1;

• connection 2 runs here (see below) --

if (myvar < 3) then

   update parent_tbl
   set status=1 where id=1;
else

   update parent_tbl
   set status=2 where id=1;
end if;

commit;

• Connection 2 --

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

begin trans;

insert into data_tbl (fk, data) values (1, 'foo');
insert into data_tbl (fk, data) values (1, 'bar');
insert into data_tbl (fk, data) values (1, 'baz');

• End example --

In what way would you use "FOR UPDATE" on data_tbl to ensure parent_tbl doesn't end up with the wrong status ? AFAIK, "FOR UPDATE" locks only the rows returned, and does nothing to prevent new inserts. using a "serialized" isolation doesn't seem appropriate either. As far as I can tell, the only options are locking the entire data_tbl at the start of both connections (which unfortunately also blocks all other transactions with id/fk != 1), or using advisory locks.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match