MyException - 我的异常网
当前位置:我的异常网» 数据库 » 锁等候分析处理

锁等候分析处理

www.myexceptions.net  网友分享于:2013-04-10  浏览:3次
锁等待分析处理

1.       锁的作用及影响

  锁是Oracle管理共享数据库资源并发访问并防止并发数据库事务之间“相互干涉”的核心机制之一。

当应用系统复杂、业务量大时,经常会出现应用进程之前的锁等待现象,影响系统正常运行。当数据库出现锁等待的情况下快速定位阻塞进程,分析阻塞原因。

 

2.       定位锁类型

数据库的锁有多种类型,每种不同的类型对业务的影响是不一样的,大致可分为以下三类:

DML锁

DDL锁

内部锁与LATCH锁

 

下面给出每种不同类型的锁的定位分析过程,各种锁处理流程如下:

 

2.1、    DML锁


 
  
                                                  引用DSI405中的图

1、 TX锁与行锁

TX锁不是行锁,一个事务不管修改了多少行,都只会有一个TX锁。TX锁算是行锁的代表,行锁上发生了等待,会表现为TX锁的等待。行锁是属于事务的,事务开始,行锁产生,事务结束,行锁也被释放。

2、 行级锁对应用的影响比较小,一般只会影响部份业务或某个特殊的进程。

3、  模拟重现TX锁

(1)会话181执行如下操作:

gyj@OCM>select distinct sid from v$mystat;

 

       SID

----------

       181

gyj@OCM>update t1 set name ='gyj111' where id=1;

 

1 row updated.

修改t1表中的id=1这行记录,不提交。

 

(2)会话179执行如下操作:

gyj@OCM>select distinct sid from v$mystat;

 

       SID

----------

       179

gyj@OCM>update t1 set name ='gyj111' where id=1;

修改t1表id=1这行记录,这里被阻塞

 

(3)使用以下脚本查找数据库中的行级锁信息:

 

 

 

gyj@OCM> col username for a10

gyj@OCM> col program for a25

gyj@OCM> col sid for 9999

gyj@OCM> col SERIAL# for 9999

gyj@OCM> col BLOCKING_INSTANCE for 99

gyj@OCM>select sid,serial#,username,program,status,sql_id, blocking_instance,blocking_session

  2  from v$session where event='enq: TX - row lock contention';

  SID SERIAL# USERNAME   PROGRAM                   STATUS   SQL_ID        BLOCKING_INSTANCE BLOCKING_SESSION

----- ------- ---------- ------------------------- -------- ------------- ----------------- ----------------

  179     241 GYJ        sqlplus@ocm (TNS V1-V3)   ACTIVE   gxzu79ffmrp35                 1              181

 

(4)输出结果说明

SID: TX锁等待的SID号

SERIAL#: TX锁等待进程的序列号

USERNAME: 数据库用户

PROGRAM: 进程程序名

STATUS: 进程状态

SQL_ID: 正在执行的SQL语句

BLOCING_INSTANCE :阻塞进程所在的实例

BLOCING_SESSION: 阻塞进程SID号

可以得到锁等待进程信息,及阻塞者的SID (注意在RAC中通过blocking_session得到的SID需要减去1,才是实际的SID) 。

 

4、 查找阻塞者进程信息

gyj@OCM> col event for a30

gyj@OCM> select sid,serial#,username,program,status,sql_id,event from v$session where sid='&sid';

Enter value for sid: 181

old   1: select sid,serial#,username,program,status,sql_id,event from v$session where sid='&sid'

new   1: select sid,serial#,username,program,status,sql_id,event from v$session where sid='181'

SID SERIAL# USERNAME   PROGRAM                   STATUS   SQL_ID        EVENT

----- ------- ---------- ------------------------- -------- ------------- ------------------------------

181     236 GYJ        sqlplus@ocm (TNS V1-V3)   INACTIVE               SQL*Net message from client

输出结果说明

SID:阻塞进程的SID号

SERIAL#:阻塞进程的序列号

USERNAME:数据库用户

PROGRAM:进程程序名

STATUS:进程状态

SQL_ID:正在执行的SQL语句

EVENT:阻塞进程的等待事件

上述进程的的STATUS为INACTIVE,参考处理流程,直接跳到第7步执行。

 

5、  如果阻塞者进程为ACTIVE状态,查找阻塞者进程正在执行的SQL语句

gyj@OCM> select sql_text from v$sqltext where sql_id='gxzu79ffmrp35';

