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;

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.

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.