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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
