如何解决Oracle数据库中的会话阻塞

我会带着你远行 2023-10-05 15:47 14阅读 0赞

数据库阻塞是一种情况，即一个用户运行的语句锁定一条记录或一组记录，而同一用户或不同用户运行的另一条语句则要求该一条或多条记录上的冲突类型由第一位用户锁定。  
数据库阻塞问题在任何应用程序中都是很常见的情况。  
如何识别阻塞会话？或更具体地说，如何识别阻塞的行和对象？如何解决数据库阻塞问题？让我们一一看。

**如何识别阻塞会话**  
**1. DBA\_BLOCKERS****：**仅提供有关阻塞会话的信息。  
SQL> select \* from dba\_blockers;  
HOLDING\_SESSION  
—————  
252

**2. v $ LOCK****：**提供阻止和等待会话的详细信息。  
SQL> select l1.sid, ‘ IS BLOCKING ‘, l2.sid  
from v$lock l1, v$lock l2  
where l1.block =1 and l2.request > 0  
and l1.id1=l2.id1  
and l1.id2=l2.id2  
/

SID ‘ISBLOCKING’ SID  
———- ————- ———-  
244 IS BLOCKING 252

要获取更多具体细节，请使用以下查询：

SQL> select s1.username || [‘@’][Link 1] || s1.machine  
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘  
|| s2.username || [‘@’][Link 1]|| s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking\_status  
from v$lock l1, v$session s1, v$lock l2, v$session s2  
where 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 ;

BLOCKING\_STATUS  
——————————————————————————–  
[MECK@machine1][MECK_machine1] ( SID=244 ) is blocking [TAMY@machine2][TAMY_machine2] ( SID=252 )

**如何识别锁定的对象**

SQL> select \* from v$lock ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK  
—————- —————- ———- — ———- ———- ——- ———- ———- ———-  
0000000451723DE8 0000000451723E20 244 TX 1310745 3139497 6 0 166 1

000000046032AFE0 000000046032B000 252 TX 1310745 3139497 0 6 33 0

锁的类型– UL，TX和AMD  
TM1。UL是用户定义的锁，这是使用DBMS\_LOCK包定义的锁。  
2. TX锁是一个行事务锁；每次更改数据的事务都会获取一次。更改对象的数量无关紧要。ID1和ID2列指向该事务的回滚段和事务表条目。  
3. TM锁是DML锁。它为每个要更改的对象获取一次。ID1列标识要修改的对象。

因此，要找到被阻止的对象，我们可以使用v $ lock中的ID1。

SQL> select object\_name from dba\_objects where object\_id=307193;

OBJECT\_NAME  
————–  
OBJ1

**如何识别锁定的行**？

SQL> select do.object\_name,  
row\_wait\_obj\#, do.data\_object\_id, row\_wait\_file\#, row\_wait\_block\#, row\_wait\_row\#,  
dbms\_rowid.rowid\_create ( 1, do.data\_object\_id, ROW\_WAIT\_FILE\#, ROW\_WAIT\_BLOCK\#, ROW\_WAIT\_ROW\# )  
from v$session s, dba\_objects do  
where sid=252  
and s.ROW\_WAIT\_OBJ\# = do.OBJECT\_ID ;

OBJECT\_NAME  
——————————————————————————–  
ROW\_WAIT\_OBJ\# DATA\_OBJECT\_ID ROW\_WAIT\_FILE\# ROW\_WAIT\_BLOCK\# ROW\_WAIT\_ROW\#  
————- ————– ————– ————— ————-  
DBMS\_ROWID.ROWID\_C  
——————  
OBJ1  
307193 307193 5 455 0  
AABK/5AAFAAAAHHAAA

由此，我们直接得到该行：  
SQL> select \* from obj1 where rowid=’ AABK/5AAFAAAAHHAAA’ ;

获取被阻止的sql查询  
如果获得sid，则使用以下sql会很容易：  
SQL>select s.sid, q.sql\_text from v$sqltext q, v$session s  
where q.address = s.sql\_address  
and s.sid = 252;

SID SQL\_TEXT  
—– —————————————————————-  
252 update obj1 set bar=:”SYS\_B\_0″ where bar=:”SYS\_B\_1″

**查找阻止会话****SID****和序列号。**

SQL> Select blocking\_session, sid, serial\#, wait\_class,seconds\_in\_wait From v$session where blocking\_session is not NULL order by blocking\_session;

BLOCKING\_SESSION SID SERIAL\# WAIT\_CLASS SECONDS\_IN\_WAIT  
—————- ———- ———- ————————————————–  
244 252 11049 Application 1634

**解决锁定问题的解决方案**  
杀死阻止会话。  
SQL> alter system kill session 244,11049′ immediate;

System altered.

你完成了！

[Link 1]: mailto:'%20rel=
[MECK_machine1]: mailto:MECK@machine1
[TAMY_machine2]: mailto:TAMY@machine2