歡迎光臨
每天分享高質量文章

錯過血虧!一文搞懂Oracle鎖相關檢視及相關操作

 

本文主要研究鎖的相關檢視,以及鎖的相關操作,透過檢視查鎖的問題。

 

一、v$transaction檢視


 

第一個檢視是v$transaction,就是Oracle資料庫所有活動的事務數,所有活動的事務每一個活動的事務在這裡有一行。

 

  • v$transaction
  • XIDUSN表示當前事務使用的回滾段的編號
  • XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號(也可以叫做槽號)
  • XIDSQN說明序列號
  • STATUS說明該事務是否為活動的

 

這是v$transaction檢視的結構,這裡做一個實驗

 

在一個hr使用者的sqlplus中執行

SQL> delete from employees where rownum=1;
1 row deleted.

 

我刪一行,開始一個事務,以管理員身份執行,去執行一下

select xidusn,xidslot,xidsqn,status from v$transaction;

 

看看有幾個事務

 

結果:

 

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;    XIDUSN    XIDSLOT     XIDSQN STATUS---------- ---------- ---------- ----------------         4         43        216 ACTIVE

 

一個事務在這裡面有一行

XIDUSN是事務使用的回滾段的編號XIDSLOT是哪個槽位數XIDSQN是改寫多少次這三個唯一的標示一個事務的編號

 

STATUS是當前事務的狀態,這個事務為ACTIVE;這是v$transaction,所有的活動事務裡面都有

 

二、v$lock檢視


 

v$lock    記錄了session已經獲得的鎖定以及正在請求的鎖定的資訊    SID說明session的ID號    TYPE說明鎖的型別,主要關註TX和TM    LMODE說明已經獲得的鎖定的樣式,以數字編碼表示    REQUEST說明正在請求的鎖定的樣式,以數字編碼表示    BLOCK說明是否阻止了其他使用者獲得鎖定,大於0說明是,等於0說明否

 

v$lock這裡面,記錄了session已經獲得的鎖定以及正在請求的鎖定的資訊,就是每個會話,它已經獲取的鎖和正在申請的鎖它都會列出來

 

上面執行了

delete from employees where rownum=1;

 

一個事務開始以後至少產生幾個鎖,第一個行上加鎖了,行上的鎖你是看不見的,因為它在行上,但是我們開始一個事務有一個事務鎖,同時在表上應該加了個RX鎖,應該這時候有兩個鎖,一個TX鎖事務鎖,一個是TM級別上的表級的RX鎖。

 

使用陳述句

 

select sid,type,id1,id2,    decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,    decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block    from v$lock    where sid=129;

 

