deadlock_example.txt

text/plain

Filename: deadlock_example.txt

Type: text/plain

Part: 0

Message: RE: Perform streaming logical transactions by background workers and parallel apply

**************CASE 1**************
CREATE TABLE test(a int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(3);

--------------On publisher--------------
Tx1:
BEGIN;
UPDATE test SET a = 2 WHERE a = 1;
DELETE FROM test where a = 3;
INSERT INTO test VALUES(3);
COMMIT;

Tx2:
BEGIN;
UPDATE test SET a = 4 WHERE a = 3;
INSERT INTO test VALUES(1);
COMMIT;

--------------On subscriber--------------

Tx1:
BEGIN;
UPDATE test SET a = 2 WHERE a = 1;
    Tx2:
    BEGIN;
    UPDATE test SET a = 4 WHERE a = 3;
Tx1:
DELETE FROM test where a = 3;
    Tx2:
    INSERT INTO test VALUES(1);

ERROR:  deadlock detected


**************CASE 2**************
CREATE TABLE test(a int);
INSERT INTO test VALUES(1);

CREATE TABLE test2(a int);
INSERT INTO test2 VALUES(1);

--------------On publisher--------------
Tx1:
BEGIN;
UPDATE test SET a = 2 WHERE a = 1;
UPDATE test2 SET a = 2 WHERE a = 1;
COMMIT;

Tx2:
BEGIN;
INSERT INTO test2 VALUES(1);
UPDATE test2 SET a = 2 WHERE a = 1;
INSERT INTO test VALUES(1);
UPDATE test SET a = 2 WHERE a = 1;
COMMIT;

--------------On subscriber--------------
Tx1:
BEGIN;
UPDATE test SET a = 2 WHERE a = 1;
    Tx2:
    BEGIN;
    INSERT INTO test2 VALUES(1);
    UPDATE test2 SET a = 2 WHERE a = 1;
Tx1:
UPDATE test2 SET a = 2 WHERE a = 1;
    Tx2:
    INSERT INTO test VALUES(1);
    UPDATE test SET a = 2 WHERE a = 1;
    COMMIT;
COMMIT;

ERROR:  deadlock detected