When an application requests a wait lock on a record that is unavailable, the transactional interface checks for a deadlock condition. You can configure the transactional interface to wait before returning a deadlock detection status code. Doing so improves performance in multi-user situations by allowing the transactional interface to wait internally, rather than forcing the application to retry the operation.

If you have a number of modifications to make to a file and you must be sure that either all or none of those modifications are made, include the operations for making those modifications in a transaction. By defining explicit transactions, you can force the transactional interface to treat multiple operations as an atomic unit. Other users cannot see the changes made to a file until the transaction ends. The transactional interface supports two types of transactions: exclusive and concurrent.

In an exclusive transaction, the transactional interface locks the entire data file when you insert, update, or delete a record in that file. Other applications (or other instances of the same application) can open the file and read its records, but they cannot modify the file. The file remains locked until the application ends or aborts the transaction.

In a concurrent transaction, the transactional interface can lock either records or pages in the file, depending on the operation you are performing. The transactional interface enables multiple applications (or multiple instances of the same application) to perform modifications inside concurrent transactions in different parts of the same file simultaneously, as long as those modifications do not affect other previously locked portions of the file. The record or page remains locked until the application ends or aborts the transaction. Concurrent transactions are available only for 6.0 and later files.

Clients can still read records from a file even if a concurrent transaction has locked the requested record. However, these clients cannot be operating from within an exclusive transaction. Also, they cannot apply a lock bias to their read operation if the file containing the requested record is currently locked by an exclusive transaction, or if a concurrent transaction has locked the requested record.

You can configure the transactional interface to guarantee Transaction Durability (page 4-50) and atomicity by logging all operations to a single transaction log. Transaction durability is the assurance that the transactional database engine finishes writing to the log when a client issues the End Transaction operation and before the transactional database engine returns a successful status code to the client. Atomicity ensures that if a given statement does not execute to completion, then the statement does not leave partial or ambiguous effects in the database, thereby ensuring the integrity of your database by keeping it in a stable state.

If you want atomicity without the overhead of Transaction Durability, you can use Transaction Logging feature in Pervasive PSQL V8 and later releases. See Advanced Operations Guide for more information on Transaction Logging.

To improve performance on specific files, you can open a file in Accelerated mode. (Version 6.x transactional database engine accepted Accelerated open requests, but interpreted them as Normal open requests.) When you open a file in Accelerated mode, the transactional database engine does not perform transaction logging on the file.

Pervasive PSQL uses a 7.x transaction log file format. In order for the transactional interface to log transactions on a file, the file must contain a log key, which is a unique (non-duplicatable) key that the transactional interface can use to track the record in the log. For files that have at least one unique (non-duplicatable) key, the transactional interface uses one of the unique keys already defined in the file.

For files that do not have any unique keys, the transactional interface can include system data upon file creation. The transactional interface includes system data in a file only if the file uses the 7.x file format or later and if at the time the file is created, the transactional interface is configured to include system data in files upon creation. System data is defined as an 8-byte binary value with the key number 125. Even if a file has a unique, user-defined key, you may want to use system data (also called the system-defined log key), to protect against a user dropping an index.

The database engine turns on data compression for the file if the file uses system data and the record length exceeds the limit shown in Table 12.

The transactional database engine uses shadow paging to protect 6.0 and later files from corruption in case of a system failure. When a client needs to change a page (either inside or outside a transaction), the transactional database engine selects a free, unused physical location in the data file itself and writes a new page image, called a shadow page, to this new location. During a single transactional interface operation, the transactional database engine might create several shadow pages all the same size as the original logical pages.

For information on backing up your files, please see the following topic in Advanced Operations Guide: Logging, Backup, and Restore.