LOCK — locks a table for exclusive access
Table locks can be used to gain exclusive access to tables across the database.
Table lock behavior in NuoDB is governed by the following rules:
SELECT(read DML) transactions do not acquire table locks. They can proceed even if a table is locked exclusively.
SELECT FOR UPDATE(write DML) acquires a
SHAREDlock on all tables that are affected by the statement.
Table locks are released when a transaction ends. The following events release a table lock:
Table locks are not released in any other situations. For example, table locks are not released by:
Rollback to savepoint
EXCLUSIVE lock has been acquired, no transaction can modify the table. Because acquiring a lock does not advance the visibility snapshot of a transaction in
CONSISTENT READ isolation mode, statements which follow might not be guaranteed to see all effects that have already been committed. To avoid unexpected behavior, NuoDB allows
LOCK to be used only in
READ COMMITTED isolation mode. For more information about isolation modes see Supported Transaction Isolation Levels.
To inspect all locks, query the TRANSACTIONALLOCKS system table. For more information on this table, see TRANSACTIONALLOCKS System Table Description.
- Example 1: The following example demonstrates how only the READ COMMITTED isolation level is supported.
LOCK TABLE Table1 EXCLUSIVE; Lock table is not supported for transaction in 'SERIALIZABLE' isolation level
- Example 2: The following example demonstrates how acquiring a lock prevents concurrent write DML.
SQL1>SET ISOLATION LEVEL read committed; SQL1>SET AUTOCOMMIT OFF; SQL1>LOCK TABLE Table1 EXCLUSIVE; SQL2>INSERT INTO Table1 values(5); # blocks