Timedout ERROR

abhishek_1

Hi, I am getting the following errors in Vertica. 1. Unavailable: [Txn 0xa00000003d521a] initiator locks for query - timeout error Timed out X locking Table:CORE.ETL_ABC_JOB_RUN_TRCKG. X held by [user ETLUSR (update CORE.ETL_ABC_JOB_RUN_TRCKG set job_fnsh_ts=sysdate, job_stat_cd_id= 3 where job_run_cycl_id= 252701;)]. Your current transaction isolation level is READ COMMITTED 2. Unavailable: [Txn 0xa00000003d50a3] lock table for query - timeout error Timed out X locking Table:CORE.ETL_ABC_JOB_RUN_TRCKG. T held by [user appdba (purge_table_projections)]. Your current transaction isolation level is SERIALIZABLE 3. CMN_1022 [select drop_partition ('RPT_NC.WF_SRVC_CALL_ACTY_F','HPSM_VALE') FnName: Execute Direct -- ERROR 2083: A Moveout operation is already in progress on projection RPT_NC.WF_SRVC_CALL_ACTY_F_DBD_414_seg_EBI_PROD_b1 [txnid 45035996278213797 session iverpv001-6145:0x76e4d] Any idea why it happens and how to avoid them? Thanks in advance !!

[Deleted User]

Hi Abhishek, Well, for the lock timeouts, that means that some other operation was holding a lock on an object that you need in order to run that query, and that operation held the lock for too long so your query timed out waiting. You can see more details about locks in the "LOCKS" system table. For the last query, it sounds like you tried to drop a partition of a projection while we were in the middle of shuffling data for that projection. It's another (more-specific) case of lock contention. You can learn more about this by reading about the Tuple Mover in the documentation, and/or querying the "TUPLE_MOVER_OPERATIONS" system table. Adam

abhishek_1

Hi Adam, Thanks for your reply !!!! Will increasing the LockTimeOut parameter help?

Navin_C

Hello abhishek, Just a small suggestion on this. For any operation to perform Vertica first confirms whether there any ongoing operations on the object referenced in the query. So try committing everything after any operation is completed, this will reduce more LOCKS level issues.

abhishek_1

Hi Navin, I have increased the Locktimeout and its working fine. !!!!!!

bhanu_kishore

Hi abhishek,

                 I increased LockTimeOut parameter to 2000 . but stil getting below error 
java.sql.SQLException: [Vertica][VJDBC](2083) ERROR: A Moveout operation is already in progress

Yeswanth

Hi,

How to increase Lock Timeout parameter?

 

Thanks in advance.

 

Navin_C

Hi Yeswanth,

 

First check what is the existing settings for Locktimeout Parameter in database :

 

SELECT * FROM CONFIGURATION_PARAMETERSWHERE parameter_name = 'LockTimeout';

 

Then, depending on your requirement you can set it like this :

 

select set_config_parameter('LockTimeout',300)

 This command will set the LockTimeout paramter to 300 seconds (Default is 300 seconds)