深入淺出oracle鎖

1、精品資料推薦深入淺出oracle鎖一原理篇深入淺出oracle鎖鎖阻塞的分析深入淺出oracle鎖一原理篇在現代的多用戶多任務系統(tǒng)中，必然會出現多個用戶同時訪問共享的某個對象，這個對象可能是表，行，或者內存結構，為了解決多個用戶并發(fā)性訪問帶來的數據的安全性,完整性及一致性問題，必須要有一種機制，來使對這些共享資源的并發(fā)性訪問串行化，oracle中的鎖就可以提供這樣的功能，當事務在對某個對象進行操作前，先向系統(tǒng)發(fā)出請求，對其加相應的鎖，加鎖后該事務就對該數據對象有了一定的控制權限，在該事務釋放鎖之前，其他的事務不能對此數據對象進行更新操作（可以做select動作，但select利用的是undo中

2、的前鏡像數據了）.Oracle鎖的分類Oracle鎖基本上可以分為二類a:共享鎖（sharelocks）也稱讀鎖，s鎖b：排它鎖（exclusivelocks）也稱寫鎖，x鎖在數據庫中有兩種基本的鎖類型:排它鎖EExclusiveLocks，即X鎖）和共享鎖（ShareLocks,即S鎖）。當數據對象被加上排它鎖時，其他的事務不能對它讀取和修改。加了共享鎖的數據對象可以被其他事務讀取，但不能修改。數據庫利用這兩種基本的鎖類型來對數據庫的事務進行并發(fā)控制。按鎖保護的內容分類oracle提供多粒度封鎖機制，按保護對象來分，據此又可以分為a：dml鎖，datalocks數據鎖，用來保護數據的完整性和

3、一致性b：ddl鎖，dictionarylocks字典鎖，用來保護數據對象的結構，如table,index的定義c：內部鎖和閂internallocksandlatchs用來保護數據庫內部結構，如sga內存結構dml鎖DML鎖主要包括TM鎖和TX鎖，其中TM鎖稱為表級鎖，TM鎖的種類有S,X,SR,SX,SRX五種，TX鎖稱為事務鎖或行級鎖。當Oracle執(zhí)行delete,update,insert,selectforupdateDML語句時，oracle首先自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，再自動申請TX類型的鎖，并將實際鎖定的數據行的鎖標志位（lb即lockbytes）

4、進行置位。在記錄被某一會話鎖定后，其他需要訪問被鎖定對象的會話會按先進先出的方式等待鎖的釋放，對于select操作而言，并不需要任何鎖，所以即使記錄被鎖定，select語句依然可以執(zhí)行，實際上，在此情況下，oracle是用到undo的內容進行一致性讀來實現的。在Oracle數據庫中，當一個事務首次發(fā)起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。在數據行上只有X鎖（排他鎖），就是說TX鎖只能是排他鎖，在記錄行上設置共享鎖沒有意義。當兩個或多個會話在表的同一條記錄上執(zhí)行DML語句時，第一個會話在該條記錄上加鎖，其他的會話處于等待狀態(tài)。當第一個會話提交后，TX鎖被釋放，其他會話才可

5、以加鎖。在數據表上，oracle默認是共享鎖，在執(zhí)行dml語句的時候，oracle會先申請對象上的共享鎖，防止其他會話在這個對象上做ddl語句，成功申請表上的共享鎖后，再在受影響的記錄上加排它所，防止其他會話對這些做修改動作。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標志，而只需檢查TM鎖模式的相容性即可，大大提高了系統(tǒng)的效率。TM鎖包括了SS、SX、S、X等多種模式，在數據庫中用06來表示。不同的SQL操作產生不同類型的TM鎖。如表1所示。和鎖相關的性能視圖介紹v$lockSID會話的sid,可以和v$session關聯TYPE區(qū)分該鎖保護對象的類型，如tm,tx,rt,mr等ID

6、1鎖表示1,詳細見下說明ID2鎖表示2,詳細見下說明LMODE鎖模式，見下面說明REQUEST申請的鎖模式，同lmodeCTIME已持有或者等待鎖的時間BLOCK是否阻塞其他會話鎖申請1:阻塞0:不阻塞LMODE取彳t0,1,2,3,4,5,6,數字越大鎖級別越高，影響的操作越多。1級鎖：Select,有時會在v$locked_object出現。2級鎖即RS鎖相應的sql有：Selectforupdate,LockxxxinRowSharemode,selectforupdate當對話使用forupdate子串打開一個游標時，所有返回集中的數據行都將處于行級（Row-X）獨占式鎖定，其他對象只

7、能查詢這些數據行，不能進行update、delete或selectforupdate操作。3級鎖即RX鎖相應的sql有：Insert,Update,Delete,LockxxxinRowExclusivemode,沒有commit之前插入同樣的一條記錄會沒有反應，因為后一個3的鎖會一直等待上一個3的鎖，我們必須釋放掉上一個才能繼續(xù)工作。4級鎖即S鎖相應的sql有：CreateIndex,LockxxxinSharemode5級鎖即SRX鎖相應的sql有：LockxxxinShareRowExclusivemode,當有主外鍵約束時update/delete.;可能會產生4,5的鎖。6級鎖即X鎖

8、相應的sql有：Altertable,Droptable,DropIndex,Truncatetable,LockxxxinExclusivemodeID1,ID2的取值含義根據type的取值而有所不同對于TM鎖ID1表示被鎖定表的object_id可以和dba_objects視圖關聯取得具體表信息，ID2值為0對于TX鎖ID1以十進制數值表示該事務所占用的回滾段號和事務槽slotnumber號，其組形式：0xRRRRSSSS,RRRR=RBS/UNDONUMBER,SSSS=SLOTNUMBERID2以十進制數值表示環(huán)繞wrap的次數，即事務槽被重用的次數v$locked_objectXID

9、USNundosegmentnumber,可以和v$transaction關聯XIDSLOTundoslotnumberXIDSQN序列號OBJECT_ID被鎖定又t象的object'd,可以和dba_objects關聯SESSION_ID持有該鎖的session_id,可以和v$session關聯ORACLE_USERNAME持有該鎖的oracle帳號OS_USER_NAME持有該鎖的操作系統(tǒng)帳號PROCESS操作系統(tǒng)的進程號，可以和v$process關聯LOCKED_MODE鎖模式，含義同v$lock.lmodeDba_locks和v$lock內容差不多，略V$session如果某

10、個session被因為某些行被其他會話鎖定而阻塞，則該視圖中的下面四個字段列出了這些行所屬對象的相關信息ROW_WAIT_FILE#等待的行所在的文件號ROW_WAIT_OBJ#等待的行所屬的object_idROW_WAIT_BLOCK#等待的行所屬的blockROW_WAIT_ROW#等待的行在blcok中的位置手工釋放鎖altersystemkillsession'sid,serial#'深入淺出oracle鎖一鎖阻塞的分析上篇深入淺出oracle鎖一原理篇是以理論為主，此文章列舉三個關于鎖阻塞的例子，并對此作詳細的說明，話不多說，直接開題。一：外鍵沒有索引，引起阻塞外鍵

