From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: Row level locking?
Organization: 
Date: Wed, 15 Apr 1992 18:36:53 GMT

In article <1992Apr14.164025.490@macc.wisc.edu>, heim@vms.macc.wisc.edu 
(JOHN HEIM	) writes:
> In article <9204100843.AA10575@asgserver.canberra.edu.au>, mmr 
> (Micheal Rainford-Watson) writes...
>> ...
>>SELECT into fields, then COMMIT (release locks)
>>Update the fields on the form.
>>Then UPDATE the database with these changed fields, then COMMIT (release 
>>locks).
>> 
>>i.e., Don't hold any locks longer than necessary
> 
> I think the above strategy defeats the purpose of locks altogether.  
> For instance ...
> 
>         Process 1:               Process 2:
>         ----------               ----------
> 
> T       SELECT tuple_x
> I       COMMIT
> M                                SELECT tuple_x
> E                                COMMIT
> 
> |       <<Changes>>
> V       UPDATE
>                                  <<Changes>>
>                                  UPDATE
> 
> 
> Process 1's changes would be lost.

This is true.  However, a cardinal rule for most transactions is: Don't
allow human think time inside a transaction.  For best concurrency, it's
important to minimize the time during which locks are held.

If you use INGRES, you're guaranteed to have concurrency problems if
your application works like this: the user selects data to the screen
(without a COMMIT), fiddles around on the screen updating it, and then
picks a menu item to update the data in the database.  The user takes
out read locks by doing the first SELECT, and hangs onto those locks
while he/she stares at the screen, goes to lunch, etc.  

You have several choices.  

1. Live with the losses incurred by the strategy above.  If it's rare
that two different users would update the same record at the same time,
this may be okay.

2. Eliminate the COMMIT after the SELECT, and live with the concurrency
problems and deadlocks that will inevitably occur.  To eliminate
deadlock, use "readlock=exclusive" before the SELECT.  However, then the
second user has to wait for the first user to COMMIT before he/she can
SELECT.

3. Leave the COMMIT in, but change the UPDATE so that its WHERE clause
includes all of the original data that was SELECTed, and any data that
will be UPDATEd, as Michael Hevern has suggested.  That way, if the
update fails, you can warn the user that they have to select the data
again, and do their updates to the screen again.  You'll have to do this
with choice 2, as well, because there will be deadlock if user 2 tries
to update before user 1.

4. Have INGRES do row-level locking by padding all rows to the 2000-byte
limit (with a filler column), and never using compressed structures (but
you can still get deadlock on indexes).

5. Use entries in other tables to create your own locks.

INGRES's locking strategy seems to be a compromise between the overhead
of row-level locking and the deadlock-eliminating strategy of
table-level locking.  I think that it works well for most, but not all,
applications.

--Mark Jaeger
University of Chicago			phone: (312) 702-0328
Graduate School of Business		  fax: (312) 702-0233
1101 East 58th Street, W309		email: cs_mj@gsbvax.uchicago.edu 
Chicago, IL  60637-1511, USA		       (internet)
Disclaimer: My opinions are my own and not those of my employer.