LOCK MODE Options

Use the LOCK MODE options to specify the locking granularity of the new table.

The following table describes the locking-granularity options.


Granularity	Effect
PAGE	Obtains and releases one lock on a whole page of rows If neither the IFX_DEF_TABLE_LOCKMODE environment variable nor the DEF_TABLE_LOCKMODE configuration parameter has set a default locking granularity, this is the system 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 locking is appropriate.
ROW	Obtains and releases one lock per row Row-level locking provides the highest level of concurrency. If you are using many rows at one time, however, the lock-management overhead can become significant. You might also exceed the maximum number of locks available, depending on the configuration of your database server, but HCL OneDB™ can support up to 18 million locks on 32-bit platforms, or 600 million locks on 64-bit platforms. Only tables with row-level locking can support the LAST COMMITTED isolation level feature.

Examples of setting the locking granularity

The following CREATE TABLE statement defines a new table tsTab_j with column id of type INT and column ts of the TIMESERIES(ts_data_j ) extended data type, with ROW as the locking granularity:

CREATE TABLE IF NOT EXISTS tsTab_j(
   id  INT NOT NULL PRIMARY KEY,
   ts  TIMESERIES(ts_data_j)
) LOCK MODE ROW;

In the next example, a user who holds the DBSECADAM role creates a Tab5 table with both row-level and column-level granularity of LBAC protection, and with PAGE as the locking granularity. is

CREATE TABLE Tab5 (C1 IDSSECURITYLABEL,
   C2 int,
   C3 char (10) COLUMN SECURED WITH label6)
   SECURITY POLICY company
   LOCK MODE(PAGE);

This ALTER TABLE . . . LOCK MODE statement can change the locking granularity, as in this example for the Tab5 table:

ALTER TABLE Tab5 LOCK MODE(ROW);

Important:

The SET LOCK MODE statement of SQL has no effect on the locking granularity of tables. For the syntax and semantics, see SET LOCK MODE statement.