Chapter 9 SQL Statements
565
LOCK TABLE statement
Use this statement to prevent other concurrent transactions from accessing or
modifying a table.
LOCK TABLE
table-name
[ WITH HOLD ]
IN { SHARE | EXCLUSIVE } MODE
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.
"SELECT statement" on page 601
"sa_locks system procedure" on page 969
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.
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.
Function
Syntax
Permissions
Side effects
See also
Description
Parameters
To Next Page
Loading...