Delivery-Date: Tue, 19 Oct 93 08:50:14 PDT
From: Gerry Stringer <gerry@camscan.co.uk>
Date: Tue, 19 Oct 93 15:20:40 BST
Message-Id: <17435.9310191420@electra.camscan.co.uk>
To: postgres-dist@postgres.Berkeley.EDU
Subject: Re: Concurrency and locking


>>Yes I agree that this is a ``classic'' example.  What I was trying to
>>point out is that trivially simple, but real, applicatins seem to
>>result in deadlock using Postgres.  As an example of this consider a
...
...
>>---
>> Robert Withrow, Tel: +1 617 598 4480, Fax: +1 617 598 4430, Net: witr@rwwa.COM
>> R.W. Withrow Associates, 21 Railroad Ave, Swampscott MA 01907-1821 USA


Someone else who feels the same as me - I have posted on just this
subject quite a bit in the past. It is too easy to to write a query
which can deadlock Postgres and probably lots of other DBs as well. DBs
are supposed to make it look like you're the only user of the data and
do all the necessary work to make it appear so. When writing a query
guaranteed not to deadlock, you have to have a lot of knowledge about
the underlying locking mechanisms. I fully understand the reasons for
this (I worked on DB internals for 4 years) it's just that most DBs
don't help much in this regard.

As far as suggestions for Postgres go, my vote is for the equivalent of a
"retrieve for update" type of clause which just uses write locks
internally instead of read locks. I think it gives just enough hooks to
be able to write a deadlock free transaction while hiding most of the
details.


	Regards,

		gerry


+--------------------------------+---------------------------------+
|Gerry Stringer                  |Phone: +44 954 780926            |
|Cambridge Scanning Company Ltd  |Fax:   +44 954 789829            |
|Saxon Way                       |UUCP : gerry@camscan.co.uk       |
|Bar Hill                        |       gerry@camscan.uucp        |
|Cambridge CB3 8SL               |       			   |
|United Kingdom                  |                                 |
+--------------------------------+---------------------------------+