Transaction Logging in FrontBase 3.x

By: Peter Haff,November 27, 2001

Contents

Implementation
Administration
SQL syntax
Invocation options
Utilities
More information

Introduction

By default, FrontBase 3.x maintains a transaction history log. This is a complete list, in the form of SQL statements, of all transactions that have altered data or structure in the database. Such a log therefore provides a complete history of the database development and it is possible to re-create a database from a particular starting point by essentially replaying the SQL statements of the transaction log. Such a particular starting point is either the creation of the database or the point where a backup of the database was created.

The concept of transaction logging in FrontBase serves several purposes:

1. It provides an extra level of security against loss of data
2. It enables the database server to decouple its client interface handling from its handling of disk operations (i.e. the client handling does not have to wait for disk write operations to be completed)
3. It serves as the basis for database clustering and replication.

The second point above implies that the transaction log may be "ahead" of the actual database contents at any given point in time. When FrontBase is started for an existing database, it therefore automatically examines if the transaction log contains transactions that have not been committed to the database and in this case the database is brought up to date.

This mechanism of automatically bringing a database up to date with respect to its transaction log is also useful in connection with clustering and replication.

Implementation

The transaction log of a database is found in the TransactionLogs directory of the FrontBase installation: TransactionLogs/<dbname>.

This directory contains one or more transaction log directories, each of which are named: L_yyyy_mm_dd-hh_mm_ss, identifying the point in time when the directory was created. The names should therefore provide the correct ordering of the transaction log directories when more than one exists.

A transaction log directory contains a file named transactions.log plus possibly files named tttttttt.sql (where tttttttt is a transaction number). The latter files contain particularly large transactions. FrontBase attempts to keep the log of a transaction in-core while it is ongoing, only writing it to the log file when the transaction is committed. However, there is a limit on the size of the in-core transaction (currently 1 MB) which, when exceeded, implies that the transaction is written to a log file of its own.

There is a limit to the (combined) size of files kept in a transaction log directory which, when exceeded, implies that a new transaction log directory is created. This limit is 512 MB by default, but may be changed by the user.

When a transaction log is used to update a database from a particular starting point, it is obviously necessary that the starting point is well defined. It is therefore such that when a backup of a database is created, indivisibly a change in the transaction log directory will take place. The names of the backup file and the new transaction log directory will be identical, except for the two first characters (B_ and L_, respectively).

Administration

The use of transaction logging is optional, but it is the FrontBase default and recommended for added safekeeping. Notice that transaction logging is required for running FrontBase in replication and clustering contexts.

When transaction logging is enabled, FrontBase permits the user interface to continue even before a transaction has been physically written to the file system. This may mean that the transaction log is "ahead" of the database as found on the disk, and in this situation the transaction log is vital for preserving transactions. The state of the database on the disk is always consistent - i.e. it reflects the latest transaction actually written to the disk - but it may actually lag behind the state of the transaction log.

When the FrontBase server is started on an existing database, it is automatically verified that the database is up to date with respect to the transaction log (if present), and if not, it is brought so. This implies that the latest transaction log directory is definitively always relevant. In general, it is difficult to predict exactly when changes to the transaction log directory (brought about by the log size limitation) takes place, and it is also difficult to predict just how far the transaction log is ahead of the database. Therefore it is safest to state that transaction log files are relevant back to the last well-defined starting point: Database creation or backup point.

It is never necessary to remove transaction log files for any other reason than disk space economy.

The following simple rule dictates the administrative handling of transaction logging:

A database backup indivisibly creates a new transaction log directory and makes all previous transaction log directories obsolete.

This means that with respect to the just created backup, all previous transaction log directories may be deleted. How many generations of backup and accompanying transaction logs an installation wishes to keep before physically deleting them is an individual matter.

SQL syntax

The following SQL statements have been introduced for handling transaction logging:

CREATE TRANSACTION LOG;
Enables transaction logging (default). If transaction logging was disabled, a new transaction log directory would be created

DROP TRANSACTION LOG;
Disables transaction logging (not recommended)

SET TRANSACTION LOG LIMIT <0integer-expr>
Sets the size of a transaction log directory to n MB, where n is the value specified by <integer-expr>

SHOW LOGS [<integer-expr>];
Shows a summary of the n latest transaction log directories, where n is the value specified by <integer-expr>. A value of 0 (or ALL) implies all existing transaction log directories. The absence of <integer-expr> implies 1 (i.e. the newest)

SWITCH TO NEW TRANSACTION LOG;
Creates a new transaction log directory

WRITE DATA;
Creates a backup of the database and indivisibly creates a new transaction log directory. This way, the backup provides a well-defined starting point for the transaction log just initiated.

Invocation options

The following new invocation options for FrontBase have been introduced:

-tlog=no|yes
Overrides, at the time of starting the server, the transaction logging mode stored with the database; this mode, however, is not permanently changed

-keeptlog
Prevents the server from clearing the transaction log, if one exists

Neither of these options are useful during normal (nominal) operation.

Utilities

The following utilities serve to inspect the state and contents of the transaction log for a given database. They can be found in your <install-path>/FrontBase/bin directory:

FBTLog [-d <transactionlog-directory>] <database-name> [<transaction-number>]
Lists transactions in a transaction log, by default starting from the oldest transaction log directory. If specified, <transaction-number> identifies the oldest transaction number that should be listed, and the -d option identifies the specified transaction log directory as the starting point.

FBTLogs [-s <file-count>] <database-name>
Shows a summary of the n latest transaction log directories, where n is the value specified by <file-count>. A value of 0 (default) implies all existing transaction log directories.

More information

The FrontBase Users Guide is available at FrontBase home page

Please mail us at support@frontbase.com

C J Date and Hugh Darwen A Guide to the SQL Standard published by Addison-Wesley - ISBN #: 0-201-96426-0