Transactions and the DataFlex Pervasive.SQL Driver
Pervasive’s MicroKernel Database Engine (MKDE) is a record management system that offers client-server facilities to applications.
Transactions and Locking
Pervasive.SQL supports two types of transactions: exclusive and concurrent. Exclusive transactions use table-level locking, while concurrent transactions use row-level locking. Pervasive.SQL fully supports the ACID transaction properties.
The moment at which a lock is applied differs from the moment the native DataFlex database acquires a lock. In native DataFlex, a lock is acquired when the lock command is issued. In Pervasive.SQL, locks are acquired as data is read. To simulate the DataFlex behavior, the driver supports the EXPLICIT_LOCKING setting. If set to true, the driver will lock active records immediately when the lock command is issued.
Explicit locking involves extra find operations on the open tables. Pervasive.SQL acquires a lock when a find operation is performed. To force the lock, a find must be executed. In the case of concurrent transactions, the driver will re-find all active records. For explicit transactions, it will re-find all active records and for the tables that do not have an active record, it will find the first (physical) record in the table.
Combining the two settings, the driver supports four different modes:
| Mode | Behavior |
|---|---|
| I (off / exclusive) | Tables are locked on the first find in the table after the lock command is issued. |
| II (off / concurrent) | Records are locked as they are found in the table. |
| III (on / exclusive) | Tables are locked the moment the lock command is issued. This is compatible with the native DataFlex database. |
| IV (on / concurrent) | Active records are locked the moment the lock command is issued. Consecutive find operations will lock records as they are found. |
Which of the four modes should be used depends on the application and the number of users. Table locks (exclusive transactions) should be used in processes that involve a lot of rows in the table. Row locking (concurrent transactions) should be used in most other cases. Whether the application needs explicit locking depends on the assumptions made in the transactions. The most likely modes to be used are mode II and III.
The Pervasive.SQL API offers two possible ways to lock: wait locking and nowait locking. When wait locking is used, the lock attempt will only return after success or if it has timed out or been chosen as a deadlock victim. When nowait locking is used, the lock attempt will immediately return, regardless of whether it succeeds or not. The DataFlex Pervasive.SQL Driver uses nowait locking. A timeout/delay mechanism is implemented in the driver itself, which can be set up through the attributes DF_LOCK_TIMEOUT and DF_LOCK_DELAY.
If the driver detects a timeout when trying to lock, it will generate the DFERR_LOCK_TIMEOUT (4106) error. Whenever this error is generated in a transaction, the automatic retry mechanism will be initiated. In case mode III is used, this is not a problem. In all other cases, it is possible that the transaction is already “well underway” when the error occurs. Variables and properties used in the transaction may have changed. Make sure to reset the variables and properties to the values they had at the transaction's beginning. A good place to trap the retry and reset variables and properties is in the Verify_Retry message sent in an object-oriented environment.
Deadlock
The DataFlex Pervasive.SQL Driver uses nowait locking. Deadlocks will never occur because transactions never wait for locks to succeed. This is handled at the driver level; the Pervasive.SQL mechanism for timeout and deadlock does not apply to transactions using nowait locking.