Postgresql: неявное получение блокировки из оценки ограничения внешнего ключа

Итак, меня смущает обработка ограничений внешнего ключа в Postgresql. (версия 8.4.4, чего стоит).

У нас есть пара таблиц, слегка анонимизированных ниже:

device:
   (id, blah, blah, blah, blah, blah x 50)…
   primary key on id
   whooooole bunch of other junk

device_foo:
   (id, device_id, left, right)
   Foreign key (device_id) references device(id) on delete cascade;
   primary key on id
   btree index on 'left' and 'right'

Поэтому я отправился с двумя окнами базы данных, чтобы выполнить несколько запросов.

db1>  begin; lock table device in exclusive mode;
db2>  begin; update device_foo set left = left + 1;

Соединение с db2 блокируется.

Мне кажется странным, что на обновление «левого» столбца в device_stuff должна влиять активность в таблице устройств. Но это. Фактически, если я вернусь к db1:

db1> select * from device_stuff for update;
          *** deadlock occurs ***

В журнале pgsql есть следующее:

blah blah blah deadlock blah.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."device" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF X: update device_foo set left = left + 1;

Я предполагаю, что у меня есть две проблемы: первая заключается в том, что я не понимаю точного механизма, с помощью которого происходит такая блокировка. У меня есть пара полезных запросов для запроса pg_locks, чтобы узнать, какие блокировки вызывает оператор, но я не смог наблюдать этот конкретный вид блокировки, когда запускал команду update device_foo изолированно. (Возможно, я делаю что-то не так.) Я также не могу найти документацию о поведении получения блокировки при проверках ограничений внешнего ключа. Все, что у меня есть, это сообщение журнала. Должен ли я сделать из этого вывод, что любое изменение в строке получит блокировку обновления для всех таблиц, для которых оно является внешним ключом?

Вторая проблема заключается в том, что я хотел бы найти способ сделать так, чтобы этого не происходило. Я получаю случайные взаимоблокировки в реальном приложении. Я хотел бы иметь возможность запускать большие операторы обновления, которые влияют на все строки в device_foo, не прибегая к большой блокировке таблицы устройств. (В таблице device происходит множество операций доступа, и получить такую ​​блокировку довольно дорого.)


postgresql foreign-keys deadlock locking database-deadlocks
person Community    schedule 14.06.2010    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Оператор lock table device in exclusive mode устанавливает очень ограничительную блокировку таблицы ("эксклюзивный режим" ). Изменение таблицы с внешним ключом в родительской таблице приводит к довольно безобидной блокировке общего доступа к родительской таблице (например, вы не можете усечь таблицу, пока строки, ссылающиеся на нее, потенциально обновляются).

На самом деле, пробуя сейчас, я не могу воспроизвести ваше поведение блокировки (на 8.4.4, как у вас). Я сделал:

create table device(device_id serial primary key, value text not null);
create table device_foo(device_foo_id serial primary key, device_id int not null references device(device_id) on delete cascade, value text not null);
insert into device(value) values('FOO'),('BAR'),('QUUX');
insert into device_foo(device_id, value) select device_id, v.value from (values('mumble'),('grumble'),('fumble')) v(value), device;

И затем в двух одновременных соединениях я сделал:

<1>=# begin; lock table device in exclusive mode;
<2>=# begin; update device_foo set value = value || 'x';

Мне кажется, что это эквивалентно тому, что вы делаете, но я не получаю блокировку второго сеанса - он сразу же дает «ОБНОВЛЕНИЕ 9», как и ожидалось. Вставка в блоки device_foo, как и следовало ожидать, то же самое делает оператор обновления, устанавливающий столбец device_id. Я вижу ExclusiveLock в pg_locks из сеанса db1 в сеансе db2. Он также блокируется, если я делаю «выбрать * с устройства для общего доступа», что является утверждением, которое вы видите в ошибке взаимоблокировки. Я также не получаю тупиковой ситуации, если я делаю «выбрать * из device_foo для обновления» из соединения db1, в то время как db2 заблокирован, пытаясь обновить столбец device_id в device_foo.

Обновление строки помечает строку как заблокированную, но эта блокировка не отображается в pg_locks. Также требуется блокировка таблицы, чтобы заблокировать любого, кто пытается удалить/обрезать/переиндексировать таблицу, пока одна из ее строк обновляется.

Чтобы заблокировать таблицу device от одновременных обновлений, вам может понадобиться менее строгий режим блокировки. В руководстве для такого рода Мероприятия. Хотя это всего на один уровень ниже «эксклюзивного», оно совместимо с оператором «выбрать... для совместного использования».

Так что на самом деле открытый вопрос: что выдает этот запрос «выбрать ... для общего доступа»? :-S Это похоже на утверждение, предназначенное для подтверждения целостности внешнего ключа, но я не могу его воспроизвести.

person araqnid    schedule 14.06.2010
comment
Очень интересно, что такое поведение не кажется нормой для Postgres. (У меня сложилось впечатление, что это так). Так что я посмотрю, смогу ли я перевернуть столы и посмотреть, что заставляет их тикать. И да, «эксклюзивный режим», который я использовал, был предназначен для изоляции проблемы, на самом деле, это не то, что мне нравится делать регулярно. Настоящие проблемы возникают, когда операторы update конфликтуют с другими блокировками, разбросанными вокруг device. - person ; 18.06.2010

arrow_upward
0
arrow_downward

Блокировка таблицы в эксклюзивном режиме означает, что ни один процесс не может прочитать эту таблицу, а проверка внешнего ключа требует чтения табличного устройства.

person Szymon Lipiński    schedule 14.06.2010
comment
Но зачем ему проверять ограничения внешнего ключа? Я не меняю столбцы внешнего ключа. - person ; 15.06.2010