Vertica Analytics Platform Version 9.2.x Documentation

Lock Modes

Vertica has different lock modes that determine how a lock acts on an object. Each lock mode has a lock compatibility and lock strength that reflect how it interacts with other locks in the same environment.

Lock Mode Description
S - Shared

Use a Shared 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 in Vertica Concepts for more information.

I - Insert

Vertica requires an Insert 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.

SI - Shared Insert

Vertica requires a Shared Insert lock when both a read and an insert occur in a transaction. Shared Insert mode prohibits delete/update operations. An SI lock also results from lock promotion.

X - Exclusive Vertica uses Exclusive locks when performing deletes and updates. Only mergeout and moveout operations (U locks) can run concurrently on objects with X locks.
T - Tuple Mover

The Tuple Mover uses 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.

U - Usage Vertica uses Usage locks for moveout and mergeout Tuple Mover operations. These Tuple Mover operations run automatically in the background, therefore, most other operations (except those requiring an O lock) can run when the object is locked in U mode.
O - Owner An O lock is the strongest Vertica lock mode. An object acquires an Owner 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.
IV - Insert-Validate

An Insert Validate lock is needed for insert operations where the system performs constraint validation for enabled PRIMARY or UNIQUE key constraints.

Lock Compatibility

Lock compatibility refers to having two locks in effect on the same object at the same time.

Lock Compatibility Matrix

This matrix shows which locks can be used on the same object simultaneously.

When two lock modes intersect in a Yes cell, those modes are compatible. If two requested modes intersect in a No cell, the second request is not granted until the first request releases its lock.

Granted Mode

Requested Mode

S

I

IV

SI

X

T

U

O

S

Yes

No

No

No

No

Yes

Yes

No

I

No

Yes

Yes

No

No

Yes

Yes

No

IV No Yes No No No Yes Yes No

SI

No

No

No

No

No

Yes

Yes

No

X

No

No

No

No

No

No

Yes

No

T

Yes

Yes

Yes

Yes

No

Yes

Yes

No

U

Yes

Yes

Yes

Yes

Yes

Yes

Yes

No

O

No

No

No

No

No

No

No

No

Lock Upgrade Matrix

This matrix shows how your object lock responds to an INSERT request.

If an object has an S lock and you want to do an INSERT, your transaction requests an SI lock. However, if an object has an S lock and you want to perform an operation that requires an S lock, no lock request is issued.

Granted Mode

Requested Mode

S

I

IV

SI

X

T

U

O

S

S

SI

SI

SI

X

S

S

O

I

SI

I

IV

SI

X

I

I

O

IV SI IV IV SI X IV IV O

SI

SI

SI

SI

SI

X

SI

SI

O

X

X

X

X

X

X

X

X

O

T

S

I

IV

SI

X

T

T

O

U

S

I

IV

SI

X

T

U

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 an O lock.

This figure depicts lock mode strength:

See Also: