Does vertica text index creation needs X Exclusive lock on the source table

rajatpaliwal86

I'm trying to create a text index on the flex table. However, it seems like the create index command is taking an X - Exclusive lock on the source table and lot letting insert operations (copy commands) on the source table. Since the table contains ~ 1 billion rows so that the create index statement might take a huge time and affect the bulk load on the table. Is it expected?

Jim_Knicely

That's right, While creating the Text Index on a Table, Vertica will lock that Table with an "S" lock.

Check out the Lock Compatibility Matrix here:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConceptsGuide/Components/Locks/LockModes.htm

You are requesting an "I" lock on a table that already has granted "S" lock. If two requested modes intersect in a No cell (on the Lock Compatibility Matrix), the second request is not granted until the first request releases its lock.

rajatpaliwal86

I further check the error code - it seems like the lock is SHARED(S) then why it doesn't let the insert queries to happen in parallel?

Exception java.sql.SQLTransactionRollbackException: [Vertica]VJDBC ERROR: Unavailable: initiator locks for query - Locking failure: Timed out I locking Table:athenav2.f_network_events. S held by [user dbadmin (set up text index)].