Deadlock and aborted queries..
От | Warren Vanichuk |
---|---|
Тема | Deadlock and aborted queries.. |
Дата | |
Msg-id | Pine.LNX.4.21.0102061556200.8325-100000@urd.street-light.com обсуждение исходный текст |
Список | pgsql-general |
Original sent out under a misleading subject line, I hit reply to get the list address and forgot to change the subject. Resent with a real subject. My apologies. ----------- Greetings. I have a smallish sized database that's getting alot of update transactions to it. It's been running fine over the past several weeks, but suddenly I'm starting to see : NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. NOTICE: current transaction is aborted, queries ignored until end of transaction block NOTICE: current transaction is aborted, queries ignored until end of transaction block NOTICE: current transaction is aborted, queries ignored until end of transaction block NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. appear in my apache error logs from my PHP statements. At the same time the following messagse appear in Postgres logs : NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. ERROR: WaitOnLock: error on wakeup - Aborting this transaction DEBUG: Last error occured while executing PL/pgSQL function add_impression DEBUG: line 16 at SQL statement NOTICE: current transaction is aborted, queries ignored until end of transaction block NOTICE: current transaction is aborted, queries ignored until end of transaction block This is PostgreSQL 7.0.3 running on a Debian Stable system. All transactions to the database are via PHP 4.0.4pl1 under Apache 1.3.17. I've made no modifications to the system itself for several weeks (outside of upgrading apache) and it was running fine under similiar loads in the past. Any suggestions one how I can rectify this situation would be appriecated. :) For reference, the function in question is declared as : CREATE function add_impression( int4, int4, text ) RETURNS int4 AS ' DECLARE tempvar record; linkidx ALIAS FOR $1; linkcat ALIAS FOR $2; linkloc ALIAS FOR $3; BEGIN SELECT INTO tempvar * FROM linkdaystatsdetail WHERE linkid = linkidx AND categorynumber = linkcat AND location = linkloc; IF FOUND THEN UPDATE linkoverviewstatsdetail SET impressions=impressions+1 WHERE linkid = linkidx AND categorynumber = linkcat AND location = linkloc; ELSE INSERT INTO linkoverviewstatsdetail ( linkid, categorynumber, location, clicks, impressions ) VALUES ( linkidx, linkcat, linkloc, 0, 1 ); END IF; UPDATE linkoverviewstats SET impressions=impressions+1 WHERE linkid = linkidx; SELECT INTO tempvar * FROM linkdaystatsdetail WHERE linkid = linkidx AND categorynumber = linkcat AND location = linkloc; IF FOUND THEN UPDATE linkdaystatsdetail SET impressions=impressions+1 WHERE linkid = linkidx AND categorynumber = linkcat AND location = linkloc; ELSE INSERT INTO linkdaystatsdetail ( linkid, categorynumber, location, clicks, impressions ) VALUES ( linkidx, linkcat, linkloc, 0, 1 ); END IF; UPDATE linkdaystats SET impressions=impressions+1 WHERE linkid = linkidx; RETURN 1; END; ' LANGUAGE 'PLPGSQL'; linkoverviewstatsdetail and linkdaystatsdetail are very similiar in composition, the schema being : freehost=# \d linkdaystatsdetail Table "linkdaystatsdetail" Attribute | Type | Modifier ----------------+------------+---------- linkid | integer | categorynumber | integer | location | varchar(1) | clicks | integer | impressions | integer | Indices: linkdaystatsdetail_linkid_idx, linkdsd_linkid_cat_loc_idx freehost=# \d linkdsd_linkid_cat_loc_idx Index "linkdsd_linkid_cat_loc_idx" Attribute | Type ----------------+------------ linkid | integer categorynumber | integer location | varchar(1) btree freehost=# \d linkdaystatsdetail_linkid_idx Index "linkdaystatsdetail_linkid_idx" Attribute | Type -----------+--------- linkid | integer btree Thanks in advance, Sincerely, Warren
В списке pgsql-general по дате отправления: