LOCK (SQL)

Locks a table.

Synopsis

LOCK [TABLE] tablename IN EXCLUSIVE MODE [WAIT seconds]

LOCK [TABLE] tablename IN SHARE MODE [WAIT seconds]

Arguments

Argument	Description
tablename	The name of the table to be locked. tablename must be an existing table. A tablename can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. A schema search path is ignored.
IN EXCLUSIVE MODE / IN SHARE MODE	The IN EXCLUSIVE MODE keyword phrase creates a regular InterSystems IRIS lock. The IN SHARE MODE keyword phrase creates a shared InterSystems IRIS lock.
WAIT seconds	Optional — An integer specifying the number of seconds to attempt to acquire the lock before timing out. If omitted, the system default timeout is applied.

Description

LOCK and LOCK TABLE are synonymous.

The LOCK command explicitly locks an SQL table. This table must be an existing table for which you have the necessary privileges. If tablename is a nonexistent table, LOCK fails with a compile error. If tablename is a temporary table, the command completes successfully, but performs no operation. If tablename is a view, the command fails with an SQLCODE -400 error.

The UNLOCK command reverses the LOCK operation. An explicit LOCK remains in effect until you issue an explicit UNLOCK for the same mode, or until the process terminates.

You can use LOCK to lock a table multiple times; you must explicitly UNLOCK the table as many times as it was explicitly locked. Each UNLOCK must specify the same mode as the corresponding LOCK.

Privileges

The LOCK command is a privileged operation. Prior to using LOCK IN SHARE MODE it is necessary for your process to have SELECT privilege for the specified table. Prior to using LOCK IN EXCLUSIVE MODE it is necessary for your process to have INSERT, UPDATE, or DELETE privilege for the specified table. For IN EXCLUSIVE MODE, the INSERT or UPDATE privilege must be on at least one field of the table. Failing to hold sufficient privileges results in an SQLCODE -99 error (Privilege Violation). You can determine if the current user has the necessary privileges by invoking the %CHECKPRIV command. You can determine if a specified user has the necessary privileges by invoking the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method. For privilege assignment, refer to the GRANT command.

These privileges are required to acquire the lock; they do not define the nature of the lock. An IN EXCLUSIVE MODE lock prevents other processes from performing INSERT, UPDATE, or DELETE operations, regardless of whether the lock holder has the corresponding privilege.

LOCK Modes

LOCK supports two modes: SHARE and EXCLUSIVE. These lock modes are independent of each other. You can apply both a SHARE lock and an EXCLUSIVE lock to the same table. A lock in EXCLUSIVE mode can only be unlocked by an UNLOCK in EXCLUSIVE mode. A lock in SHARE mode can only be unlocked by an UNLOCK in SHARE mode.

LOCK mytable IN SHARE MODE prevents other processes from issuing an EXCLUSIVE lock on mytable, or invoking a DDL operation, such as DROP TABLE.
LOCK mytable IN EXCLUSIVE MODE prevents other processes from issuing an EXCLUSIVE lock or a SHARE lock on mytable, performing an insert, update, or delete operation, or invoking a DDL operation, such as DROP TABLE.

LOCK permits read access to the table. Neither LOCK mode prevents other processes from performing a SELECT on the table in READ UNCOMMITTED mode (the default SELECT mode).

Locking Conflicts

If a table is already locked by another user IN EXCLUSIVE MODE, you cannot lock it in any mode.
If a table is already locked by another user IN SHARE MODE, you can also lock the table IN SHARE MODE, but you cannot lock it IN EXCLUSIVE MODE.

These LOCK conflicts generate an SQLCODE -110 error and generates a %msg such as the following: Unable to acquire shared table-level lock for table 'Sample.Person'.

Lock Timeout

LOCK attempts to acquire the specified SQL table lock until timeout occurs. When timeout occurs, LOCK generates an SQLCODE -110 error.

