This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Vertica database locks

When multiple users concurrently access the same database information, data manipulation can cause conflicts and threaten data integrity.

When multiple users concurrently access the same database information, data manipulation can cause conflicts and threaten data integrity. Conflicts occur because some transactions block other operations until the transaction completes. Because transactions committed at the same time should produce consistent results, Vertica uses locks to maintain data concurrency and consistency. Vertica automatically controls locking by limiting the actions a user can take on an object, depending on the state of that object.

Vertica uses object locks and system locks. Object locks are used on objects, such as tables and projections. System locks include global catalog locks, local catalog locks, and elastic cluster locks. Vertica supports a full range of standard SQL lock modes, such as shared (S) and exclusive (X).

For related information about lock usage in different transaction isolation levels, see READ COMMITTED isolation and SERIALIZABLE isolation.

1 - 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

2 - Lock examples

In this example, two sessions (A and B) attempt to perform operations on table T1.

Automatic locks

In this example, two sessions (A and B) attempt to perform operations on table T1. These operations automatically acquire the necessary locks.

At the beginning of the example, table T1 has one column (C1) and no rows.

The steps here represent a possible series of transactions from sessions A and B:

  1. Transactions in both sessions acquire S locks to read from Table T1.

  2. Session B releases its S lock with the COMMIT statement.

  3. Session A can upgrade to an SI lock and insert into T1 because Session B released its S lock.

  4. Session A releases its SI lock with a COMMIT statement.

  5. Both sessions can acquire S locks because Session A released its SI lock.

  6. Session A cannot acquire an SI lock because Session B has not released its S lock. SI locks are incompatible with S locks.

  7. Session B releases its S lock with the COMMIT statement.

  8. Session A can now upgrade to an SI lock and insert into T1.

  9. Session B attempts to delete a row from T1 but can't acquire an X lock because Session A has not released its SI lock. SI locks are incompatible with X locks.

  10. Session A continues to insert into T1.

  11. Session A releases its SI lock.

  12. Session B can now acquire an X lock and perform the delete.

This figure illustrates the previous steps:

Manual locks

In this example, Alice attempts to manually lock table customer_info with LOCK TABLE while Bob runs an INSERT statement:

Bob runs the following INSERT statement to acquire an INSERT lock and insert a row:

=> INSERT INTO customer_info VALUES(37189,'Albert','Quinlan','Frankfurt',2022);

In another session, Alice attempts to acquire a SHARE lock with LOCK TABLE. As shown in the lock compatibility table, the INSERT lock is incompatible with SHARE locks (among others), so Alice cannot acquire a SHARE lock until Bob finishes his transaction:

=> LOCK customer_info IN SHARE MODE NOWAIT;
ERROR 5157:  Unavailable: [Txn 0xa00000001c48e3] S lock table - timeout error Timed out S locking Table:public.customer_info. I held by [user Bob (LOCK TABLE)]. Your current transaction isolation level is READ COMMITTED

Bob then releases the lock by calling COMMIT:

=> COMMIT;
COMMIT

Alice can now acquire the SHARE lock:

=> LOCK customer_info IN SHARE MODE NOWAIT;
LOCK TABLE

Bob tries to insert another row into the table, but because Alice has the SHARE lock, the statement enters a queue and appears to hang; after Alice finishes her transaction, the INSERT statement will automatically acquire the INSERT lock:

=> INSERT INTO customer_info VALUES(17441,'Kara','Shen','Cairo',2022);

Alice calls COMMIT, ending her transaction and releasing the SHARE lock:

=> COMMIT;
COMMIT;

Bob's INSERT statement automatically acquires the lock and completes the operation:

=> INSERT INTO customer_info VALUES(17441,'Kara','Shen','Cairo',2022);
 OUTPUT
--------
      1
(1 row)

Bob calls COMMIT, ending his transaction and releasing the INSERT lock:

=> COMMIT;
COMMIT

3 - Deadlocks

Deadlocks can occur when two or more sessions with locks on a table attempt to elevate to lock types incompatible with the lock owned by another session.

