数据库锁表分析
1、会话1 update Scott.emp set deptno=10;
2、会话2 update scott.emp set deptno=30;
查询是否存在锁定
select s1.INST_ID,
s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where l1.INST_ID = s1.INST_ID
and s1.INST_ID = s2.INST_ID
and s2.INST_ID = l2.INST_ID
and s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
19:22:53 SQL> select s1.INST_ID,
19:24:50 2 s1.username || '@' || s1.machine || ' (SID=' || s1.sid ||
19:24:50 3 ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
19:24:50 4 s2.sid || ' ) ' AS blocking_status
19:24:50 5 from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
19:24:50 6 where l1.INST_ID = s1.INST_ID
19:24:50 7 and s1.INST_ID = s2.INST_ID
19:24:50 8 and s2.INST_ID = l2.INST_ID
19:24:50 9 and s1.sid = l1.sid
19:24:50 10 and s2.sid = l2.sid
19:24:50 11 and l1.BLOCK = 1
19:24:50 12 and l2.request > 0
19:24:50 13 and l1.id1 = l2.id1
19:24:50 14 and l2.id2 = l2.id2;
INST_ID
BLOCKING_STATUS
1
SYS@localhost.localdomain (SID=16 ) is blocking SYS@WORKGROUP\LAPTOP-5RF2B7J0 ( SID=15 )
查询update后未提交的会话
SELECT
A.SID,
A.SERIAL#,
A.USERNAME,
A.EVENT,
A.WAIT_CLASS,
A.SECONDS_IN_WAIT,
A.PREV_EXEC_START,
B.LOCKED_MODE,
C.OWNER,
C.OBJECT_NAME,
C.OBJECT_TYPE
FROM
V$SESSION A
INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID
WHERE
A.WAIT_CLASS = 'Idle'
AND A.SECONDS_IN_WAIT >10
/SESSION空闲后一段时间还锁定的才算有问题,这里随便给了个数值10秒/
19:27:05 SQL> SELECT
19:27:07 2 A.SID,
19:27:07 3 A.SERIAL#,
19:27:07 4 A.USERNAME,
19:27:07 5 A.EVENT,
19:27:07 6 A.WAIT_CLASS,
19:27:07 7 A.SECONDS_IN_WAIT,
19:27:07 8 A.PREV_EXEC_START,
19:27:07 9 B.LOCKED_MODE,
19:27:07 10 C.OWNER,
19:27:07 11 C.OBJECT_NAME,
19:27:07 12 C.OBJECT_TYPE
19:27:07 13 FROM
19:27:07 14 V$SESSION A
19:27:07 15 INNER JOIN V$LOCKED_OBJECT B ON A.SID = B.SESSION_ID
19:27:08 16 INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID
19:27:08 17 WHERE
19:27:08 18 A.WAIT_CLASS = 'Idle'
19:27:08 19 AND A.SECONDS_IN_WAIT >10;
SID SERIAL# USERNAME EVENT WAIT_CLASS SECONDS_IN_WAIT PREV_EXEC_START LOCKED_MODE OWNER OBJECT_NAME OBJECT_TYPE
16 61 SYS SQL*Net message from client Idle 38 2021-12-15 19:26:31 3 SCOTT DEPT TABLE
16 61 SYS SQL*Net message from client Idle 38 2021-12-15 19:26:31 3 SCOTT EMP TABLE
Elapsed: 00:00:00.06
----------查看被锁对象
set lin 400 pages 49999
col USERNAME for a15
col owner for a15
col OBJECT_NAME for a35
col PROGRAM for a35
col PROCESS for a15
select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
from gv$locked_object a, dba_objects b,gv$session c
where a.object_id=b.object_id and c.sid=a.session_id;
19:32:51 SQL> set lin 400 pages 49999
19:33:07 SQL> col USERNAME for a15
19:33:07 SQL> col owner for a15
19:33:07 SQL> col OBJECT_NAME for a35
19:33:07 SQL> col PROGRAM for a35
19:33:07 SQL> col PROCESS for a15
19:33:07 SQL> select a.LOCKED_MODE,a.inst_id,b.owner,b.object_name,a.object_id,a.session_id,c.serial#,c.username,c.sql_id,c.PROCESS ,c.PROGRAM
19:33:07 2 from gv$locked_object a, dba_objects b,gv$session c
19:33:07 3 where a.object_id=b.object_id and c.sid=a.session_id;
LOCKED_MODE INST_ID OWNER OBJECT_NAME OBJECT_ID SESSION_ID SERIAL# USERNAME SQL_ID PROCESS PROGRAM
3 1 SCOTT DEPT 87106 15 9 SYS 022scd5v03tnp 23708:31696 navicat.exe
3 1 SCOTT DEPT 87106 16 61 SYS 19093 sqlplus@localhost.localdomain (TNS
V1-V3)
alter system kill session 'sid,serial#'
---------------------创建触发器（若审计没开，可创建触发器追踪用户被锁原因）
create table logon_error(AUTHENTICATED_IDENTITY varchar2(200),HOST varchar2(200),ip_address varchar2(200),"sysdate" date);
CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_error VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'),sys_context('userenv', 'ip_address'),SYSDATE);
COMMIT;
END IF;
END;
/
查询触发器监控结果：
set pages 130;
set lines 1300;
col AUTHENTICATED_IDENTITY for a20;
col HOST for a20;
col IP_ADDRESS for a20;
select * from logon_error;