Constraint Enforcement and Locking

Vertica uses an insert/validate (IV) lock for DML operations that require validation for enabled primary key and unique constraints.

When you run these operations on tables that enforce primary or unique key constraints, Vertica sets locks on the tables as follows:

  1. Sets an I (insert) lock in order to load data. Multiple sessions can acquire an I lock on the same table simultaneously, and load data concurrently.

  2. Sets an IV lock on the table to validate the loaded data against table primary and unique constraints. Only one session at a time can acquire an IV lock on a given table. Other sessions that need to access this table are blocked until the IV lock is released. A session retains its IV lock until one of two events occur:

    • Validation is complete and the DML operation is committed.
    • A constraint violation is detected and the operation is rolled back.

    In either case, Vertica releases the IV lock.

IV Lock Blocking

While Vertica validates a table's primary or unique key constraints, it temporarily blocks other DML operations on the table. These delays can be especially noticeable when multiple sessions concurrently try to perform extensive changes to data on the same table.

For example, each of three concurrent sessions attempts to load data into table t1, as follows:

  1. All three session acquire an I lock on t1 and begin to load data into the table.
  2. Session 2 acquires an exclusive IV lock on t1 to validate table constraints on the data that it loaded. Only one session at a time can acquire an IV lock on a table, so sessions 1 and 3 must wait for session 2 to complete validation before they can begin their own validation.
  3. Session 2 successfully validates all data that it loaded into t1. On committing its load transaction, it releases its IV lock on the table.
  4. Session 1 acquires an IV lock on t1 and begins to validate the data that it loaded. In this case, Vertica detects a constraint violation and rolls back the load transaction. Session 1 releases its IV lock on t1.
  5. Session 3 now acquires an IV lock on t1 and begins to validate the data that it loaded. On completing validation, session 3 commits its load transaction and releases the IV lock on t1. The table is now available for other DML operations.

See Also

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.