LOCK TABLE Statement

Quick Links:

Syntax

LOCK TABLE table-list [ WITH HOLD ] 
   IN { SHARE | WRITE | EXCLUSIVE } MODE [  WAIT time ] 

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

Parameters

(back to top)

table-name – must be a base table, not a view. WRITE mode is only valid for IQ base tables. LOCK TABLE either locks all tables in the table list, or none. The table must not be enabled for row-level versioning (RLV). If obtaining a lock for an SQL Anywhere table, or when obtaining SHARE or EXCLUSIVE locks, you may only specify a single table. Standard SAP Sybase IQ object qualification rules are used to parse table-name.

WITH HOLD – the lock is held until the end of the connection. If the clause is not specified, the lock is released when the current transaction is committed or rolled back. Using the WITH HOLD clause in the same statement with WRITE MODE is unsupported and returns the error SQLCODE=-131, ODBC 3 State="42000".

SHARE – prevents other transactions from modifying the table, but allows them read access. In this mode, you can change data in the table as long as no other transaction has locked the row being modified, either indirectly, or explicitly by using LOCK TABLE.

WRITE – prevents other transactions from modifying a list of tables. Unconditionally commits the connections outermost transaction. The transaction’s snapshot version is established not by the LOCK TABLE IN WRITE MODE statement, but by the execution of the next command processed by SAP Sybase IQ.
WRITE mode locks are released when the transaction commits or rolls back, or when the connection disconnects.

EXCLUSIVE – prevents other transactions from accessing the table. In this mode, no other transaction can execute queries, updates of any kind, or any other action against the table.

WAIT time – specifies maximum blocking time for all lock types. This clause is mandatory when lock mode is WRITE. When a time argument is given, the server locks the specified tables only if available within the specified time. The time argument can be specified in the format hh:nn:ss:sss. If a date part is specified, the server ignores it and converts the argument into a timestamp. When no time argument is given, the server waits indefinitely until a WRITE lock is available or an interrupt occurs.

Examples

(back to top)

Example 1 – obtain a WRITE lock on the Customers and Employees tables, if available within 5 minutes and 3 seconds:
```
LOCK TABLE Customers, Employees IN WRITE MODE WAIT
'00:05:03'
```
Example 2 – wait indefinitely until the WRITE lock on the Customers and Employees tables is available, or an interrupt occurs:
```
LOCK TABLE Customers, Employees IN WRITE MODE WAIT
```

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