Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA15734; Tue, 27 Apr 93 10:50:20 -0700
Message-Id: <9304271750.AA15734@postgres.Berkeley.EDU>
From: Jeff Meredith <mer@miro.com>
Subject: Re: Re deadlock ( bug in begin/end? replace?)
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of "Tue, 27 Apr 93 05:15:47 PDT."
             <9304271215.AA13941@postgres.Berkeley.EDU> 
Date: Tue, 27 Apr 93 10:46:35 -0700
From: Jeff Meredith <mer@miro.com>

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