LOCK MODE Clause

Use the LOCK MODE keywords to change the locking granularity of an existing table.

This syntax fragment is part of the ALTER TABLE statement.

LOCK MODE Clause

The following table describes the locking-granularity options available.

Granularity: Effect
PAGE: Obtains and releases one lock on a whole page of rows
If no system default locking granularity has been set by the IFX_DEF_TABLE_LOCKMODE environment variable or by the DEF_TABLE_LOCKMODE configuration parameter, this is the default. Page-level locking is especially useful when you know that the rows are grouped into pages in the same order that you are using to process all the rows. For example, if you are processing the contents of a table in the same order as its cluster index, PAGE is usually more appropriate setting than than ROW.
ROW: Obtains and releases one lock per row
Row-level locking supports the highest level of concurrency. Only tables with row-level locking support the LAST COMMITTED feature, which can improve performance in the Committed Read and Dirty Read isolation levels when another session holds an exclusive lock on a row that you attempt to read. If you are using many rows at one time, however, the lock-management overhead of row-level locking can become significant. You can also exceed the maximum number of locks available, depending on the configuration of your database server.

The following statement changes the lock mode for the customer table to page-level locking:

ALTER TABLE customer LOCK MODE(PAGE);

The next example changes the lock mode for the customer table to row-level locking:

ALTER TABLE customer LOCK MODE(ROW);

Important:

The SET LOCK MODE statement of SQL has no effect on the locking granularity of tables. You can use that statement, however, to define how the database server resolves locking conflicts when one process tries to access a row or a table that has been locked by a concurrent process. For the syntax and semantics, see SET LOCK MODE statement.

The term lock mode also has a third meaning for smart large objects in JDBC contexts, when a row contains one or more smart large objects. Here the scope of a lock can be either only a subset the smart large object, or else it can lock the entire BLOB or CLOB.