Обсуждение: Need help with : org.postgresql.util.PSQLException : ERROR: deadlock detected
Need help with : org.postgresql.util.PSQLException : ERROR: deadlock detected
От
ries van Twisk
Дата:
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