HP Vertica Lock Types and Conflicts Management
- Types of locks in Vertica
S — Share lock needed for select operations (serializable only)
I — Insert lock needed for insert operations
X — Exclusive lock needed for delete operations
T — Tuple Mover lock needed by the Tuple Mover and also used for COPY
into pre-join projections
U — Usage lock needed for query processing
O — Owner lock needed when owner is altering table structure
// ]]>
- Vertica Complete Compatibility Matrix
- To identify any possible locks we need to query the v_monitor.locks table.
dbadmin= select * from locks;
-[ RECORD 1 ]-----------+--------------------------------------------------------
node_names | v_test_node0001
object_name | Table:public.test_lock
object_id | 45035996273773554
transaction_id | 45035996273775496
transaction_description | Txn: a0000000011388 'insert into test_lock values (1);'
lock_mode | I
lock_scope | TRANSACTION
request_timestamp | 2014-04-22 16:17:46.537463-03
grant_timestamp | 2014-04-22 16:17:46.537469-03- To see how long the lock is going on use the following query:
select timestampdiff(second,request_timestamp,GETDATE()) from locks;- To get a complete view of your process that is currently locking an object use the DUMP_LOCKTABLE function.
dbadmin= SELECT DUMP_LOCKTABLE();
DUMP_LOCKTABLE | Transaction in progress: tid=a0000000011388
digraph Lock_Wait_Graph {
graph [rankdir=BT label="nNode 'v_test_node0001' Locktable (1 entries)n(This is a Waits
-For graphnCircles are transactions, Squares are things lockednEdges from Txns to Oids =
= Txn waiting for LocknEdges from Oids to Txns == Lock in use by Txn)nn" labelloc=t]
1[label="Table:public.test_lock(a0000000010bf2)" shape=box];
2[label="Txn: a0000000011388 'insert into test_lock values (1);'" shape=ellipse];
1-2[label="I; TRANSACTION; 2014-04-22 16:17:46.537463-03; 2014-04-22 16:17:46.537469-03"
color="orange"];
}
digraph Lock_Wait_Graph {
graph [rankdir=BT label="nCombined Distributed Lock Graphn(This is a Waits-For graphnCi
rcles are transactions, Squares are things lockednEdges from Txns to Oids == Txn waiting
for LocknEdges from Oids to Txns == Lock in use by Txn)nn" labelloc=t]
1[label="Table:public.test_lock(a0000000010bf2)" shape=box];
2[label="Txn: a0000000011388 'insert into test_lock values (1);'" shape=ellipse];
1-2[label="I; TRANSACTION; 2014-04-22 16:17:46.537463-03; 2014-04-22 16:17:46.537469-03"
color="orange"];
}
// ]]>
To get a better view of the this crazy code use the copy and paste it into this online Graphviz Api or you
can download the open source software and install it on your machine.
The output should be something like this:
- To fix this situation you need to wait for the session to close/commit or you can kill that session using it's transaction_id.
dbadmin= select * from sessions where transaction_id in (select transaction_id from
locks);
-[ RECORD 1 ]--------------+-----------------------------------------------------
------------------------------
node_name | v_test_node0001
user_name | dbadmin
client_hostname | 127.0.0.1:59169
client_pid | 32583
login_timestamp | 2014-04-22 16:08:16.487623-03
session_id | bih001-20862:0x43de2
client_label |
transaction_start | 2014-04-22 16:17:46.513285-03
transaction_id | 45035996273775496
transaction_description | user dbadmin (insert into test_lock values (1);)
statement_start | 2014-04-22 16:47:01.657181-03
statement_id | 67
last_statement_duration_us | 9434
runtime_priority |
current_statement | select * from sessions where transaction_id in (select transa
ction_id from locks);
last_statement | select * from locks;
ssl_state | None
authentication_method | Password
client_type | vsql
client_version | 07.00.0000
client_os | Linux 2.6.32-220.el6.x86_64 x86_64
-- to kill or close the transaction
dbadmin= select close_session('bih001-20862:0x43de2');
// ]]>
- The v_monitor.sessions table offers us valuable information about our running sessions suck as :
node_name - the node name for which information is listed
user_name - the user running the query
client_hostname - host name and port of the TCP socket from which the client co
nnection was made
client_pid - process identifier(operational system)
login_timestamp - user login time
session_id - unique session identifier
client_label - user-specified label for the client connection
transaction_start - current transaction start time
transaction_id - hexadecimal representation of the transaction ID
transaction_description - current transaction content
statement_start - start of the execution
statement_id - unique id for the currently-executing statement
last_statement_duration_us - duration of the last completed statement in microseconds
runtime_priority - determines the amount of run-time resources (CPU, I/O bandwid
th) the Resource Manager dedicates(HIGH,MEDIUM,LOW)
current_statement - currently executing statement
last_statement - last executed statement
ssl_state - Secure Socket Layer(SSL) state (None,Server,Mutual)
authentication_method - type of client authentication
client_type - type of client from which the connection was made
client_version - client version
client_os - client operating system