项目中涉及到一条更新语句: update语句:update ... where 主键=xxx的形式,执行时间特别久,于是查看一下数据库正在执行sql及死锁表的信息
1.以下SQL用于查看当前数据库登陆用户及正在执行的SQL
SELECT b.sid oracleID,
b.username 登录Oracle用户名,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
2.查看哪些表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
3.查看哪个session引起的表被锁住
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
4.清楚锁表的session
alter system kill SESSION '575,11477';
其中575是步骤3中查出的sid,11477是步骤3中查出的serial#
pgsql数据库
1 查找锁表的pid
select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'lockedtable';
2 查找锁表的语句
select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'lockedtable');
3 查找所有活动的被锁的表
select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid
and t.relkind = 'r'
);
4 解锁
SELECT pg_cancel_backend(pid);
5 批量(未排除自己)
select pg_cancel_backend(pid)
from pg_stat_activity
where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid
and t.relkind = 'r'
);