Deadlocks can occur when two or more sessions with locks on a table attempt to elevate to lock types incompatible with the lock owned by another session. For example, suppose Bob and Alice each run an INSERT statement:

--Alice's session
=> INSERT INTO customer_info VALUES(92837,'Alexander','Lamar','Boston',2022);

--Bob's session
=> INSERT INTO customer_info VALUES(76658,'Midori','Tanaka','Osaka',2021);

INSERT (I) locks are compatible, so both Alice and Bob have the lock:

=> SELECT * FROM locks;
-[ RECORD 1 ]-----------+-------------------------------------------------------------------------------------------------
node_names              | v_vmart_node0001,v_vmart_node0002,v_vmart_node0003
object_name             | Table:public.customer_info
object_id               | 45035996274212656
transaction_id          | 45035996275578544
transaction_description | Txn: a00000001c96b0 'INSERT INTO customer_info VALUES(92837,'Alexander','Lamar','Boston',2022);'
lock_mode               | I
lock_scope              | TRANSACTION
request_timestamp       | 2022-10-05 12:57:49.039967-04
grant_timestamp         | 2022-10-05 12:57:49.039971-04
-[ RECORD 2 ]-----------+-------------------------------------------------------------------------------------------------
node_names              | v_vmart_node0001,v_vmart_node0002,v_vmart_node0003
object_name             | Table:public.customer_info
object_id               | 45035996274212656
transaction_id          | 45035996275578546
transaction_description | Txn: a00000001c96b2 'INSERT INTO customer_info VALUES(76658,'Midori','Tanaka','Osaka',2021);'
lock_mode               | I
lock_scope              | TRANSACTION
request_timestamp       | 2022-10-05 12:57:56.599637-04
grant_timestamp         | 2022-10-05 12:57:56.599641-04

Alice then runs an UPDATE statement, attempting to elevate her existing INSERT lock into an EXCLUSIVE (X) lock. However, because EXCLUSIVE locks are incompatible with the INSERT lock in Bob's session, the UPDATE is added to a queue for the lock and appears to hang:

=> UPDATE customer_info SET city='Cambridge' WHERE customer_id=92837;

A deadlock occurs when Bob runs an UPDATE statement while Alice's UPDATE is still waiting. Vertica detects the deadlock and terminates Bob's entire transaction (which includes his INSERT), allowing Alice to elevate to an EXCLUSIVE lock and complete her UPDATE:

=> UPDATE customer_info SET city='Shibuya' WHERE customer_id=76658;
ROLLBACK 3010:  Deadlock: initiator locks for query - Deadlock X locking Table:public.customer_info. I held by [user Alice (INSERT INTO customer_info VALUES(92837,'Alexander','Lamar','Boston',2022);)]. Your current transaction isolation level is SERIALIZABLE

Preventing deadlocks

You can avoid deadlocks by acquiring the elevated lock earlier in the transaction, ensuring that your session has exclusive access to the table. You can do this in several ways:

To prevent a deadlock in the previous example, Alice and Bob should both begin their transactions with LOCK TABLE. The first session to request the lock will lock the table, and the other waits for the first session to release the lock or until a user-specified timeout:

=> LOCK TABLE customer_info IN EXCLUSIVE MODE;

4 - Troubleshooting locks

The LOCKS and LOCK_USAGE system tables can help identify problems you may encounter with Vertica database locks.

The LOCKS and LOCK_USAGE system tables can help identify problems you may encounter with Vertica database locks.

This example shows one row from the LOCKS system table. From this table you can see what types of locks are active on specific objects and nodes.

=> SELECT node_names, object_name, lock_mode, lock_scope FROM LOCKS;
node_names         | object_name                     | lock_mode | lock_scope
-------------------+---------------------------------+-----------+-----------
v_vmart_node0001   | Table:public.customer_dimension | X         | TRANSACTION

This example shows two rows from the LOCK_USAGE system table. You can also use this table to see what locks are in use on specific objects and nodes.

=> SELECT node_name, object_name, mode FROM LOCK_USAGE;
node_name         | object_name      | mode
------------------+------------------+-------
v_vmart_node0001  | Cluster Topology | S
v_vmart_node0001  | Global Catalog   | X