SQL_TEXT

----------------------------

update t1 setname='aaaaaaa' where id=1

 

6、  如果阻塞者进程为ACTIVE状态,查找阻塞者进程SQL语句执行计划

gyj@OCM> select * from table(dbms_xplan.display_cursor(‘gxzu79ffmrp35’));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

SQL_ID  f9mwduaxs47kk, child number 0

-------------------------------------

update t1 setname='aaaaaaa' where id=1

 

Plan hash value:2927627013

 

---------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------

|   0 | UPDATE STATEMENT   |     |       |       |    3 (100)|          |

|   1 | UPDATE            | T1   |       |      |            |          |

|*  2 |  TABLE ACCESS FULL| T1   |     1 |   65 |     3   (0)| 00:00:01 |

---------------------------------------

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   2 - filter("ID"=1)

 

Note

-----

   - dynamic sampling used for this statement(level=2)

 

 

23 rows selected.

 

7、 分析阻塞原因

TX锁阻塞原因一般有两种,一种是阻塞进程没有及时提交事务;另一种是阻塞进程SQL语句执行缓慢。对于第一种情况可找到相关进程结束进程事务,释放锁资源。第二种情况通过分析SQL语句执行计划,找到SQL执行缓慢的原因,提供优化建议,要求开发人员优化SQL语句。

8、 若情况紧急,可与项目经理和开发人员确认后终止阻塞者进程,释放锁资源。

9、 TM锁这里就不详细说了网上资料很多。

 

2.2、    DDL锁



                                                         引用DSI405中的图

 

1、 对象锁等待,一般发生在重编译存储过程等对象维护时,相关对象的library cache pin等待。发生该种锁等待时,长时间无法完成存储过程编译。

2、 使用下面的脚本查到对象锁等待的进程信息

selectsid,serial#,username,program,status,sql_id from v$session where event like'library cache%';

输出结果如下:

       SID   SERIAL# USERNAME                      PROGRAM                                          STATUS   SQL_ID       EVENT

---------- ---------------------------------------- -------------------- -----------------------------------------

      6577        456  GYJ                        sqlplus@ocm(TNS V1-V3)                 INACTIVE 1h3aq2wzhn5n6 SQL*Net message from client

输出结果说明

SID:阻塞进程的SID号

SERIAL#:阻塞进程的序列号

USERNAME:数据库用户

PROGRAM:进程程序名

STATUS:进程状态

SQL_ID:正在执行的SQL语句

 

3、 使用下面语句查找阻塞进程信息

select Distinct /*+ ordered */ w1.sid waiting_session,

h1.sid holding_session,

w.kgllktype lock_or_pin,

od.to_owner object_owner,

od.to_name object_name,

oc.Type,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested,

xw.KGLNAOBJ wait_sql,xh.KGLNAOBJ hold_sql

from dba_kgllock w, dba_kgllock h, v$session w1,

v$session h1,v$object_dependency od,V$DB_OBJECT_CACHE oc,x$kgllkxw,x$kgllk xh

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr

And od.to_address = w.kgllkhdl

And od.to_name=oc.Name

And od.to_owner=oc.owner

And w1.sid=xw.KGLLKSNM

And h1.sid=xh.KGLLKSNM

And (w1.SQL_ADDRESS=xw.KGLHDPAR And w1.SQL_HASH_VALUE=xw.KGLNAHSH)

And (h1.SQL_ADDRESS=xh.KGLHDPAR And h1.SQL_HASH_VALUE=xh.KGLNAHSH) 

         

         输出结果如下:

WAITING_SESSION HOLDING_SESSION LOCK_OR_PIN OBJECT_OWNER OBJECT_NAME TYPE MODE_HELD MODE_REQUESTED WAIT_SQL HOLD_SQL
20 45 Lock SUK P_SLEEP PROCEDURE Exclusive Exclusive grant execute on p_sleep to system grant execute on p_sleep to system

输出结果说明:

WAITING_SESSION:被阻塞进程号

HOLDING_SESSION:阻塞进程号

LOCK_OR_PIN:锁类型

OBJECT_OWNER:被锁对象属主

OBJECT_NAME:被锁对象名

TYPE:锁定类别

MODE_HELD:阻塞模式

MODE_REQUESTED:请求模式

WAIT_SQL:被阻塞进程正在执行的SQL

HOLD_SQL:阻塞进程正在执行的SQL

4、 查找阻塞者进程信息

