Set Nologging

From: Roy Hann 
Subject: Re: Transaction Logging for certain tables o
To: Enrico Ben , info-ingres@ams.org

>In the Ingres DBA manual (1.2 and 2.0) is written that the nologging
>statement disables logging for the CURRENT SESSION, and not for the entire
>database. So I think I can make nologging deletes while other people is
>using normally the database using tansactions. In fact, the manual says also
>that in a nologging operation the DBA must have the exclusive access, and
>this confuses me.


This is a nasty feature, and IMO is just one more reason to avoid
SET NOLOGGING.  As you have discovered, there is nothing to prevent
some other poor soul toiling away in the database while you have turned
off logging for your session.  In fact to other users everything looks fine,
with proper transaction management and everything.  Unfortunately
things only look fine.  From the moment you turn off logging, the journal
files are invalidated and rollforward recovery is impossible.  You need to
do another checkpoint to make the database recoverable again.

>I made some tests in a test database (2.0 on NT) and while doing nologging
>transactions in session 1, the session 2 was able to do updates with commits
>and rollbacks (on other tables) without problems. Just doing a rollback on
>session 1 marked the database inconsistent, but this was expected.

Yes, the manual is reasonably clear about that.  It is not so clear about
what happens to your journal files.  Also, its comment that SET LOGGING makes
automatic database recovery automatic again refers only to the ability of
the recovery server to operate properly again.

To sum up, SET NOLOGGING can be used safely only in the way described
in the manual.  i.e. do checkpoints before and after, and single-user access
while logging is turned off.

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