Lock modes

Vertica has different lock modes that determine how a lock acts on an object.

Vertica has different lock modes that determine how a lock acts on an object. Each lock mode is unique in its compatibility with other lock modes; each lock mode has its own strength vis a vis other lock modes, which determines whether Each lock mode has a lock compatibility and strength that reflect how it interacts with other locks in the same environment.

Lock mode Description
Usage (U) Vertica uses usage (U) locks for Tuple Mover mergeout operations. These Tuple Mover operations run automatically in the background, therefore, other operations on a table except those requiring an O or D locks can run when the object is locked in U mode.
Tuple Mover (T) Vertica uses Tuple Mover (T) locks for operations on delete vectors. Tuple Mover operations upgrade the table lock mode from U to T when work on delete vectors starts so that no other updates or deletes can happen concurrently.
Shared (S)

Use a shared (S) lock for SELECT queries that run at the serialized transaction isolation level. This allows queries to run concurrently, but the S lock creates the effect that transactions are running in serial order. The S lock ensures that one transaction does not affect another transaction until one transaction completes and its S lock is released.

Select operations in READ COMMITTED transaction mode do not require S table locks. See Transactions for more information.

Insert (I) Vertica requires an insert (I) lock to insert data into a table. Multiple transactions can lock an object in insert mode simultaneously, enabling multiple inserts and bulk loads to occur at the same time. This behavior is critical for parallel loads and high ingestion rates.
Insert Validate (IV) An insert validate (IV) lock is required for insert operations where the system performs constraint validation for enabled PRIMARY or UNIQUE key constraints.
Shared Insert (SI) Vertica requires a shared insert (SI) lock when both a read and an insert occur in a transaction. SI mode prohibits delete/update operations. An SI lock also results from lock promotion.
Exclusive (X) Vertica uses exclusive (X) locks when performing deletes and updates. Only Tuple Mover mergeout operations (U locks) can run concurrently on objects with X locks.
Drop Partition (D) DROP_PARTITIONS requires a D lock on the target table. This lock is only compatible with I-lock operations, so only table load operations such as INSERT and COPY are allowed during drop partition operations.
Owner (O) An owner (O) lock is the strongest Vertica lock mode. An object acquires an O lock when it undergoes changes in both data and structure. Such changes can occur in some DDL operations, such as DROP_PARTITIONS, TRUNCATE TABLE, and ADD COLUMN. When an object is locked in O mode, it cannot be locked simultaneously by another transaction in any mode.

Lock compatibility

Bulleted (•) cells in the following matrix shows which locks can be used on the same object simultaneously. Empty cells indicate that a query's requested mode is not granted until the current (granted) mode releases its lock on the object.

Requested mode Granted mode
U T S I IV SI X D O
U • • • • • • •
T • • • • • •
S • • •
I • • • • •
IV • • •
SI • •
X •
D •
O

Lock conversion

Often, the same object is the target of concurrent lock requests from different sessions. The matrix below shows how Vertica responds to multiple lock requests on the same object, one of the following:

  • Locks are granted to concurrent requests on an object if the respective lock modes are compatible. For example, D (drop partition) and I (insert) locks are compatible, so Vertica can grant multiple lock requests on the same table for concurrent load and drop partition operations.
  • Lock modes for concurrent requests on an object are incompatible, but the requests also support a higher (stronger) lock mode. In this case, Vertica converts (upgrades) the lock modes for these requests—for example, S and I to SI. The upgraded lock mode enables requests on the object to proceed concurrently.
  • Lock modes for concurrent requests on an object are incompatible, and none can be upgraded to a common lock mode. In this case, object lock requests are queued and granted in sequence.
    Requested mode Granted mode
    U T S I IV SI X D O
    U U T S I IV SI X D O
    T T T S I IV SI X D O
    S S S S SI SI SI X O O
    I I I SI I IV SI X D O
    IV IV IV SI IV IV SI X D O
    SI SI SI SI SI SI SI X O O
    X X X X X X X X O O
    D D D O D D O O D O
    O O O O O O O O O O

Lock strength

Lock strength refers to the ability of a lock mode to interact with another lock mode. O locks are strongest and are incompatible with all other locks. Conversely, U locks are weakest and can run concurrently with all other locks except D and O locks.

The following figure depicts the spectrum of lock mode strength:

See also