11、沒有建立索引而引起的阻塞應該是最常見到，下面舉例對此詳細分析SQL>DELETEEMPWHEREEMPNO=7900;已刪除1行。SQL>Selectsid,type,id1,id2,lmode,request,ctime,blockFromv$lockWhereTypeIn('TM','TX');SIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK10TX458791181360416010TM30139030416010TM301370204160SQL>selecttrunc(458791/power(2,16),mod

12、(458791,power(2,16)fromdual;TRUNC(458791/POWER(2,16)MOD(458791,POWER(2,16)739再來查v$transaction,看看sid=10會話當前使用的undosegment和slotSQL>selectaddr,xidusn,xidslot,xidsqnfromv$transaction2whereaddrin(selecttaddrfromv$sessionwheresid=10);ADDRXIDUSNXIDSLOTXIDSQN67BAB0CC7391813對于tx鎖，v$lock中的id1即為持有該鎖的事務的回滾段號

13、，事務槽號的組合再來看SQL>selectowner,object_namefromdba_objectswhereobject_idin(30139,30137);OWNEROBJECT_NAMESCOTTDEPTSCOTTEMP對于tm鎖，id1即為持有該鎖的對象的id,結合上一個查詢，可以看到，該會話對30139即emp表力口了lmode=3的鎖，即rx鎖，同時，對30137即dept力口了lmode=2的鎖，即rs鎖。接下來在另外一會話中執(zhí)行如下語句deletedeptwhere1=0該會話被阻塞查看v$lockSQL>Selectsid,type,id1,id2,lmod

14、e,request,ctime,blockFromv$lockWhereTypeIn('TM','TX');SIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK10TX458791181360416010TM30139030416110TM30137020416012TM30139004372012TM301370303720可以看到，另一個會話(sid=12)對30137即dept加了lmode=3的鎖，即rx鎖。同時請求對30139即emp表力口了lmode=4的鎖，即s鎖(request=4),但該請求被阻塞(s和rx不相容),可以通過i

15、d1,id2,blo不字段看到,此會話sid=10會話阻塞。接下來，在emp的外鍵字段deptno建立索引SQL>createindexidx_emp_deptonemp(deptno);Indexcreated按原步驟重復上面的實驗SQL>DELETEEMPWHEREEMPNO=7900;已刪除1行。SQL>Selectsid,type,id1,id2,lmode,request,ctime,blockFromv$lockWhereTypeIn('TM','TX');SIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK10T

16、X196632225160386010TM30139030386010TM301370203860可以看到，是否建立索引，對emp表的鎖情況沒有影響。在另外一個會話中執(zhí)行如下語句SQL>DELETEDEPTWHERE1=0;已刪除0行。該語句沒有被阻塞，可以執(zhí)行?？纯串斍暗逆i信息SQL>Selectsid,type,id1,id2,lmode,request,ctime,blockFromv$lockWhereTypeIn('TM','TX');6精品資料推薦SIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK10TX1966322

17、25160435010TM30139030435010TM30137020435012TM301390209012TM3013703090可以看到，在外鍵上建立索引后，刪除父表，對子表加的是rs鎖，比沒有索引加s的強度要低，這樣避免了阻塞的發(fā)生，提高了系統(tǒng)的并行性。二：位圖索引帶來的阻塞位圖索引適合建在低基數列上面，在數據倉庫比較常用，如果是在并發(fā)性要求較高的01tp系統(tǒng)就要慎重了，不當的應用，可能會帶來嚴重的阻塞。SQL>createbitmapindexbidx_emp_jobonemp(job);索引已創(chuàng)建。SQL>DELETEEMPWHEREEMPNO=7369;已刪除1行

18、。在另外一會話中執(zhí)行sql_語句SQL>DELETEEMPWHEREEMPNO=7876;該語句阻塞SQL>Selectsid,type,id1,id2,lmode,request,ctime,blockFromv$lockWhereTypeIn('TM','TX');SIDTYPEID1ID2LMODEREQUESTCTIMEBLOCK10TX524312236260116110TM30137020116012TX58986423996098012TM3013903098012TM3013702098012TX524312236204980可以看到，sid=12的會話等待sid=10持有的tx鎖，雖然刪除的是不同記錄，但因為empno等于7876,7369的job字段值都是CLERK沖我們恰恰在job字段建立了位圖索引，第一個dml語句引起了對job=CLERK位圖段的鎖定，進而阻塞了第二個會話對該位圖段其他記錄的dml操作(注：如此時對job&