If you have specified WAIT seconds, SQL table lock timeout occurs when that number of seconds elapses.
Otherwise, SQL table lock timeout occurs when the current process SQL timeout elapses. You can set the lock timeout for the current process using the ProcessLockTimeout option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. You can also set the lock timeout for the current process using the SQL command SET OPTION with the LOCK_TIMEOUT option. (SET OPTION cannot be used from the SQL Shell.) The current process SQL lock timeout defaults to the system-wide SQL lock timeout.
Otherwise, SQL table lock timeout occurs when the system-wide SQL timeout elapses. The system-wide default is 10 seconds. You can set the system-wide lock timeout in two ways:
- Using the LockTimeout option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method. This immediately changes the system-wide lock timeout default for new processes, and also resets the ProcessLockTimeout for the current process to this new system-wide value. Setting the system-wide lock timeout has no effect on the ProcessLockTimeout setting for other currently running processes.
- Using the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Lock timeout (seconds). This changes the system-wide lock timeout default for new processes that start after you save the configuration change. It has no effect on currently running processes.

To return the current system-wide lock timeout value call the $SYSTEM.SQL.Util.GetOption("LockTimeout")Opens in a new tab method.

To return the lock timeout value for the current process call the $SYSTEM.SQL.Util.GetOption("ProcessLockTimeout")Opens in a new tab method.

Transaction Processing

A LOCK operation is not part of a transaction. Rolling back a transaction in which a LOCK is issued does not release the lock. An UNLOCK can be defined as occurring at the conclusion of the current transaction, or occurring immediately.

Other Locking Operations

Many DDL operations, including ALTER TABLE and DELETE TABLE acquire an exclusive table lock.

The INSERT, UPDATE, and DELETE commands also perform locking. By default they lock at the record level for the duration of the current transaction; if one of these commands locks a sufficiently large number of records (1000 is the default setting), the lock is automatically elevated to a table lock. The LOCK command allows you to explicitly set a table level lock, giving you greater control over the locking of data resources. An INSERT, UPDATE, or DELETE can override a LOCK by specifying the %NOLOCK keyword.

The InterSystems SQL SET OPTION with the LOCK_TIMEOUT option sets the timeout for the current process for an INSERT, UPDATE, DELETE, or SELECT operation.

InterSystems SQL supports the CachedQueryLockTimeout option of the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method.

Examples

The following embedded SQL examples create a table and then lock it:

  NEW SQLCODE,%msg
  &sql(CREATE TABLE mytest (
      ID NUMBER(12,0) NOT NULL,
      CREATE_DATE DATE DEFAULT CURRENT_TIMESTAMP(2),
      WORK_START DATE DEFAULT SYSDATE) )
  IF SQLCODE=0 { WRITE "Table created",! }
  ELSEIF SQLCODE=-201 { WRITE "Table already exists",! }
  ELSE { WRITE "CREATE TABLE error: ",SQLCODE
         QUIT }

  NEW SQLCODE,%msg
  SET x=$ZHOROLOG
  &sql(LOCK mytest IN EXCLUSIVE MODE WAIT 4) 
  IF SQLCODE=0 { WRITE !,"Table locked" }
  ELSEIF SQLCODE=-110 { WRITE "waited ",$ZHOROLOG-x," seconds"
         WRITE !,"Table is locked by another process",!,%msg }
  ELSE { WRITE !,"Unexpected LOCK error: ",SQLCODE,!,%msg }

SQL programs run from the Management Portal spawn a process that terminates as soon as the program executes. Thus a lock is almost immediately released. Therefore, to observe a lock conflict, first issue a LOCK mytest IN EXCLUSIVE MODE command from a Terminal running the SQL Shell in the same namespace. Then run the above embedded SQL locking program. Issue an UNLOCK mytest IN EXCLUSIVE MODE from the Terminal SQL Shell. Then rerun the above Embedded SQL locking program.