Prevents other concurrent transactions from accessing or modifying a table within the specified time.
LOCK TABLE table-list [ WITH HOLD ] IN { SHARE | WRITE | EXCLUSIVE } MODE [ WAIT time ]
[ owner. ] table-name [ , [ owner. ] table-name, … ]
time:
string
For example, the following statement obtains 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'
The following statement waits indefinitely, until the WRITE lock on the Customers and Employees tables, if available or an interrupt occurs:
LOCK TABLE Customers, Employees IN WRITE MODE WAIT
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 the section “Identifiers” in Chapter 2, “SQL Language Elements” in Reference: Building Blocks, Tables, and Procedures and “Types of tables” in Chapter 5, “Working with Database Objects” in the System Administration Guide: Volume 1.
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. For more information on the SUBSUME_ROW_LOCKS option, refer to “subsume_row_locks option [database]” in SQL Anywhere Server – Database Administration > Configuring Your Database > Database options > Introduction to database options > Alphabetical list of options.
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 the error "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 (TSQL) stored procedure dialect does not
support LOCK TABLE. For example, the following
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 wproc() BEGIN COMMIT; LOCK TABLE t1 IN SHARE MODE; INSERT INTO t1 VALUES (20); END
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.
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.
sp_iqlocks procedure in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures
