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