ASA SQL Reference
SQL Statements
Use this statement to prevent other concurrent transactions from accessing or modifying a table.
LOCK TABLE table-name
[ WITH HOLD ]
IN { SHARE | EXCLUSIVE } MODE
table-name The table must be a base table, not a view. As temporary table data is local to the current connection, locking global or local temporary tables has no effect.
WITH HOLD clause If this clause is specified, the lock is held until the end of the connection. If the clause is not specified, the lock is release when the current transaction is committed or rolled back.
SHARE mode Prevent other transactions from modifying the table, but allow 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 using LOCK TABLE.
EXCLUSIVE mode Prevent other transactions from accessing the table. 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 to not acquire row locks for t. This behavior can be disabled by setting the SUBSUME_ROW_LOCKS option OFF.
The LOCK TABLE statement allows direct control over concurrency at a table level, independent of the current isolation level.
While the isolation level of a transaction generally governs the kinds of locks that are set when the current transaction executes a request, the LOCK TABLE statement allows more explicit control locking of the rows in a table.
The locks placed by LOCK TABLE in SHARE mode are phantom and anti-phantom locks, which are displayed by the sa_locks procedure as PT and AT.
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.
Other transactions that require access to the locked table may be delayed or blocked.
SQL/92 Vendor extension.
SQL/99 Vendor extension.
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 Adaptive Server Anywhere.
The following statement prevents other transactions from modifying the customer table for the duration of the current transaction:
LOCK TABLE customer IN SHARE MODE