Looking into details of locking
Bryan_H
Vertica Employee Administrator
(thanks @skeswani for query and advice)
If you have a lot of concurrent queries, especially mixing DDL and DML, you might see lock contention. If you'd like to see how locks interact in your system, the following queries generate a temporary table with Gantt chart to show an ordered list of locks over time:
(P.S. be sure you don't have a lock_gantt table in your schema already!)
DROP TABLE IF EXISTS lock_gantt;
CREATE LOCAL TEMPORARY TABLE lock_gantt ON COMMIT PRESERVE ROWS AS
select dc_lock_attempts.node_name,
dc_lock_releases.object_name,
dc_lock_releases.mode,
dc_lock_releases.transaction_id,
(dc_lock_attempts.start_time-mv_offset)second(5) lock_attempt,
(dc_lock_releases.grant_time-mv_offset)second(5) lock_grant,
(dc_lock_releases.time-mv_offset)second(5) lock_releases,
(dc_lock_releases.time-grant_time)second(5) hold_time,
(grant_time-dc_lock_attempts.time)second(5) wait_time,
concat(
concat(
concat(
lpad ('', ((dc_lock_attempts.start_time-mv_offset)second(0)/scale)::int ,' '),
lpad ('', ((grant_time-dc_lock_attempts.time)second(0)/scale)::int ,'.')),
rpad('|', ((dc_lock_releases.time-grant_time)second(0)/scale)::int, 'x')),
'|') chart
,dc_lock_attempts.start_time as attempted
,dc_lock_releases.grant_time as granted
,dc_lock_releases.time as released
from dc_lock_releases join dc_lock_attempts on
dc_lock_releases.node_name = dc_lock_attempts.node_name
and dc_lock_releases.object_name = dc_lock_attempts.object_name
and dc_lock_releases.transaction_id = dc_lock_attempts.transaction_id
and dc_lock_releases.object = dc_lock_attempts.object
and dc_lock_releases.session_id = dc_lock_attempts.session_id
and dc_lock_releases.user_id = dc_lock_attempts.user_id
and dc_lock_releases.mode = dc_lock_attempts.mode
and dc_lock_attempts.start_time <= dc_lock_releases.grant_time
and dc_lock_releases.grant_time <= dc_lock_attempts.time
cross
join (
select min(time) mv_offset,
'20'::int scale
from dc_lock_attempts
) MinVal
where
-- dont care about locks held for less than 1 second
(dc_lock_releases.time-grant_time)second(5) > interval '1 second'
order by 1,4,5;
select node_name, transaction_id, object_name, mode, attempted, granted, released, chart from lock_gantt
-- TO LIMIT BY NODE:
-- where node_name in ('node001')
order by node_name, lock_attempt, lock_grant;
Example output:
node_name transaction_id object_name mode attempted granted released chart
v_docker_node0001 45035996292075719 Table:public.dump1090new I 2019-07-22 08:33:40 2019-07-22 08:33:40 2019-07-22 08:33:42 |
v_docker_node0001 45035996292075722 Table:public.dump1090new I 2019-07-22 08:33:42 2019-07-22 08:33:42 2019-07-22 08:33:45 |
v_docker_node0001 45035996292075730 Table:public.dump1090new I 2019-07-22 08:33:45 2019-07-22 08:33:45 2019-07-22 08:33:47 |
You can get more information by looking up transaction_id to see which transaction is locking, or show more fields like session_id and node_name to isolate issues further.
Have fun!