TX锁处理

实际处理后，在测试环境中模拟还原TX锁，及处理。

本篇博客目录：

1.TX锁模拟实际环境

2.登陆数据库，查询相关信息

3.确认锁源头，kill进程释放资源

一、TX锁模拟

sess_1

SQL> update yz.a set name='cc' where id=1;

1 row updated.

  sess_2

SQL>  delete a where name is null;

  sess_3

SQL> update a set id=5 where id=1;

······数据库tx锁达到30个，短信告警

二、DBA介入，查询相关信息

视图查询当前数据库中活跃会话状态，发现存在TX锁信息
SQL>  select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where status='ACTIVE' and username is not null;

INST_ID    SID SERIAL# USERNAME             STATUS     MACHINE              SQL_ID               EVENT                                s LAST_CALL_ET
------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------
      1   1076      18 YZ                   ACTIVE     enmo                 3xz40fnbtp8gw        enq: TX - row lock contention      216          114
      1   1081      11 YZ                   ACTIVE     enmo                 9hjrz7bh8a0st        enq: TX - row lock contention      100           75
      1   1083      58 SYS                  ACTIVE     enmo                 2ymxxw3mapxd9        SQL*Net message to client            2            0

查询被阻塞会话执行的SQL，已经阻塞会话信息

SQL> select sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION,count(*) from gv$session where event='enq: TX - row lock contention' group by sql_id,BLOCKING_INSTANCE,BLOCKING_SESSION;

SQL_ID BLOCKING_INSTANCE BLOCKING_SESSION COUNT(*)
-------------------- ----------------- ---------------- ----------
9hjrz7bh8a0st 1 1094 1
3xz40fnbtp8gw 1 1094 1

查询被阻塞SQL文本

SQL>select sql_text from v$sql where sql_id='9hjrz7bh8a0st'

SQL_TEXT
----------------------------------------------------------------
update a set id=5 where id=1

  通过cursor查询SQL文本

SQL> select * from table(dbms_xplan.display_cursor('&SQL_IDorSQL_HASH_VALUE',0,'advanced'));

查询源头会话信息及状态

SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where inst_id=1 and sid=1094;

INST_ID SID SERIAL# USERNAME STATUS MACHINE SQL_ID EVENT s LAST_CALL_ET
------- ------ ------- -------------------- ---------- -------------------- -------------------- ------------------------------ ------- ------------
1 1094 11 SYS INACTIVE enmo SQL*Net message from client 525 504

无法找到SQL_id，源头阻塞会话

SQL> select sql_text from v$session a,v$sqltext_with_newlines b where decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value and a.sid=1094;

SQL_TEXT
----------------------------------------------------------------
update yz.a set name='cc' where id=1

[分析上述SQL查询的列信息

SQL> select sql_hash_value,prev_hash_value,sql_hash_value,sql_id from v$session where sid=1094;

SQL_HASH_VALUE PREV_HASH_VALUE SQL_HASH_VALUE SQL_ID
-------------- --------------- -------------- --------------------
0 1358140474 0           

SQL_HASH_VALUE=> 会话正在执行的SQL_ID

PREV_HASH_VALUE=>会话上一条执行的SQL_ID

SQL_ID=> 正在执行的SQL_ID

]

三、KILL释放资源

SQL>alter system kill session 'sid,serial#' immediate;