oracle相關(guān)特性篇之閂鎖、和并發(fā)性

Oracle是一個(gè)多用戶并發(fā)處理系統(tǒng)，在同一個(gè)時(shí)間點(diǎn)上，可能會(huì)有多個(gè)1、這些用戶之間的操作不會(huì)互相破壞，比如兩個(gè)用戶同時(shí)在相同的物2、在滿足串行化的前提下，如何將并發(fā)性提升型的鎖控制對(duì)數(shù)據(jù)的并發(fā)，以防止用戶之間出現(xiàn)破壞性的交互操作。個(gè)進(jìn)程同時(shí)資源，會(huì)造成資源的破壞，那么就需要給這種資源上一個(gè)鎖，如果這種資源很簡(jiǎn)單，例如內(nèi)存的分配和，那么就使用latch，如果這種資源相對(duì)復(fù)雜，有一定的邏輯判斷，那么就需要使用lock。閂鎖Oracle使用閂鎖來實(shí)現(xiàn)內(nèi)存的分配和例某個(gè)用戶進(jìn)程A發(fā)出一條update語句，要去58號(hào)數(shù)據(jù)塊里的某條出insert語句，要將某個(gè)新的記錄到58號(hào)數(shù)據(jù)塊中，如果沒有一定的保護(hù)機(jī)制，A正要寫入的空間可能被B搶先寫入，B要寫入的空間可能被A搶先寫入，不管哪個(gè)用戶先搶先寫入，造成的結(jié)果就是，58號(hào)數(shù)據(jù)塊的數(shù)據(jù)都了在寫入過程中，一直持有l(wèi)atch，寫完以后，latch。B嘗試latch，發(fā)現(xiàn)latchA持有，B進(jìn)入等待狀態(tài)，直Alatch，Blatch以后，才能在58號(hào)數(shù)據(jù)塊中寫入數(shù)據(jù)。凡是涉及內(nèi)存的讀和寫，就需要通過latch來實(shí)現(xiàn)，一次只能有一Oracle在實(shí)例管理中，不管是buffercache、sharedpoollogbuffer，都引入了各種各樣的latch?？梢赃@樣來理解latch，通過某個(gè)變量值的變化來實(shí)現(xiàn)，變0則說明latch沒有被其它進(jìn)程獲取，變量值為非0則說明latch被其他進(jìn)程獲取Latch分為兩種類這種類latch都是test-and-set的方式獲得的，也就是說，如果當(dāng)前進(jìn)程不能獲latch的時(shí)候CPU旋轉(zhuǎn)，而不放CPU，這就占用著CPU。latch，為什么還要占用CPUlatch本身是但是沒有獲得latchCPU，那么需要進(jìn)行上下文的切換，下次再次嘗試獲得latch時(shí)，又要進(jìn)行上下文切換，可能消耗的時(shí)間，因此，會(huì)通過執(zhí)行一段空代碼繞著CPU轉(zhuǎn)，然后再次嘗試獲得latch。如果仍然不能夠獲latch，繼續(xù)旋轉(zhuǎn)，當(dāng)反復(fù)旋轉(zhuǎn)CPU并嘗試獲得latch的次數(shù)超過某個(gè)上限時(shí)（該上限由隱藏參數(shù)控制，這時(shí)進(jìn)程會(huì)CPUsleep狀態(tài)，進(jìn)程一旦進(jìn)入sleep狀態(tài)，就會(huì)拋出一個(gè)等待事件，并記錄v$session_wait里，進(jìn)程正在等latch的信息會(huì)出現(xiàn)一個(gè)進(jìn)程會(huì)睡眠0.01秒，然后醒來，并再次嘗試獲得latch，如果旋轉(zhuǎn)CPU的次數(shù)達(dá)到上限值，仍然不能獲得latch，則再次進(jìn)入睡眠，這時(shí)會(huì)睡眠兩倍的時(shí)間，以此類推，直到達(dá)到睡眠的最大值：0.2秒。上面的情況是數(shù)據(jù)庫服務(wù)器具有CPU的情況，如果只有CPU，就不存在旋轉(zhuǎn)CPU的情況，一旦獲得不了latch，就進(jìn)入睡眠。2、不等待（no-這種類型的latch較少，對(duì)于這種類型latch來說，都會(huì)有很多個(gè)可用的latch。當(dāng)一個(gè)進(jìn)程請(qǐng)求其中的一個(gè)latch時(shí)no-wait模式開始請(qǐng)求，如果有當(dāng)所有的latch都不能獲得的時(shí)候，才會(huì)進(jìn)入等待。源很緊張，利用率總是在90%以上，甚至總是在100%，主要原因如下時(shí)隨意性過大，比如大小寫混用、則OracleSQL語句都要進(jìn)行解析，也就是非常頻繁地讀寫sharedpool里的內(nèi)存塊，從而導(dǎo)致與解析SQL相關(guān)的latch爭(zhēng)用。sqlSQL語句寫的比較低一旦發(fā)生latch資源爭(zhēng)用，就會(huì)導(dǎo)致CPU繁假設(shè)進(jìn)程A執(zhí)行一條SQL語句需要10000個(gè)數(shù)據(jù)塊，那么該進(jìn)掃描數(shù)據(jù)塊的過程中，一直持有l(wèi)atch，而另一個(gè)進(jìn)程B也要執(zhí)行SQL得latch，直到睡眠才CPU，接下來C進(jìn)程也要執(zhí)行SQL，同樣的，由于A持有了latchC無法獲得，于是旋CPU，再去獲latch，直到進(jìn)入睡眠才CPU，如果類似進(jìn)程很多的話，CPU總是在旋轉(zhuǎn)，也就是在做空的循環(huán)，而無法做其他的事情，因此體現(xiàn)出CPU使用率過鎖定Lock用來控制多個(gè)用戶對(duì)表里相同數(shù)據(jù)如果沒有使用鎖定來管理事務(wù)，則在9：04的時(shí)候，用戶A檢索id=1列值時(shí)，顯示為20，用戶A的修改被B覆蓋使用鎖定以后的情況在鎖定中，存在下面的兩種基1、排他鎖（X鎖，一旦用戶對(duì)某個(gè)資源添加了排他鎖，則其他用戶都不能再對(duì)該資源添加任何類型的鎖，直到該用戶了資源上的X鎖在該資源上添加X鎖，只能添加S鎖，直到該用戶了S鎖為止DML事務(wù)鎖定機(jī)制行級(jí)鎖（TX鎖updateemployeesetlast_name=‘xkj’where1、OracleSQL語句進(jìn)行解析，找exployee_id100的記錄所在的列上被更新前的舊值放入該undoSCN號(hào)、所使用的undo數(shù)據(jù)塊的地址，以及未提交的標(biāo)記等信息358號(hào)數(shù)據(jù)塊中，找到被更新的數(shù)據(jù)行，在數(shù)據(jù)行頭部設(shè)置一個(gè)鎖定標(biāo)記，并在頭部記錄當(dāng)前使用的ITL槽號(hào)，做完這些工作X鎖如果這時(shí)，另一個(gè)用B也對(duì)exployee_id100的記錄進(jìn)行修改，則其過上面描述一樣B在對(duì)數(shù)據(jù)行的頭部設(shè)置鎖定標(biāo)記的時(shí)候，發(fā)現(xiàn)該數(shù)據(jù)行頭部已經(jīng)有一個(gè)鎖定標(biāo)記了，說明該記錄已經(jīng)添加X鎖，于是用戶B必須等待，等待X鎖被。鎖定一條記錄，并不影響其他用戶對(duì)該記錄的，例如果用戶發(fā)出一SQLexployee_id100的記錄信這時(shí)服了，但是還沒有提交。于是根據(jù)數(shù)據(jù)行頭部ITL槽的號(hào)碼，在數(shù)據(jù)塊頭部ITL槽，并根據(jù)其中記錄undo數(shù)據(jù)塊的地址，找到undo讀一致性）塊，根據(jù)CR塊的內(nèi)容，將用戶所需要的信息進(jìn)行返回。表級(jí)A已經(jīng)發(fā)出了更新exployee_id100的記錄的SQL語句，當(dāng)A還沒有提交之前，另外一個(gè)用戶D發(fā)出下面的語句droptable由于A還沒有提交所做的事務(wù)，該事務(wù)還沒有結(jié)束，其他用戶不能刪除該表，否則A所發(fā)出的事務(wù)就無法正常結(jié)束，為了用戶D的刪除操作，能夠想到的最直觀的方法是顯然這樣存在很大的性能問表級(jí)鎖共5種模1、行級(jí)排他鎖（Rowexclusive）RX當(dāng)進(jìn)行DML時(shí)，會(huì)自動(dòng)在被更新的表上添加RX鎖，可以執(zhí)允許其他事務(wù)通過DML語句修改相同表里的其他數(shù)據(jù)行允許使用lock命令對(duì)表添加RX鎖定不允許其他事務(wù)對(duì)表添加XSYS@orcl>createtabletestx(idnumber);SYS@orcl>insertintotestxvalues(1);在Sess1中數(shù)據(jù)，不提交，再打開Sess2，添加XSYS@orcl>locktabletestxinexclusive不能添加X中可以添加X鎖。SYS@orcl>locktabletestxinexclusivemode;Table(s)Locked.成功添加XSYS@orcl>createtabletest1(idnumber);SYS@orcl>insertintotest1values(1);務(wù)使用lock添加一個(gè)行級(jí)排他鎖SYS@orcl>locktabletest1inrowexclusivemode;Table(s)Locked.在Sess1中無法XSYS@orcl>locktabletest1inexclusive不成在sess2中rollbacksess1可以添加X鎖，Sess2中無XSYS@orcl>SYS@orcl>locktabletest1inexclusivemode;Sess1成功SYS@orcl>locktabletest1inexclusiveSess2不成行級(jí)共享鎖（rowsharedRS鎖這些命令。查詢完成以后，發(fā)出rollback命令表上添加RS鎖以后，不允許其他事務(wù)對(duì)相同的表添X鎖，但是允許SYS@orcl>createtabletest2(idnumber,namevarchar2(10));SYS@orcl>insertintotest2values(1,'aa');SYS@orcl>insertintotest2values(2,'bb');SYS@orcl>commit;SYS@orcl>select*fromtest2whereid=1forupdate;Sess2，不允許對(duì)鎖定的數(shù)據(jù)進(jìn)行修改SYS@orcl>deletefromtest2;SYS@orcl>deletefromtest2whereid=2;SYS@orcl>commit;在對(duì)表進(jìn)行DML的時(shí)候，已經(jīng)在表上添加了行級(jí)排他鎖，因此在有行級(jí)共SYS@orcl>locktabletest2inrowexclusivemode;SYS@orcl>locktabletest2inexclusive不成Sess2，rollback后，sess1可以添加排他鎖SYS@orcl>locktabletest2inexclusivemode;共享鎖（shared）SSYS@orcl>createtabletest3(idnumber);SYS@orcl>insertintotest3values(1);SYS@orcl>insertintotest3values(2);SYS@orcl>commit;SYS@orcl>locktabletest3insharemode;Sess2，不允許進(jìn)行DML操作SYS@orcl>insertintotest3values(5);Sess1，rollback后，sess2可以數(shù)據(jù)Sess2，允許進(jìn)行RSSYS@orcl>locktabletest3inrowshare注：共享鎖和排他鎖的區(qū)別是可以允許排他鎖（exclusive）X其他事務(wù)不能對(duì)表進(jìn)行任何的DMLDDL操作，只能進(jìn)行查SYS@orcl>locktabletest3inexclusive共享行級(jí)排他鎖（sharedrowexclusive）SRX不能對(duì)相同的表進(jìn)行DML操作，也不能添加共鎖之間的兼容性，有了這個(gè)鎖，能不能有另外一 exclusive][exclusive]mode;可以通過DML進(jìn)行自動(dòng)的加鎖，也可以使用LOCK手工共享鎖（ShareTableLockS加鎖語ockTableTableNameInShare表。一個(gè)有效的共享鎖明確地SelectForupdate形式鎖定行，或執(zhí)行LOCKTABLETableNameINSHAREROWEXCLUSIVEMODE;LOCKTABLETableNameINROWEXCLUSIVEMODE;排它鎖（ExclusiveTableLockX加鎖語ockTableTableNameInExclusive定義排它鎖的語法LOCKTABLETableNameINEXCLUSIVE行級(jí)鎖（RowShareTableLock，RS一個(gè)行級(jí)鎖（有時(shí)SubshareTableLock，簡(jiǎn)SS，子共享鎖）需要該事務(wù)在被鎖定行的表update的形式加鎖。當(dāng)有下面語句被執(zhí)行的時(shí)候SELECT...FROMTableName...FORUPDATEOF...;LOCKTABLETableNameINROWSHAREMODE;行級(jí)鎖（RowShareTableLock）在鎖類型中是限制最少的，也是在表的并允許的操作：行級(jí)共享鎖由一個(gè)事務(wù)控制，允許其它事務(wù)查詢、、更的操作：擁有行級(jí)鎖的事務(wù)不允許其它事務(wù)執(zhí)行排它鎖，即LockTableTableNameInExclusive行級(jí)排它鎖（RowExclusiveTableLock，RXINSERTINTOTableName...;UPDATETableName...;DELETEFROMTableName...;LOCKTABLETableNameINROWEXCLUSIVE行級(jí)排它鎖比行級(jí)鎖稍微多一些限制LOCKTABLEtableINSHARELOCKTABLEtableINSHAREEXCLUSIVEMODE;LOCKTABLEtableINEXCLUSIVEMODE共享行級(jí)排它鎖（ShareRowExclusiveTableLock，SRX共享行級(jí)排它鎖有時(shí)也稱共享子排它鎖（ShareSubexclusiveTableLock，SSX，它比共享鎖有限制。定義共享行級(jí)排它鎖的語法為：LockTableTableNameInShareRowExclusive鎖事務(wù)允許其它事務(wù)在被鎖定的表上執(zhí)行查詢或使用SelectFromTableNameForupdate…來準(zhǔn)確在鎖定行而不能更新行。LOCKTABLETableNameINSHAREMODE;LOCKTABLETableNameINSHAREROWEXCLUSIVEMODE;LOCKTABLETableNameINROWEXCLUSIVEMODE;LOCKTABLETableNameINEXCLUSIVE據(jù)庫掛起，或?qū)е滤梨i的發(fā)生，產(chǎn)生ORA-60的錯(cuò)誤。解決DML事務(wù)鎖定的兼容的鎖定，就會(huì)發(fā)生鎖定的現(xiàn)象。鎖定相關(guān)視其中比較重要的字段包括XIDUSN表示當(dāng)前事務(wù)使用的回滾段的、做槽號(hào)、XIDSQN說明序列號(hào)、STATUS說明該事務(wù)是否為活動(dòng)的。記錄了當(dāng)session已經(jīng)獲得的鎖定以及正在請(qǐng)求的鎖定的信息。其中比較TX和TM、LMODE說明已經(jīng)獲得的鎖定的模式，以數(shù)字編碼表示、REQUEST說明正在請(qǐng)求的鎖定的模式，以數(shù)字編碼表示。BLOCK說明是否了其他用戶獲得鎖定，大于0說明是，等于0說明否。該視圖中包含的字段以及字段含義v$lock中的字段一模一樣。只不過該錄按照申請(qǐng)鎖定的時(shí)間先后順序排列，先申請(qǐng)鎖定的session排面，排面的session將會(huì)先獲得鎖定。記錄了當(dāng)前已經(jīng)被鎖定的對(duì)象的信息。其中比較重要的字段包括XIDUSN表示當(dāng)前事務(wù)使用的回滾段的、XIDSLOT說明該事務(wù)在回滾段頭部的事務(wù)表中對(duì)應(yīng)的記錄、XIDSQN說明序列號(hào)、OBJECT_ID說明當(dāng)前被鎖定的對(duì)象的ID號(hào)，可以IDdba_objects里查找被鎖定的對(duì)象名稱、LOCKED_MODE說明鎖定模式的數(shù)字編碼。記錄了當(dāng)前session的相關(guān)信息。其中比較重要的字段包SID表示sessionSYS@orcl>createtabletest5(idnumber,namevarchar(10),salarynumber);SYS@orcl>insertintotest5values(1,'a',12000);SYS@orcl>insertintotest5values(1,'a',3000);SYS@orcl>insertintotest5values(1,'a',20000);SYS@orcl>insertintotest5values(2,'b',11000);SYS@orcl>commit;SYS@orcl>updatetest5setname='wl'whereid=1;SYS@orcl>selectsidfromv$mystatwhererownum=1;Sess1的sessionIDSYS@orcl>selectxidusn,xidslot,xidsqn,statusfromv$transaction; XIDSQNSTATUS 該會(huì)話使用了5號(hào)回該會(huì)話在回滾段事務(wù)表中的槽號(hào)是事務(wù)是活動(dòng)的，也就是沒有提SYS@orcl>selectsid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'rowsharee',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive') exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_mode,blockfromv$lockwheresid=31;SIDTYID1ID2LOCK_MODEREQUEST_MODE row ID1：對(duì)于TM鎖來說，被鎖定對(duì)象的IDSYS@orcl>selectobject_namefromdba_objectswhereobject_id=88622;被鎖定對(duì)象是另外啟動(dòng)一個(gè)sessionSYS@orcl>selectsidfromv$mystatwhererownum=1;SessionIDSYS@orcl>updatetest5setname='ww'where這個(gè)會(huì)話被鎖定了回到session1，查看一下會(huì)話的鎖的情況SYS@orcl>selectsid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'rowsharee',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive') exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_mode,blockfromv$lockwheresidin(31,34);SIDTYID1ID2LOCK_MODEREQUEST_MODE000000row00row0134請(qǐng)求的鎖是行級(jí)排他鎖，但是該記錄31會(huì)話鎖定，因此鎖定模式為NONE34在TM上獲得了行級(jí)排他鎖，因?yàn)檫@個(gè)鎖和已有的鎖并不，31的鎖再次啟動(dòng)一個(gè)sessionSYS@orcl>selectsidfromv$mystatwhererownum=1;SYS@orcl>updatetest5setname='ww'where事務(wù)也被鎖定回到session1執(zhí)行這個(gè)命SYS@orcl>selectsid,type,decode(request,0,'none',1,'null',2,'rowshare',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_modefromv$enqueue_lockwheresidin(31,34,56); TY TX TX AE AE AE exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andBLOCKER_SIDSERIAL#BLOCKER_USERNAMETYLOCK_MODTIME_HELDWAITER_SIDREQUEST_MODE切換sess1會(huì)話，在31會(huì)話中，提交事務(wù)。再次運(yùn)行查SYS@orcl> exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andBLOCKER_SIDSERIAL#BLOCKER_USERNAMETYLOCK_MODTIME_HELDWAITER_SIDREQUEST_MODE SYS@orcl>altersystemkillsession'34,2657';再次查詢，發(fā)現(xiàn)已經(jīng)沒有了鎖SYS@orcl> exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andb.block=1;norows2/*selectsid,id1,id2,lmode,request,typefromv$lockwhereid1in(selectid1fromv$lockwherelmode=0)orderbyid1,request;--TMTXselectsid,type,id1,id2, reqtime,blockfromv$lockwheretypein('TM','TX');--TMTX decode(request,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X')request, ctime,blockfromv$lockwheretypein('TM','TX');3IDselectl.os_user_name,s.username, o.object_name,o.object_type,s.logon_timefromv$locked_objectl,v$sessions,dba_objectsowherel.session_id=orderbys.logon_time;或selectl.oracle_usernameo_name,l.session_idsid,o.object_name,o.object_type,l.xidusn,l.xidslot,l.xidsqnfromv$locked_objectl,all_objectsowherel.object_id=o.object_idorderby或SELECTp.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_nameFROMv$processp,v$sessiona,v$locked_objectb,all_objectscWHEREp.addr=a.paddrANDcess=cessANDc.object_id=b.object_id;如何查找引起鎖的S