HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

事务队列等待(Tx Enqueue)深入分析——记录锁

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2009-09-22 07:18:46

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

    在我之前的文章中,曾经分析过产生TX锁的几种情况。但是,在发现TX锁时,我们如何鉴别是哪一种情况导致的呢?当存在TX等待队列时,如何找到锁所在的对象呢?
 

记录锁

    这类锁是事务插入/删除/更新数据记录时加在记录的锁。对于插入操作,数据在未提交之前对其他事务是“不可见”的,因而不会导致TX等待。这一类的TX锁是比较容易鉴别的——只有这类锁的模式(mode)是6(即排它锁,exclusive)。通过v$lock很容易鉴定出来:

SQL代码
  1. HELLODBA.COM> create table tx_lock_tab (a number, b char(1), c varchar2(20));   
  2.   
  3. Table created.   
  4.   
  5. HELLODBA.COM> insert into tx_lock_tab (a, b, c) values (99, 'C''AOAKAPSOD');   
  6.   
  7. 1 row created.   
  8.   
  9. HELLODBA.COM> insert into tx_lock_tab (a, b, c) values (100, 'C''AOAKAPSOD');   
  10.   
  11. 1 row created.   
  12.   
  13. HELLODBA.COM> insert into tx_lock_tab (a, b, c) values (101, 'd''AOAKAPSOD');   
  14.   
  15. 1 row created.   
  16.   
  17. HELLODBA.COM> commit;   
  18.   
  19. Commit complete.   
  20.   
  21. session 1中:   
  22.   
  23. HELLODBA.COM> delete from tx_lock_tab where a=100;   
  24.   
  25. 1 row deleted.   
  26.   
  27. HELLODBA.COM> select * from v$lock where type='TX';   
  28.   
  29. ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK   
  30. -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------   
  31. 1E200F98 1E2010B4        135 TX       458781     104223          6          0         36          0  

    可以看到,Lock Mode为6.

等待队列分析

    当发现系统中由于此类锁导致的等待而致使会话hung住时,可以通过以下过程来找到导致阻塞的会话、语句和对象。
    续上,在Session 2中执行:

SQL代码
  1. HELLODBA.COM> delete from tx_lock_tab where a=100;  

    此时,锁等待队列形成。从V$ENQUEUE_LOCK中可以查询到这一锁队列:

SQL代码
  1. HELLODBA.COM> select * from V$ENQUEUE_LOCK where type='TX';   
  2.   
  3. ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK   
  4. -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------   
  5. 1EC34448 1EC3445C        127 TX     458781     104223          0          6        213          0  

    注意:V$ENQUEUE_LOCK的结构与V$LOCK结构很相似,但是,V$LOCK查询到的是被持有的锁及其SID,V$ENQUEUE_LOCK查询到的导致队列等待的锁以及请求锁的SID。
    同时,通过v$session_event可以查询到会话等待事件是"enq: TX - row lock contention":
 

SQL代码
  1. HELLODBA.COM> select s.sid, s.event   
  2.   2  from v$session_event s, v$enqueue_lock l   
  3.   3  where s.sid = l.sid   
  4.   4  and s.event like 'enq: TX%';   
  5.   
  6.        SID EVENT   
  7. ---------- -----------------------------------------------------   
  8.        127 enq: TX - row lock contention  

    这一类锁是代码逻辑造成的——第一个获取锁的事务没有提交或回滚,导致其他会话等待。因此,找到逻辑代码是解决此类锁等待的关键。对于等待事务,由于其语句被锁阻塞住,因此从V$SESSION中得到的SQL信息就是其当前正在执行的语句,也就是发生等待的语句:

SQL代码
  1. HELLODBA.COM> select w.waiting_session,   
  2.   2         s2.username waiting_user,   
  3.   3         q2.sql_text waiting_sql   
  4.   4    from dba_waiters w,   
  5.   5         v$session   s2,   
  6.   6         v$sqlarea  q2   
  7.   7   where w.waiting_session = s2.sid   
  8.   8     and s2.sql_address = q2.address;   
  9.   
  10. WAITING_SESSION WAITING_USER                   WAITING_SQL   
  11. --------------- ------------------------------ ---------------------------------------------   
  12.             127 DEMO                           delete from tx_lock_tab where a=100  

    对于持有锁的会话,由于其在申请到锁资源后还可能会执行其他语句,因此不能通过v$session直接查询到发生锁的语句,而需要通过v$open_cursor来找到该语句:

SQL代码
  1. HELLODBA.COM> select w.holding_session,   
  2.   2         s1.username holding_user,   
  3.   3         q1.sql_text holding_sql   
  4.   4    from dba_waiters w,   
  5.   5         v$session   s1,   
  6.   6         v$open_cursor  q1,   
  7.   7         v$locked_object l1,   
  8.   8         dba_objects o1   
  9.   9   where w.holding_session = s1.sid   
  10.  10     and s1.sid = q1.sid(+)   
  11.  11     and l1.session_id = s1.sid   
  12.  12     and l1.object_id = o1.object_id   
  13.  13     and (upper(q1.sql_text) like '%DELETE%' or upper(q1.sql_text) like '%UPDATE%')   
  14.  14     and upper(q1.sql_text) like '%'||o1.object_name||'%';   
  15.   
  16. HOLDING_SESSION HOLDING_USER                   HOLDING_SQL   
  17. --------------- ------------------------------ ------------------------------------------------------------   
  18.             129 DEMO                           delete from tx_lock_tab where a=100  

    这一查询结果可能会存在多条记录,那我们就需要结合等待会话的语句,从逻辑上分析是哪一条语句产生的锁。
    除了语句,我们还可以通过会话信息中找到发生等待的具体数据记录:
 

