How Enabled Primary and Unique Key Constraints Affect Locks

If you enable automatic constraint enforcement, Vertica uses an Insert-Validate (IV) lock. The IV lock is needed for operations where the system performs constraint validation for enabled PRIMARY or UNIQUE key constraints. Such operations can include INSERT, COPY, MERGE, UPDATE, MOVE_PARTITIONS_TO_TABLE.

How DML Operates with Constraints

With enforced primary or unique key constraints, DML operates in two-stages.

Delays in Bulk Loading Caused by Constraint Validation

In bulk load situations, some transactions could be temporarily blocked while primary or unique key constraints are validated. For example:

You could have three sessions (for example, sessions 1, 2 and 3). Each session concurrently has an I lock for a bulk load. Session 1 takes an IV lock to validate constraints. Only one session can hold an IV lock on a given table; other sessions can continue loading the table while holding I locks.

Sessions 2 and 3 wait for session 1 to validate constraints, and then commit, releasing the IV lock. (If session 1 fails, the statement rolls back, and the next session can obtain the IV lock. While sessions can load the table in parallel, an IV lock requires that sessions takes turns obtaining the IV lock for the final stage of constraint validation.)

For information on lock modes and compatibility and conversion matrices, see Lock Modes in Vertica Concepts. See also the LOCKS and LOCK_USAGE sections in the SQL Reference Manual.