Index Locking Order is important

Поиск
Список
Период
Сортировка
От PG Doc comments form
Тема Index Locking Order is important
Дата
Msg-id 160069274124.14061.4149200726948746846@wrigleys.postgresql.org
обсуждение исходный текст
Список pgsql-docs
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/index-unique-checks.html
Description:

When there are multiple unique indexes on a table, at least one that allows
NULL, the order of the locks are important.  This should be explained in the
documentation.

Consider two tables:

create table foo(id1 bigint primary key, id2 bigint unique);
and
create table bar (id1 bigint not null, id2 bigint unique);

With indexes create in this order:
create unique index bar_id2 on bar(id2);
create unique index zbar_id1 on bar(id1);
alter table bar add primary key using index zbar_id1;

One might expect foo and bar to behave in identical ways.
However, if one opens two connection and does
BEGIN;
insert into bar(id1) values (1);
-- Do not commit, switch to second connection
BEGIN;
insert into bar(id1, id2) values (1, 2); -- This blocks as it should
-- switch back to the first connection
update bar set id2=2 where id1=1;
commit;

One of the processes should get something like

ERROR:  deadlock detected
DETAIL:  Process 6888 waits for ShareLock on transaction 142875965; blocked
by process 6897.
Process 6897 waits for ShareLock on transaction 142875736; blocked by
process 6888.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (0,3) in relation "bar_id2_key"

If one does the same thing on foo, process 2 will get
ERROR:  duplicate key value violates unique constraint "foo_pkey"
DETAIL:  Key (id1)=(1) already exists.

Which is what I would expect from both tables.

At a minimum, index locking order should be documented and explained. 

It may also be desirable to have a configuration option to switch the index
locking order to alphabetical by name to make it easy to control like
triggers.

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Please provide examples of rows from
Следующее
От: PG Doc comments form
Дата:
Сообщение: Add support files