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:

Lock Modes

Lock Compatibility

Lock Strength

Version 9.2.x Documentation