Configuring the lock mode

When you create a table, the default lock mode is page. You can change the lock mode (and thus increase or decrease concurrency) when you create or alter tables or by setting the IFX_DEF_TABLE_LOCKMODE environment variable or the DEF_TABLE_LOCKMODE configuration parameter.

If you know that most of your applications might benefit from a lock mode of row, you can take one of the following actions:
  • Use the LOCK MODE ROW clause in each CREATE TABLE statement or ALTER TABLE statement.
  • Set the IFX_DEF_TABLE_LOCKMODE environment variable to ROW so that all tables you subsequently create within a session use ROW without the need to specify the lock mode in the CREATE TABLE statement or ALTER TABLE statement.
  • Set the DEF_TABLE_LOCKMODE configuration parameter to ROW so that all tables subsequently created within the database server use ROW without the need to specify the lock mode in the CREATE TABLE statement or ALTER TABLE statement.

If you change the lock mode with the IFX_DEF_TABLE_LOCKMODE environment variable or DEF_TABLE_LOCKMODE configuration parameter, the lock mode of existing tables are not affected. Existing tables continue to use the lock mode with which they were defined at the time they were created.

In addition, if you previously changed the lock mode of a table to ROW, and subsequently execute an ALTER TABLE statement to alter some other characteristic of the table (such as add a column or change the extent size), you do not need to specify the lock mode. The lock mode remains at ROW and is not set to the default PAGE mode.

You can still override the lock mode of individual tables by specifying the LOCK MODE clause in the CREATE TABLE statement or ALTER TABLE statement.

The following list shows the order of precedence for the lock mode on a table:
  • The system default is page locks. The database server uses this system default if you do not set the configuration parameter, do not set the environment variable, or do not specify the LOCK MODE clause in the SQL statements.
  • If you set the DEF_TABLE_LOCKMODE configuration parameter, the database server uses this value when you do not set the environment variable, or do not specify the LOCK MODE clause in the SQL statements.
  • If you set the IFX_DEF_TABLE_LOCKMODE environment variable, this value overrides the DEF_TABLE_LOCKMODE configuration parameter and system default. The database server uses this value when you do not specify the LOCK MODE clause in the SQL statements.
  • If you specify the LOCK MODE clause in the CREATE TABLE statement or ALTER TABLE statement, this value overrides the IFX_DEF_TABLE_LOCKMODE, the DEF_TABLE_LOCKMODE configuration parameter and system default.