Lock tree

Home > Select Project > Analysis > Lock Tree

This tool analyzes the trend of locks that occurred during the day. This function allows you to visually check the tree structure of the session that generated the lock (lock holder) and the session waiting for the lock (lock waiter). You can analyze the relationship between the holder and waiter of the lock that occurred at a specific time point. Through the WhaTap's Lock Tree feature, the DB administrator can identify and resolve the DB lock issues and optimize the DB performance.

Key features are as follows:

• Lock trend analysis: The trends of locks that occurred during the set search period are traced over time and displayed in a graph. This allows you to visually check how the locks are created and resolved.

• Check lock holder and waiter: You can see the holder and waiter for each lock in a tree structure. Through this, when a specific lock occurs, you can check the relationship between the session that generated the lock and the waiting session.

• Inter-session relationship analysis: By analyzing the relationship between the session that generated the lock and the waiting session, you can identify the cause of the lock that occurred during the query execution and transaction handling.

• Visual lock trend by time: Through the 1-Min Lock Trends and Lock Wait Sessions features, the lock occurrence data can be analyzed in time zones.

Basic usage guide

Select the desired date, time, and instance to view in Time and Instance. If a lock occurs at the set time, the searched data appears in Lock Wait Sessions and the Lock Tree table at the bottom of the screen.

• You can see the data for up to two weeks within a chart. If you click a specific time in the graph chart, you can check the lock information for the selected time in the table at the bottom of the screen. If you select the button in seconds, you can view the lock information in 5-second increments.

  Note

  Through the tags on the upper right of the Lock Wait Sessions chart, you can see the units to view.

• As you continuously drag (drill down) a specific time zone on the graph chart, you can see the charts and lock trees for detailed time zones.

• You can see the data in 5-second increments by dragging within 3 hours on the chart. However, the data in 5-second increments can only be viewed within the most recent month. The data earlier than one month can only be viewed as 5-minute summary data. For example, you can drag (drill down) lock tree data from 40 days ago for the 5-minute summary data, but you cannot see 5-second data.

Lock Wait Sessions

The Lock Wait Sessions feature allows you to analyze the lock wait sessions. Depending on the query period, the lock wait session data is provided at 5-minute intervals when the query is for 3 hours or more, and at 5-second intervals when the query is for less than 3 hours. You can also adjust the time range by dragging the chart flexibly.

1-Min Lock Trends

If you click a specific time zone in Lock Wait Sessions, the lock occurrence status for 30 minutes, from 15 minutes before to 15 minutes after is visualized. This allows you to effectively analyze the lock occurrence trend at 30-minute intervals.

Note

Total Locks indicates the total number of locks generated in the time zone.

Viewing time

1-Min Lock Trends and Lock Tree tables are timed based on the maximum number of locks that occurred at the clicked time in Lock Wait Sessions. This allows you to find the zone with the most locks.

If you click the bar of a different time zone in the 1-Min Lock Trends chart with the Lock Wait Sessions unchanged, the time range is synced to the Lock Tree table by using the seconds with the highest number of locks for the minute. This allows you to see the information reflected in the Lock Tree table when changing the time zone.

• Viewing for 3 hours or more (using 5-minute data)

  The bar color of the 5-minute zone from the time of inquiry appears more prominent.

  When the 07:15:00 time point is clicked, the color of the 07:15:00-07:20:00 zone's bar chart appears more prominent.

  

• Viewing for less than 3 hours (using 5-minute data)

  It is useful for analyzing the locks that occur within short time intervals.

  

Column information guide

Active session

Item	Description
pid	Process ID
db	Database name
user	SQL execution account
application	Client application name
client_host	Host name of the connected client (reported by reverse DNS lookup of client_addr)
client_addr	Client IP address
client_port	Client port number (in case of using the UNIX socket -1)
query_hash	Hash value of query (internal WhaTap control value)
query_start	Time when the active query started (if the state is not active, it is the last query started time)
state	Current status of the backend process (active, idle, etc.)
state_change	Last time when the state was changed
xact_start	Time when the current transaction for the process started (null if no transaction is active)
backend_start	Time when the backend process started
backend_xmin	Minimum value of xid whose backend process is being processed
backend_type	Type of the backend process (e.g. autovacuum worker, logical replication launcher, client backend, checkpointer)
backend_xid	xid whose backend is being processed
wait_event_type	Type of the event whose backend process is in standby
wait_event	Waiting event name where the backend process is in standby

Note

WhaTap basically stores the client-related information.

Lock tree

Item	Description
holder_relation	Object (table or index) whose holder session has any lock.
holder_type	Type of the holder session (transaction ID, tuple, etc.)
lock_mode	holder_mode (exclusive lock, shared lock, etc.)
lock_request	waiter_mode (exclusive lock, shared lock, etc.)
state	Current status of each session (backend process)
waiter_relation	Object whose waiter session is waiting for lock acquisition.
waiter_type	Type of the waiter session (transaction ID, tuple, etc.)

Process information

The following items are the metrics collected if the XOS agent has been installed.

Item	Description
cputime	CPU usage time
cpuusage	CPU Utilization
elapse	Elapsed time of CPU usage
vsize	Virtual memory size (Kb)
rss	Resident Set Size (RSS) that is the number of physical pages associated with the process.
state	Process status
ioread	Actual time spent reading the block (milliseconds)
iowrite	Actual time spent writing the block (milliseconds)
pss	Process specific memory usage + Percentage of shared memory occupied by one process
uid	user id
cmd	Executing command
longcmd	Full path of cmd