這裡把它取名為(#A)陳述句,最後where條件有sid,sid是會話的編號

 

先查一下我們會話的編號是多少,在我的實驗環境下,在hr使用者的sqlplus中

SQL> select sid from v$mystat where rownum=1;select sid from v$mystat where rownum=1                *ERROR at line 1:ORA-00942: table or view does not exist

 

hr使用者無權訪問v$mystat,所以換個方法,使用下麵的命令得到hr使用者當前的sid

 

SQL> select userenv('sid') from dual;
USERENV('SID')--------------           132

 

得到SID是132

 

然後用132替換前面(#A)陳述句where條件下的sid的值,然後在管理員使用者下查一下,因為hr使用者依然無法訪問v$lock。

 

執行結果

 

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block  2    3        from v$lock  4     where sid=132;
       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       132 TM      51852          0 Row share           None                         0       132 TM      51855          0 Row share           None                         0       132 TM      51857          0 Row Exclusive       None                         0       132 TM      51864          0 Row share           None                         0       132 TM      51871          0 Row share           None                         0       132 TM      51889          0 Row share           None                         0       132 TM      51894          0 Row share           None                         0       132 TM      51902          0 Row share           None                         0       132 TX     262187        216 Exclusive           None                         0
9 rows selected.

 

我們看一下132這個會話在很多的表上產生了TM鎖,132這個會話至少底下產生了一個TX鎖,同時132產生了TM鎖,LOCK_MODE中是Row share說明是RS鎖,是select for update產生的鎖;132這個會話產生的TM鎖的ID1列的ID數,這個TM在某個表上產生的鎖,ID1就是這個表的編號

 

有一個是51902,我們可以根據51902查出來

select object_name from dba_objects where object_id=51902;

 

查出51902是哪個表

 

執行結果

SQL> select object_name from dba_objects where object_id=51902;
OBJECT_NAME-------------------------------------------------------------------------------------------------------PRODUCT_INFORMATION

 

 

物件編號51902是PRODUCT_INFORMATION表,說明我們找錯了,這個表上加的鎖是Row share型別的鎖刪除操作的表應該產生Row Exclusive型別的鎖,前面delete陳述句刪除EMPLOYEES表中行時牽涉到了PRODUCT_INFORMATION這個表,是主外來鍵約束關係的原因在PRODUCT_INFORMATION表產生了RS鎖。

 

再看剛才的結果

132 TM      51857          0 Row Exclusive       None                         0

 

鎖應該是RX鎖,TM級別的RX鎖,是51857

 

把陳述句改為

select object_name from dba_objects where object_id=51857;

 

改一下,執行一下,看一下

 

SQL> select object_name from dba_objects where object_id=51857;
OBJECT_NAME----------------------------------------------------------------------------------------------------EMPLOYEES

 

結果是EMPLOYEES,是對的。所以這個TM這個表級鎖在哪個表上,根據ID1對應的ID可以找出來;另外結果中TX所在的行,有ID1對應的ID和ID2列對應的ID,ID1和ID2這兩個數字標示著這個事務用的那個回滾段、事務表裡面的槽位號還有改寫次數,我們可以透過一個sql陳述句查出來

 

將ID1拆解

 

select trunc(393249/power(2,16)) as undo_blk#,bitand(393249,to_number('ffff','xxxx')) + 0 as slot#from dual;

 

 

剛才delete陳述句產生的TX鎖是這一行

132 TX 262187 216 Exclusive None 0

 

ID1列的值262187替換上面的陳述句中的值393249

得到

 

select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#from dual;

 

執行一下

 

SQL> select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number('ffff','xxxx')) + 0 as slot#        from dual;  2
 UNDO_BLK#      SLOT#---------- ----------         4         43

 

看結果是4號回滾段,回滾段事務表中槽位號SLOT#是43

 

和以前查詢結果是一樣的

 

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN STATUS---------- ---------- ---------- ----------------         4         43        216 ACTIVE

 

 

剛才這個陳述句XIDSQN的值216

 

(#A)陳述句結果行

 

132 TX     262187        216 Exclusive           None                         0

直接有了

 

(#A)陳述句結果中ID2是改寫次數

 

透過這個sql陳述句

 

select trunc(262187/power(2,16)) as undo_blk#,bitand(262187,to_number(‘ffff’,’xxxx’)) + 0 as slot#from dual; 2

 

找出來用的哪個回滾段、槽位號是多少、改寫次數這三個資訊

 

也就是(#A)陳述句結果中TX這一行,ID1列和ID2列可以找到哪個事務

 

行是TM鎖

132 TM 51857 0 Row Exclusive None 0

 

ID1對應的編號51857是物件的編號,這是v$lock

 

Oracle中的表級鎖有

 

鎖定樣式   鎖定簡稱   編碼數值Row Exclusive   RX   3Row Shared   RS   2Share   S   4Exclusive   X   6Share Row Exclusive   SRX   5NULL   N/A   0或者1

 

這是鎖的一些編號,v$lock檢視裡面是用編號給列出來了,如編號3對應Row Exclusive鎖樣式,並且v$lock持有鎖和申請鎖的都列出來了,我們查詢以後得出的結論,SID為132的LOCK_MODE它是持有鎖的樣式。

 

(#A)結果中SID為132的行有9行,它是持有這麼多的鎖,REQUEST_MODE都是None,請求這塊是none

 

我們看(#A)結果中這些列,SID是session id,Type是型別,有表級鎖TM和事務鎖TX,對於ID,對TM來講ID1是哪個物件,對於TX來講ID1和ID2分別對應哪個事務。

 

LOCK_MODE是SID會話持有的鎖,它持有這個鎖,REQUEST_MODE這個是請求什麼鎖,這裡我請求都是None,沒有請求任何鎖,同時,BLOCK這個數字是0或者1,其中:這個0表示,比如說SID為132的會話持有LOCK_MODE為Row share的鎖,這個鎖並沒有把其它的別人鎖住,BLOCK就為0;BLOCK如果是1的話,132持有這個鎖同時還鎖住了別人到底鎖住了誰可以去查。

 

BLOCK是這個意思,這裡結果中block都是0,也就是對132來講,我持有這麼多的鎖,但是沒有鎖其它的任何人,也就是132持有的鎖沒有對別人造成影響。

 

三、v$enqueue_lock檢視


 

v$enqueue_lock該檢視中包含的欄位以及欄位含義與v$lock中的欄位一模一樣。只不過該檢視中只顯示那些申請鎖定,但是無法獲得鎖定的session資訊。其中的記錄按照申請鎖定的時間先後順序排列,先申請鎖定的session排在前面,排在前面的session將會先獲得鎖定。

 

我們接著看v$enqueue_lock,vlock是把會話持有的鎖和請求的鎖全給列出來了,v$enqueue_lock只是把請求鎖的都列出來了,v$enqueue_lock只是把哪些會話它正在請求鎖,它把請求的列出來了,它持有鎖它沒列;因為對我們來講,有時候我們只關心誰在請求鎖,因為請求鎖就有可能被鎖住,但有時候我們並不關心持有鎖

 

四、v$locked_object檢視


 

  • v$locked_object記錄了當前已經被鎖定的物件的資訊
  • XIDUSN表示當前事務使用的回滾段的編號
  • XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號
  • XIDSQN說明序列號
  • OBJECT_ID說明當前被鎖定的物件的ID號,可以根據該ID號到dba_objects裡查詢被鎖定的物件名稱
  • LOCKED_MODE說明鎖定樣式的數字編碼
  • v$locked_object記錄了當前已經被鎖定的物件的資訊,哪些物件被鎖定了
  • XIDUSN、XIDSLOT、XIDSQN是鎖這些物件的事務資訊
  • OBJECT_ID是哪個物件被鎖住了
  • LOCKED_MODE是鎖的樣式是什麼,用什麼方式鎖了,比如某個表被鎖住的話這裡面可以查出來

 

五、v$session檢視


 

  • v$session記錄了當前session的相關資訊
  • SID表示session的編號
  • SERIAL#表示序列號
  • SID和SERIAL#可以認為是v$session的主鍵,它們共同唯一標識一個session

 

記錄的是會話資訊,透過SID和SERIAL#,它倆可以唯一的標示一個會話

 

六、選擇hr使用者做實驗


 

1)hr使用者

 

在sqldeveloper裡面開的會話有的是用hr使用者登陸的,sqldeveloper開啟後,在 連線 設定卡部分,選擇其中的一個連線,右鍵點選連線名,再點選右鍵選單中的屬性,開啟 新建/選擇資料庫連線 設定卡,將其中的 使用者名稱和口令 改成你需要的使用者,我們使用hr做的測試,這裡用hr,這個sqldeveloper就是使用hr使用者,也可以在sqldeveloper中使用sys使用者,這裡使用的sqlplus裡面都是用hr使用者登的。

 

hr使用者它預設不能訪問v$mystat,這個檢視要經常被使用,可以使用下麵的命令:

grant select on v_$mystat to hr;

 

以管理員身份執行,給hr使用者授權,在sys使用者裡面執行一下

 

SQL> grant select on v_$mystat to hr;
Grant succeeded.

 

執行以後這個hr使用者就可以訪問v$mystat了

 

我們看一下比如這個

 

select sid from v$mystat where rownum=1;

 

看看會話的當前的SID是多少,如果grant沒有執行的話,hr使用者使用這個陳述句查詢它會報錯的,它會報沒有這個物件,去grant一下以後它就ok了,所以你需要用sys使用者grant一下

 

在sqldeveloper中,一個hr使用者建立的連線傳回了一個結果

       SID       134

 

當前的SID是134,我們看看第二個hr使用者建立的會話    

SID131

 

 

執行時傳回結果慢,在sqldeveloper執行慢主要和sqldeveloper的原理有關係,當一個會話長時間不用的時候它會關掉,sqldeveloper確實非常佔資源,我們現在開了三個所以非常的慢了。

 

在sqlplus中也可以查,這個比較的快

 

SQL> select sid from v$mystat where rownum=1;
       SID----------       139

 

這個hr使用者會話的SID是139,在另一個sqlplus中查

 

SQL> select sid from v$mystat where rownum=1;
       SID----------       145

 

這個hr使用者會話的SID是145,簡單記錄一下134,131,第三個sqldeveloper不用,第四個是sqlplus是139,第五個sqlplus是145,hr使用者一共建了了四個會話,SID分別是134,131,139,145

 

最終放棄了使用sqldeveloper做hr使用者的實驗,後面的實驗只使用了前面hr會話中的139和145的兩個會話

 

2)v_$mystat和v$mystat

 

可能大家註意到了,grant陳述句中使用的v_$mystat和hr使用者訪問的v$mystat不一樣,這裡補充說一下 v$mystat 和 v_$mystat 的區別。

 

初始狀態下hr使用者訪問v$mystat時

 

SQL> select sid from v$mystat where rownum=1;select sid from v$mystat where rownum=1                *ERROR at line 1:ORA-00942: table or view does not exist

 

提示訪問的表或者檢視不存在,當sys使用者給hr使用者授權後

grant select on v_$mystat to hr;

 

hr使用者就可以訪問了,大家註意到上面兩個陳述句中執行的物件並不一樣,要訪問的是v$mystat,而授權的是v_$mystat,如果我們直接給v$mystat授權

 

SQL> grant select on v$mystat to hr;grant select on v$mystat to hr                *ERROR at line 1:ORA-02030: can only select from fixed tables/views

 

說明這裡授權的v$mystat不是固有檢視,不能直接授權,實際這裡hr訪問的v$mystat是一個同義詞,sys不能給同義詞授權,只能授權給固定的表或檢視,同義詞在Oracle中可以理解為一個物件的別名,有私有和共用之分,每個使用者都可以給自己的物件建立自己的同義詞,這樣建立的同義詞只能自己使用。

 

建立私有同義詞語法:

Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;

 

預設只有系統管理員可以建立共用同義詞,共用同義詞屬於Oracle的public,public擁有了的許可權,Oracle所有的使用者都自動擁有了並可以使用

 

建立公有同義詞語法:

Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;

 

刪除同義詞的語法:

drop [public] synonym 同義詞名稱;

 

私有同義詞不能和自己已有的物件同名,公用同義詞可以和建立者已有的物件同名,當一個使用者的一個物件和公有同義詞同名時,使用時自己的物件優先;私有同義詞和共用同義詞可以同名,使用時私有同義詞優先。

 

所以這幾種物件如果有同名,陳述句中的使用順序是:先使用自己的固有物件或私有同義詞,最後使用公用同義詞。

 

查詢V$MYSTAT的說明:

 

SQL> select * from dict where table_name='V$MYSTAT';
TABLE_NAME------------------------------COMMENTS------------------------------ V$MYSTATSynonym for V_$MYSTAT

 

V$MYSTAT是一個同義詞是V_$MYSTAT的同義詞,再在庫中的同義詞資料字典中查詢這個同義詞

SQL> select * from dba_synonyms where SYNONYM_NAME='V$MYSTAT';
OWNER      SYNONYM_NAME         TABLE_OWNE TABLE_NAME           DB_LINK---------- -------------------- ---------- -------------------- --------------------PUBLIC     V$MYSTAT             SYS        V_$MYSTAT

 

說明V$MYSTAT是SYS使用者的V_$MYSTAT檢視的共用同義詞,查詢V_$MYSTAT檢視的定義

 

SQL> select OWNER,VIEW_NAME,TEXT from dba_views where view_name = 'V_$MYSTAT';
OWNER      VIEW_NAME                      TEXT---------- ------------------------------ --------------------------------------------------------------------------------SYS        V_$MYSTAT                      select "SID","STATISTIC#","VALUE" from v$mystat

 

TEXT欄位是這個檢視的定義

selectSID”,”STATISTIC#”,”VALUE” from v$mystat

 

也可以透過dbms_metadata.get_ddl查詢V_$MYSTAT檢視的定義,dbms_metadata.get_ddl傳回的是long型別的結果,long型資料為可變長字串,最大長度限制是2GB,sqlplus處理long型資料非常困難,因為裡面存的資料一般都很長,sqlplus顯示時一般只能顯示出來一部分,所以想顯示完整的long型資料,要先給sqlplus環境設定LONG引數

 

SQL> SET LONG 9999SQL> select dbms_metadata.get_ddl('VIEW','V_$MYSTAT'from dual;
DBMS_METADATA.GET_DDL('VIEW','V_$MYSTAT')--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."V_$MYSTAT" ("SID", "STATISTIC#", "VALUE") AS  select "SID","STATISTIC#","VALUE" from v$mystat

 

 

結果和從dba_views得到的一樣,這裡又有一個v$mystat,前面的一個是同義詞,這一個看看檢視定義裡面有沒有,而檢視的定義在v$fixed_view_definition中有

 

這裡查詢V$MYSTAT的定義

 

SQL> select * from v$fixed_view_definition where VIEW_NAME = 'V$MYSTAT';
VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------V$MYSTATselect  SID , STATISTIC# , VALUE from GV$MYSTAT where inst_id = USERENV('Instance')

 

說明有一個固定檢視也叫V$MYSTAT它來源於GV$MYSTAT,經過查詢和分析,GV$MYSTAT同樣有一個同名的PUBLIC同義詞和一個固定檢視,而且也有一個關聯的GV_$MYSTAT檢視PUBLIC同義詞,V$MYSTAT來自於GV_$MYSTAT,而GV_$MYSTAT又產生自固定檢視GV$MYSTAT

 

再看一下固定檢視GV$MYSTAT的定義

SQL> select * from v$fixed_view_definition where VIEW_NAME = 'GV$MYSTAT';
VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------GV$MYSTATselect inst_id,ksusenum,ksusestn,ksusestv from x$ksumysta where bitand(ksspaflg,1)!=0 and bitand(ksuseflg,1)!=0 and ksusestn

 

說明固定檢視GV$MYSTAT來自於x$ksumysta,這裡x$ksumysta表中的資訊是Oracle實體當前會話的狀態資訊,可以從v$fixed_table繼續查到x$ksumysta和結果中出現的x$ksusgif的資訊

 

SQL> SELECT * FROM v$fixed_table WHERE NAME in ('X$KSUMYSTA','X$KSUSGIF');
NAME                            OBJECT_ID TYPE   TABLE_NUM------------------------------ ---------- ----- ----------X$KSUSGIF                      4294951930 TABLE         33X$KSUMYSTA                     4294951106 TABLE         35

 

這是固定表,不用再繼續找了,一般使用者使用的V$MYSTAT是一個共用同義詞,它的來源渠道追溯過程是:V$MYSTAT共用同義詞,來自於V_$MYSTAT檢視、V$MYSTAT固定檢視、GV$MYSTAT、固定表X$KSUMYSTA,所以V$MYSTAT最終來自於固定表X$KSUMYSTA。

 

前面查詢中用到的dict實際也是個共用同義詞,SYS使用者的DICTIONARY檢視有兩個共用同義詞DICT和DICTIONARY,我們使用了DICT同義詞,DICTIONARY是一個資料字典,官方描述是:DICTIONARY contains descriptions of data dictionary tables and views. 就是DICTIONARY是個資料字典,內容中包含Oracle系統中所有資料字典,包括所有資料字典表和資料字典檢視的名稱和說明。

 

3)Oracle中的PUBLIC角色

 

前面講了一個共用同義詞建立時,自動的屬於了PUBLIC,PUBLIC在Oracle中比較特殊,有很多人弄不清楚它到底是什麼型別的物件。

 

查詢PUBLIC在角色中有沒有定義:

 

SQL> select dbms_metadata.get_ddl('ROLE','PUBLIC') from dual;
DBMS_METADATA.GET_DDL('ROLE','PUBLIC')--------------------------------------------------------------------------------
   CREATE ROLE "PUBLIC"

 

 

查詢PUBLIC在使用者中有沒有定義:

 

SQL> select dbms_metadata.get_ddl('USER','PUBLIC') from dual;ERROR:ORA-31603: object "PUBLIC" of type USER not found in schema "SYS"ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105ORA-06512: at "SYS.DBMS_METADATA", line 2805ORA-06512: at "SYS.DBMS_METADATA", line 4333ORA-06512: at line 1
no rows selected

 

 

所以PUBLIC是一個角色,不是使用者,但是在DBA_ROLES查詢

SQL> select * from DBA_ROLES;
ROLE                           PASSWORD------------------------------ --------CONNECT                        NORESOURCE                       NODBA                            NOSELECT_CATALOG_ROLE            NOEXECUTE_CATALOG_ROLE           NODELETE_CATALOG_ROLE            NOEXP_FULL_DATABASE              NOIMP_FULL_DATABASE              NORECOVERY_CATALOG_OWNER         NOGATHER_SYSTEM_STATISTICS       NOLOGSTDBY_ADMINISTRATOR         NOAQ_ADMINISTRATOR_ROLE          NOAQ_USER_ROLE                   NOGLOBAL_AQ_USER_ROLE            GLOBALSCHEDULER_ADMIN                NOHS_ADMIN_ROLE                  NOAUTHENTICATEDUSER              NOOEM_ADVISOR                    NOOEM_MONITOR                    NOWM_ADMIN_ROLE                  NOJAVAUSERPRIV                   NOJAVAIDPRIV                     NOJAVASYSPRIV                    NOJAVADEBUGPRIV                  NOEJBCLIENT                      NOJAVA_ADMIN                     NOJAVA_DEPLOY                    NOCTXAPP                         NOXDBADMIN                       NOXDBWEBSERVICES                 NOOLAP_DBA                       NOOLAP_USER                      NOMGMT_USER                      NO
33 rows selected.

 

並沒有PUBLIC。

 

查詢DBA_ROLES的定義

SQL> select dbms_metadata.get_ddl('VIEW','DBA_ROLES','SYS') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_ROLES','SYS')--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_ROLES" ("ROLE", "PASSWORD_REQUIRED") AS  select name, decode(password, null, 'NO', 'EXTERNAL', 'EXTERNAL',                      'GLOBAL', 'GLOBAL', 'YES')from  user$where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')

 

結果中有這麼一句,name not in (‘PUBLIC’, ‘_NEXT_USER’),說明DBA_ROLES生成時過濾掉了PUBLIC

 

Oracle中的角色是一種許可權的集合,如常用的CONNECT連線角色,RESOURCE資源角色,DBA資料庫管理員角色是ORACLE系統的三個內建角色,可以把單個的許可權再賦予角色使角色的許可權增加

如:

SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';ROLE                           PRIVILEGE------------------------------ ----------------------------------------CONNECT                        CREATE SESSIONSQL> grant CREATE ANY VIEW to CONNECT;Grant succeeded.SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';ROLE                           PRIVILEGE------------------------------ ----------------------------------------CONNECT                        CREATE ANY VIEWCONNECT                        CREATE SESSION

 

也可以回收角色的許可權

 

SQL> revoke CREATE ANY VIEW from CONNECT;
Revoke succeeded.
SQL> select ROLE, PRIVILEGE from role_sys_privs where role='CONNECT';
ROLE                           PRIVILEGE------------------------------ ----------------------------------------CONNECT                        CREATE SESSION

 

 

但最終這些角色和單個的許可權是要被賦予使用者來起作用的

 

檢視使用者擁有的許可權

select grantee,privilege from dba_sys_privs where grantee=’HR’;

如結果:

SQL> select grantee,privilege from dba_sys_privs where grantee='HR';
GRANTEE                        PRIVILEGE------------------------------ ----------------------------------------HR                             CREATE VIEWHR                             UNLIMITED TABLESPACEHR                             CREATE DATABASE LINKHR                             CREATE SEQUENCEHR                             CREATE SESSIONHR                             ALTER SESSIONHR                             CREATE SYNONYM
7 rows selected.

 

使用授權陳述句時可以把單個許可權分別賦予單個使用者,也可以把許可權的集合角色授予一個使用者,我們可以把許可權賦予PUBLIC

 

SQL> grant CREATE ANY VIEW to PUBLIC;Grant succeeded.

 

同時也可以把PUBLIC賦予使用者

 

SQL> grant PUBLIC to HR;Grant succeeded.

 

 

如果把使用者賦予使用者是不允許的:

 

SQL> grant sys to HR;grant sys to HR      *ERROR at line 1:ORA-01919: role 'SYS' does not exist

 

 

進一步說明PUBLIC是個角色,再查HR使用者的許可權,HR的系統許可權

 

SQL> select * from dba_sys_privs where grantee = 'HR';GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---HR                             CREATE VIEW                              NOHR                             UNLIMITED TABLESPACE                     NOHR                             CREATE DATABASE LINK                     NOHR                             CREATE SEQUENCE                          NOHR                             CREATE SESSION                           NOHR                             ALTER SESSION                            NOHR                             CREATE SYNONYM                           NO7 rows selected.

 

 

HR的角色許可權

 

SQL> select * from dba_role_privs where grantee = 'HR';GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---HR                             RESOURCE                       NO  YESHR                             PUBLIC                         NO  YES

 

這時HR使用者我們看到在已經賦予的角色GRANTED_ROLE中有了PUBLIC角色許可權,這是我前面手動賦予了HR使用者的許可權,儘管它原來就有,但在這裡顯示出來了,而HR使用者初始的角色許可權是這樣的:

SQL> select * from dba_role_privs where grantee = 'HR';GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---HR                             RESOURCE                       NO  YES

 

 

這是HR角色許可權的預設狀態,所以可以確定PUBLIC是一個角色了,但這個角色比較特殊,在很多的表和檢視都給遮蔽掉了,但有的地方還是可以查到的

 

 

SQL> select * from dba_role_privs where grantee = 'PUBLIC';
GRANTEE                        GRANTED_ROLE                   ADM DEF------------------------------ ------------------------------ --- ---PUBLIC                         RESOURCE                       NO  YES
SQL> select * from dba_sys_privs where grantee = 'PUBLIC';
GRANTEE                        PRIVILEGE                                ADM------------------------------ ---------------------------------------- ---PUBLIC                         UNLIMITED TABLESPACE                     NOPUBLIC                         CREATE ANY VIEW                          NO

 

 

這兩個查詢列出了PUBLIC擁有的角色許可權和系統許可權,同樣也可以給PUBLIC新增新的許可權,CREATE ANY VIEW就是我自己給它新增的,public擁有的許可權,所有的使用者都自動的擁有了,也就是所有的使用者初始預設都擁有PUBLIC角色的許可權。

 

七、兩個事務間鎖爭用實體


 

1)兩個事務爭用鎖

 

我們執行一個update employees set last_name=last_name||’a’ where department_id=60;開始一個事務,開始實驗,在其中一個sqlplus中執行

 

SQL> update employees set last_name=last_name||'a' where department_id=60;5 rows updated.

 

之前提過,這個事務一旦開始以後伴隨著一堆的鎖,執行這個陳述句的SID是139

 

我們先查一下和事務相關的

select xidusn,xidslot,xidsqn,status from v$transaction;

 

剛才已經開始一個事務了,使用sys使用者看一下有多少事務,因為剛開始一個事務,它是active的沒有提交,它在v$transaction裡面一定會出現

 

SQL> select xidusn,xidslot,xidsqn,status from v$transaction;    XIDUSN    XIDSLOT     XIDSQN STATUS---------- ---------- ---------- ----------------         7         23        238 ACTIVE

 

 

這就是剛才我們的事務,然後我們可以去查一下,剛才在139開始的一個事務,sys使用下麵的陳述句查一下

 

查詢結果

 

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block  2    3        from v$lock        where sid=139;  4       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       139 TM      51857          0 Row Exclusive       None                         0       139 TX     458775        238 Exclusive           None                         0

 

 

從結果看139會話,產生了一個TM鎖和一個TX鎖。

 

TM的ID2總是0,ID1是代表著操作所在的表,TX鎖的ID1和ID2透過陳述句可以找到這個事務,從LOCK_MODE看出他們都持有鎖,REQUEST_MODE看出都沒有請求鎖,從BLOCK都是0看出持有的鎖都沒有阻塞別人,再另外開一個session

 

同樣的去執行

update employees set last_name=last_name||’b’ where department_id=60;

 

在SID為145的hr會話中執行,它需要的資源被鎖住

SQL> update employees set last_name=last_name||'b' where department_id=60;

 

這時的執行被卡住

 

我們再去查一下這裡是139和145,對(#A)陳述句稍作修改

 

得到陳述句

 

select sid,type,id1,id2,decode(lmode,0,’None’,1,’Null’,2,’Row share’,3,’Row Exclusive’,4,’Share’,5,’Share Row Exclusive’,6,’Exclusive’) lock_mode,decode(request,0,’None’,1,’Null’,2,’Row share’,3,’Row Exclusive’,4,’Share’,5,’Share Row Exclusive’,6,’Exclusive’) request_mode,blockfrom v$lockwhere sid in(139,145)order by sid;

 

查的是v$lock,看看這個鎖的狀況,好執行一下,結果

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,  2    3  decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,blockfrom v$lock  4    5  where sid in(139,145)  6  order by sid;       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       139 TM      51857          0 Row Exclusive       None                         0       139 TX     458775        238 Exclusive           None                         1       145 TM      51857          0 Row Exclusive       None                         0       145 TX     458775        238 None                Exclusive                    0

 

 

139和145都出現了,139的TM和TX鎖沒變,一開始執行的139,後面執行的145,139一開始執行的,持有TX和TM鎖,145的TM鎖LOCK_MODE為Row Exclusive,持有145和139的TM鎖的ID1相同就是物件還一樣,也就是說139和145都在這個51857物件上加了RX鎖;但是145的TX鎖行的REQUEST_MODE的值是Exclusive出現了Exclusive,也就是145被139這個事務鎖住了。

 

然後我們看139的TX這行,BLOCK的值是1說明阻塞了別人,阻塞了145,而145 TX的REQUEST_MODE是Exclusive,它正在請求Exclusive鎖,也就是被鎖住了

 

透過這個我們看到一些問題,但是我們知道這個鎖出現這個問

題,也不見得有問題,因為鎖住很正常,139一旦提交以後,145馬上就獲取到這個鎖了

 

2)關於等待鎖中的ID1和ID2

 

另外從結果我們可能發現一個問題

  139 TX     458775        238 Exclusive           None                         1  145 TX     458775        238 None                Exclusive                    0

 

 

我們看到139會話和145會話的TX鎖的ID1和ID2是相同的,這裡的145的鎖狀態的ID1和ID2並不是145會話的事務資訊,145會話的TX鎖的REQUEST_MODE為Exclusive說明它在請求一個鎖,這個例子中145自己本身的事務還沒有開始,這時查詢v$transaction並沒有145會話的事務。

 

TX鎖REQUEST_MODE為Exclusive時,這裡的ID1和ID2的值是被請求鎖的事務資訊,這裡在請求139會話的鎖,這裡ID1和ID2的值就是139會話的資訊

 

當145得到鎖以後,本例中這時145會話開始了一個事務,這裡的ID1和ID2 的值會自動改變為145事務的資訊,REQUEST_MODE為Exclusive的鎖ID1和ID2的資訊始終是被請求的持有鎖的事務的資訊,有多個事務等待同一個鎖,前一個持有鎖的事務釋放鎖後,一個新事務得到了這個鎖,這時佇列中的其它事務的Exclusive狀態的鎖資訊的ID1和ID2都變為了這個新持有鎖的事務的資訊。

 

如果145事務在請求鎖之前,145已經開始了一個事務,也就是它已經持有了事務鎖,這時的結果會把它本身的事務鎖也列出,並且ID1和ID2的值是145事務的資訊,同時也會列出它正在請求的鎖的資訊,這條資訊的ID1和ID2是被請求鎖的資訊

 

做出了下麵的一個例子的結果

 

       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       148 TX     262165        242 None                Exclusive                    0       148 TM      51857          0 Row Exclusive       None                         0       148 TX     524327        303 Exclusive           None                         0       150 TX     262165        242 Exclusive           None                         1       150 TM      51857          0 Row Exclusive       None                         0

 

148會話本身持有一個TX鎖

148 TX     524327        303 Exclusive           None                         0

 

這條資訊的ID1和ID2的資訊是它本身事務的資訊,148會話還請求一個TX鎖

148 TX     262165        242 None                Exclusive                    0

 

這條資訊的ID1和ID2的資訊是148正在請求的被請求鎖的事務的資訊,這裡正在請求150正在持有的鎖,所以這行的ID1和ID2列出了150事務的資訊

 

有一行

150 TX     262165        242 Exclusive           None                         1

 

這行的BLOCK為1,說明150事務它持有的鎖有別的事務正在等待,正好和148正在請求鎖對應,驗證了前面的分析

 

八、三個事務的鎖爭用


 

1)三個事務爭同一個鎖

 

我們再開啟一個會話,再新建一個會話,以hr使用者身份連線

 

先查一下SID

 

SQL> select sid from v$mystat where rownum=1;       SID----------       136

 

這個新的當前會話的SID是136,接著前面的實驗,也去做同樣的一個操作

update employees set last_name=last_name||’b’ where department_id=60;

 

它肯定也被鎖住

 

執行結果

 

SQL> update employees set last_name=last_name||'b' where department_id=60;

 

暫時無傳回值,也被鎖住,這裡是136,現在是139 145 和136操作,139應該把145和136鎖住了

 

再去查一個陳述句

 

select sid,type,    decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')    request_mode    from v$enqueue_lock    where sid in(145,136);

   

根據分析兩個被鎖住的是145和136,查的是v$enqueue_lock,這裡面它會只是把誰被鎖住了誰給列出來,請求鎖的被列出來,執行一下,這裡訪問的是v$enqueue_lock

 

執行結果

 

SQL> select sid,type,        decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')        request_mode        from v$enqueue_lock        where sid in(145,136);  2    3    4    5       SID TY REQUEST_MODE---------- -- -------------------       145 TX Exclusive       136 TX Exclusive

 

我們看145和136都在,v$enqueue_lock列出的都在請求鎖,145和136都在請求事務鎖,都被別人鎖住了,其實139鎖住了145 和 136,根據陳述句執行的順序,145是第一個被鎖住的,136是第二個被鎖住的

 

這裡面如果我139釋放了的話,139把145和136同時鎖住了,第一個獲得鎖的應該是145,再就是136,鎖是可以排隊的。

 

我們看一個TX鎖這個鎖是139的,這個鎖把145和136同時鎖住了,145和136會到139下麵去排隊,先是145,後面是136要過來排隊,139釋放以後,145第一個獲取,第一個獲得鎖,有可能獲得鎖以後145又把136鎖住了。

 

如果它們獲取一樣的資源,145把136鎖住了,如果說139釋放以後,145獲取的資源和136獲取的資源不一樣的話,這兩個可以同時獲取到鎖。透過這個我們可以看出並記住鎖是排隊的。

 

2)v$lock中BLOCK欄位的值

 

