Author: nunofsantos Date: 2008-12-11 13:50:48 -0500 (Thu, 11 Dec 2008) New Revision: 2979 Modified: mgmt/trunk/mint/python/mint/schemaparser.py mgmt/trunk/mint/sql/triggers.sql Log: try 'select ... for update' to deal with deadlocks Modified: mgmt/trunk/mint/python/mint/schemaparser.py =================================================================== --- mgmt/trunk/mint/python/mint/schemaparser.py 2008-12-11 18:45:53 UTC (rev 2978) +++ mgmt/trunk/mint/python/mint/schemaparser.py 2008-12-11 18:50:48 UTC (rev 2979) @@ -236,13 +236,21 @@ CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' DECLARE parent_table varchar; + select_parent varchar; update_parent varchar; BEGIN -- remove "_stats" suffix from table name parent_table := substr(tg_relname, 0, char_length(tg_relname) - 5); + + select_parent := ''SELECT id FROM '' || parent_table || + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || + new.id || '') FOR UPDATE''; + update_parent := ''UPDATE '' || parent_table || '' SET stats_prev_id = stats_curr_id, stats_curr_id = '' || new.id || - '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || new.id || '')''; + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || + new.id || '')''; + EXECUTE select_parent; EXECUTE update_parent; RETURN new; END Modified: mgmt/trunk/mint/sql/triggers.sql =================================================================== --- mgmt/trunk/mint/sql/triggers.sql 2008-12-11 18:45:53 UTC (rev 2978) +++ mgmt/trunk/mint/sql/triggers.sql 2008-12-11 18:50:48 UTC (rev 2979) @@ -19,13 +19,21 @@ CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' DECLARE parent_table varchar; + select_parent varchar; update_parent varchar; BEGIN -- remove "_stats" suffix from table name parent_table := substr(tg_relname, 0, char_length(tg_relname) - 5); + + select_parent := ''SELECT id FROM '' || parent_table || + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || + new.id || '') FOR UPDATE''; + update_parent := ''UPDATE '' || parent_table || '' SET stats_prev_id = stats_curr_id, stats_curr_id = '' || new.id || - '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || new.id || '')''; + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || + new.id || '')''; + EXECUTE select_parent; EXECUTE update_parent; RETURN new; END