Looking into details of locking

(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:
Query (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.
Reference:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Components/Locks/AboutVerticaLocks.htm
Have fun!