|
|
|
In applications where a large number of rows will be accessed for either reading or modifying, c-treeACE 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.
Performance Gains
The charts below shows the results of a C# stored procedure adding 10 million records to a c‑tree data file (non-transaction file with no index). The blue line indicates performance (records added per second) and the red line indicates memory use.
|
Using individual record locks, performance (blue line) decreases over time and memory use (red line) increases over time. When the adds are finished and the commit happens, noticeable time is spent freeing locks. Insert time: 356 sec. |
|
Using table locks, record insert performance (blue line) starts higher than with individual record locks—however it stays at the same level of performance throughout. Memory use (red line) initially increases by a small amount and then stays at that level. Insert time: 284 sec (20% faster). |
