|
|
|
In V11 and later, c-treeACE 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.
These locks can be invoked using standard SQL commands or from any of the supported c-treeACE 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 c-treeACE 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 c-treeACE, 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.
