Specifying the Lock Mode for Indexes

Home | Previous Page | Next Page Managing Tables and Indexes > Planning and Managing Indexes >

Specifying the Lock Mode for Indexes

Unless you specify a different lock mode in the CREATE INDEX statement, the index has the same lock granularity as the indexed table.

Row or page locking adds unnecessary lock overhead to index accesses for indexes in which the keys are rarely or never changed. If you create indexes with the explicit COARSE lock mode, the database server places a shared lock on the entire index fragment instead of using key or page-level locking.

If applications rarely update the key columns in a table, increase locking efficiency by setting the lock mode for its indexes to COARSE.

Important:

If a user updates the index and associated table when the index is locked in COARSE mode, the updating user sets an exclusive lock on the table through the index. Other users cannot access the table until the updating user releases the lock.

To change the index lock mode after the index is created, use the ALTER INDEX statement. For information, refer to the IBM Informix: Guide to SQL Syntax. You can easily switch the lock mode between COARSE and NORMAL to accommodate periods when changes are made to key columns in the table. The onutil CHECK INDEX output displays the index lock mode.