SQL代码
  1. HELLODBA.COM> select s.sid, o.object_name wait_object   
  2.   2         s.row_wait_obj#,   
  3.   3         s.row_wait_file#,   
  4.   4         s.row_wait_block#,   
  5.   5         s.row_wait_row#,   
  6.   6         dbms_rowid.rowid_create(1,   
  7.   7                                 s.row_wait_obj#,   
  8.   8                                 s.row_wait_file#,   
  9.   9                                 s.row_wait_block#,   
  10.  10                                 s.row_wait_row#) l_rowid   
  11.  11    from v$session s, v$enqueue_lock l, dba_objects o   
  12.  12   where s.sid = l.sid and s.row_wait_obj# = o.object_id(+)   
  13.  13     and s.sid = 127;   
  14.   
  15.        SID WAIT_OBJECT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# L_ROWID   
  16. ---------- ----------- ------------- -------------- --------------- ------------- ------------------   
  17.        127 TX_LOCK_TAB 198074        5              67366           1             AAAwW6AAFAAAQcmAAB  

    通过得到的rowid,可以查询到具体记录:

SQL代码
  1. HELLODBA.COM> select * from tx_lock_tab where rowid = chartorowid('AAAwW6AAFAAAQcmAAB');   
  2.   
  3.          A B C   
  4. ---------- - --------------------   
  5.        100 C AOAKAPSOD  

死锁分析

    对于此类锁所引发的死锁问题,通过Trace文件可以很快定位出来。首先,我们可以通过锁的请求、持有模式(x)可以知道这是行级锁:

SQL代码
  1.                        ---------Blocker(s)--------  ---------Waiter(s)---------   
  2. Resource Name          process session holds waits  process session holds waits   
  3. TX-000a005e-0016ce18       231     197     X            295     305           X   
  4. TX-000e001d-00496580       295     305     X            591     569           X   
  5. TX-00040016-001d820b       591     569     X            574     510           X   
  6. TX-00030002-001fadc2       574     510     X            231     197           X  

    同时,我们还可以找到导致死锁的语句、执行语句的用户、客户端及模块信息:

SQL代码
  1. Current SQL statement for this session:   
  2. UPDATE CSS_CARRIER SET REC_UPD_DT = TO_TIMESTAMP (:"SYS_B_0", :"SYS_B_1"WHERE (CARRIER_ID = :"SYS_B_2")   
  3. ...   
  4. Information on the OTHER waiting sessions:   
  5. Session 305:   
  6.   pid=295 serial=186 audsid=902537392 user: 33/CSSJAVA   
  7.   O/S info: user: oracle, term: unknown, ospid: , machine: as04.cargosmart.com   
  8.             program: JDBC Thin Client   
  9.   application name: JDBC Thin Client, hash value=0   
  10.   Current SQL Statement:   
  11.   UPDATE CSS_CARRIER SET REC_UPD_DT = TO_TIMESTAMP (:"SYS_B_0", :"SYS_B_1"WHERE (CARRIER_ID = :"SYS_B_2")   
  12. ...   
  13. End of information on OTHER waiting sessions.  

    此外,还能知道锁所在的对象和数据记录(ROWID):

SQL代码
  1. Rows waited on:   
  2. Session 305: obj - rowid = 00005B9A - AAAFuaABBAAAAaKAAI   
  3.   (dictionary objn - 23450, file - 65, block - 1674, slot - 8)   
  4. Session 569: obj - rowid = 00005B9A - AAAFuaABBAAAAaKABU   
  5.   (dictionary objn - 23450, file - 65, block - 1674, slot - 84)   
  6. Session 510: obj - rowid = 00005B9A - AAAFuaABBAAAAaKAAQ   
  7.   (dictionary objn - 23450, file - 65, block - 1674, slot - 16)   
  8. Session 197: obj - rowid = 00005B9A - AAAFuaABBAAAAaKAAF   
  9.   (dictionary objn - 23450, file - 65, block - 1674, slot - 5)  

    然后再通过以上语句及会话的其它信息,找到相应代码,结合数据对象及产生锁的记录分析逻辑过程,修正会导致死锁的代码。

解决方法

    要解决等待会话被“僵死”的问题,关键要看导致阻塞的会话正在做什么、或者正在等待什么,找到其事务长时间不提交的根本原因:

SQL代码
  1. select s.sid, s.event, s.wait_time, q.sql_text   
  2. from v$session s, v$sqlarea q   
  3. where s.sql_address = q.address(+)   
  4. and s.sql_hash_value = q.hash_value(+)   
  5. and s.sid = 129;  

    根本解决方法就是要调整应用逻辑,避免死锁。
    例如,我们有一个案例:一个应用是多线程服务的,当收到请求事件后,服务进程会打开一个游标,对游标中数据逐一进行业务处理、统一更新。由于不同进程打开游标的时间不同,游标查询语句获取的数据顺序也会不同,因此这个应用经常抛出死锁错误。我们给出的解决方案就是在游标查询语句中加入排序,使更新数据按某一特定顺序进行,从而避免死锁。

--- Fuyuncat TBC ---

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat