Stack Exchange Network

Stack Exchange network consists of 181 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Sign up to join this community

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

ERROR: current transaction is aborted, commands ignored until end of transaction block SQL state: 25P02

Ask Question

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:
https://stackoverflow.com/a/22776994/2516892
https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/
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
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.
        
Thanks for contributing an answer to Database Administrators Stack Exchange!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.