The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!


Can anyone give how to resolve this error.
throw er; // Unhandled 'error' event
Error: Unavailable: initiator locks for query - Locking failure: Timed out X locking Table:public.test2. X held by [user user_testing (select count(*) from test2;)]. Your current transaction isolation level is READ COMMITTED


  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited October 2017


    The issue is that the user "user_testing" took an exclusive "X" lock on the table public.test2 (via an UPDATE or DELETE) and did not commit leaving the transaction open. Then another session came along and also tried to grab an exclusive "X" lock on the table (also via an UPDATE or DELETE) . But it can't and has to wait in a queue until that first session either commits or rollbacks the transaction. The default timeout for waiting on an X lock is controlled via the "LockTimeout" configuration parameter:

    dbadmin=> select parameter_name, current_value, default_value, description from configuration_parameters where parameter_name = 'LockTimeout';
     parameter_name | current_value | default_value |                       description
     LockTimeout    | 300           | 300           | Time to wait for a table lock before giving up (seconds)
    (1 row)


    Session #1 (usera):

    usera=> select * from some_table;
    (1 row)
    usera=> update some_table set c = 2;
    (1 row)
    usera=> SELECT object_name, lock_mode, transaction_description FROM v_monitor.locks WHERE object_name ilike '%some_table%';
           object_name       | lock_mode |             transaction_description
     Table:public.some_table | X         | Txn: a00000001157e8 'select * from some_table;'
    (1 row)

    Session #2 (userb):

    userb=> delete from some_table;

    After 5 minutes, I get this error:

    ERROR 5156:  Unavailable: initiator locks for query - Locking failure: Timed out X locking Table:public.some_table. X held by [user usera (select * from some_table;)]. Your current transaction isolation level is READ COMMITTED

    Solution? Either make sure users aren't holding onto "X" locks longer than 10 minutes, or increase the "LockTimeout" configuration parameter value. But be careful as you could end up with a lot of transactions in the queue!

    Here is a link to more info. on Vertica lock modes:

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file