Subject: Re deadlock ( bug in begin/end? replace?)

From: Gerry Stringer <[email-masked]>
Date: Tue, 27 Apr 93 05:15:47 -0700

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?

	thanks,
		gerry

+--------------------------------+---------------------------------+
|Gerry Stringer                  |Phone: +44 954 780926            |
|Cambridge Scanning Company Ltd  |Fax:   +44 954 789829            |
|Saxon Way                       |UUCP : [email-masked]       |
|Bar Hill                        |       [email-masked]        |
|Cambridge CB3 8SL               |       			   |
|United Kingdom                  |                                 |
+--------------------------------+---------------------------------+

---

From: Jeff Meredith <[email-masked]>
Date: Tue, 27 Apr 93 10:46:35 -0700

> Would a dummy append do it or should I be considering my own locking

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
[email-masked]