From: godfrind@ricard.enet.dec.com (Albert Godfrind)
Subject: Re: Row level locking?
Organization: Digital Equipment Corporation, Valbonne, France
Date: Fri, 17 Apr 1992 13:31:37 GMT


In article <1992Apr15.123653.1@gsbacd.uchicago.edu>, cs_mj@gsbacd.uchicago.edu (Mark Jaeger) writes...

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

I agree.

Row-level vs page-level locking can become somewhat of a religious issue!

I personally have mixed feelings about row-level locks. When I started working
with relational databases, my belief was that row-level locking was a must, and
that anything else was bad. Now, I no longer hold the same strong feelings.

The options go like this:

- if you have a fine granularity of locking (like row-level), then your system
  will need more overhead managing all those locks: they need to be described
  somewhere - usually in memory tables. There will be all sorts of chains and
  organizations to speed up enqueues and dequeues. Overall, you will burn more
  CPU and need more memory. Also, if the system is distributed, managing 
  locks can get really expensive (and complex too!)

  Also, there will be more chances of deadlocks.

  But of course, you should get better concurrency.

- if on the other hand, you have a very coarse granularity of locking (say
  table level), you will have much less locks to manage, hence less overhead.
  Also there will be less chances of deadlocks. Obviously, if you take table
  locks, this implies that only one user can ever update that table. You 
  effectively serialize access to each given table. Deadlock can still 
  happen when your queries use multiple tables.

  But of course, your concurrency will suffer

  However, if you can be sure that your transactions are short (definitely
  no terminal I/O within transactions), then this could be a valid approach.

- You could even take this to the extreme by having only _one_ lock for the 
  complete database - that would certainly solve all concurrency problems :-)
  This means no more deadlocks, very simple lock management ... In fact the
  effect would be that of single-threading all database operations. Of course,
  your transactions have to be short, and the database engine must be very
  fast.

The bottom line is that the level of locking depends very much on what the
application does to the database and what kind of service it expects. Page
locking is a sort of nice compromise between a fine but expensive row locking,
and a coarse but inefficient table level locking.

Note also that Ingres will start taking page locks, then escalate to table
level locks when it has decided you have taken too many page locks sofar (you
can set this threshold yourself using the SET MAXLOCKS statement)

/albert
--
Albert Godfrind                            
Digital Equipment Corporation      INET:  godfrind@beagle.enet.dec.com
European Technical Center                 Albert.Godfrind@vbo.mts.dec.com
BP 027                             Voice: [+33] 92.95.51.63
F-06901 Valbonne, France           FAX:   [+33] 93.65.34.24
Those are my own views, not the ones of my employer