舉一個三個事務爭用相同鎖的例子,查詢v$lock檢視的結果

 

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,  2    3  decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,block  4  from v$lock  5  where sid in(132,135,139)  6  order by sid;
       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       132 TM      51857          0 Row Exclusive       None                         0       132 TX     655370        242 Exclusive           None                         1       135 TM      51857          0 Row Exclusive       None                         0       135 TX     655370        242 None                Exclusive                    0       139 TM      51857          0 Row Exclusive       None                         0       139 TX     655370        242 None                Exclusive                    06 rows selected.

 

 

這三個事務開始執行的順序是132,135,139,這時132事務的資訊是ID1:655370 ,ID2:242,135和139會話中的事務還沒有開始,執行rollback釋放第一個事務佔用的鎖,然後執行相同的陳述句

 

得到的結果是:

     SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       135 TM      51857          0 Row Exclusive       None                         0       135 TX     327683        338 Exclusive           None                         1       139 TM      51857          0 Row Exclusive       None                         0       139 TX     327683        338 None                Exclusive                    0

 

132事務釋放鎖後,第一個排隊的135得到了鎖,得到鎖後135會話中的事務就開始了,這時135會話中事務的資訊是ID1:327683,ID2:338,這時它鎖住了139,139還在等待,39會話中的事務仍然沒有開始

 

