AnalyticDB:Diagnose and handle lock waits

Create a lock monitoring view

Run the following statement to create the v_locks_monitor view. This view joins pg_locks and pg_stat_activity to surface both blocking and waiting processes in a single query.

create view v_locks_monitor as
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.transactionid,
b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.transactionid,
b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and

r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||
' , Mode: '||case when mode is null then 'NULL' else mode::text end||
' , Username: '||case when usename is null then 'NULL' else usename::text end||
' , Database: '||case when datname is null then 'NULL' else datname::text end||
' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||
' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||
' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
' , Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||
' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||
' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||
chr(10)||'--------'||chr(10), (case when granted then '0' else '1' end)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc )
as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,transactionid::text,classid,objid,objsubid;

The lock_conflict column aggregates details for each lock conflict:

Query lock conflicts

When a lock wait occurs, enable expanded display and query the view:

postgres=# \x

postgres=# select * from v_locks_monitor;

Each row in the result represents a lock conflict. Within each lock_conflict entry, identify the process where Lock_Granted is t — that is the blocking process.

Terminate the blocking process

Run the following statement to terminate the blocking process, replacing PID with the Pid value of the blocking entry (Lock_Granted = t):

postgres=# select pg_terminate_backend(PID);

After terminating the process, verify that the locks are released:

postgres=# select * from v_locks_monitor;

• If the result is 0 rows, the locks are released.

• If a row still appears, run select pg_terminate_backend(PID); again for the remaining blocking process.

Prevent lock waits

Terminating a blocking process resolves the immediate issue. To reduce lock waits in your application:

• Commit transactions promptly. Idle transactions that hold locks block other sessions. Enable autocommit where possible, and audit code paths that are missing COMMIT, ROLLBACK, or END.

• Avoid DDL in long-running transactions. Data definition language (DDL) operations such as ALTER TABLE acquire AccessExclusiveLock, which blocks all other access to the table. Run DDL during low-traffic periods.

• Use NOWAIT or lock_timeout. For queries that use SELECT ... FOR UPDATE or explicit LOCK statements, add NOWAIT to fail immediately if the lock is unavailable, or set lock_timeout to limit how long the statement waits.

• Keep transactions short. Long-running transactions increase the window during which locks are held, raising the likelihood of contention.

References

SQL statements to monitor PostgreSQL lock waits - who blocked whom