深入淺出oracle鎖

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

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

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

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

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

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

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

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

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

10、個(gè)session被因?yàn)槟承┬斜黄渌麜?huì)話鎖定而阻塞，則該視圖中的下面四個(gè)字段列出了這些行所屬對(duì)象的相關(guān)信息ROW_WAIT_FILE#等待的行所在的文件號(hào)ROW_WAIT_OBJ#等待的行所屬的object_idROW_WAIT_BLOCK#等待的行所屬的blockROW_WAIT_ROW#等待的行在blcok中的位置手工釋放鎖altersystemkillsession'sid,serial#'深入淺出oracle鎖一鎖阻塞的分析上篇深入淺出oracle鎖一原理篇是以理論為主，此文章列舉三個(gè)關(guān)于鎖阻塞的例子，并對(duì)此作詳細(xì)的說明，話不多說，直接開題。一：外鍵沒有索引，引起阻塞外鍵

11、沒有建立索引而引起的阻塞應(yīng)該是最常見到，下面舉例對(duì)此詳細(xì)分析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會(huì)話當(dāng)前使用的undosegment和slotSQL>selectaddr,xidusn,xidslot,xidsqnfromv$transaction2whereaddrin(selecttaddrfromv$sessionwheresid=10);ADDRXIDUSNXIDSLOTXIDSQN67BAB0CC7391813對(duì)于tx鎖，v$lock中的id1即為持有該鎖的事務(wù)的回滾段號(hào)

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

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

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

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

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

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