不管是1個事務還是2個事務在等待鎖,持有鎖的事務的資訊的BLOCK都為1,這個欄位並不是說明有多少個事務在等待鎖,只是說明有沒有事務在等待這個鎖

 

等待鎖的139會話

 

在第一次查詢時的結果

139 TX 655370 242 None Exclusive 0

 

在第二次查詢時的結果

139 TX 327683 338 None Exclusive 0

 

ID1和ID2的值變化了,但都是139它等待的當前正在持有這個鎖的事務的資訊

 

九、鎖的時間


 

我們找一個非常有意義的一個

 

select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,b.ctime as time_held,c.sid as waiter_sid,decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,c.ctime time_waited from   v$lock b, v$enqueue_lock c, v$session a where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1order by time_held, time_waited;

 

這個sql陳述句是我們用的最多的一個sql陳述句,它做一件什麼事情呢?

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,  2    3  b.ctime as time_held,c.sid as waiter_sid,  4  decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,  5  c.ctime time_waited  6  from   v$lock b, v$enqueue_lock c, v$session awhere  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1  7    8  order by time_held, time_waited;BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------        139       2746 HR                             TX Exclusive                 3909        136 Exclusive                  1790        139       2746 HR                             TX Exclusive                 3909        145 Exclusive                  2931

 

