|
|
|
It can be useful for performance reasons to avoid transaction memory usage in some common maintenance operations. Consider the case of upgrading a large database where some tables are added with a SQL query from another table that returns a large number of rows. As this occurs inside a single SQL statement the transaction consumes memory until committed. For large files, this can potentially exhaust memory on some systems and result in complete failure.
FairCom DB can allow the transaction processing mode of a file to be disabled which avoids consuming memory for a very large operation. To make this available to FairCom DB SQL, a built-in stored procedure allowing the transaction mode of a file to be changed has been added. This operation requires exclusive file access.
fc_set_file_tran_state(VARCHAR owner, VARCHAR tablename, TINYINT mode)
Valid values for mode are
When a FairCom DB SQL file is set to mode of 0 (no transaction control), all changes to the file will have immediate and permanent effect, such that a ROLLBACK operation will have no effect on this file. If another user is able to open the file, isolation levels are undefined. Furthermore, FairCom DB SQL operations that modify multiple rows of this table will not be atomic in the event of an error.
For update statements, it is important to ensure no errors are encountered during execution. For example, a statement such as
UPDATE tbl SET col=99
that encounters an error midway through the update, will leave the state of the table undefined and the operation should be re-examined. It is possible it will be necessary to restore from a backup to return to a previous state.
Because of these effects, the intended use of this mode is limited in scope. No file definition/schema changes to this file should occur while in this mode. All previous schema changes should be committed before calling this procedure. If an error occurs while operating on a file with a mode 0, the table should be deleted and the transaction rolled back.
Note: The stored procedure fc_check_file_tran_state() can be used to ensure that no files remain in modes other than the database default.
