liquid_feedback_core
diff update/core-update.v2.0.11-v2.1.0.sql @ 297:779ba19a07df
Different locking levels and different locking order in function "lock_issue"("issue"."id") to avoid deadlocks
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
(includes update of update/core-update.v2.0.11-v2.1.0.sql)
author | jbe |
---|---|
date | Tue Sep 25 02:32:51 2012 +0200 (2012-09-25) |
parents | 703986b6de29 |
children | dbd237d86a8a |
line diff
1.1 --- a/update/core-update.v2.0.11-v2.1.0.sql Sun Aug 26 21:04:20 2012 +0200 1.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql Tue Sep 25 02:32:51 2012 +0200 1.3 @@ -414,6 +414,38 @@ 1.4 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")'; 1.5 1.6 1.7 +-- different locking levels and different locking order to avoid deadlocks 1.8 + 1.9 +CREATE OR REPLACE FUNCTION "lock_issue" 1.10 + ( "issue_id_p" "issue"."id"%TYPE ) 1.11 + RETURNS VOID 1.12 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.13 + BEGIN 1.14 + -- The following locking order is used: 1.15 + -- 1st) row-level lock on the issue 1.16 + -- 2nd) table-level locks in order of occurrence in the core.sql file 1.17 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 1.18 + -- NOTE: The row-level exclusive lock in combination with the 1.19 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 1.20 + -- acquire a row-level share lock on the issue) ensure that no data 1.21 + -- is changed, which could affect calculation of snapshots or 1.22 + -- counting of votes. Table "delegation" must be table-level-locked, 1.23 + -- as it also contains issue- and global-scope delegations. 1.24 + LOCK TABLE "member" IN EXCLUSIVE MODE; -- exclusive avoids deadlocks 1.25 + LOCK TABLE "privilege" IN SHARE MODE; 1.26 + LOCK TABLE "membership" IN SHARE MODE; 1.27 + LOCK TABLE "policy" IN SHARE MODE; 1.28 + LOCK TABLE "delegation" IN SHARE MODE; 1.29 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 1.30 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 1.31 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 1.32 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 1.33 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 1.34 + RETURN; 1.35 + END; 1.36 + $$; 1.37 + 1.38 + 1.39 -- new comment on function "delete_private_data"() 1.40 1.41 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';