READ COMMITED ERROR

naresh_way2

Hi,
Can anyone give how to resolve this error.
events.js:163
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_Knicely

Hi,

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)

Example:

Session #1 (usera):

usera=> select * from some_table;
 c
---
 1
(1 row)

usera=> update some_table set c = 2;
 OUTPUT
--------
      1
(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:

https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/ConceptsGuide/Components/Locks/LockModes.htm