About Lock Behavior

NuoDB supports two types of locking. Firstly, there are row locks that are part of MVCC and guarantee DML row level consistency. Secondly, NuoDB supports schema modification locks, sometimes called table locks or transactional locks. Both lock types are considered for distributed deadlock detection, but are otherwise independent. This section focuses on Transactional Locks.

About Transactional Locks

Transactional locks enable synchronized access by multiple users to the same piece of data at the same time. In a NuoDB database, you can enforce locking for:

  • Schemas

  • Tables

  • Sequences

Transactional Lock Types

Across schemas, tables and sequences, the types of lock available for use are as follows:

Exclusive locks: These locks are automatically acquired by all DDL statements such as ALTER TABLE, ALTER SEQUENCE, DROP TABLE and others. For a complete list, refer to the end of this document. Exclusive locks can also be acquired manually using the LOCK statement.

Shared locks: These locks are automatically acquired by DML statements which write to tables (UPDATE, DELETE, INSERT, SELECT FOR UPDATE). Read DML (SELECT) does not acquire any locks and hence does not conflict with other statements. Some DDL statements acquire SHARED locks as well. Shared locks can not be acquired manually using the LOCK statement.

Views, stored procedures, triggers, java stored procedures, and user-defined functions are not considered first class objects and can not be locked.

Shared locks do not conflict with each other but conflict with an exclusive lock. There can only be one owner of an exclusive lock on a resource.

Behavior of queries using Exclusive Locks and Shared Locks

It is possible to mix DDL and DML in a single transaction. When multiple statements target the same resource, the highest level of locking will be selected.

Schema Locking

Statements that automatically acquire shared and or exclusive locks on a schema object are as follows:

Statement Shared Exclusive

Drop Schema

-

x

Create Function

x

-

Create Procedure

x

-

Create Role

x

-

Create Sequence

x

-

Create Table

x

-

Create View

x

-

Table Locking

Statements that automatically acquire shared and or exclusive locks on a table object are as follows:

Statement Shared Exclusive

Select

-

-

Insert

x

-

Update

x

-

Delete

x

-

Create Index

-

x

Drop Index

-

x

Create Trigger

-

x

Drop Trigger

-

x

Alter Trigger

-

x

Alter Table

-

x

Rename Table

-

x

Drop Table

-

x

Truncate Table

-

x

Sequence Locking

Operations that automatically acquire shared and or exclusive locks on a sequence object are as follows:

Operation Shared Exclusive

Select next value

-

-

Create table using sequence as generator

x

-

Drop Sequence

-

x

Alter Sequence

x

-