Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   

Locks a table.

LOCK [TABLE] tablename IN SHARE MODE [WAIT seconds]
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 system-wide default schema name. A schema search path is ignored.
IN EXCLUSIVE MODE / IN SHARE MODE The IN EXCLUSIVE MODE keyword phrase creates a regular Caché lock. The IN SHARE MODE keyword phrase creates a shared Caché lock.
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.
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.
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.CheckPriv() 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 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
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.
The SetLockTimeout() method sets a value and returns the previous value. You can use GetLockTimeout() to return the current system-wide lock timeout value:
   DO $SYSTEM.SQL.SetLockTimeout()
   SET oldval=$SYSTEM.SQL.GetLockTimeout()
   WRITE oldval," seconds initial system-wide lock setting",!
   DO $SYSTEM.SQL.SetLockTimeout(30,.oldval2)
   WRITE "system-wide lock timeout changed from ",oldval2," to "
   WRITE $SYSTEM.SQL.GetLockTimeout(),!
   DO $SYSTEM.SQL.SetLockTimeout(oldval,.oldval3)
   WRITE "system-wide lock timeout reset from ",oldval3," to "
   WRITE $SYSTEM.SQL.GetLockTimeout()
The SetProcessLockTimeout() method sets a value and returns a status code. You can use GetProcessLockTimeout() to return the lock timeout value for the current process:
   SET sysinit=$SYSTEM.SQL.GetLockTimeout()
   WRITE sysinit," initial system-wide lock seconds",!
   SET procinit=$SYSTEM.SQL.GetProcessLockTimeout()
   WRITE procinit," initial process lock seconds",!
  DO $SYSTEM.SQL.SetProcessLockTimeout(50,.stat)
    IF stat {WRITE $SYSTEM.SQL.GetProcessLockTimeout()," set process lock seconds",! }
  DO $SYSTEM.SQL.SetProcessLockTimeout(60,.stat2)
    IF stat2 {WRITE $SYSTEM.SQL.GetProcessLockTimeout()," set process lock seconds",! }
  DO $SYSTEM.SQL.SetProcessLockTimeout()
  WRITE $SYSTEM.SQL.GetProcessLockTimeout()," minimal process lock seconds",!
  DO $SYSTEM.SQL.SetProcessLockTimeout(procinit)
  WRITE $SYSTEM.SQL.GetProcessLockTimeout()," reset process lock seconds",!
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 Caché SQL SET OPTION with the LOCK_TIMEOUT option sets the timeout for the current process for an INSERT, UPDATE, DELETE, or SELECT operation.
Caché SQL supports the SetCachedQueryLockTimeout() method. See Cached Queries in the Caché SQL Optimization Guide.
The following embedded SQL examples create a table and then lock it:
  ZNSPACE "Samples"
  &sql(CREATE TABLE mytest (
      ID NUMBER(12,0) NOT NULL,
  IF SQLCODE=0 { WRITE "Table created",! }
  ELSEIF SQLCODE=-201 { WRITE "Table already exists",! }
         QUIT }
  ZNSPACE "Samples"
  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 documentation, or 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 Samples 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.
See Also

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA