4

I'm creating multiple tables asynchronously when I get deadlock detected. Both tables (Table A & B) have FK to one table (Table C) and I get deadlock on relation to Table C.

Scripts

CREATE TABLE "TableA"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableA_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableA_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

CREATE TABLE "TableB"
(
  "Id" uuid NOT NULL,
  "TableCId" uuid NOT NULL,
  CONSTRAINT "PK_TableB_Id" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_TableB_TableC" FOREIGN KEY ("TableCId")
      REFERENCES "TableC" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

This table exists in moment of above scripts execution

CREATE TABLE "TableC"
(
  "Id" uuid NOT NULL,
  CONSTRAINT "PK_TableC_Id" PRIMARY KEY ("Id"),
)
WITH (
  OIDS=FALSE
);

So when I take a look at the logs I get the following error, and relation 87066 is the TableC. Note that TableA & TableB are created in separate transaction using the READ COMMITTED isolation.

Process 464 waits for AccessExclusiveLock on relation 87066 of database 86965; blocked by process 1180. Process 1180 waits for AccessExclusiveLock on relation 87066 of database 86965; blocked by process 464.

When I take a look at PostgreSQL documentation it says that one should execute create tables in same order in order to avoid the deadlocks and they are executed in the same order so no deadlock should occur.

Is there any reason why this ends with deadlock? I think that process 464 should wait for 1180 if 464 was the first one to execute ?

Edit

One other note is that I'm running one insert in the same transaction before create table script.

INSERT INTO public."TableD"("Id", "TableCId", ....)
VALUES (:p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)

PostgreSQL 9.3

5
  • May be related (and unanswered, too): dba.stackexchange.com/questions/66445/…
    – dezso
    Nov 5, 2014 at 9:54
  • Yes I see similar issue there, I asked the same question on StackOverflow stackoverflow.com/questions/26753053/… and managed to solve the issue with EXCLUSIVE LOCK on TableC at the beginning of the transaction but I still don't know why a deadlock ?
    – khorvat
    Nov 5, 2014 at 10:02
  • 1
    Are you sure there isn't a 3rd operation (besides create table A & create table B) that would need to acquire a lock on either A,B or C? Can you reproduce the deadlock in two parallel psql sessions? Nov 5, 2014 at 12:31
  • There is one as per my edit notes, but I'm not sure if simple insert can acquire a lock that can later on deadlock everything.
    – khorvat
    Nov 5, 2014 at 13:21
  • @khorvat you should probably flag your post on SO for migration to here, then the two posts will be merged and all collected information seen in one place.
    – dezso
    Nov 5, 2014 at 16:09

1 Answer 1

6

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends.

Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC".

Later on, in order to do a CREATE TABLE involving a foreign key to "TableC", T1 must acquire an access exclusive lock on "TableC".

Since T2 has already a shared lock on "TableC", T1 will be put to wait until T2 commits or rollbacks.

At this point, if T2 tries to create a table also involving a foreign key to "TableC" (which again implies acquiring an access exclusive lock on "TableC"), it must wait for T1 to commit or rollback, because T1 holds a shared lock on "TableC".

So now T1 is waiting for T2 to finish and T2 is waiting for T1 to finish: there's the deadlock.

To avoid this situation, an explicit and exclusive lock may be taken at the beginning of the transaction, or at least at the beginning of the sequence of instructions that can't be run safely in parallel by concurrent transactions.

1
  • Well put, @CraigRinger mentioned that on my cross-post on StackOverflow but you explained it in detail.
    – khorvat
    Nov 6, 2014 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.