![]() |
DML Statements and Performance
|
Reference Manual |
Locking and PerformanceRow-level locking improves concurrency in a multi-user system. However, a large number of row locks can degrade performance. About the Optimizer's Selection of Lock Granularity discussed the way the optimizer makes some compile-time decisions about escalating row locks to table locks for performance reasons. This section discusses ways in which the Cloudscape system and the user can make similar lock escalations. Transaction-Based Lock EscalationThe optimizer makes its decisions for the scope of a single statement at compile time; the runtime overrides are also for the scope of a single statement. As you know, a transaction may span several statements. For connections running in TRANSACTION_SERIALIZABLE isolation and for connections that are doing a lot of inserts or updates, a transaction may accumulate a number of row locks even though no single statement would touch enough rows to make the optimizer choose table-level locking for any single table. However, during a transaction, the Cloudscape system tracks the number of locks for all tables in the transaction, and when this number exceeds a threshold number (which you can configure; see Lock Escalation Threshold), the system attempts to escalate locking for at least one of the tables involved from row-level to table-level locking. The system attempts to escalate to table-level locking for each table that has a burdensome number of locks by trying to obtain the relevant table lock. If the system can lock the table without waiting, the system locks the entire table and releases all row locks for the table. If the system cannot lock the table without waiting, the system leaves the row locks intact. Once a table is locked in either mode, a transaction does not acquire any subsequent row-level locks on a table. For example, if a transaction locks the entire Hotels table in share mode in order to read data, it may later need to lock a particular row in exclusive mode in order to update the row. However, the previous table-level lock on Hotels forces the exclusive lock to be table-level as well. This transaction-based runtime decision is independent of any compilation decision. If when the escalation threshold was exceeded the system did not obtain any table locks because it would have had to wait, the next lock escalation attempt is delayed until the number of held locks has increased by some significant amount, for example from 5000 to 6000. Here are some examples assuming the escalation threshold is 5000:
LOCK TABLE StatementIn addition, you can explicitly lock a table for the duration of a transaction with the LOCK TABLE statement. This is useful if you know in advance that an entire table should be locked and want to save the resources required for obtaining row locks until the system escalates the locking. For information about this feature, see LOCK TABLE statement of the Cloudscape Reference Manual. |
|||||||||||||||||||||||||||||||||||||||||||||
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |