4

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

1 Answer 1

12

This error occurs when a previous query has failed and the client still issues queries in that transaction. The only thing to do in that state is a ROLLBACK.

It's not particularly tied to deadlocks or even having two concurrent sessions.

Example with psql:

test=> begin;
BEGIN

test=*> select 1/0;
ERROR:  division by zero

test=!> select 2;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

test=!> 

In your test with pgAdmin, you want to submit your queries one by one. Do not submit the whole BEGIN......COMMIT block as a whole. Presumably this is why you don't see the deadlock error: it's immediately replaced by the error of the next query.

Your Answer

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

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