transactions - Bug in PostgreSQL locking mechanism or misunderstanding of the mechanism -

we encountered issue postgresql 9.0.12 locking mechanism.

this our minimal code reproduce issue:

scenario

transaction 1      transaction 2 begin              begin ......             select trees update;                 update apples;       --passes update apples;     -- stuck!       

reproduce code: if want try in postgresql - here code can copy/paste.

i have following db schema:

create table trees (     id       integer primary key );  create table apples (     id       integer primary key,     tree_id  integer references trees(id) );  insert trees values(1); insert apples values(1,1); 

open 2 psql shells:

on shell 1:

begin;     select id trees id = 1 update;     

on shell 2:

begin; update apples set id = id id = 1; update apples set id = id id = 1; 

the second update of apples stuck , seems porcess of shell 2 wating on transaction of shell 1 finish.

relname  |transactionid|procpid|mode              |substr                                    |       age      |procpid -----------+-------------+-------+------------------+------------------------------------------+----------------+-------            |             | 4911  | exclusivelock    | <idle> in transaction                    | 00:05:42.718051|4911            |   190839904 | 4911  | exclusivelock    | <idle> in transaction                    | 00:05:42.718051|4911 trees      |             | 4911  | rowsharelock     | <idle> in transaction                    | 00:05:42.718051|4911            |             | 5111  | exclusivelock    | update apples set id = id id = 1;  | 00:05:21.67203 |5111            |   190839905 | 5111  | exclusivelock    | update apples set id = id id = 1;  | 00:05:21.67203 |5111 apples_pkey|             | 5111  | rowexclusivelock | update apples set id = id id = 1;  | 00:05:21.67203 |5111 apples     |             | 5111  | rowexclusivelock | update apples set id = id id = 1;  | 00:05:21.67203 |5111 trees      |             | 5111  | rowsharelock     | update apples set id = id id = 1;  | 00:05:21.67203 |5111 trees      |             | 5111  | sharelock        | update apples set id = id id = 1;  | 00:05:21.67203 |5111            |             | 2369  | exclusivelock    | <idle> in transaction                    | 00:00:00.199268|2369            |             | 2369  | exclusivelock    | <idle> in transaction                    | 00:00:00.199268|2369            |             | 5226  | exclusivelock    | select pg_class.relname,pg_locks.transac | 00:00:00       |5226 

have misunderstood or bug in postgres?

there no bug, , don't think you're misunderstanding anything; you're missing couple of pieces of puzzle.

foreign keys implemented internally using row-level locking; starting postgres 8.1 , 9.2, whenever update referencing table (apples in case), query fired select share on referenced table (trees). select update in first transaction blocks select share of referential integrity second transaction. causes block in second command.

now hear yell, “wait! how come blocks on second command , not first? explanation simple, -- that's because there simple optimization skips internal select share when key not being modified. however, simplistic in if update tuple second time, optimization not fire because it's harder track down original values. hence blockage.

you might wondering why said 9.2 --- what's 9.3? main difference there in 9.3 uses select key share, new, lighter lock level; allows better concurrency. if try example in 9.3 , change select update select no key update (which lighter mode select update says maybe going update tuple, promise not modify primary key , promise not delete it), should see doesn't block. (also, can try update on referenced row , if don't modify primary key, not block.)

this 9.3 stuff introduced patch yours http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0ac5ad5134f2769ccbaefec73844f8504c4d6182 , think pretty cool hack (the commit message has more details, if care sort of stuff). beware, not use versions prior 9.3.4 because patch hugely complex few serious bugs went unnoticed , fixed recently.