I am trying to create a deadlock in my postgreSQL-database but the error I'm getting is not really the one I would expect:
ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02
My setup is basically a mix of these two:
I opened two browser windows of pgAdmin 4 and have two query windows. One with the following:
BEGIN; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 1 RETURNING *; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 2 RETURNING *; COMMIT;
In the other is use this one:
BEGIN; UPDATE "Products" SET "Price" = "Price" * 3 WHERE "PID" = 2 RETURNING *; UPDATE "Products" SET "Price" = "Price" * 0.03 WHERE "PID" = 1 RETURNING *; COMMIT;
My intention was to execute both as close to each other as possible but I got that error. Strangely even when I just try to run them single the same error persists. I can't make much of that error code. What could it be?
The table itself is as basic as it gets:
CREATE TABLE IF NOT EXISTS "Products" ( "PID" integer NOT NULL, "Name" character varying(255), "Price" double precision, "Stock" integer, CONSTRAINT "Products_pkey" PRIMARY KEY ("PID") )
The answers I found online are not particularly helpful. Like this one from here:
This log event happens when a transaction fails due to a potentially unrelated error, and you try to run another query in the failed transaction.
So I have tried to simply do :
BEGIN; Select "Price" from "Products" COMMIT;
And receive the same error. Without the transaction block it works