kingbase常用锁处理

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

kingbase常用锁问题处理

使用步骤

锁表处理：
–查看sys_stat_activity和sys_locks这两个视图
–查看sys_state_activity表中state为active的行，如果event_wait_type为lock时，发生了锁表。
SELECT pid,sys_blocking_pids(pid),wait_event,wait_event_type,state,query FROM sys_stat_activity WHERE state != ‘idle’;
–当sys_locks表中granted字段有false时，出现了锁表。
select * from sys_locks where granted=‘false’;
–会把相应的事务释放掉
SELECT sys_terminate_backend(pid);

释放空连接
SELECT sys_terminate_backend(pid) FROM sys_stat_activity WHERE state=‘idle’;

检测表是否被使用
SELECT MODE,pl.pid,datname,(SELECT relname FROM sys_class c WHERE c.oid=pl.relation)
,client_addr,query,locktype
FROM sys_locks pl LEFT JOIN sys_stat_activity psa
ON pl.pid = psa.pid;
终止相应的进程或连接，如果此连接正在写数据，终止可能导致操作丢失
SELECT sys_terminate_backend(pid);

查看锁类型
SELECT
a.datname,
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc(‘second’,query_start) AS timestamp) AS query_start
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
pg_stat_activity a
WHERE NOT pg_locks.pid = pg_backend_pid()
AND pg_locks.pid=a.pid
and pg_class.relname=‘xxx’;