POSTGRES DEADLOCK UPDATE
I would just expect one of the processes to fail when trying to update the Parent row. Running these two simultaneously would result in the error above.Īt first it seems a little weird that this scenario gives a deadlock. UPDATE "Parent" SET "Name"='Parent_C' WHERE "ID"=1 INSERT INTO "Child" VALUES (2, 1, 'CHILD_B') UPDATE "Parent" SET "Name"='Parent_B' WHERE "ID"=1 INSERT INTO "Child" VALUES (1, 1, 'CHILD_A') (Assume the Parent has columns: ID, Name, and the Child has columns: ID, Parent_ID, Name) After each insert we will also try to update the name of the parent. Given 2 tables, Parent and Child, we will try to add two children to the same parent from two different processes. These are usually hard to reproduce, and not simple to solve.Īfter reading a little about the different locks in Postgres and when they are used I came up with the following simple scenario which reproduces this error: Usually for me a deadlock error is a bad way to start the day. Process 4924 waits for ShareLock on transaction 689 blocked by process 552. Of database 12002 blocked by process 4924.
This would look something like that:ĭETAIL: Process 552 waits for ExclusiveLock on tuple (0,3) of relation 16393
The general setting we are talking about is Java, Spring-Data, Hibernate, Postgres, though the main issue is the Postgres DB. What better way to do that than through a blog. It took me some time to figure out the exact situation that was causing this deadlock, and so I decided to share what I've learned. So I ran a few days ago into a pretty weird deadlock error in my logs.
0 kommentar(er)
