LOCK TABLE Statement

Prevents other concurrent transactions from accessing or modifying a table within the specified time.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

LOCK TABLE table-listWITH HOLD ] 
   INSHARE | WRITE | EXCLUSIVE } MODEWAIT time ] 

table-listowner. ] table-name [ , [ owner. ] table-name, ...]

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

LOCK TABLE statements run on tables in the IQ main store on the coordinator do not affect access to those tables from connections on secondary servers. For example:

On a coordinator connection, issue the command:

LOCK TABLE coord1 WITH HOLD IN EXCLUSIVE MODE

sp_iqlocks on the coordinator confirms that the table coord1 has an exclusive (E) lock.

The result of sp_iqlocks run on a connection on a secondary server does not show the exclusive lock on table coord1. The user on this connection can see updates to table coord1 on the coordinator.

Other connections on the coordinator can see the exclusive lock on coord1 and attempting to select from table coord1 from another connection on the coordinator returns User DBA has the row in coord1 locked.

LOCK TABLE on views is unsupported. Attempting to lock a view acquires a shared schema lock regardless of the mode specified in the command. A shared schema lock prevents other transactions from modifying the table schema.

The Transact-SQL (T-SQL) stored procedure dialect does not support LOCK TABLE. For example, this statement returns Syntax error near LOCK:

CREATE PROCEDURE tproc()
AS
BEGIN
COMMIT;
LOCK TABLE t1 IN SHARE MODE
INSERT INTO t1 VALUES(30)
END

The Watcom-SQL stored procedure dialect supports LOCK TABLE. The default command delimiter is a semicolon (;). For example:

CREATE PROCEDURE tproc()
AS
BEGIN
COMMIT;
LOCK TABLE t1 IN SHARE MODE
INSERT INTO t1 VALUES(30)
END

Standards

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product—Supported in Adaptive Server. The WITH HOLD clause is not supported in Adaptive Server. Adaptive Server provides a WAIT clause that is not supported in SQL Anywhere.

Permissions

(back to top)

To lock a table in SHARE mode, SELECT privileges are required.

To lock a table in EXCLUSIVE mode, you must be the table owner or have any of the following system privileges:
  • ALTER ANY OBJECT
  • INSERT ANY TABLE
  • UPDATE ANY TABLE
  • DELETE ANY TABLE
  • ALTER ANY TABLE
  • LOAD ANY TABLE
  • TRUNCATE ANY TABLE
Related reference
SELECT Statement