Skip to main content

UNLOCK (SQL)

Unlocks a table.

Synopsis

UNLOCK [TABLE] tablename IN EXCLUSIVE MODE [IMMEDIATE]

UNLOCK [TABLE] tablename IN SHARE MODE [IMMEDIATE]

Description

The UNLOCK command unlocks an SQL table that was locked by the LOCK command. This table must be an existing table for which you have the necessary privileges. 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.

UNLOCK and UNLOCK TABLE are synonymous.

The UNLOCK command reverses the LOCK operation. The UNLOCK command completes successfully even when no lock is held. You can use LOCK to lock a table multiple times; you must explicitly UNLOCK the table as many times as it was explicitly locked.

Privileges

The UNLOCK command is a privileged operation. Prior to using UNLOCK IN SHARE MODE it is necessary for your process to have SELECT privilege for the specified table. Prior to using UNLOCK 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 table-level privileges by invoking the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method. For privilege assignment, refer to the GRANT command.

Nonexistent Table

If you try to unlock a nonexistent table, UNLOCK fails with a compile error, and the message SQLCODE=-30 : Table 'SQLUser.mytable' not found.

Arguments

tablename

The name of the table to be unlocked. 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 releases a regular InterSystems IRIS lock. The IN SHARE MODE keyword phrase releases a shared lock at the InterSystems IRIS level.

IMMEDIATE

An optional argument. If not specified, InterSystems IRIS releases the lock at the end of the current transaction. If specified, InterSystems IRIS releases the lock immediately.

Examples

The following embedded SQL examples create a table, lock it and then unlock 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" }
  ELSE { WRITE !,"CREATE TABLE error: ",SQLCODE
         QUIT }
  NEW SQLCODE,%msg
  &sql(LOCK mytest IN EXCLUSIVE MODE) 
  IF SQLCODE=0 { WRITE !,"Table locked" }
  ELSEIF SQLCODE=-110 { WRITE !,"Table is locked by another process",!,%msg }
  ELSE { WRITE !,"Unexpected LOCK error: ",SQLCODE,!,%msg }
  &sql(UNLOCK mytest IN EXCLUSIVE MODE) 
  IF SQLCODE=0 { WRITE !,"Table unlocked" }
  ELSE { WRITE !,"Unexpected UNLOCK error: ",SQLCODE,!,%msg }

See Also

FeedbackOpens in a new tab