Transaction Logging and Durability

PSQL offers two levels of data integrity assurance for database operations that involve transactions: Transaction Logging and Transaction Durability.

This section contains the following sub-topics:

•

Using These Features

•

Feature Comparison

•

Which Feature Should I Use?

•

How Logging Works

•

See Also

Using These Features

Both of these features can be turned on or off in the database engine using configuration within PSQL Control Center, or programmatically using the Distributed Tuning Interface. See Transaction Durability and Transaction Logging.

The default value for Transaction Durability is Off, and the default value for Transaction Logging is On.

Feature Comparison

Both features offer multifile transaction atomicity, to ensure that the data files remain consistent as a set and that incomplete transactions are never written to any data files.

Atomicity means that, if any given data operation within a transaction cannot successfully complete, then none of the operations within the transaction are allowed to complete. An atomic change does not leave partial or ambiguous effects in the database. Changes to individual files are always atomic whether Transaction Logging and Transaction Durability are on or off. But transactions make it possible to group changes to multiple files into one atomic group. The atomicity of these multifile transactions are assured by the MicroKernel only when using transactions in your application, and Transaction Logging or Transaction Durability is turned on.

In addition to these benefits, Transaction Durability guarantees that, in the event of a system crash, the data files will contain the full results of any transaction that returned a successful completion status code to the application prior to the crash.

In the interest of higher performance, Transaction Logging does not offer this guarantee. Whereas Transaction Durability ensures that a completed transaction is fully written to the transaction log before the engine returns a successful status code, Transaction Logging returns a successful status code as soon as the logger thread has been signaled to flush the log buffer to disk.

Transaction Logging is a sub-set of Transaction Durability; that is, if Transaction Durability is turned on, then logging takes place and the Transaction Logging setting is ignored by the database engine.

The main differences between Transaction Logging and Transaction Durability are shown in the following tables:

Table 27

Transaction Logging vs. Transaction Durability: Benefits

Feature

Guaranteed data consistency and transaction atomicity across multiple files

Guaranteed commit for all completed transactions that have returned a successful status code

Transaction Logging

Yes

Transaction Durability

Yes

Table 28

Transaction Logging vs. Transaction Durability: Function

Feature

Timing of log buffer writes to disk

Transaction Logging

The log buffer is written to the log file when the log buffer is full or Initiation Time Limit is reached. A successful status code for each End Transaction operation is returned to the application as soon as the logger thread has been signaled to flush the buffer to disk.

Transaction Durability

The log buffer is written to the transaction log file with each End Transaction operation. A successful status code for each End Transaction operation is not returned to application until the log disk write is successful. For insert or update operations that are not part of a transaction, the log buffer is written to the log file when the log buffer is full or Initiation Time Limit is reached.

Which Feature Should I Use?

For the fastest performance, you want to use the lowest level of logging that meets your transaction safety needs. The best way to determine your appropriate level of logging is to ask your application vendor. If you have multiple applications that use PSQL on the same computer, you must use the highest level of logging required by any of the applications.

If you only have one data file, or if none of your applications perform transactions involving multiple data files, you generally do not need to use Transaction Durability or Transaction Logging. Under these circumstances, PSQL guarantees the internal consistency of each data file, with or without logging.

Transaction Logging

Turn on Transaction Logging if at least one of your PSQL applications performs transactions across multiple data files. Without Transaction Logging, PSQL cannot guarantee multifile atomicity of transactions or multifile data integrity.

In the event of a system crash, this level of logging does not guarantee that every completed transaction has been written to the data files.

Transaction Durability

Turn on Transaction Durability if at least one of your PSQL applications requires that completed transactions across multiple data files be absolutely guaranteed to have been written to the data files under almost any circumstances.

In the event of a system crash, this level of logging guarantees that every transaction that has been successfully completed has been written to the data files.

How Logging Works

Note that these features ensure atomicity of transactions, not of operations. If you are using SQL, a transaction is defined as a set of operations that take place between a BEGIN statement or START TRANSACTION statement, and an END or COMMIT statement. If you are using Btrieve, a transaction is defined as a set of operations that take place between a Start Transaction operation and an End Transaction operation.

All data file inserts and updates are stored in the log buffer. When a transaction is completed (Transaction Durability) or when the buffer gets full or the Initiation Time Limit is reached (Transaction Durability or Transaction Logging), the buffer is flushed to the transaction log file.

In the case of Transaction Logging, when the engine receives the operation ending the transaction and successfully signals the logger thread to flush the log buffer to disk, the engine returns a successful status code to the application that initiated the transaction. In the case of Transaction Durability, the engine does not return the successful status code until the logger thread signals that is has successfully written the buffer to disk.

Transaction log file segments are stored in the location specified in the setting Transaction Log Directory. The log segments are named *.LOG, where the prefix can be 00000001 through FFFFFFFF.

Note: All operations, regardless of whether they take place within a transaction, are written to the log file when Transaction Logging or Transaction Durability is in effect. However, only operations executed within a transaction are guaranteed to be atomic. In the case where a system crash has occurred and the transaction log is being rolled forward, only completed transactions are committed to the data files. All operations without an associated End Transaction operation are rejected, and are not committed to the data files.

Tip: If your database is highly used, consider configuring your system to maintain the transaction logs on a separate physical volume from the volume where the data files are located. Under heavy load, performance is typically better when the writes to the log files and to the data file are split across different drives instead of competing for I/O bandwidth on a single drive. The overall disk I/O is not reduced, but the load is better distributed among the disk controllers.

You can specify the location of the transaction logs using the configuration setting Transaction Log Directory.

If a system failure occurs after the log file has been written but before the committed operations are flushed to the data files in a system transaction, the committed operations are not lost. In order to flush the committed operations the affected files need to be opened and operations performed after the system failure. When the files are opened and operations attempted, it is then that the data is rolled forward to the files affected at the time of system failure. Simply restarting the database engine will not invoke the roll forward operation nor will it make the data consistent.

Note: Log files associated with the rolled forward files will not be automatically deleted, as they may be associated with more than one data file.

This feature allows individual client transactions to receive a successful status code as soon as possible while at the same time taking advantage of performance gains offered by grouping multiple client transactions together and writing them to the data files sequentially.

If your database server suffers a disk crash of the volume where the data files are stored, and you have to restore the data from an archival log, the engine does not roll forward the transaction log file. The archival log contains all the operations in the transaction log, so there is no need to roll forward the transaction log.

Tip: After a system failure, open all data files and perform a stat or read operation on those files. Once you are certain that all data has been restored, old log files may then be stored in a safe location.