Robert Haas wrote: > Just for kicks I tried this out and the behavior is as the OP > describes: after a little poking around, it sees that the INSERT grabs > a share-lock on the referenced row so that a concurrent update can't > modify the referenced column. > > It's not really clear how to get around this. If it were possible to > lock individual columns within a tuple, then the particular update > above could be allowed since only the name is being changed. Does > anyone know what happens in Oracle if the update targets the id column > rather than the name column?
I have investigated what Oracle (10.2) does in this situation. First the original sample as posted by wangyuxiang: insert into tb_a(id,b_id) values('a1','b1'); will place a ROW EXCLUSIVE lock on tb_a, an EXCLUSIVE lock on the row that was inserted and a ROW SHARE lock on tb_b. No lock on any row in the parent table is taken. update tb_b set name='changed' where id='b1'; will place a ROW EXCLUSIVE lock on tb_b and an EXCLUSIVE lock on the modified column. Since ROW EXCLUSIVE and ROW SHARE do not conflict, both statements will succeed. Now to your question: update tb_b set id='b2' where id='b1'; This will place a ROW EXCLUSIVE lock on tb_b, an EXCLUSIVE lock on the updated row and a SHARE lock on tb_a. This last lock is only held for the duration of the UPDATE statement and *not* until the end of the transaction. So this update will block, because the SHARE and the ROW EXCLUSIVE lock on tb_a are incompatible. So it seems that Oracle handles this quite differently. I was particularly surprised that it uses locks that are not held until end-of-transaction. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance