Subject: Concurrency and locking

From: [email-masked]
Date: Thu, 14 Oct 93 11:11:15 -0400

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] (Paul M. Aoki)
Date: Thu, 14 Oct 93 10:34:54 -0700

> I get *both* stale data updates and deadlocks.

i think the stale data bit is a bug in the tqual code that has been 
fixed..

> would expect, that is it should demonstrate *neither* deadlocks or

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).  boom, deadlock.  (someone
just posted a more lucid explanation of this in comp.databases.sybase..
i'm not in lucid mode here.)

putting updates first reduces the window of vulnerability but does
not eliminate it.  slapping a write lock on the update relation
before starting to read it might help a bit, i guess.

university ingres gets around this by not having multi-statement
transactions :-)  real systems have finer-granularity locking and
hacks like SELECT ... FOR UPDATE.  but without something like
SELECT ... FOR UPDATE, this kind of application is likely to 
exhibit similar behavior on almost any system if it ends up doing
a heap scan.  (correct me if i'm wrong.)  some people were hacking 
around this by putting an update to a dummy relation as the first 
thing to serialize all access :-P

i may be hallucinating (i have been out sick all week) but i think 
jeff meredith tried to deploy a row-level locking scheme but found 
(on decs, which don't have spinlocks and have to use semaphores for 
all synchronization) that it died in system call overhead.  don't 
know if he tried it on sparcs.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked] (Kai Petzke)
Date: Fri, 15 Oct 93 23:08:48 MET

> thing to serialize all access :-P

SQL has another statement:

LOCK TABLE ...

When used with care, it has some positive effects:

- If the same locks are always issued in the same order, danger of
  deadlock is minimized.

- Transactions, which update a large number of rows, don't produce
  errors, because the number of row level locks is exhausted.

When used without thinking, performance may be lowered quite a lot.

Implementing such a statement into Postgres is, in my opinion, much
better then the obscure and maybe unsafe access to lock relations.

Kai Petzke
[email-masked]