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

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.

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.

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:

• 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 LOCKS_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