[Xen-devel] [OSSTEST PATCH 2/2] Executive DB: Reduce strength of DB locks

[Ian Jackson <ian.jackson@xxxxxxxxxxxxx>]

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;
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 archaelogists.

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 <Ian.Jackson@xxxxxxxxxxxxx>
---
 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
         "
     }
 }
-- 
1.7.10.4


_______________________________________________
Xen-devel mailing list
Xen-devel@xxxxxxxxxxxxx
http://lists.xen.org/xen-devel