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

[Ian Campbell <ian.campbell@xxxxxxxxxx>]

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@xxxxxxxxxxxxx>

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

> ---
> Â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@xxxxxxxxxxxxx
http://lists.xen.org/xen-devel