Looking into details of locking

Bryan_H

(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.

Reference:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConceptsGuide/Components/Locks/AboutVerticaLocks.htm

Have fun!