Re: [OSSTEST PATCH 2/2] Executive DB: Reduce strength of DB locks

[Ian Campbell <ian.campbell@citrix.com>]

On Fri, 2015-12-11 at 16:23 +0000, Ian Jackson wrote:
> 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!

 ^does not?

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

                            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 <Ian.Jackson@eu.citrix.com>

Acked-by: Ian Campbell <ian.campbell@citrix.com>

> ---
>  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
>          "
>      }
>  }

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