agora inbox for postgres@postgres.berkeley.edu  

help / color / mirror / Atom feed

From: Jeff Meredith <mer@miro.com>
To: postgres@postgres.berkeley.edu
Subject: Re: Re deadlock ( bug in begin/end? replace?)
Date: Tue, 27 Apr 93 10:46:35 -0700
Message-ID: <9304271750.AA15734@postgres.Berkeley.EDU> (raw)
In-Reply-To: <9304271215.AA13941@postgres.Berkeley.EDU>

message <9304271215.AA13941@postgres.Berkeley.EDU> read as follows:
> I've been watching the mail over the last couple of days but still
> haven't seen a definitive answer to avoiding deadlock. If:
> 
> >a replace query of the form:  
> > 
> >       replace foo (attribute1 =foo.attribute1 + 1) 
> > 
> >will first do a scan over foo (acquiring a readlock) then attempt to 
> >write a new instance/row/tuple (acquiring a write lock).  any time you 
> >"upgrade" a read lock to a write lock you're vulnerable to deadlock.
> 
> then how do I code a transaction that is guaranteed not to deadlock?
> Would a dummy append do it or should I be considering my own locking
> outside Postgres?

as a workaround you might try (my syntax is rusty please forgive me):

	begin\g
	append foo (attribute1 = somedummyvalue)\g
	replace foo (attribute1 =foo.attribute1 + 1)\g
	end\g

note that the write lock on foo will only be acquired if you actually
write something to the relation.  so for example having an append with
a qual that is never satisfied will not do:

	append foo (attribute1 = 1) where 1 = 0\g

the ugly side effect is that you end up with dummy values in your relation.

i did not do a good job in deploying the lock manager after fixing it up
at the university.  the system should probably watch for the above case and
automatically set a write at the outset.  it doesn't.

what also might be nice is for the postgres group to provide a built-in
function that takes a relation name and sets an exclusive (write) lock on
that relation.  this would allow things like:

	begin\g
	retrieve (x=set_excl_lock("foo"))\g
	replace foo (attribute1 =foo.attribute1 + 1)
	end\g

the lock manager already does a good job releasing all locks acquired
during a xact at commit/abort.  This would give users more control (the
ability to hang themselves?), but should probably guard against the
obvious abuses e.g. locking a system table, locking a table you don't have
permission to replace or append to etc.

incidentally, in my opinion doing your own locking outside of the db
is not a good idea, but then again i'm a product of the mike stonebraker 
school of software development.


jeff meredith
mer@miro.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: postgres@postgres.berkeley.edu
  Cc: mer@miro.com
  Subject: Re: Re deadlock ( bug in begin/end? replace?)
  In-Reply-To: <9304271750.AA15734@postgres.Berkeley.EDU>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox