Postresql SQL State: 25P02 Deadlock?

Question

My task is to create a deadlock with two SQL Transactions. I work in PgAdmin. The task says: "Create two SQL transactions that can be executed interactively (annotate in comments in which order the transactions should be interleaved) to create a deadlock and explain the reason of the deadlock." I tried to create it and read about it online, but this is the only error I get when trying to make two processes go at the same time without COMMIT/ROLLBACK. I get this error message:

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

If that is not deadlock, can you help me make one?

http://zarez.net/?p=1069

    CREATE TABLE deadlock_example_table_1 (column1 int)

    CREATE TABLE deadlock_example_table_2 (column1 int)

    INSERT INTO deadlock_example_table_1 (column1)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    GO

    INSERT INTO deadlock_example_table_2 (column1)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
    GO

    BEGIN;
    DELETE FROM deadlock_example_table_1 WHERE column1 = 2

    BEGIN;
    DELETE FROM deadlock_example_table_2 WHERE column1 = 2

https://medium.com/@clairesimmonds/postgresql-decoding-deadlocks-183e6a792fd3 I was thinking the output should look something like in this article.

    ERROR: deadlock detected
    DETAIL:  Process 16121 waits for AccessExclusiveLock on relation 17389 of database 16390; blocked by process 15866.
    Process 15866 waits for AccessShareLock on relation 17309 of database 16390; blocked by process 16121.
    HINT:  See server log for query details.

Easier way is to insert 2 rows with a process 1, then update the two rows with a process 2 , do not commit, nor rollback, then update same rows in reverse order with a process 3 , the second update of process 3 should dead lock you — Gar, May 21 '19 at 08:57

Gar · Accepted Answer · 2019-05-21T10:20:07.317

1

Step 1: populate

INSERT INTO deadlock_example_table_1 (column1)
    SELECT 1
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
go
commit;

Step 2: process 2 , update

 update deadlock_example_table_1 set columns1=5 where column1=1;

Step 3 : process 3 (another command window) update

 update deadlock_example_table_1 set columns1=7 where column1=2;
 update deadlock_example_table_1 set columns1=4 where column1=1;

This normally should get locked

step 4: return to process 2

 update deadlock_example_table_1 set columns1=80 where column1=2;

Dead lock here

Process 2 and process 3 SHOULD be on different sessions

edited May 21 '19 at 10:20

answered May 21 '19 at 09:04

Gar

852
13
20

update deadlock_example_table_1 set column1=5 where column1=1 GO I get an syntax error at or near "GO" – Lazar Gugleta May 21 '19 at 09:13
Sorry, fixed that – Gar May 21 '19 at 09:23
Should this answers you, please do not hesitate to accept the question as an answer. – Gar May 21 '19 at 09:52
Here are the catches : 1: do not forget to commit after the INSERT , 2: use two different command windows (one for process 2 and one for process 3) , if you still have issues running this, please contact me via the chat system – Gar May 21 '19 at 10:21

score 0 · Answer 2 · answered May 21 '19 at 10:43

0

Must be simultaneously executed. Open two windows and do this: Process

answered May 21 '19 at 10:43

Lazar Gugleta

115
1
2
14

Postresql SQL State: 25P02 Deadlock?

2 Answers2