Icon Transactions

Introduction
DBISAM allows for transactions in order to provide the ability to execute multi-table updates and have them treated as an atomic unit of work. Transactions are implemented logically in the same fashion as most other database engines, however at the physical level there are some important considerations to take into account and these will be discussed here.

Executing a Transaction
A transaction is executed entirely by using the StartTransaction, Commit, and Rollback methods of the TDBISAMDatabase component. A typical transaction block of code looks like this:

begin
   with MyDatabase do
      begin
      StartTransaction;
      try
         { Perform some updates to the table(s) in this database }
         Commit;
      except
         Rollback;
      end;
      end;
end;

Information It is very important that you always ensure that the transaction is rolled back if there is an exception of any kind during the transaction. This will ensure that the locks held by the transaction are released and other sessions can continue to update data while the exception is dealt with. Also, if you roll back a transaction it is always a good idea to refresh any open TDBISAMTable or TDBISAMQuery components linked to the TDBISAMDatabase component involved in the transaction so that they reflect the current data and not any data from the transaction that was just rolled back. Along with refreshing, you should make sure that any pending inserts or edits for the TDBISAMTable or TDBISAMQuery components are cancelled using the Cancel method before the transaction is rolled back to ensure that the inserts or edits are not accidentally posted using the Post method after the transaction is rolled back (unless that is specifically what you wish to do).

Restricted Transactions
It is also possible with DBISAM to start a restricted transaction. A restricted transaction is one that specifies only certain tables be part of the transaction. The StartTransaction method accepts an optional list of tables that can be used to specify what tables should be involved in the transaction and, subsequently, locked as part of the transaction (see below regarding locking). If this list of tables is nil (the default), then the transaction will encompass the entire database.

The following example shows how to use a restricted transaction on two tables, the Customer and Orders table:

var
   TablesList: TStrings;
begin
   TablesList:=TStringList.Create;
   try
      with MyDatabase do
         begin
         TablesList.Add('Customer');
         TablesList.Add('Orders');
         StartTransaction(TablesList);
         try
            { Perform some updates to the table(s) in the transaction }
            Commit;
         except
            Rollback;
            raise;
         end;
   finally
      TablesList.Free;
   end;
end;

Flushing Data to Disk During a Commit
By default, the Commit method will cause a flush of all data to disk within the operating system, which is eqivalent to calling the FlushBuffers method of all TDBISAMTable or TDBISAMQuery components involved in the transaction that were updated. The Commit method has an optional parameter that controls this called ForceFlush and it defaults to True. Passing False as the ForceFlush parameter will improve the performance of a commit operation at the expense of possible data corruption if the application is improperly terminated after the commit takes place. This is due to the fact that the operating system may wait several minutes before it lazily flushes any modified data to disk. Please see the Buffering and Caching topic for more information.

Locking During a Transaction
When a transaction on the entire database is started, DBISAM acquires a special transaction write lock on the entire database. This prevents any other sessions from adding, updating, or deleting any data from the tables in the database while the current transaction is active. When a restricted transaction is started on a specific set of tables, DBISAM will only acquire this special transaction write lock on the tables specified as part of the transaction. This special transaction write lock is a very important concept since it illustrates the importance of keeping transactions short (not more than a couple of seconds) in DBISAM. However, this special transaction write lock does not prevent other sessions from reading data from the tables involved in the transaction or acquiring record or table locks on the tables involved in the transaction while the current transaction is active. This means that it is still possible for other sessions to cause a TDBISAMTable or TDBISAMQuery Edit or Delete method call within the current transaction to fail due to not being able to acquire the necessary record lock.

Any record locks acquired by calling the TDBISAMTable or TDBISAMQuery Edit or Delete methods during a transaction will remain locked even after a call to the TDBISAMTable or TDBISAMQuery Post method. This is also the case for table locks acquired via the TDBISAMTable LockTable method, which will remain locked even after a call to the TDBISAMTable UnlockTable method has been made. These locks will be released when the transaction is rolled back or committed, but not until that point. This can have some adverse side affects with some network operating systems such as Netware that only permit a fixed number of locks per connection. These locks can accumulate over the course of a lengthy transaction and you can run into this limit rather quickly and end up with a locking error that is seemingly coming from nowhere. If you plan on executing many inserts, updates, or deletes within a single transaction then you should make sure to check your network operating system documentation in order to verify that you won't run into any limitations such as this.

Opening and Closing Tables
If a transaction on the entire database is active and a new table is opened via the TBISAMTable or TDBISAMQuery components, that table will automatically become part of the active transaction. Unlike a transaction on the entire database, if a table involved in a restricted transaction is not currently open at the time that StartTransaction is called, then an attempt will be made to open it at that time. Also, any tables that are opened during the restricted transaction and not initially specified as part of the restricted transaction will be excluded from the transaction. If a table involved in a transaction, either restricted or not, is closed while the transaction is still active, the table will be kept open internally by DBISAM until the transaction is committed or rolled back, at which point the table will then be closed. However, the TDBISAMTable or TDBISAMQuery component that opened the table originally will indicate that the table is closed.

SQL and Transactions
The INSERT, UPDATE, and DELETE SQL statements implicitly use a restricted transaction on the updated tables if a transaction is not already active. The interval at which the implicit transaction is committed is based upon the record size of the table being updated in the query and the amount of buffer space configured for the TDBISAMEngine component via its MaxTableDataBufferCount and MaxTableDataBufferSize properties. The COMMIT INTERVAL clause can be used within these SQL statements to manually control the interval at which the transaction is committed, and applies both to situations where a transaction was explicitly started by the developer and situations where the transaction was implicitly started by DBISAM. In the case where a transaction was explicitly started by the developer, the absence of a COMMIT INTERVAL clause in the SQL statement being executed will force DBISAM to never commit any of the effects of the SQL statement and leaves this up to the developer to handle after the SQL statement completes. The COMMIT INTERVAL clause can also contain the FLUSH keyword, which indicates that any transaction commit that takes place during the execution of the SQL statement should also force an operating system flush to disk. By default, commits that occur during the execution of SQL statements do not force an operating system flush to disk.

In addition to implicit transactions with the INSERT, UPDATE, and DELETE SQL statements, DBISAM also allows the use of the START TRANSACTION, COMMIT, and ROLLBACK SQL statements.

Incompatible Operations
The following operations are not compatible with transactions and will cause a transaction to commit if encountered during a transaction.

   Backing Up and Restoring Databases

   Verifying and Repairing Tables

   Creating and Altering Tables

   Adding and Deleting Indexes from a Table

   Optimizing Tables

   Upgrading Tables

   Deleting Tables

   Renaming Tables

   Emptying Tables

   Copying Tables

Isolation Level
The default and only isolation level for transactions in DBISAM is serialized. This means that only the session in which the transaction is taking place will be able to see any inserts, updates, or deletes made during the transaction. All other sessions will see the data as it existed before the transaction began. Only after the transaction is committed will other sessions see the new inserts, updates, or deletes.

Data Integrity
A transaction in DBISAM is buffered, which means that all inserts, updates, or deletes that take place during a transaction are cached in memory for the current session and are not physically applied to the tables involved in the transaction until the transaction is committed. If the transaction is rolled back, then the updates are discarded. With a local session this allows for a fair degree of stability in the case of a power failure on the local workstation, however it will not prevent a problem if a power failure happens to occur while the commit operation is taking place. Under such circumstances it's very likely that physical and/or logical corruption of the tables involved in the transaction could take place. The only way corruption can occur with a remote session is if the database server itself is terminated improperly during the middle of a transaction commit. This type of occurrence is much more rare with a server than with a workstation.
Image