DML Statements and Performance
Page 3 of 4

Locking and Performance

Row-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 Escalation

The 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:

  • single table holding the majority of the locks

    Table

    Number of Row Locks

    Promote?

    Hotels

    4853

    yes

    Countries

    3

    no

    Cities

    12

    no

  • two tables holding the majority of the locks

    Table

    Number of Row Locks

    Promote?

    Hotels

    2349

    yes

    Countries

    3

    no

    Cities

    1800

    yes

  • many tables holding a small number of locks

    Table

    Number of Row Locks

    Promote?

    table001

    279

    no

    table002

    142

    no

    table003

    356

    no

    table004

    79

    no

    table194

    384

    no

    table195

    416

    no

LOCK TABLE Statement

In 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.