Introduction

---

DBISAM manages most locking and concurrency issues without requiring any action on the part of the developer. The following information details the steps that DBISAM takes internally in order to maximize concurrency while still resolving conflicts for shared resources using locking.

How DBISAM Performs Locking

---

All locks in DBISAM are performed using calls to the operating system. If using a local session accessing DBISAM tables on a network file server, these calls are then routed by the operating system to the file server's operating system, which could be Windows, Linux, etc. The benefit of this approach is that dangling locks left from an improper shutdown can be cleaned up by the operating system rather quickly.

DBISAM takes advantage of the fact that both Windows and the Linux operating systems allow an application to lock portions of a file beyond the actual size of the file. This process is known as virtual byte offset locking. DBISAM restricts the size of any physical data, index, or BLOB file that is part of a table to 128,000,000,000 bytes by default, or a little under 128 gigabytes. DBISAM does this so it can reserve the space available between the 128 gigabyte mark and the 128,000,000,000 byte mark for record and semaphore locks in the table. For table locks DBISAM uses a special hidden file called "dbisam.lck" (by default) that it automatically creates in the database directory where the tables are stored. This file is only used for keeping a list of the tables in the database and for placing virtual byte offset locks for table read, write, and transaction locks. Using this one file for table locks allows DBISAM to perform transaction locking without encountering deadlocks, which was an issue in past versions of DBISAM. The default lock file name "dbisam.lck" can be modified to any file name desired by modifying the TDBISAMEngine LockFileName property.


Record Locking Protocols

---

DBISAM offers two types of record locking protocols, pessimistic (default) and optimistic locking. The record locking protocol is configurable via the TDBISAMSession LockProtocol property.


The two record locking protocols can safely and reliably be used among multiple sessions on the same set of tables, although it is not recommended due to the potential for confusion for the developer and user of the application.

User or Developer-Controlled Locks

---

There are three types of user or developer-controlled locks in DBISAM:

   Record Locks
   Table Locks
   Semaphore Locks

Record locks are initiated by the user or developer when a record is appended, edited, or deleted. Table locks and semaphore locks, on the other hand, must be specifically set by the developer.

Record Locks

---

Record locks are used to enforce DBISAM's pessimistic or optimistic record locking protocols and prevent the same or multiple sessions from editing or posting modifications to the same record at the same time. Record locks block other record or table lock attempts, but do not block any reads of the locked records. The following details what happens in the various scenarios that use record locks:


Table Locks

---

Table locks are used to allow the developer to prevent any other sessions from adding, editing, or deleting any records or placing any record or table locks on a given table. Table locks block other record or table lock attempts, but do not block any reads of the locked table. A table lock is equivalent to locking all of the records in a table, including any records that may be added in the future. Table locks are always pessimistic and are not affected by the record locking protocol in use for record locks.

The TDBISAMTable LockTable method is used to acquire a table lock. If the table lock fails, then an EDBISAMEngineError exception will be raised. The error code that is given when a table lock fails is 10241 and is defined as DBISAM_LOCKED in the dbisamcn unit (Delphi) or dbisamcn header file (C++). The TDBISAMTable UnlockTable method is used to remove a table lock. The following is an example of using the LockTable and UnlockTable methods of the TDBISAMTable component:

begin
   with MyTable do
      begin
      LockTable;
      try
         { Perform some updates to the table }
      finally
         UnlockTable;
      end;
      end;
end;

Locking all of the records in a table using the LockTable method is useful for ensuring that no other users or processes make changes to a given table while a batch process is executing.

Semaphore Locks

---

Semaphore locks are used to provide access serialization in specific user-defined application functionality such as batch updates or system configuration updates and are not required in the normal operation of DBISAM. Semaphore locks can be placed in what are simply referred to as slots, and these slots are numbered from 1 to 1024. Semaphore locks only block other semaphore lock attempts for the same slot.


The TDBISAMTable LockSemaphore method is used to place a semaphore lock. If the semaphore lock fails, then the result of the LockSemaphore method will be False. The TDBISAMTable UnlockSemaphore method is used to remove a semaphore lock. The following is an example of using the LockSemaphore and UnlockSemaphore methods of the TDBISAMTable component:

begin
   with MyTable do
      begin
      if LockSemaphore(1) then
         begin
         try
            { Perform a batch process }
         finally
            UnlockSemaphore(1);
         end;
         end;
      end;
end;

Lock Retry Count and Wait Time

---

The number of record and table lock retries and the amount of time between each retry can be controlled using the TDBISAMSession LockRetryCount and LockWaitTime properties. In a busy multi-user application it may be necessary to increase these values in order to relieve lock contention and provide for smoother concurrency between multiple users. However, in most cases the default values should work just fine.

Internal Locks Used by the Engine

---

There are three types of internal locks in DBISAM:

   Table Read Locks
   Table Write Locks
   Database Transaction Locks

Table tead locks are used by DBISAM to allow reads by multiple sessions while blocking any table write locks. Table read locks do not block other table read lock attempts. Table write locks, on the other hand, are used to serialize writes to a given table and therefore block any table read lock attempts or table write lock attempts.

Table Read Locks

---

Table read locks allow DBISAM to accurately treat reads on internal table structures such as the indexes or BLOB fields as atomic, or a single unit of work. Table read locks ensure that no other session writes to the table by blocking any table write locks. Table read locks are the most widely-used locks in DBISAM and are the cornerstone of correct multi-user operation. They especially play a large role in change detection. Please see the Change Detection topic for more information.

Table read locks are also acquired during table scans for un-optimized filter or query conditions. You can control the maximum number of table read locks acquired during a table scan via the TDBISAMEngine TableMaxReadLockCount property. Please see the Filter Optimization topic for more information on how filter conditions are optimized, and the SQL Optimizations topic for more information on optimizing SQL query conditions.

Table Write Locks

---

Table write locks allow DBISAM to accurately treat writes on internal table structures such as the indexes or BLOB fields as atomic, or a single unit of work. Table write locks ensure that no other session reads from or writes to the table by blocking any table read lock or write locks.

Database Transaction Locks

---

Database transaction locks allow DBISAM to treat multi-table updates within a transaction as atomic, or a single unit of work. Database transaction locks ensure that no other session writes to the database by blocking any table write locks while the transaction is in effect. Table read locks are allowed, however, and other sessions can read the data from tables and acquire record and table locks. During the commit of a transaction, the database transaction lock is escalated so that table read locks are also blocked while the transaction is written to the database.