LOCK — locks a table for exclusive access




Table locks can be used to gain exclusive access to tables across the database.

Table Lock Behavior

Table lock behavior in NuoDB is governed by the following rules:

NuoDB 3.2.2 does not support the explicit acquisition of SHARED locks. These locks are automatically acquired by write DML. Concurrent DML and DDL can be prevented by acquiring an EXCLUSIVE lock.

All forms of table lock are released when a transaction is resolved. The following events release a table lock:

Table locks are not released in any other situations. For example, table locks are not released during:

Once an 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.

Table Locks for DDL Operations

Table locks are not implicitly acquired for DDL operations. As DDL operations are not executed using the user transaction, acquiring a lock on a DDL transaction would conflict with other locks acquired via user transaction (either implicitly acquired, for example by executing an INSERT, or explicitly acquired by executing a LOCK TABLE statement). This creates a virtual, undetectable deadlock; the DDL transaction awaits the lock held by the user transaction while the user transaction waits for control to be handed back to it by the DDL transaction.

To achieve isolation for DDL operations, execute a LOCK TABLE statement before any DDL operation on the target table is executed. This ensures that the user transaction blocks write operations from any other user transactions while the DDL is being executed. For more information on DDL transactions, see DDL Versus DML Transaction Behavior.