liquid_feedback_core

changeset 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)
author jbe
date Tue Sep 25 02:32:51 2012 +0200 (2012-09-25)
parents 703986b6de29 bcfde30040f6
children dbd237d86a8a
files core.sql update/core-update.v2.0.11-v2.1.0.sql
line diff
     1.1 --- a/core.sql	Sun Aug 26 21:04:20 2012 +0200
     1.2 +++ b/core.sql	Tue Sep 25 02:32:51 2012 +0200
     1.3 @@ -2989,10 +2989,9 @@
     1.4    RETURNS VOID
     1.5    LANGUAGE 'plpgsql' VOLATILE AS $$
     1.6      BEGIN
     1.7 -      LOCK TABLE "member"     IN SHARE MODE;
     1.8 -      LOCK TABLE "privilege"  IN SHARE MODE;
     1.9 -      LOCK TABLE "membership" IN SHARE MODE;
    1.10 -      LOCK TABLE "policy"     IN SHARE MODE;
    1.11 +      -- The following locking order is used:
    1.12 +      -- 1st) row-level lock on the issue
    1.13 +      -- 2nd) table-level locks in order of occurrence in the core.sql file
    1.14        PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
    1.15        -- NOTE: The row-level exclusive lock in combination with the
    1.16        -- share_row_lock_issue(_via_initiative)_trigger functions (which
    1.17 @@ -3000,6 +2999,10 @@
    1.18        -- is changed, which could affect calculation of snapshots or
    1.19        -- counting of votes. Table "delegation" must be table-level-locked,
    1.20        -- as it also contains issue- and global-scope delegations.
    1.21 +      LOCK TABLE "member"     IN EXCLUSIVE MODE;  -- exclusive avoids deadlocks
    1.22 +      LOCK TABLE "privilege"  IN SHARE MODE;
    1.23 +      LOCK TABLE "membership" IN SHARE MODE;
    1.24 +      LOCK TABLE "policy"     IN SHARE MODE;
    1.25        LOCK TABLE "delegation" IN SHARE MODE;
    1.26        LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
    1.27        LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
     2.1 --- a/update/core-update.v2.0.11-v2.1.0.sql	Sun Aug 26 21:04:20 2012 +0200
     2.2 +++ b/update/core-update.v2.0.11-v2.1.0.sql	Tue Sep 25 02:32:51 2012 +0200
     2.3 @@ -414,6 +414,38 @@
     2.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")';
     2.5  
     2.6  
     2.7 +-- different locking levels and different locking order to avoid deadlocks
     2.8 +
     2.9 +CREATE OR REPLACE FUNCTION "lock_issue"
    2.10 +  ( "issue_id_p" "issue"."id"%TYPE )
    2.11 +  RETURNS VOID
    2.12 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.13 +    BEGIN
    2.14 +      -- The following locking order is used:
    2.15 +      -- 1st) row-level lock on the issue
    2.16 +      -- 2nd) table-level locks in order of occurrence in the core.sql file
    2.17 +      PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
    2.18 +      -- NOTE: The row-level exclusive lock in combination with the
    2.19 +      -- share_row_lock_issue(_via_initiative)_trigger functions (which
    2.20 +      -- acquire a row-level share lock on the issue) ensure that no data
    2.21 +      -- is changed, which could affect calculation of snapshots or
    2.22 +      -- counting of votes. Table "delegation" must be table-level-locked,
    2.23 +      -- as it also contains issue- and global-scope delegations.
    2.24 +      LOCK TABLE "member"     IN EXCLUSIVE MODE;  -- exclusive avoids deadlocks
    2.25 +      LOCK TABLE "privilege"  IN SHARE MODE;
    2.26 +      LOCK TABLE "membership" IN SHARE MODE;
    2.27 +      LOCK TABLE "policy"     IN SHARE MODE;
    2.28 +      LOCK TABLE "delegation" IN SHARE MODE;
    2.29 +      LOCK TABLE "direct_population_snapshot"     IN EXCLUSIVE MODE;
    2.30 +      LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
    2.31 +      LOCK TABLE "direct_interest_snapshot"       IN EXCLUSIVE MODE;
    2.32 +      LOCK TABLE "delegating_interest_snapshot"   IN EXCLUSIVE MODE;
    2.33 +      LOCK TABLE "direct_supporter_snapshot"      IN EXCLUSIVE MODE;
    2.34 +      RETURN;
    2.35 +    END;
    2.36 +  $$;
    2.37 +
    2.38 +
    2.39  -- new comment on function "delete_private_data"()
    2.40  
    2.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.';

Impressum / About Us