What is a Lock Anyway?

We talk a lot about database security and how you can protect your sensitive data from outside threats. But what about internal, unintentional data corruption? What if the data you are trying to analyze or manipulate is simultaneously being manipulated by another transaction? A scenario such as this could lead to data loss and inconsistency. In some cases, this can be as bad as an external threat. This is where locks come into the picture.

Database locks help protect the shared resources in your database. Best of all, Vertica takes care of locks automatically, so you as a user don’?t have to worry about data corruption or inconsistency.

What Exactly is a Database Lock?

A database lock is a mechanism for preventing conflicts in data manipulation by limiting the actions a user can take on an object depending on the state of that object. For example, if you are editing a table, Vertica uses locks to make sure no one else can simultaneously edit the same table. This is just one scenario. Vertica uses many types of locks for many different purposes.

What Are the Types of Locks and What Are They Used For?

On a basic level, Vertica uses object locks and system locks. Object locks are used on objects, like tables and projections. System locks include global catalog locks, local catalog locks, and elastic cluster locks. In general, SELECT queries do not require locks because they are, by default, run at the READ COMMITTED transaction isolation level, meaning you are only accessing committed data. However, other operations, like DML or DDL commands, require locks because they are run at the serializable transaction isolation level. Additionally, SELECT statements can be run at the serializable level using the SET SESSION CHARACTERISTICS statement.

Vertica also has different lock modes that determine how a lock acts on an object. Here are the lock modes Vertica uses:

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 for more information.
I – Insert	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	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	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	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 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 a specific lock?’s ability to be in effect at the same time as another lock on the same object. We hinted at this briefly in the table above, but the compatibility matrix below provides a fuller picture:

	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

When a requested mode and a granted mode intersect in a ?”Yes?” cell, those modes are said to be compatible with each other. For example, if one transaction is granted an I lock on a table, and another transaction requests an I lock on the same table, the second transaction will be granted an I lock because I locks are compatible with each other. On the other hand, if the second transaction were to request an X lock, its request would not be granted since the X lock is incompatible with the first transaction?’s I lock. This incompatibility is shown in the “?No?” cells.

Lock Strength

The table above also suggests lock strength. As you can see, an 0 lock is not compatible with any other lock mode, meaning it is the strongest type of lock mode. A U lock on the other hand, is the weakest type of lock mode since the only operation that cannot run concurrently is one that requires an O lock. Here is a visual representation of lock mode strength:

Lock Requests

When a transaction requests a lock, Vertica checks if the lock mode requested is compatible with other locks on the same object. If the lock mode is not compatible, for example, if an O lock is currently held on that object, the transaction waits for the incompatible lock to be released. If the lock is not released before the lock timeout interval, usually five minutes, the transaction requesting the lock is canceled.

Locks in Action

The following example illustrates locking within concurrent transactions running with SERIALIZABLE transaction isolation. Both sessions are requesting locks on table T1.

 

Troubleshooting Locks

The LOCKS and LOCK_USAGE system tables can help identify any problems you may encounter.

Learn More

As you can see, locks play an important part in data access and manipulation. You can read more about Vertica locks here.