Subject: Concurrency and locking

From: [email-masked]
Date: 1 Nov 1993 20:27:15 +0100

The following short pgperl program demonstrates `shortcomings' with
perl's concurrency and locking.  When I run two or more copies of it
I get *both* stale data updates and deadlocks.

This has been discussed before, but I don't understand what can or
should be done to remedy this so that this sort of thing works as one
would expect, that is it should demonstrate *neither* deadlocks or
stale data problems.

#!/usr/local/bin/pgperl

# Test postgres concurrency.
# 1) Createdb concur
# 2) Monitor concur
#    destroy TEST \g
#    create TEST (tkey = text, tvalue = int4) \g
#    append TEST (tkey = "foo", tvalue = 1) \g
# 3) Run two or more copies of this program.

$dbname = "concur";
&PQsetdb ($dbname);

while (1) {
  &PQexec("begin");
  $query = "retrieve portal p (t.tvalue) from t in TEST where t.tkey =
\"foo\" ";
  &PQexec($query);
  &PQexec("fetch all in p");

  $p = &PQparray("p");
  $n = &PQntuples($p);

  $value = &PQgetvalue($p, $n-1, 0);
  $value += 1;

  print("Replacing tvalue with $value.\n");
  $query = "replace TEST(tvalue=$value) where TEST.tkey = \"foo\" ";
  &PQexec($query);

  &PQexec ("close p");
  &PQexec ("end");
  &PQclear("p");
}
---
 Robert Withrow, Tel: +1 617 598 4480, Fax: +1 617 598 4430, Net: [email-masked]
 R.W. Withrow Associates, 21 Railroad Ave, Swampscott MA 01907-1821 USA

---

From: [email-masked] (Gerry Stringer)
Date: 1 Nov 1993 21:10:37 +0100

>>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
...
...
>> 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 : [email-masked]       |
|Bar Hill                        |       [email-masked]        |
|Cambridge CB3 8SL               |       			   |
|United Kingdom                  |                                 |
+--------------------------------+---------------------------------+