前兩個欄位,BLOCKER_SID為139和SERIAL#為2746標明一個會話,這個會話使用BLOCKER_USERNAME為hr使用者登陸的,它的TY是TX鎖,它持有TIME_HELD為3909釐秒。

 

第一行WAITER_SID為136事務,TIME_WAITED等待了1790這麼長時間,也就是說136目前在等待139,就是136被139鎖住了,139持有鎖的時間是TIME_HELD 3909這麼長了,WAITER_SID 136等待TIME_WAITED 1790這麼長了,有了時間了就能判斷這個鎖是不是有沒有問題。

 

還有一個136等待了1790這麼長的時間,145等待139等待了2931這麼長時間,就說明145比136等的時間長了,過了一段時間再執行一次上面的命令

 

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,  2    3  b.ctime as time_held,c.sid as waiter_sid,  4  decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,  5  c.ctime time_waitedfrom   v$lock b, v$enqueue_lock c, v$session a  6    7  where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1  8  order by time_held, time_waited;BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------        139       2746 HR                             TX Exclusive                 6334        136 Exclusive                  4215        139       2746 HR                             TX Exclusive                 6334        145 Exclusive                  5356

 

 

145等待的時間比136時間長,也就是從某種意義上來講145它排在136的前面,這個命令是有意義的

 

