Delivery-Date: Thu, 14 Oct 93 10:46:45 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9310141731.AA09894@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199310141734.KAA07083@faerie.CS.Berkeley.EDU>
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: witr@rwwa.COM
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: Concurrency and locking 
In-Reply-To: Your message of Thu, 14 Oct 93 11:11:15 -0400 
	     <9310141511.AA02499@relay2.UU.NET> 
Date: Thu, 14 Oct 93 10:34:54 -0700
Resent-To: postgres-dist
Resent-Date: Thu, 14 Oct 93 10:31:24 PDT

witr@rwwa.COM writes:
> 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.

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

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

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  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki