Oracle 数据库的锁排查方法

关键字

oracle lock

问题描述

Oracle 数据库上锁问题如何排查

解决问题思路

准备数据
create table lock_test(name varchar(10),age varchar(10));

insert into lock_test values('ff','10');
insert into lock_test values('yy','20');
insert into lock_test values('ll','30');


Oracle 会话默认开启事务：
session1：
update lock_test set age = 'session1' where name = 'ff';

session2：
update lock_test set age = 'session2' where name = 'ff';

session2 因为更新同一行数据肯定 pending



-- 查新活跃会话
select s.sid,
       s.sql_id,
       s.SQL_CHILD_NUMBER CHILD,
       s.USERNAME,s.CLIENT_INFO,
       s.LOGON_TIME,
       s.EVENT,
       s.BLOCKING_SESSION BLOCK,
       v.SQL_TEXT
 from V$SQL v right join v$session s  on v.sql_id=s.sql_id
 where  s.WAIT_CLASS<>'Idle'
 group by s.sid,s.sql_id,s.SQL_CHILD_NUMBER,s.USERNAME,s.CLIENT_INFO,s.LOGON_TIME,s.EVENT,s.BLOCKING_SESSION,v.SQL_TEXT;
 
 
|SID|SQL_ID       |CHILD|USERNAME|CLIENT_INFO|LOGON_TIME             |EVENT                        |BLOCK|SQL_TEXT                                                                                                                                                                                                                                                                                                                                                                                                  |
|---|-------------|-----|--------|-----------|-----------------------|-----------------------------|-----|-----------------------------------------------------------
|8  |5cnbp94b459wf|0    |ADMIN   |           |2023-09-22 09:47:44.000|enq: TX - row lock contention|1,837|update lock_test set age = 'session2' where name = 'ff'                                                                                                                                                                                                                                                                                                                                                   |




-- 查看正在锁的会话
SELECT * FROM V$SESSION_BLOCKERS;
SELECT * FROM V$SESSION_BLOCKERS  WHERE  BLOCKER_SID  NOT IN (SELECT SID  FROM V$SESSION_BLOCKERS);

|SID|SESS_SERIAL#|WAIT_ID|WAIT_EVENT|WAIT_EVENT_TEXT              |BLOCKER_INSTANCE_ID|BLOCKER_SID|BLOCKER_SESS_SERIAL#|CON_ID|
|---|------------|-------|----------|-----------------------------|-------------------|-----------|--------------------|------|
|8  |59,610      |43     |307       |enq: TX - row lock contention|1                  |1,837      |43,494              |0     |


-- 杀掉SESSION
SELECT DISTINCT  'ALTER SYSTEM KILL SESSION ''' || a.BLOCKER_SID  || ',' ||a."BLOCKER_SESS_SERIAL#" || ''';' FROM V$SESSION_BLOCKERS a ;

|'ALTERSYSTEMKILLSESSION'''||A.BLOCKER_SID||','||A."BLOCKER_SESS_SERIAL#"||''';'|
|-------------------------------------------------------------------------------|
|ALTER SYSTEM KILL SESSION '1837,43494';                                        |





模拟二
session1：
update lock_test set age = 'session1' where name = 'ff';

session2：
alter table lock_test add  name1 varchar(100);


以上方法可以查询处理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

问题总结

Oracle 数据库的锁排查方法