Delivery-Date: Mon, 18 Oct 93 11:16:21 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9310181750.AA03061@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Mon, 18 Oct 93 13:54:08 -0400
From: witr@rwwa.COM
Message-Id: <9310181754.AA09271@relay2.UU.NET>
To: uunet!postgres.Berkeley.EDU!postgres@uunet.UU.NET
Subject: Re: Concurrency and locking
Content-Type: text
Content-Length: 2291
Resent-To: postgres-dist
Resent-Date: Mon, 18 Oct 93 10:50:11 PDT

> actually, this application is a classic example of an application
> that *does* deadlock.  it reads TEST, thereby read locking what it's
> scanning, then tries to write TEST.  when two processes run, sometimes
> they both get read locks, then one tries to get a write lock and can't
> (because of the other process's read lock).

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
order entry system that a) requires a serialized order ID, and b) must
support a policy where the discount offered varies on the customer's
yearly volume, and must modify the discount (per customer, due to
contracts and what not) on the fly when processing an order.  The
straight-forward way of dealing with these things will result in
deadlocks. 

Of course, an easy (?) way out is to use some external locking
strategy (either using a dummy relation, or fcntl, or semaphores, or
what have you) and this works fine, but it seems that it's a shame to
go outside of postgres instead of having postgres do it for you
somehow.

When I drink enough coffee in the morning I can envision something
like a postgres statement, similar to the retrieve statement:

  ``lock [instance_variable(expression,...)] [for {read|write}] [from
         clause] [where clause] [,...]''

Which would have the following semantic properties.

  1) It would do a backoff and retry (with smarts) to lock things in
some predictable order,
  2) Would allow varying granularity, from ``lock for write'' which
would lock all of postgres, to ``lock t(value) for write from t in
TEST where t.name = "foo"'', to lock the single attribute of a single
tuple.  This would include ``lock TEST for write'' to lock then entire
TEST class, and ``lock t(all) from t in TEST where t.value > 5'' which
would readlock a set of tuples.
  3) Would have a lifetime of a single transaction (begin...end) and
only 1 would be allowed for each transaction.

But even this is likely to be either 1) impossible, 2) insufficient,
or 3) both... ;-)


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