我們就看TIME_WAITED列,再看TIME_HELD,如果你持有時間太長了,也就是說明你這個事務遲遲不提交,就根據BLOCKER_SID和SERIAL#這裡是139和2746,就可以執行一個sql陳述句

 

可以用

alter system kill session139,2746’;

 

把它kill掉,執行這個命令,執行它以後,它就可以把139給kill掉,kill以後它就會自動回滾

 

系統管理員sys會話中做一下

SQL> alter system kill session '139,2746';System altered.

 

kill以後我們看,139已經kill掉了,這時我們看145

 

SQL> update employees set last_name=last_name||'b' where department_id=60;5 rows updated.

 

 

145的等待狀態解除了,update操作成功了,也就是145現在持有了鎖,136仍在等待,136還被鎖著得不到執行,因為145又把136鎖了

 

我們再去查

SQL> select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,b.ctime as time_held,c.sid as waiter_sid,  2    3    4  decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,  5  c.ctime time_waited  6  from   v$lock b, v$enqueue_lock c, v$session awhere  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and  b.type = 'TX' and  b.block   = 1  7    8  order by time_held, time_waited;BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED----------- ---------- ------------------------------ -- ------------------- ---------- ---------- ------------------- -----------        145       1015 HR                             TX Exclusive                  221        136 Exclusive                   221

 

