deadlock.sql

application/octet-stream

Download

DROP TABLE B;
DROP TABLE A;
DROP VIEW vLocks;

CREATE TABLE A (
AID integer not null,
Col1 integer,
PRIMARY KEY (AID)
);


CREATE TABLE B (
BID integer not null,
AID integer not null,
Col2 integer,
PRIMARY KEY (BID),
FOREIGN KEY (AID) REFERENCES A(AID)
);

INSERT INTO A (AID) VALUES (1);
INSERT INTO B (BID,AID) VALUES (2,1);

CREATE VIEW vLocks AS SELECT clock_timestamp(),pg_class.relname,pg_locks.locktype,pg_locks.database,pg_locks.relation,pg_locks.page,pg_locks.tuple,
pg_locks.virtualtransaction,pg_locks.pid,pg_locks.mode,pg_locks.granted FROM pg_locks
JOIN pg_class ON pg_locks.relation = pg_class.oid WHERE relname !~ '^pg_' and relname <> 'vlocks';

Process 1:                                Process 2:
BEGIN;
SELECT pg_backend_pid();
                                                        BEGIN;
                                                        SELECT pg_backend_pid();
UPDATE A SET Col1 = 1 WHERE AID = 1;
SELECT * FROM vLocks WHERE PID IN (2165,2157);
                                                        UPDATE B SET Col2 = 1 WHERE BID = 2;
                                                        SELECT * FROM vLocks WHERE PID IN (2165,2157);
UPDATE B SET Col2 = 1 WHERE BID = 2;
SELECT * FROM vLocks WHERE PID IN (2165,2157);
                                                        UPDATE B SET Col2 = 1 WHERE BID = 2;
                                                        SELECT * FROM vLocks WHERE PID IN (2165,2157);


http://screencast.com/t/NTk2Y2VhMW