liquid_feedback_core
changeset 333:b83ab26828a8
Completely removed table locking in favor of transaction isolation requirements
author | jbe |
---|---|
date | Fri Feb 15 02:51:56 2013 +0100 (2013-02-15) |
parents | f17ee916711a |
children | a2ab4fb1d0c7 |
files | core.sql test.sql |
line diff
1.1 --- a/core.sql Fri Feb 15 02:13:09 2013 +0100 1.2 +++ b/core.sql Fri Feb 15 02:51:56 2013 +0100 1.3 @@ -1567,7 +1567,7 @@ 1.4 1.5 1.6 --------------------------------------------------------------- 1.7 --- Ensure that votes are not modified when issues are frozen -- 1.8 +-- Ensure that votes are not modified when issues are closed -- 1.9 --------------------------------------------------------------- 1.10 1.11 -- NOTE: Frontends should ensure this anyway, but in case of programming 1.12 @@ -2859,19 +2859,56 @@ 1.13 1.14 1.15 1.16 +--------------------------- 1.17 +-- Transaction isolation -- 1.18 +--------------------------- 1.19 + 1.20 +CREATE FUNCTION "require_transaction_isolation"() 1.21 + RETURNS VOID 1.22 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.23 + BEGIN 1.24 + IF 1.25 + current_setting('transaction_isolation') NOT IN 1.26 + ('repeatable read', 'serializable') 1.27 + THEN 1.28 + RAISE EXCEPTION 'Insufficient transaction isolation level'; 1.29 + END IF; 1.30 + RETURN; 1.31 + END; 1.32 + $$; 1.33 + 1.34 + 1.35 +CREATE FUNCTION "dont_require_transaction_isolation"() 1.36 + RETURNS VOID 1.37 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.38 + BEGIN 1.39 + IF 1.40 + current_setting('transaction_isolation') IN 1.41 + ('repeatable read', 'serializable') 1.42 + THEN 1.43 + RAISE WARNING 'Unneccessary transaction isolation level: %', 1.44 + current_setting('transaction_isolation'); 1.45 + END IF; 1.46 + RETURN; 1.47 + END; 1.48 + $$; 1.49 + 1.50 + 1.51 + 1.52 ------------------------------------------------------------------------ 1.53 -- Regular tasks, except calculcation of snapshots and voting results -- 1.54 ------------------------------------------------------------------------ 1.55 1.56 + 1.57 CREATE FUNCTION "check_activity"() 1.58 RETURNS VOID 1.59 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.60 DECLARE 1.61 "system_setting_row" "system_setting"%ROWTYPE; 1.62 BEGIN 1.63 + PERFORM "dont_require_transaction_isolation"(); 1.64 SELECT * INTO "system_setting_row" FROM "system_setting"; 1.65 IF "system_setting_row"."member_ttl" NOTNULL THEN 1.66 - LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE; 1.67 UPDATE "member" SET "active" = FALSE 1.68 WHERE "active" = TRUE 1.69 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE; 1.70 @@ -2887,12 +2924,7 @@ 1.71 RETURNS VOID 1.72 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.73 BEGIN 1.74 - LOCK TABLE "member" IN SHARE MODE; 1.75 - LOCK TABLE "member_count" IN EXCLUSIVE MODE; 1.76 - LOCK TABLE "unit" IN EXCLUSIVE MODE; 1.77 - LOCK TABLE "area" IN EXCLUSIVE MODE; 1.78 - LOCK TABLE "privilege" IN SHARE MODE; 1.79 - LOCK TABLE "membership" IN SHARE MODE; 1.80 + PERFORM "require_transaction_isolation"(); 1.81 DELETE FROM "member_count"; 1.82 INSERT INTO "member_count" ("total_count") 1.83 SELECT "total_count" FROM "member_count_view"; 1.84 @@ -2989,6 +3021,7 @@ 1.85 "weight_ary" FLOAT[]; 1.86 "min_weight_v" FLOAT; 1.87 BEGIN 1.88 + PERFORM "require_transaction_isolation"(); 1.89 UPDATE "initiative" SET "harmonic_weight" = NULL 1.90 WHERE "issue_id" = "issue_id_p"; 1.91 LOOP 1.92 @@ -3352,6 +3385,7 @@ 1.93 "initiative_id_v" "initiative"."id"%TYPE; 1.94 "suggestion_id_v" "suggestion"."id"%TYPE; 1.95 BEGIN 1.96 + PERFORM "require_transaction_isolation"(); 1.97 PERFORM "create_population_snapshot"("issue_id_p"); 1.98 PERFORM "create_interest_snapshot"("issue_id_p"); 1.99 UPDATE "issue" SET 1.100 @@ -3534,6 +3568,7 @@ 1.101 DECLARE 1.102 "event_v" "issue"."latest_snapshot_event"%TYPE; 1.103 BEGIN 1.104 + PERFORM "require_transaction_isolation"(); 1.105 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue" 1.106 WHERE "id" = "issue_id_p" FOR UPDATE; 1.107 UPDATE "issue" SET "latest_snapshot_event" = "event_p" 1.108 @@ -3575,6 +3610,7 @@ 1.109 "issue_row" "issue"%ROWTYPE; 1.110 "policy_row" "policy"%ROWTYPE; 1.111 BEGIN 1.112 + PERFORM "require_transaction_isolation"(); 1.113 SELECT * INTO "issue_row" FROM "issue" 1.114 WHERE "id" = "issue_id_p" FOR UPDATE; 1.115 SELECT * INTO "policy_row" 1.116 @@ -3616,8 +3652,9 @@ 1.117 "policy_row" "policy"%ROWTYPE; 1.118 "initiative_row" "initiative"%ROWTYPE; 1.119 BEGIN 1.120 + PERFORM "require_transaction_isolation"(); 1.121 SELECT * INTO "issue_row" FROM "issue" 1.122 - WHERE "id" = "issue_id_p" FOR SHARE; 1.123 + WHERE "id" = "issue_id_p"; 1.124 SELECT * INTO "policy_row" FROM "policy" 1.125 WHERE "id" = "issue_row"."policy_id"; 1.126 FOR "initiative_row" IN 1.127 @@ -3658,6 +3695,7 @@ 1.128 "policy_row" "policy"%ROWTYPE; 1.129 "initiative_row" "initiative"%ROWTYPE; 1.130 BEGIN 1.131 + PERFORM "require_transaction_isolation"(); 1.132 SELECT * INTO "issue_row" FROM "issue" 1.133 WHERE "id" = "issue_id_p" FOR UPDATE; 1.134 SELECT * INTO "policy_row" FROM "policy" 1.135 @@ -3824,6 +3862,7 @@ 1.136 "unit_id_v" "unit"."id"%TYPE; 1.137 "member_id_v" "member"."id"%TYPE; 1.138 BEGIN 1.139 + PERFORM "require_transaction_isolation"(); 1.140 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.141 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.142 -- delete timestamp of voting comment: 1.143 @@ -3930,6 +3969,7 @@ 1.144 "winners_ary" INTEGER[]; 1.145 "initiative_id_v" "initiative"."id"%TYPE; 1.146 BEGIN 1.147 + PERFORM "require_transaction_isolation"(); 1.148 SELECT * INTO "issue_row" 1.149 FROM "issue" WHERE "id" = "issue_id_p"; 1.150 SELECT * INTO "policy_row" 1.151 @@ -4241,6 +4281,7 @@ 1.152 "issue_row" "issue"%ROWTYPE; 1.153 "state_v" "issue_state"; 1.154 BEGIN 1.155 + PERFORM "require_transaction_isolation"(); 1.156 IF "persist" ISNULL THEN 1.157 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p" 1.158 FOR UPDATE; 1.159 @@ -4391,6 +4432,7 @@ 1.160 "issue_id_v" "issue"."id"%TYPE; 1.161 "persist_v" "check_issue_persistence"; 1.162 BEGIN 1.163 + RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes'; 1.164 DELETE FROM "expired_session"; 1.165 PERFORM "check_activity"(); 1.166 PERFORM "calculate_member_counts"();
2.1 --- a/test.sql Fri Feb 15 02:13:09 2013 +0100 2.2 +++ b/test.sql Fri Feb 15 02:51:56 2013 +0100 2.3 @@ -3,6 +3,9 @@ 2.4 2.5 BEGIN; 2.6 2.7 +-- set transaction isolation level to be able to call "check_everything"() function 2.8 +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 2.9 + 2.10 INSERT INTO "member" ("activated", "last_activity", "active", "login", "name") VALUES 2.11 ('now', 'now', TRUE, 'user1', 'User #1'), -- id 1 2.12 ('now', 'now', TRUE, 'user2', 'User #2'), -- id 2