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 examples create a table and then lock it:
CREATE TABLE mytest (
ID NUMBER(12,0) NOT NULL,
CREATE_DATE DATE DEFAULT CURRENT_TIMESTAMP(2),
WORK_START DATE DEFAULT SYSDATE)
LOCK mytest IN EXCLUSIVE MODE WAIT 4
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 code locking program. Issue an UNLOCK
mytest IN EXCLUSIVE MODE from the Terminal SQL Shell. Then
rerun the above locking program.
