Re: foreign key constraint lock behavour in postgresql

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: foreign key constraint lock behavour in postgresql
Дата
Msg-id D960CB61B694CF459DCFB4B0128514C20393814A@exadv11.host.magwien.gv.at
обсуждение исходный текст
Ответ на Re: foreign key constraint lock behavour in postgresql  (Robert Haas <robertmhaas@gmail.com>)
Ответы Re: foreign key constraint lock behavour in postgresql  (Robert Haas <robertmhaas@gmail.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Glenn Maynard
Дата:
Сообщение: Re: Slow query: table iteration (8.3)
Следующее
От: Yeb Havinga
Дата:
Сообщение: Re: Slow query: table iteration (8.3)