hey all,
I have a stored procedure that updates a couple of tables within my
database.
org.postgresql.util.PSQLException : ERROR: deadlock detected
Detail: Process 31580 waits for AccessExclusiveLock on relation
289553 of database 285107; blocked by process 16024.
Process 16024 waits for AccessShareLock on relation 289471 of database
285107; blocked by process 31580.
All tables in that database are heavy readed, and only my stored
procedure copies some data within a table.
The process within my stored procedure is like this but I have a
couple of these within my stored procedure:
LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE;
ALTER TABLE mytable DISABLE TRIGGER trg_mytable_log;
CREATE TEMPORARY TABLE mytemptable AS SELECT * FROM mytable WHERE
country_code=_country_code_to;
CREATE TEMPORARY TABLE mytemptable_log AS SELECT * FROM mytable_log
WHERE country_code=_country_code_to;
CREATE INDEX tmytemptable_idx ON mytemptable(part_num,
vehicle_names_item_id,country_code);
DELETE FROM mytable where country_code=_country_code_to;
DELETE FROM mytable_log where country_code=_country_code_to;
INSERT INTO mytable (p..............)
SELECT .
FROM mytable
WHERE ....................
INSERT INTO mytable_log (...........)
SELECT ........
FROM mytable_log
WHERE .............
INSERT INTO mytable SELECT * FROM mytemptable
WHERE ..........
INSERT INTO mytable_log SELECT * FROM mytemptable_log
WHERE .................
UPDATE mytable a SET .............................
ALTER TABLE mytable ENABLE TRIGGER trg_mytable_log;
For me it's perfectly fine to wait until the tables can get locked,
but I am actually in a loss why it happens in the first place.
I don't think that the table should have been locked at all??
Other users do only complex SELECTS on the tables...
Ries