select sid,serial#,username,program,status,sql_id,eventfrom v$session where sid=’&sid’;

5、  查找阻塞者进程正在执行的SQL语句

select sql_text from v$sqltextwhere sql_id=’&sql_id’ order by piece;

6、 查找阻塞者进程SQL语句执行计划

select * fromtable(dbms_xplan.display_cursor(‘&sql_id’));

7、 分析阻塞原因

对象锁阻塞一般在使用PL/SQL DEV等工具进程相关对象操作时容易引发该种锁等待。正常的业务事务不会导致该种等待事件发生。

8、 若情况紧急,可与项目经理和开发人员确认后终止阻塞者进程,释放锁资源。

 

2.3、    LATCH锁



                                                             引用DSI405中的图

 

1、  数据库级别的LATCH锁,往往是由于性能不良的应用程序进程,长时间持有相关LATCH不释放引起。引如热点块问题,数据库运行异常缓慢,数据库HANG住等问题。

2、 对于热点块问题,使用下面语句查找热点块竞争的进程信息,并将相关信息提交开发人员,要求分析避免数据的热点访问
selectsid,serial#,username,program,status,sql_id from v$session where event like'%cache buffers chains%';

输出结果如下所示:

SID  SERIAL# USERNAME   PROGRAM           STATUS      SQL_ID     

3054    41415 GYJ   sqlplus@ocm (TNS V1-V3) ACTIVE  1h3aq2wzhn5n6 

7029    51613 GYJ   sqlplus@ocm (TNS V1-V3) ACTIVE  1h3aq2wzhn5n6 

3064    16713 GYJ   sqlplus@ocm (TNS V1-V3) ACTIVE  1h3aq2wzhn5n6 

6089    30813 GYJ   sqlplus@ocm (TNS V1-V3) ACTIVE  1h3aq2wzhn5n6 

3055    50213 GYJ   sqlplus@ocm (TNS V1-V3) ACTIVE  1h3aq2wzhn5n6 

输出结果说明:

SID:阻塞进程的SID号

SERIAL#:阻塞进程的序列号

USERNAME:数据库用户

PROGRAM:进程程序名

STATUS:进程状态

SQL_ID:正在执行的SQL语句

 

3、  检查数据库latch锁等待进程数,如果数据超过50个,并不断增加,则可能导致数据库异常缓慢甚至hang住。用下面语句检查latch等待进程数。

sys@OCM>select sid,serial#,username,program,status,sql_id from v$session where eventlike '%latch%';

 

       SID   SERIAL# USERNAME                      PROGRAM                                          STATUS   SQL_ID

-------------------- ------------------------------ -------------------- -------------

       181          2 GYJ                            sqlplus@ocm (TNSV1-V3)                          ACTIVE   1h3aq2wzhn5n6

输出结果说明:

SID:阻塞进程的SID号

SERIAL#:阻塞进程的序列号

USERNAME:数据库用户

PROGRAM:进程程序名

STATUS:进程状态

SQL_ID:正在执行的SQL语句

 

4、  取得数据库hang analyze trace

sqlplus'/as sysdba'

oradebugsetmypid

oradebugsetinst all;

oradebug-g def hanganalyze 3

5、  分析数据库hang analyze trace 信息,找出阻塞进程的源头

6、  如果数据库还能查v$session进程信息,则查找出源头进程的相关信息及SQL语句。

selectb.spid,a.sid,a.serial#,a.program,a.machine,a.sql_id,a.event,a.PREV_SQL_ID fromv$session a,v$process b   wherea.paddr=b.addr and a.sid=&sid;

7、  用下面的方法对源头进程做DUMP。

$sqlplus '/ as sysdba'

SQL>oradebug setospid xxx (操作系统进程ID)

SQL>oradebug unlimit

SQL>oradebug dump processstate 10

SQL>exit

DUMP出来的文件在USER_DUMP_DEST所指向的目录,将相关文件取出,并提交ORACLE分析。

8、  与项目经理和开发人员确认后,中止源头进程。数据库可恢复正常。

9、  分析源头进程信息,获得阻塞原因,如SQL语句执行缓慢,进程未及时结束事务等。并提供优化建议,提给开发人员改进。






**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name:    guoyJoe

QQ:      252803295

Email:    oracledba_cn@hotmail.com

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:    http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!

答案在:http://blog.csdn.net/guoyjoe/article/details/8624392

DSI&Core Search(QQ群):127149411


文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有