鎖等待分析處理

鎖的作用及影響

　　鎖是Oracle管理共享資料庫資源并發通路并防止并發資料庫事務之間“互相幹涉”的核心機制之一。

當應用系統複雜、業務量大時，經常會出現應用程序之前的鎖等待現象，影響系統正常運作。當資料庫出現鎖等待的情況下快速定位阻塞程序，分析阻塞原因。

定位鎖類型

資料庫的鎖有多種類型，每種不同的類型對業務的影響是不一樣的，大緻可分為以下三類：

DML鎖

DDL鎖

内部鎖與LATCH鎖

下面給出每種不同類型的鎖的定位分析過程，各種鎖處理流程如下：

2.1、 DML鎖

1、 TX鎖與行鎖

TX鎖不是行鎖，一個事務不管修改了多少行，都隻會有一個TX鎖。TＸ鎖算是行鎖的代表，行鎖上發生了等待，會表現為TX鎖的等待。行鎖是屬于事務的，事務開始，行鎖産生，事務結束，行鎖也被釋放。

2、行級鎖對應用的影響比較小，一般隻會影響部份業務或某個特殊的程序。

3、模拟重制TX鎖

（1）會話181執行如下操作：

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

SID

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

1 row updated.

修改t1表中的id=1這行記錄，不送出。

（2）會話179執行如下操作：

SID

修改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 a30gyj@OCM> select sid,serial#,username,program,status,sql_id,event from v$session where sid='&sid';Enter value for sid: 181old 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

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鎖這裡就不詳細說了網上資料很多。

２.２、 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号

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

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’));

對象鎖阻塞一般在使用PL/SQL DEV等工具程序相關對象操作時容易引發該種鎖等待。正常的業務事務不會導緻該種等待事件發生。

２.３、 LATCH鎖

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

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

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語句執行緩慢，程序未及時結束事務等。并提供優化建議，提給開發人員改進。

鎖等待分析處理

SQL_TEXT

PLAN_TABLE_OUTPUT

SQL_ID f9mwduaxs47kk, child number 0

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

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

PredicateInformation (identified by operation id):

Note

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List