Database consistency is handled on two levels: physical and logical.
Physical consistency means that the tables are readable by Mimer SQL. This is ensured as long as the databank file is not physically damaged.
Logical consistency means that the tables contain valid data. This is ensured by Mimer SQL's transaction handling. All transactions are saved in the
TRANSDBdatabank during build-up and are applied to the databanks when they are committed. To use transaction handling, the databank must be created with the
Transaction handling makes it possible to ensure that a user cannot commit a read write transaction which has read data that is being concurrently updated by another user. If a transaction is successfully committed then all operations in the transaction are performed. If the transaction is aborted due to a conflict, none of the operations in the transaction are performed.
The tables may be logically inconsistent if Mimer SQL is stopped before all operations in a committed transaction have been performed. At some time after the system is restarted, all uncompleted transactions will be read from
TRANSDBfor automatic completion. This happens in the background on a per-databank basis, after a databank is first accessed following the restart. Transactions that were not committed before the stop are aborted.
DBOPENfacility, see Databank Open Function, can be used to open all databanks in one operation and thus achieve transaction consistency quickly.
LOGDB and TRANSDB Importance
Important: The information stored in
TRANSDBis vital to keep the database consistent in all circumstances, not only in case of failure.
LOGDBinformation will contain data on all the changes made to the databank from the time the backup copy of the databank was taken until the time of the disk crash. This information is used if a backup copy of a databank file is to be restored.
Note: Data changes that are not logged cannot be restored by this process, therefore it is important to consider the issue of transaction logging carefully.
If a databank becomes unavailable (because the Mimer SQL system is stopped deliberately or by a system failure) during the commitment of a transaction, information is retained in the
TRANSDBsystem databank and used to complete the transaction when the databank becomes available again.
This is only true for databanks with the
LOGoption. Once information has been successfully written to both
LOGDBand the databank file, it is removed from
It is recommended practice to back up all the databanks of the database at the same time, and to ensure that
LOGDBis always backed up whenever other databanks are backed up, because the
LOGDBinformation provides the transaction data which links the previous backup copy of a databank with the databank as it exists at the current point in time.
Thus, when restoring a databank it should be brought to a state consistent with the latest backup. This is done either by using the latest backup copy of the databank or by using backed up
LOGDBinformation with an older backup copy of the databank.
LOGDBsystem databank is then used to restore the final changes made between the time of the latest backup and the time the databank was lost.
The graphic above describes a scenario which ends up in a system crash.
To recover from this situation the common operation is to start from the most recent backup (T2) and then use the current
LOGDBto recover data up to the state at T3.
When the system is restarted, the current
TRANSDBis used to automatically recover up to the moment of the crash.
If the most recent backup cannot be used, an older backup has to be brought in (T1). This backup is restored up to the consistent state at T2 by using the
LOGDBstored in the backup at T2.
Then the current
TRANSDBare used to restore the transactions committed after the backup at T2.
Note: Wherever possible, LOGDB should be stored on a different disk unit, with a separate disk controller, from the other databanks in order to minimize the risk that a disk crash or damaged disk controller destroys both the log and the other databanks.
LOGDB and TRANSDB should always be located on different physical disks which are ideally served by separate disk controllers and no other databank files should be located on either disk, since data may be lost if both TRANSDB and LOGDB are destroyed.
Refer to Organizing Databank Files, for more details on data security and databank files.
Updates Recorded in LOGDB
LOGDBsystem databank contains logged update information for each databank with
It is recommended that all databanks, including
LOGDB, are backed up at the same time and that
LOGDBis cleared after backup by resetting the log. Thus, the backed up
LOGDBwill contain the information required to make databanks from the preceding backup consistent with the current backup.
This will provide double backup protection by allowing a lost databank to be recovered in one of the two ways listed below:
- restore the databank from the most recent backup and apply the updates currently held in
- restore the databank from an earlier backup, then sequentially use the
LOGDBfiles from each subsequent backup to make the databank consistent with the most recent backup, and finally apply the updates currently held in
The records in
LOGDBshould be cleared after a complete backup, in order to maintain consistency between the backup and
LOGDB. This ensures that
LOGDBonly contains information about changes made to a databank since the last backup of it was taken. (It is possible to backup databanks without clearing
LOGDBrecords, although care must be taken as this leaves the backup and
LOGDBin an inconsistent state).
The ability to restore databank updates from a backup copy of
LOGDBreplaces the databank incremental backups which were supported in previous versions of Mimer SQL. These are still supported for backward compatibility but it is now recommended that
LOGDBbackups always be taken to offer the same double protection.
Caution: If, for any reason, the LOGDB databank is lost, no problems will be encountered immediately. All changes will have been properly recorded in the application databanks. A new, empty, LOGDB can simply replace the log that was lost.
However, a backup of the entire database must be taken immediately. The new LOGDB will be empty, and therefore in a state consistent with a backup of all databanks having just been taken and all LOGDB records cleared.
TRANSDBrequires backup protection since it nearly always contains unfinished transactions. If
TRANSDBis lost before the Mimer SQL system is restarted, the database will be left in a logically inconsistent state.
Possible effects of losing
TRANSDBbefore the database server is restarted are described in the following scenarios:
TRANSDBis lost, some of the databank updates that apply to the most recently committed transactions may have been made while others remain unfinished.
The only safe operation to do to avoid a logically inconsistent database is to bring up the most recent backup copy and restore from
LOGDB. In this case, the only loss is those transactions that were not completely written to
- If both
LOGDBare lost, the restoration, as described in the previous bullet, cannot be accomplished.
In the case where a restore is not possible, the best solution is to repair the inconsistency immediately after restarting the database server. This is done by using a tool such as BSQL for manual verification and update of data. This is usually possible if the user who initiated the interrupted transaction can be identified and contacted. (Many applications maintain a parallel audit log file for tracking purposes which can be used as a basis for repair work).
An alternative solution if both
TRANSDBare lost, is to start over from the most recent backup of your databanks and reprocess all transactions since that time. This may be a costly operation.
LOGDBon separate disks under separate disk controllers will minimize the risk that both databanks are lost at the same time.
TRANSDBshadow is another possible security enhancement, see Mimer SQL Shadowing.
Note: The TRANSDB system databank must never be deliberately deleted, because uncompleted transactions nearly always remain saved in the databank even if the database server is currently stopped.
If a TRANSDB file containing uncompleted transactions is deleted, inconsistency will occur because the information required to complete those transactions when the database server is re-started will have been lost.
The contents of
SQLDBis transient, so this databank does not need backup protection.
However, it may be convenient to have
SQLDBincluded in the backup so that a complete system can be restored easily, without any additional operations to recreate an empty
Some data retrieval requests in Mimer SQL may require large work areas or transaction handling areas for intermediate processing of the data (for instance, requests to sort or group large result sets will require large work tables in
SQLDB). This is particularly relevant when ad-hoc queries may be submitted with little thought for the processing requirements or performance of the query. In systems where files expand automatically, the file for
SQLDBcan become very large as the result of one badly-planned query.
The databank attributes
MAXSIZEare to manage databank file sizes. See Mimer SQL Reference Manual, CREATE DATABANK.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40