FairCom ISAM for C

Table Lock Support

In V11 and later, FairCom DB supports file-level c-tree data file locks, or more commonly referred to as "table locks." An application can request a file lock to prevent other connections from reading from and/or writing to the file.

• A file read lock (table read lock or shared table lock) allows other connections to acquire read locks on records in the file but not write locks.
• A file write lock (table write lock or exclusive table lock) prevents other connections from acquiring read and write locks on records in the file.

These locks can be invoked using standard SQL commands or from any of the supported FairCom DB interfaces using commands similar to those shown later in this section.

Table locks reduce resource usage with large sequences of actions on a table. A bulk add operation, for example, generates many lock requests. With a table lock the table is in an exclusive open state, thereby avoiding the necessity of managing a large number of new lock requests. This benefits both memory resource usage, as well as great gains in performance as overhead of lock management is greatly reduced.

Performance

The chart below shows the results of a test of the table lock. A C# stored procedure is adding 10 million records to a c-tree data file. The data file is a non-transaction file and has no index (to help isolate the effect of record locking).

The test was run twice: first with individual record locks and then with a table lock. In the chart below, the blue line indicates performance (records added per second) and the red line indicates memory use.

The chart below shows the results for the individual record lock case: the record insert performance (blue line) decreases over time, and memory use (red line) increases over time. And when the adds are finished and the commit happens, a noticeable time is spent freeing those locks. Insert time: 356 sec.

The chart below shows the results for the table lock case: the record insert performance (blue line) is higher than with individual record locks and stays at the same level of performance throughout. The memory use (red line) initially increases by a small amount and then stays at that level. Insert time: 284 sec (20% faster).

Table Locks with FairCom DB SQL

In applications where a large number of rows will be accessed for either reading or modifying, SQL provides an explicit locking construct for locking all the rows of a table. The LOCK TABLE statement explicitly locks a table in either SHARE or EXCLUSIVE mode.

The following example shows how to acquire a lock in the EXCLUSIVE mode for a table called customer from an ESQL command line:

EXEC SQL

LOCK TABLE customer IN EXCLUSIVE MODE;

The above statement will prevent other transactions from either reading or modifying the table customer until the transaction either commits or performs a rollback.

The following example shows acquiring lock in the SHARE mode for a table called orders:

EXEC SQL

LOCK TABLE orders IN SHARE MODE;

The above statement will prevent other transactions from modifying the orders table until the transaction either commits or performs a rollback.

Table Locks with c-tree API Functions

To acquire a file (table) lock using FairCom DB, call LOKREC() with a new ctLOCK_FILE mode as follows:

LOKREC(datno, ctLOCK_FILE, lockmode);

See Table Lock Mode for LOKREC for details.

Table Lock Behavior with Transactions

If a table write lock is in effect when a record is updated in a transaction, the table write lock cannot be removed until the transaction commits or aborts. Note that the call to free the table write lock returns success (NO_ERROR) and sysiocod is set to UDLK_TRN (-3) in this situation, indicating that the table write lock was not released.

In This Section Table Lock Mode for LOKREC

Table Lock Mode for LOKREC

To acquire a file (table) lock using FairCom DB call LOKREC() with a new ctLOCK_FILE mode as follows:

LOKREC(datno, ctLOCK_FILE, lockmode);

where:

• datno is the data file number
• lockmode is the desired locking mode, which can be any of the following values:

  ctREADREC - Acquire a file read lock. If the lock cannot be immediately acquired because a file write lock or record write locks exist on the file, then return an error.

  ctREADREC_BLK - Acquire a file read lock. If the lock cannot be immediately acquired because a file write lock or record write locks exist on the file, then block until those locks are released and the lock can be acquired.

  ctENABLE - Acquire a file write lock. If the lock cannot be immediately granted because file or record locks exist on the file, then return an error.

  ctENABLE_BLK - Acquire a file write lock. If the lock cannot be immediately acquired because file or record locks exist on the file, then block until those locks are released and the lock can be acquired.

To free a file lock, call LOKREC() as follows:

LOKREC(datno, ctUNLOCK_FILE, 0);

A file lock request can fail with the following errors:

• FACS_ERR(26) - The specified file number (datno) is not active.
• FMOD_ERR(48) - The specified file is a type of file that does not support file locks (for example, an index file, a superfile host, or a partitioned file).
• DEAD_ERR(86) - Could not block on file lock request due to deadlock.
• TLOK_ERR(1025) - Could not obtain table lock because the table is locked, or a request to lock the table is pending, or a conflicting data record read or write lock exists.

A record lock request that fails due to a table lock may encounter:

• DLKT_COD(1024) - Could not obtain data record lock because the table is locked, or a request to lock the table is pending (sysiocod).

A connection can only acquire one file lock on a particular file at a time.

A connection can acquire a table write lock when:

• no table write lock is held on the file

  and

• no table read lock is held on the file

  and

• either no read and write locks are held on the file, or only the table lock requester is holding read and/or write locks on the file.

A connection can acquire a table read lock when:

• no table write lock is held on the file

  and

• no write locks are held on the file

When a table write lock request is granted, the requesters write and read locks are released. All lock waiters are made to wait for the table to be unlocked.

Record lock and unlock requests for a connection that holds a table write lock have no effect.

A connection that has acquired a table read lock can promote the table read lock to a table write lock by requesting a table write lock. The request is granted if:

• only one table read lock is held on the file

  and

• no read locks are held on the file

  and

• no record read or write lock or table lock requests are waiting.

Notes

• Blocked table lock requests take priority over record lock requests. If releasing a lock makes it possible for a blocked table lock to be acquired, any record lock requests that are waiting on the lock that is being released queue up waiting for the table lock to be released.
• Blocked table write lock requests take priority over blocked table read lock requests.
• When a table read lock request is granted, the requester’s read locks are released. (Note that the requester has no write locks on the table when the table read lock request is granted; otherwise, the table read lock request would have been denied.) Record read lock requests for a connection that holds a table read lock have no effect. Record write lock requests for a connection that holds a table read lock are denied.
• A table lock is not supported on a superfile host, only on its data file members. A table lock request on a superfile host fails with error FMOD_ERR(48).
• A table lock is not currently supported on a partition host or partition member file. A table lock request on these types of files fails with error FMOD_ERR(48).

Table lock behavior with transactions

If a table write lock is in effect when a record is updated in a transaction, the table write lock cannot be removed until the transaction commits or aborts. Note that the call to free the table write lock returns success (NO_ERROR) and sysiocod is set to UDLK_TRN (-3) in this situation, indicating that the table write lock was not released.

A table lock cannot be released even if the transaction is restored to a savepoint that precedes the first update of a record in the locked table.

The ctMARK_XFREED and ctKEEP_XFREED transaction modes, used in calls to start and commit a transaction respectively, cause the commit to keep only the locks that were explicitly freed during the transaction. When these modes are used and a table lock is acquired before the transaction commits, the mode applies to the table lock: if a call is made to free the table lock before the transaction commits, the commit releases the table lock; otherwise, the commit keeps the table lock.

This feature is only available with the FairCom DB Server operational model.