IFX_SESSION_LIMIT_LOCKS session environment option

When IFX_SESSION_LIMIT_LOCKS is set in the session, its setting can specify a lower limit than the SESSION_LIMIT_LOCKS configuration parameter value for the maximum number of locks for users who are not administrators. This option, however, cannot restrict the number of locks in the session of a user who holds administrative privileges, such as user informix or a DBSA user, and cannot specify a limit for nomadministrative users below 500 locks.

The SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS statement cannot reset the maximum number of locks in a session to a value higher than the current setting of the SESSION_LIMIT_LOCKS configuration parameter.

The SET ENVIRONMENT AUTOLOCATE statement of SQL supports the following syntax:

IFX_SESSION_LIMIT_LOCKS environment option


Element	Description	Restrictions	Syntax
integer	Nonnegative integer that defines how many locks in the internal lock table are available during the session for nonadministrative users	Must be in the range `500 < integer <` SESSION_LIMIT_LOCKS value	Quoted String

Usage

For users who are not administrators of the database server instance, the IFX_SESSION_LIMIT_LOCKS session environment option can have an integer values greater than 499 but no greater than the current value of the SESSION_LIMIT_LOCKS configuration parameter.

'integer' or "integer": This defines the maximum number of locks that a nonadministrative user can hold during the session. If the SESSION_LIMIT_LOCKS configuration parameter is not set, the upper limit is 2,147,483,647 locks.

If the IFX_SESSION_LIMIT_LOCKS session environment option and the SESSION_LIMIT_LOCKS configuration parameter are set to different values, the session environment option takes precedence over the configuration parameter for operations during the current sessions by users who are not administrator, but only until the current session ends. If neither of those values is set, the default limit for every user is 2,147,483,647 locks.

Lock limits set by users who are not administrators

If you are a regular user, you (or the sysdbopen routine that configures your session) can set the IFX_SESSION_LIMIT_LOCKS session environment option only to an unsigned integer value between the minimum (500) and the current value set by the SESSION_LIMIT_LOCKS configuration parameter.

For example, you might execute the following statement to restrict subsequent DDL and DML operations during the session to no more than 1056 locks:

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS '1056';

If you attempt to specify an invalid value, the database server returns an error:

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS '400';  --Below lower limit

 -26041: Invalid values specified for the environment variable.

Issuing this exception is unlike the behavior of the SESSION_LIMIT_LOCKS configuration parameter, which replaces any nonnegative setting in the range from 0 to 499, or any negative value, with 500, the minimum value for nonadministrative users.

Important:

Because an insufficient locks can cause DML or DDL operations to fail, nonadministrative users should generally use caution when considering whether to use this session environment variable to restrict the number of locks available in contexts like these:

If the session is using Repeatable Read isolation level for operations on large tables, because each row touched requires a lock.
If the session of a non-DBSA user is running commands of the cdr utility of Enterprise Replication.

In data processing contexts that require very large numbers of locks, however, administrators might set SESSION_LIMIT_LOCKS (or might set IFX_SESSION_LIMIT_LOCKS in sysdbopen routines for specific users or for specific roles) to a value intended to reduces the risk of nonadministrative users in concurrent sessions depleting the lock resources of the database server, thereby interfering with massively lock-intensive operations.

Lock limits set by administrative users

If you are a user who holds administrative privileges, such as user informix or a DBSA user, the setting of IFX_SESSION_LIMIT_LOCKS has no effect. The only value that you can set is 2147483647, which is already the default limit for administrative users. If you attempt to set this option to any other specific value, the database server will either issue the -26041 invalid value error, or else error -26000, indicating that administrative users cannot set a limit on the locks in their own sessions:

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "2147456789";

-26000   Locks cannot be limited for a user who has administrative privileges.

In releases earlier than version 12.10.xC4, it was possible for administrators to restrict themselves, but this is no longer an option.

Lock limits for sessions in tenant databases

An administrator can use the SQL administration API to create a tenant database with the tenant create argument to the admin() or task() function. The function call can include a session_limit_locks: parameter value to set a nondefault limit on the number of locks that nonadministrative users can hold in a session.

For example, this statement creates a tenant database called companyC with a session_limit_locks: limit of 250000 locks for each nonadministrative user session.

EXECUTE FUNCTION task('tenant create','companyC', 
   '{dbspace:"companyC_dbs1,companyC_dbs2,companyC_dbs3", 
     sbspace:"companyC_sbs", 
     vpclass:"tvp_C,num=4", 
     dbspacetemp:"companyC_tdbs",
     session_limit_locks:"250000",
     logmode:"UNBUFFERED",
     locale:"en_us.8859-1"}'
);

Administrators can also use the "tenant update" argument to the admin() or task() function to reset the number of locks and other attributes of a tenant database. For example, the following function uses a session_limit_locks: value of 3000 to change that limit for the same companyC tenant database:

EXECUTE FUNCTION task('tenant update','companyC', 
   '{session_limit_locks:"3000"}');

Multiple properties can be reset by a single "tenant update" function call, but this example resets only the session_limit_locks property of companyC. The changed limit takes effect for new sessions.

If the companyC database was assigned a session limit of 3000 by the previous SQL administration API "tenant update" example, the following statement fails:

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS '5000';

The example fails because this session environment variable cannot be set above the limit defined in the current setting of the session_limit_locks property of the tenant database.

In tenant databases, a sysdbopen session configuration routine that includes the SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS statement can set a limit on locks that is lower than the session_limit_locks setting of the tenant database:

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS '1600';

If the session_limit_locks property currently specifies a limit of 3000, that limit would be temporarily reduced to 1600. This lower limit on locks persists in the tenant database for all new sessions of nonadministrative users, until the session in which sysdbopen routine set the new lower limit terminates. The limit on locks then reverts to the current session_limit_locks setting.

In tenant databases, as in all databases, the IFX_SESSION_LIMIT_LOCKS setting has no effect on users who hold administrative privileges, such as user informix or DBSA users.