Vertica database locks

• 1: Lock modes
• 2: Lock examples
• 3: Deadlocks
• 4: Troubleshooting locks

1 - Lock modes

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

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

• Vertica database locks
• LOCKS

2 - Lock examples

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. 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:

• SELECT...FOR UPDATE: You should prefer this method when possible for your transaction.

• LOCK TABLE...IN EXCLUSIVE MODE

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.

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