From 4f64ad90e32fb21da9503513bb98041dafc0ca95 Mon Sep 17 00:00:00 2001 From: Ian Jackson Date: Fri, 11 Dec 2015 16:13:00 +0000 Subject: [PATCH] Executive DB: Reduce strength of DB locks The purpose of these locks is partly to prevent transactions being aborted (which I'm not sure the existing code would in practice cope with, although this is a bug) and also to avoid bugs due to the fact that SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; does not mean that the transactions are necessarily serialisable! http://www.postgresql.org/docs/8.3/static/transaction-iso.html In SQL in general it is possible for read-only transactions to conflict with writing transactions. However, in PostgreSQL this is not a problem because Postgres uses multi-version concurrency control: it retains the old version of the data while the read transaction is open: http://www.postgresql.org/docs/8.3/static/transaction-iso.html So a read transaction cannot cause a write transaction to abort, nor vice versa. So there is no need to have the database explicit table locks prevent concurrent read access. Preventing concurrent read access means that simple and urgent updates can be unnecessarily delayed by long-running reader transactions in the history reporters and archaeologists. So, reduce the lock mode from ACCESS EXCLUSIVE to ACCESS. This still conflicts with all kinds of updates and prospective updates, but no longer with SELECT: http://www.postgresql.org/docs/8.3/static/explicit-locking.html Signed-off-by: Ian Jackson Acked-by: Ian Campbell --- v2: Fix grammar and typo in commit message. --- Osstest/JobDB/Executive.pm | 2 +- tcl/JobDB-Executive.tcl | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/Osstest/JobDB/Executive.pm b/Osstest/JobDB/Executive.pm index 69cb276..124e7c0 100644 --- a/Osstest/JobDB/Executive.pm +++ b/Osstest/JobDB/Executive.pm @@ -43,7 +43,7 @@ sub begin_work ($$$) { #method return if $ENV{'OSSTEST_DEBUG_NOSQLLOCK'}; foreach my $tab (@$tables) { - $dbh->do("LOCK TABLE $tab IN ACCESS EXCLUSIVE MODE"); + $dbh->do("LOCK TABLE $tab IN EXCLUSIVE MODE"); } } diff --git a/tcl/JobDB-Executive.tcl b/tcl/JobDB-Executive.tcl index f37bbaf..a3dbb9e 100644 --- a/tcl/JobDB-Executive.tcl +++ b/tcl/JobDB-Executive.tcl @@ -133,7 +133,7 @@ proc lock-tables {tables} { # must be inside transaction foreach tab $tables { db-execute " - LOCK TABLE $tab IN ACCESS EXCLUSIVE MODE + LOCK TABLE $tab IN EXCLUSIVE MODE " } } -- 2.39.5