現在是145持有鎖,但它阻塞了WAITER_SID 為136的會話,139會話就沒鎖了

 

我們把剛才的事務都回滾了,在139會話中執行

 

SQL> rollback;rollback*ERROR at line 1:ORA-00028: your session has been killed

 

看出session 139已經被kill了,再把145的會話回滾了

SQL> rollback;Rollback complete.

 

 

再把136的會話回滾了

 

SQL> update employees set last_name=last_name||'b' where department_id=60;5 rows updated.SQL> rollback;Rollback complete.

 

136會話在145會話回滾後得到了執行,最終它也得到了鎖

為了試驗把它也回滾了

 

十、一個事務多個TM鎖


 

一個事務修改多行,產生一個TX鎖,可以在多個表上產生多個TM鎖,一個事務只產生一個事務鎖TX鎖

 

我們在一個事務裡面多執行幾條sql陳述句

 

update employees set last_name=last_name||'a' where department_id=60;update departments set department_name='unknow' where department_id=10;update locations set city='unknown' where location_id=1100;

 

在一個hr會話

 

SQL> select sid from v$mystat where rownum=1;       SID----------       132

 

先執行一條update

 

SQL> update employees set last_name=last_name||'a' where department_id=60;5 rows updated.

 

更新了employees這個表

 

第二個陳述句,它接著更新departments

 

SQL> update departments set department_name='unknow' where department_id=10;1 row updated.

 

 

都是一個事務裡面的,下麵陳述句是更新locations

 

SQL> update locations set city='unknown' where location_id=1100;1 row updated.

 

 

更新了三個陳述句,然後我們再使用

 

select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,blockfrom v$lockwhere sid=132;

 

再去查132這個會話它持有鎖的情況,在sys使用者會話中執行結果

 

SQL> select sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,  2    3  decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block  4  from v$lock  5  where sid=132;
       SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK---------- -- ---------- ---------- ------------------- ------------------- ----------       132 TM      51857          0 Row Exclusive       None                         0       132 TM      51852          0 Row Exclusive       None                         0       132 TM      51847          0 Row Exclusive       None                         0       132 TX     589860        329 Exclusive           None                         0

 

 

一個會話的一個事務它修改了三個表,對三個表產生TM鎖,它產生了一個TX鎖,TX鎖就只有一個。

 

十一、transactions和dml_locks引數


 

再看一個sql陳述句

select name,value from v$parameter where name in(‘transactions’,’dml_locks’);

 

可以獲得的TX鎖定的總個數由初始化引數transactions決定,而可以獲得的TM鎖定的個數則由初始化引數dml_locks決定

 

transactions引數表示Oracle一個實體最多可有的事務數

dml_locks引數表示一個Oracle實體中最多可產生的TM鎖就是表級鎖的數量

 

對整個資料庫來講

它能獲得的TX鎖和TM鎖的總數由’transactions’和’dml_locks’它倆限制

 

如果這兩個引數過小的話

有可能影響併發的事務的數量以及訪問的表的數量

 

我們執行一下看有多大

 

SQL> select name,value from v$parameter where name in('transactions','dml_locks');NAME            VALUE--------------- ----------dml_locks       748transactions    187

 

這個有時候也會碰到一些問題,結果中一個dml_locks是748,一個transactions是187,對資料庫來講,同時可以有187個事務可以同時執行,而鎖的數量,同時修改的表可以有700多個。

 

一般的我們把這個都修改的抬高一些,比如把transactions修改為300,比如dml_locks我們修改成1500,可以給它增加,到底該不該增加,我們有一個查詢

select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" from v$resource_limit where resource_name in('transactions','dml_locks');

 

 

這個很有用,很有幫助,有v$resource_limit這個檢視,我們大家可以查一下,裡面有好多的資訊

 

我們先查這一個transactions和dml_locks引數

 

SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"from v$resource_limitwhere resource_name in('transactions','dml_locks');  2    3R_N                                   C_U        M_U I_U------------------------------ ---------- ---------- --------------------dml_locks                               3         48        748transactions                            2          9        187 

 

R_N這個列是資源名字,如dml_locks是資源名,C_U是current_utilization當前已經使用的數目,當前鎖定了3個表,M_U是max_utilization最大同時使用的數目,最大鎖過48個,I_U是initial_allocation初始可分配的數量,最大可分配的748

 

這是dml_locks,當前C_U是3個,最大可以是I_U是748,M_U為48是曾經達到的最大值是48,只要這個48沒達到748,說明我這個dml_locks沒出現過問題

 

那麼transactions,曾經最大是9個,最大可以是187,這都沒問題,v$resource_limit檢視我們查一下訪問一下,裡面有很多資源

