LOCK

LOCK — locks a table for exclusive access

Syntax

LOCK {TABLE} name [ EXCLUSIVE ];

Description

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:

  • SELECT (read DML) transactions do not acquire table locks. They can proceed even if a table is locked exclusively.

  • INSERT, UPDATE, DELETE, SELECT FOR UPDATE (write DML) acquires a SHARED lock on all tables that are affected by the statement.

Table locks are released when a transaction ends. The following events release a table lock:

  • Commit

  • Rollback

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

  • Rollback to savepoint

  • Kill statement

  • XA prepare

  • Deadlock exception

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.

Parameters

name

The name of the table to lock.

Examples

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