Readlock

From: Roy Hann 
Subject: Re: Perils of readlock=nolock

>And, with Ingres II, there is a decent alternative (isolation level
>read committed) which is acceptable in many instances.  (Although
>there are still inconsistencies that can occur.)


Yes.  You can read about this in the manuals obviously (although there is a
howling error in the manuals, where they suggest that you can use SET
LOCKMODE to adjust the isolation level--that tripped me up in my CA World
presentation last month.)

The idea of isolation "levels" is that you can gradually reduce the level of
isolation that you find acceptable, to achieve improved response-time.   By
accepting a more inconsistent view of the database you can reduce
lock-contention and hence lock waits.  But using SET LOCKMODE is a
take-it-or-leave-it deal: you can have a completely consistent database view
and (potentially) serious lock contention, or you can have an inconsistent
database view and (probably) very little lock contention.  There is no
in-between using LOCKMODES.

Ingres II allows you to SET TRANSACTION  to achieve finer
control.  You can for example choose either READ_COMMITTED or
REPEATABLE_READ.  I would guess that most people would find READ_COMMITTED
to be a more palatable alternative to READLOCK=NOLOCK.  READ_COMMITTED
prevents you from seeing the transitional states, but should not block
writers (significantly).

Roy Hann
BKB Client/Server (UK) Ltd.
From: Roy Hann 
Subject: Re: Taking an exclusive lock on a table?




[snip]
>It's during the "do some stuff" that we are expecting exclusive access
>to the table in question, but this isn't happening.  Users are able to
>run reports against it, etc.
>
>Is this the correct way to have exclusive access to a table?

You are requesting an exclusive lock correctly, but the problem is that all
those reports are cheerfully doing SET LOCKMODE SESSION WHERE
READLOCK=NOLOCK.  Most people do this to prevent long-running, wide-ranging
reports from blocking users trying to update the database.  The (seemingly)
little-known side-effect of this is that the reports also ignore any
exclusive locks on tables and pages.  I wrote a testy little harangue about
this a couple of weeks ago (which deja news has no doubt preserved for the
reading pleasure of future generations).

It's a bit of a tricky problem, because on the one hand you don't want
reports blocking all the users for minutes or hours on end, but on the other
hand you would prefer not to report an inconsistent view of the database.
If you are using Ingres II you have the ability to set the transaction
isolation level to READ_COMMITTED in the reports, which is probably a
reasonable compromise in most cases.

Roy Hann
BKB Client/Server (UK) Ltd.
Ingres Q & A
Back to William's Home Page
Email William