Thread: Avoid deadlocks on alter table

On 07/05/2016 06:30 AM, Christian Castelli wrote:
> Hi everybody,
> my database is composed of multiple schemata, one for each customer, and
> some global views which do UNION across schemata.
> I create a new customer with a single transaction, with queries like:
>
> CREATE TABLE table1 WITHOUT OIDS AS
> TABLE base_template.table1 WITH NO DATA;
>
> base_template is just an empty schema used as template. The very first
> statement is:
>
> CREATE SCHEMA :CUSTOMER_SCHEMA AUTHORIZATION user;
> SET search_path TO :CUSTOMER_SCHEMA, public;
>
> So every following statement doesn't need to be prefixed with schema. At
> some point I receive the following error:
>
> Caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
>   Detail: Process 385 waits for AccessExclusiveLock on relation 17248 of
> database 16385; blocked by process 18854.
> Process 18854 waits for AccessShareLock on relation 17016 of database
> 16385; blocked by process 385. Hint: See server log for query details.

Does the server log have the details, per above?

>
> Process 385 seems to be the last executed statement:
>
> ALTER TABLE smartphone
>   ADD CONSTRAINT pk_smartphone PRIMARY KEY (id),
>   ADD CONSTRAINT fk1 FOREIGN KEY (id_contact)
>       REFERENCES contact (id) MATCH SIMPLE
>       ON UPDATE RESTRICT ON DELETE RESTRICT,
>   ADD CONSTRAINT fk_plan FOREIGN KEY (id_tf)
>       REFERENCES public.tariff_plan(id) MATCH SIMPLE
>       ON UPDATE RESTRICT ON DELETE RESTRICT,
>   ADD CONSTRAINT fk_ram FOREIGN KEY (ret_id)
>       REFERENCES ram (id) MATCH SIMPLE
>       ON UPDATE RESTRICT ON DELETE RESTRICT,
>   ADD CONSTRAINT u_imei UNIQUE (imei_code);
>
> relation 17248 is tariff_plan and 17016 is customers, both only in
> public schema (shared tables).
> I cannot understand why altering a table in a customer schema bumps into
> a deadlock with these two tables. Any hints how to solve this problem?

Does the table smartphone have data in it when you do the above ALTER?

I would say the issue is with the FKs fk_plan and fk1. The link back to
tariff_plan in fk_plan is explicit. I am guessing fk1 --> contacts leads
to a link contacts --> customers?

Have you looked at separating the FK creation and validation?:

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

"ADD table_constraint [ NOT VALID ]

     This form adds a new constraint to a table using the same syntax as
CREATE TABLE, plus the option NOT VALID, which is currently only allowed
for foreign key and CHECK constraints. If the constraint is marked NOT
VALID, the potentially-lengthy initial check to verify that all rows in
the table satisfy the constraint is skipped. The constraint will still
be enforced against subsequent inserts or updates (that is, they'll fail
unless there is a matching row in the referenced table, in the case of
foreign keys; and they'll fail unless the new row matches the specified
check constraints). But the database will not assume that the constraint
holds for all rows in the table, until it is validated by using the
VALIDATE CONSTRAINT option.
"

....

"VALIDATE CONSTRAINT

     This form validates a foreign key or check constraint that was
previously created as NOT VALID, by scanning the table to ensure there
are no rows for which the constraint is not satisfied. Nothing happens
if the constraint is already marked valid.

     Validation can be a long process on larger tables. The value of
separating validation from initial creation is that you can defer
validation to less busy times, or can be used to give additional time to
correct pre-existing errors while preventing new errors. Note also that
validation on its own does not prevent normal write commands against the
table while it runs.

     Validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table
being altered. If the constraint is a foreign key then a ROW SHARE lock
is also required on the table referenced by the constraint.
"

> Thanks.
>
>
> --
> /Christian Castelli
> skype:  christrack/


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 07/05/2016 06:30 AM, Christian Castelli wrote:
>> ALTER TABLE smartphone
>> ADD CONSTRAINT pk_smartphone PRIMARY KEY (id),
>> ADD CONSTRAINT fk1 FOREIGN KEY (id_contact)
>> REFERENCES contact (id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT fk_plan FOREIGN KEY (id_tf)
>> REFERENCES public.tariff_plan(id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT fk_ram FOREIGN KEY (ret_id)
>> REFERENCES ram (id) MATCH SIMPLE
>> ON UPDATE RESTRICT ON DELETE RESTRICT,
>> ADD CONSTRAINT u_imei UNIQUE (imei_code);

> Does the table smartphone have data in it when you do the above ALTER?

I doubt it would matter.  The problem with this is that it needs to take
exclusive lock on each one of the referenced tables (so as to add a new
foreign-key enforcement trigger).  So any other transaction that is
accessing any two of those tables in a different order than this does
creates a deadlock hazard.

> Have you looked at separating the FK creation and validation?:

I think it'd likely be enough to add the FKs one at a time, rather
than all in one transaction.

            regards, tom lane