LOCK TABLE Statement

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

Syntax

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

Parameters

table-list: – [ owner. ] table-name [ , [ owner. ] table-name, … ]
time:

string

Examples

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

table-name—The table 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. If obtaining a lock for a SQL Anywhere table, or when obtaining SHARE or EXCLUSIVE locks, you may only specify a single table. Standard Sybase IQ object qualification rules are used to parse table-name. For related details, see Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Identifiers and System Administration Guide: Volume 1 > Database Object Management > Table Management > Guidelines for Creating Tables > Types of Tables.

WITH HOLD—If this clause is specified, 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.

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 Sybase IQ.

A WRITE mode lock on an IQ table that participates in a join index also locks:

The top table of the join index hierarchy in WRITE mode when X is a non-top table
The corresponding join virtual table (JVT)

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. If a table t is locked exclusively with LOCK TABLE t IN EXCLUSIVE MODE, the default server behavior is not to acquire row locks for t. This behavior can be disabled by setting the SUBSUME_ROW_LOCKS option OFF. See SQL Anywhere Server - Database Administration > Configuring your database > Database options > Alphabetical list of options > subsume_row_locks option.

Note: This reference points to SQL Anywhere documentation.

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.

WAIT time— Wait options specify maximum blocking time for all lock types. This option 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.

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

See also Reference: Building Blocks, Tables, and Procedures > System Procedures > System Stored Procedures > sp_iqlocks Procedure.

Standards

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

Permissions

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 DBA authority.