935649 - Vertica locking issue

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Sheeri Cabral [:sheeri]]

From an e-mail from Anurag:

Hey Sheeri,
I am trying to delete some data from vertica, keep getting this error 
even though I have no instances connected to vertica, any idea why?

aphadke=> delete from snippet_count where date = '2013-11-04';
ERROR 5156:  Unavailable: initiator locks for query - Locking failure: 
Timed out X locking Table:public.snippet_count. X held by [user aphadke 
(select * from snippet_count where date='2013-11-04';)]. Your current 
transaction isolation level is SERIALIZABLE

-anurag

Comment 1

[Sheeri Cabral [:sheeri]]

According to the LOCKS system table, it looks like you ran that query about an hour ago:

dbadmin=> \x
Expanded display is on.
dbadmin=> select * from LOCKS;
-[ RECORD 1 ]-----------+---------------------------------------------------------------------------
node_names              | v_metrics_node0001,v_metrics_node0002,v_metrics_node0003
object_name             | Table:public.snippet_count
object_id               | 45035996295745588
transaction_id          | 54043195530116099
transaction_description | Txn: c0000000197c03 'select * from snippet_count where date='2013-11-04';'
lock_mode               | X
lock_scope              | TRANSACTION
request_timestamp       | 2013-11-06 18:05:00.160248+00
grant_timestamp         | 2013-11-06 18:05:00.160257+00


I'm trying to figure out how to remove that from the LOCKS table......
dbadmin=> select now();
-[ RECORD 1 ]---------------------
now | 2013-11-06 19:07:10.85093+00

Comment 2

[Sheeri Cabral [:sheeri]]

All the info about this trnasaction.....but we care mostly about the session_id:

dbadmin=> select * from TRANSACTIONS where transaction_id=54043195530116099;
-[ RECORD 1 ]--------+---------------------------------------------------------------------------
start_timestamp      | 2013-11-06 18:03:46.478982+00
end_timestamp        | 
node_name            | v_metrics_node0003
user_id              | 45035996279160576
user_name            | aphadke
session_id           | vertica6.metrics.sc-20796:0xa6373a
transaction_id       | 54043195530116099
description          | Txn: c0000000197c03 'select * from snippet_count where date='2013-11-04';'
start_epoch          | 134916
end_epoch            | 
number_of_statements | 
isolation            | READ COMMITTED
is_read_only         | f
is_committed         | 
is_local             | f
is_initiator         | t
is_ddl               | 

Close the session:

dbadmin=> select close_session('vertica6.metrics.sc-20796:0xa6373a');
-[ RECORD 1 ]-+-------------------------------------------------------------------
close_session | Session close command sent. Check v_monitor.sessions for progress.

dbadmin=> select * from LOCKS;
(No rows)

You should be all set now.

Comment 3

[Anurag Phadke[:[e-mail]]]

tried to copy data, gave similar error:
SQL: COPY firefox_download_counts_aphadke(date, country_code, count, ua_family, ua_major, os_family, product_name, product_type, download_type, other) FROM LOCAL '/home/aphadke/fhr_vertica_output/download_count/out.tab.delimited.txt' delimiter '' EXCEPTIONS '/home/aphadke/fhr_vertica_logs/download_counts_exception.txt' REJECTED DATA '/home/aphadke/fhr_vertica_logs/download_counts_bad_data.txt' ;
Traceback (most recent call last):
  File "/home/aphadke/vertica_connectors/downloadCounts.py", line 71, in <module>
    cursor.execute(sql)
pyodbc.Error: ('HY000', "[HY000] ERROR 5156:  Unavailable: initiator locks for query - Locking failure: Timed out I locking Table:public.firefox_download_counts_aphadke. X held by [user aphadke (select * from snippet_count where date='2013-11-05';)]. Your current transaction isolation level is SERIALIZABLE\n (5156) (SQLExecDirectW)")


select * from locks;
                        node_names                        |                 object_name                  |     object_id     |  transaction_id   |                          transaction_description                           | lock_mode | lock_scope  |       request_timestamp       |        grant_timestamp        
----------------------------------------------------------+----------------------------------------------+-------------------+-------------------+----------------------------------------------------------------------------+-----------+-------------+-------------------------------+-------------------------------
 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.versioncheck_requests_raw       | 45035996277984996 | 49539595902727585 | Txn: b00000001935a1 '-'                                                    | I         | TRANSACTION | 2013-11-06 19:31:05.945773+00 | 2013-11-06 19:31:05.945782+00
 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.amo_download_requests_raw       | 45035996277985522 | 49539595902727589 | Txn: b00000001935a5 '-'                                                    | I         | TRANSACTION | 2013-11-06 19:31:13.226702+00 | 2013-11-06 19:31:13.226709+00
 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.blocklist_requests_raw          | 45035996277986558 | 49539595902727583 | Txn: b000000019359f '-'                                                    | I         | TRANSACTION | 2013-11-06 19:31:04.149656+00 | 2013-11-06 19:31:04.149663+00
 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.snippet_count                   | 45035996295745588 | 54043195530116217 | Txn: c0000000197c79 'select * from snippet_count where date='2013-11-05';' | X         | TRANSACTION | 2013-11-06 19:20:53.613843+00 | 2013-11-06 19:20:53.613849+00
 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.firefox_download_counts_aphadke | 54043195545326680 | 54043195530116217 | Txn: c0000000197c79 'select * from snippet_count where date='2013-11-05';' | X         | TRANSACTION | 2013-11-06 19:21:11.933587+00 | 2013-11-06 19:21:11.933593+00



maybe my session isn't releasing locks?

Comment 4

[Sheeri Cabral [:sheeri]]

Well, the relevant entry is the last one:

 v_metrics_node0001,v_metrics_node0002,v_metrics_node0003 | Table:public.firefox_download_counts_aphadke | 54043195545326680 | 54043195530116217 | Txn: c0000000197c79 'select * from snippet_count where date='2013-11-05';' | X         | TRANSACTION | 2013-11-06 19:21:11.933587+00 | 2013-11-06 19:21:11.933593+00

Do you commit your transactions? Have you been exiting normally or with Ctrl-C or something?

If you think it's your session, try exiting normally and going back in.

Comment 5

[Anurag Phadke[:[e-mail]]]

yup, it was coz of lost network connectivity, closed the session and i can now run queries.
thank u sheeri!
-anurag