Delivery-Date: Fri, 15 Oct 93 16:21:17 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9310152313.AA21941@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199310152309.AA01465@marie.physik.tu-berlin.de>
Subject: Re: Concurrency and locking
To: postgres@postgres.Berkeley.EDU
Date: Fri, 15 Oct 93 23:08:48 MET
From: wpp@marie.physik.tu-berlin.de (Kai Petzke)
In-Reply-To: <199310141734.KAA07083@faerie.CS.Berkeley.EDU>; from "Paul M. Aoki" at Oct 14, 93 10:34 am
Content-Type: text
Content-Length: 1318
Resent-To: postgres-dist
Resent-Date: Fri, 15 Oct 93 16:13:35 PDT

Paul M. Aoki (aoki@postgres.berkeley.edu) writes:

> 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

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
wpp@marie.physik.tu-berlin.de