SQL> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"from v$resource_limit;  2R_N                                   C_U        M_U I_U------------------------------ ---------- ---------- --------------------processes                              27         37        150sessions                               31         42        170enqueue_locks                          13         22       2300enqueue_resources                      17         38        968ges_procs                               0          0          0ges_ress                                0          0          0ges_locks                               0          0          0ges_cache_ress                          0          0          0ges_reg_msgs                            0          0          0ges_big_msgs                            0          0          0ges_rsv_msgs                            0          0          0gcs_resources                           0          0          0gcs_shadows                             0          0          0dml_locks                               3         48        748temporary_table_locks                   0          0  UNLIMITEDtransactions                            2          9        187branches                                0          0        187cmtcallbk                               0          2        187sort_segment_locks                      0          1  UNLIMITEDmax_rollback_segments                  11         11        187max_shared_servers                      1          1  UNLIMITEDparallel_max_servers                    0          2         4022 rows selected.

 

只要這個M_U的值沒有跟I_U的值相等,當然不可能超過,沒有跟它相等就說明我設定的引數都沒有問題,比如有人經常說這個processes、這個sessions是不是設小了,連不上可以看看sessions的M_U的值有沒有超過I_U的值,有沒有等於它,等於它說明可能就有問題

 

記住v$resource_limit這個很有意義,這裡講了和事務相關的一些操作

 

十二、死鎖


 

1)死鎖的發生

 

Oracle裡面還有一種鎖叫死鎖,Oracle一直這麼說,Oracle中只要產生死鎖一定是你的應用寫的有問題,碰到死鎖的時候Oracle自動會釋放,會殺掉一個事務,一個死鎖會產生一個trc檔案,我們來看什麼叫死鎖一個事務要修改一個資源,A事務修改了這個資源,B事務修改了另一個資源,A事務修改了一個資源以後在這個資源上加了鎖了,事務修改了另一個資源後也加了鎖。

A想持有B正在修改的這個資源,但已被B鎖住了A修改了一個資源但是它還想修改B正在修改的資源但已被B鎖住A被B鎖住了B修改了一個資源後又想去修改A正在修改的資源B被A鎖住了

 

產生死鎖,並且這個結它解不開,因為,這時只有A回滾了以後,B才能持有A現在擁有的資源

 

死鎖以後會有什麼現象呢?

 

我把原來實驗的會話都給它rollback,併在新會話中實驗,在session1裡面,我更新100

 

SQL> select sid from v$mystat where rownum=1;       SID----------       150       SQL> update employees set last_name=last_name||'a'
where employee_id=100;  21 row updated.

 

我把100給鎖住了

 

然後在 session2裡面呢?

 

SQL> select sid from v$mystat where rownum=1;       SID----------       148       SQL> update employees set last_name=last_name||'b'where employee_id=101;  21 row updated.

 

把101給鎖住了,A裡面把100鎖住了,B把101鎖住了,然後session1想去:

SQL> update employees set last_name=last_name||'c' where employee_id=101;

 

想去鎖B鎖住的資源,已被B鎖住了,這時A被B鎖住了,然後session2中:

SQL> update employees set last_name=last_name||'d' where employee_id=100;

 

也在等待鎖,B又被A鎖住了,形成一個死迴圈了,這時在A裡面出現

 

SQL> update employees set last_name=last_name||'c' where employee_id=101;update employees set last_name=last_name||'c' where employee_id=101         *ERROR at line 1:ORA-00060: deadlock detected while waiting for resource

 

 

這時候A會話馬上出一個問題,另外一個會話B中我們回車以後,這個A會話一下子檢測到死鎖,被回滾了,馬上被回滾了,這裡回滾的不是A會話中的整個事務,只是被回滾了一條陳述句,就是把造成死鎖的那條陳述句給回滾了,這個事務中前面其它陳述句沒有影響,並沒有回滾整個的事務

 

如果這時查詢A會話,查詢後可以得知這條陳述句前執行的陳述句仍然有效,也就是當死鎖發生的時候,Oracle馬上會檢測,同時將其中一個事務的一條造成死鎖的陳述句給自動回滾

 

這裡是回滾了第一個會話造成死鎖的陳述句,就是請求第二個會話佔有的鎖但是未得到鎖的陳述句,但這時第二個會話請求的鎖,第一個會話仍然佔有,既然如此,死迴圈被解除了,這樣在在造成死鎖的兩個會話中解除了死鎖。

 

我們執行rollback將其中一個會話全部回滾

SQL> rollback;Rollback complete.

 

並且這裡又把第一個會話中佔用鎖的陳述句回滾後

 

第二個會話中等待鎖的陳述句得到了執行

 

SQL> update employees set last_name=last_name||'d' where employee_id=100;1 row updated.

 

把第二個會話也回滾

 

SQL> rollback;Rollback complete.

 

當死迴圈發生的時候會做幾件事情,第一個Oracle自動的對死鎖自動的檢測,而且還能快速檢測,而且把其中一個事務給回滾,但只是回滾部分sql陳述句

 

2)死鎖的資訊

 

同時當死鎖發生的時候會出現一件很重要的事情,Oracle會記錄下死鎖的資訊

 

死鎖發生的時候

 

[oracle@redhat4 bdump]$ pwd/u01/app/oracle/admin/jiagulun/bdump

 

在這個目錄裡面

 

[oracle@redhat4 bdump]$ lsalert_jiagulun.log       jiagulun_lgwr_13577.trc  jiagulun_mmnl_6638.trcjiagulun_cjq0_13651.trc  jiagulun_lgwr_13643.trc  jiagulun_p000_6646.trcjiagulun_lgwr_13460.trc  jiagulun_lgwr_6626.trc   jiagulun_p001_6648.trc

 

有alert日誌alert_jiagulun.log,alert日誌是資料庫的總日誌,可以檢視這個alert日誌cat alert_jiagulun.log

 

執行結果

[oracle@redhat4 bdump]$ cat alert_jiagulun.logMon Apr 11 13:38:53 2016Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Shared memory segment for instance monitoring createdTue Nov 21 06:45:15 2017MMNL absent for 63369 secs; Foregrounds taking overMMNL absent for 63369 secs; Foregrounds taking overMMNL absent for 63369 secs; Foregrounds taking overTue Nov 21 08:14:17 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.Tue Nov 21 08:25:31 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

 

在alert日誌裡面會自動把死鎖資訊給列出來,說死鎖發生了

 

如:

 

Tue Nov 21 08:25:31 2017ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc.

 

在資訊裡面可以得到死鎖對應的trc檔案

/u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_21608.trc

 

以上就是講的Oracle的鎖的一些情況,分享至此,供大家參考學習,如有幫助,歡迎轉發+在看~


    已同步到看一看
    贊(0)

    分享創造快樂