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 has a lock compatibility and lock strength that reflect how it interacts with other locks in the same environment.

Lock mode	Description
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.
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.
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.
Usage (U)	Vertica uses Usage (U) locks for Tuple Mover mergeout 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.
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.
Insert Validate (IV)	An Insert Validate (IV) 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.

Lock modes are compatible when they intersect in a cell that is marked with a bullet (•). If two requested modes intersect in an empty cell, the second request is not granted until the first request releases its lock.

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

Lock upgrade matrix

This matrix shows how an 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.

Requested mode	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: