【鎖】Oracle鎖系列
創(chuàng)新互聯(lián)是一家成都網(wǎng)站建設(shè)、成都做網(wǎng)站，提供網(wǎng)頁設(shè)計(jì)，網(wǎng)站設(shè)計(jì)，網(wǎng)站制作，建網(wǎng)站，按需搭建網(wǎng)站，網(wǎng)站開發(fā)公司，成立與2013年是互聯(lián)行業(yè)建設(shè)者，服務(wù)者。以提升客戶品牌價值為核心業(yè)務(wù)，全程參與項(xiàng)目的網(wǎng)站策劃設(shè)計(jì)制作，前端開發(fā)，后臺程序制作以及后期項(xiàng)目運(yùn)營并提出專業(yè)建議和思路。
各位技術(shù)愛好者，看完本文后，你可以掌握如下的技能，也可以學(xué)到一些其它你所不知道的知識，~O(∩_∩)O~：
①鎖的概念、分類、及其模擬
②查詢鎖的視圖及視圖之間的關(guān)聯(lián)
③鎖的參數(shù)（DML_LOCKS、DDL_LOCK_TIMEOUT）
④FOR UPDATE及FOR UPDATE OF系列
⑤ 帶ONLINE和不帶ONLINE創(chuàng)建索引的鎖情況（是否阻塞DML操作）
⑥ 包或存過不能編譯的解決方法
⑦ORA-08104解決方法
Tips：
①本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。
②文章中用到的所有代碼、相關(guān)軟件、相關(guān)資料及本文的pdf版本都請前往小麥苗的云盤下載，小麥苗的云盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。
③若網(wǎng)頁文章代碼格式有錯亂，請下載pdf格式的文檔來閱讀。
④在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。其中，需要特別關(guān)注的地方我都用灰色背景和粉紅色字體來表示，比如在下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關(guān)注的地方；而命令一般使用黃色背景和紅色字體標(biāo)注；對代碼或代碼輸出部分的注釋一般采用藍(lán)色字體表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
有網(wǎng)友一直催著說發(fā)一些鎖系列的文章，其實(shí)小麥苗一直對鎖這塊也沒有徹底去研究過，今年寫書里邊寫到了鎖的內(nèi)容，干脆就徹底把這一塊整理了一下，現(xiàn)在分享給大家，若有錯誤，還請大家及時指正。
文章很多內(nèi)容來源于網(wǎng)絡(luò)或Concepts的內(nèi)容，若有侵權(quán)還請聯(lián)系小麥苗刪除。
鎖的定義：鎖（lock）機(jī)制用于管理對共享資源的并發(fā)訪問，用于多用戶的環(huán)境下，可以保證數(shù)據(jù)庫的完整性和一致性。鎖是防止訪問相同資源的事務(wù)之間的破壞性交互的機(jī)制。既可以是用戶對象(例如表或行)，也可以是對用戶不可見的系統(tǒng)對象(例如共享數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)字典行)。
鎖的解釋：當(dāng)多個用戶并發(fā)地存取數(shù)據(jù)時，在數(shù)據(jù)庫中就會產(chǎn)生多個事務(wù)同時存取同一數(shù)據(jù)的情況。若對并發(fā)操作不加控制就可能會讀取和存儲不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫的完整性和一致性。當(dāng)事務(wù)在對某個數(shù)據(jù)對象進(jìn)行操作前，先向系統(tǒng)發(fā)出請求，對其加鎖。加鎖后事務(wù)就對該數(shù)據(jù)對象有了一定的控制。
鎖的作用：在并發(fā)事務(wù)之間防止破壞性的交互作用，不需要用戶的動作，自動使用最低的限制級別，在事務(wù)處理期間保持。
數(shù)據(jù)庫是一個多用戶使用的共享資源。當(dāng)多個用戶并發(fā)地存取數(shù)據(jù)時，在數(shù)據(jù)庫中就會產(chǎn)生多個事務(wù)同時存取同一數(shù)據(jù)的情況。若對并發(fā)操作不加控制就可能會讀取和存儲不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫的一致性。
鎖（lock）是防止訪問相同資源（例如表或數(shù)據(jù)行等用戶對象，或內(nèi)存中的共享數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)字典等對用戶不可見的系統(tǒng)對象）的事務(wù)產(chǎn)生破壞性交互的機(jī)制。
在任何情況下，Oracle都能夠自動地獲得執(zhí)行SQL語句所必須的所有鎖，無需用戶干預(yù)。Oracle會盡可能地減少鎖產(chǎn)生的影響，從而最大程度地保證數(shù)據(jù)的并發(fā)訪問能力，并確保數(shù)據(jù)一致性及錯誤恢復(fù)。同時，Oracle也支持用戶手工加鎖的操作。
Oracle 從來不會升級鎖，但是它會執(zhí)行鎖轉(zhuǎn)換（lock conversion）或鎖提升（lock promotion）。
Alock is a mechanism that preventsdestructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data. Locks play a crucial row in maintaining database concurrency and consistency.
鎖是一種機(jī)制，用來防止多個共同訪問共享數(shù)據(jù)的事務(wù)之間的破壞性交互，包括不正確地更新數(shù)據(jù)或不正確地更改基礎(chǔ)數(shù)據(jù)結(jié)構(gòu)。鎖在維護(hù)數(shù)據(jù)庫并發(fā)性和一致性當(dāng)中扮演著一個關(guān)鍵的角色。
并發(fā)(concurrency)和并行(parallel)。并發(fā)意思是在數(shù)據(jù)庫中有超過兩個以上用戶對同樣的數(shù)據(jù)做修改，而并行的意思就是將一個任務(wù)分成很多小的任務(wù)，讓每一個小任務(wù)同時執(zhí)行，最后將結(jié)果匯總到一起。所以說，鎖產(chǎn)生的原因就是并發(fā)，并發(fā)產(chǎn)生的原因是因?yàn)橄到y(tǒng)和客戶的需要。
在單用戶數(shù)據(jù)庫中，鎖不是必需的，因?yàn)橹挥幸粋€用戶在修改信息。但是，當(dāng)多個用戶在訪問和修改數(shù)據(jù)時，數(shù)據(jù)庫必須提供一種方法，以防止對同一數(shù)據(jù)進(jìn)行并發(fā)修改。鎖實(shí)現(xiàn)了以下重要的數(shù)據(jù)庫需求：
v ·一致性
一個會話正在查看或更改的數(shù)據(jù)不能被其它會話更改，直到用戶會話結(jié)束。
v ·完整性
數(shù)據(jù)和結(jié)構(gòu)必須按正確的順序反映對他們所做的所有更改。數(shù)據(jù)庫通過其鎖定機(jī)制，提供在多個事務(wù)之間的數(shù)據(jù)并發(fā)性、一致性、和完整性。鎖定將自動執(zhí)行，并且不需要用戶操作。
執(zhí)行SQL語句時，Oracle數(shù)據(jù)庫自動獲取所需的鎖。例如，在數(shù)據(jù)庫允許某個會話修改數(shù)據(jù)之前，該會話必須先鎖定數(shù)據(jù)。鎖給予該會話對數(shù)據(jù)的獨(dú)占控制權(quán)，以便在釋放該鎖之前，任何其它事務(wù)都不可以修改被鎖定的數(shù)據(jù)。
因?yàn)閿?shù)據(jù)庫的鎖定機(jī)制與事務(wù)控制緊密地綁定在一起，應(yīng)用程序設(shè)計(jì)人員只需要正確地定義事務(wù)，而數(shù)據(jù)庫會自動管理鎖定。
Oracle數(shù)據(jù)庫自動使用最低適用的限制級別，來提供最高程度的數(shù)據(jù)并發(fā)，但還能提供非常安全的數(shù)據(jù)完整性。限制級別越低、則有更多的可用數(shù)據(jù)供其他用戶訪問。相反，限制級別越高，則其它事務(wù)為獲取其所需的鎖類型就將遭受更多的限制。
在多用戶的數(shù)據(jù)庫系統(tǒng)中，Oracle使用兩種模式的鎖：
事務(wù)內(nèi)各語句獲得的鎖在事務(wù)執(zhí)行期內(nèi)有效，以防止事務(wù)間破壞性的相互干擾，例如：臟讀取（dirty read），無效地更新（lost update），以及其它并發(fā)事務(wù)中具有破壞性的DDL操作。如果某個事務(wù)中的SQL語句對數(shù)據(jù)進(jìn)行了修改，只有在此事務(wù)提交后開始的事務(wù)才能看到前者修改的結(jié)果。
當(dāng)用戶提交（commit）或撤銷（undo）一個事務(wù)后，Oracle將釋放此事務(wù)內(nèi)各個SQL語句獲得的鎖。當(dāng)用戶在事務(wù)內(nèi)回滾到某個保存點(diǎn)（savepoint）后，Oracle也會釋放此保存點(diǎn)后獲得的鎖。只有當(dāng)前沒有等待被鎖資源的事務(wù)才能獲得可用資源的鎖。等待事務(wù)不會對可用資源加鎖而是繼續(xù)等待，直至擁有其所等待資源的事務(wù)完成提交或回滾。
有兩種類型：顯式鎖定和隱式鎖定。Oracle鎖被自動執(zhí)行，并且不要求用戶干預(yù)的鎖為隱式鎖。對于SQL語句隱式鎖是必須的，依賴被請求的動作。隱式鎖定除SELECT外，對所有的SQL語句都發(fā)生。用戶也可以手動鎖定數(shù)據(jù)，這是顯式鎖定。
隱式鎖定：這是Oracle中使用最多的鎖。通常用戶不必聲明要對誰加鎖，Oracle自動可以為操作的對象加鎖，這就是隱式鎖定。
顯式鎖定：用戶可以使用命令明確的要求對某一對象加鎖。顯式鎖定很少使用。
LOCK TABLE沒有觸發(fā)行鎖，只有TM表鎖。
|
LOCK TABLE TABLE_NAME IN ROW SHARE MODE NOWAIT; --2：RS LOCK TABLE TABLE_NAME IN SHARE UPDATE MODE; --2：RS LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE NOWAIT; --3：RX LOCK TABLE TABLE_NAME IN SHARE MODE; --4：S LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE; --5：SRX LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE NOWAIT; --6：X |
隱式鎖定：
Select * from table_name……
Insert into table_name……
Update table_name……
Delete from table_name……
Select * from table_name for update
鎖在用戶修改之前就發(fā)揮作用：
Select ..for update（nowait)
Select * from tab1 for update
用戶發(fā)出這條命令之后，oracle將會對返回集中的數(shù)據(jù)建立行級封鎖，以防止其他用戶的修改。
如果此時其他用戶對上面返回結(jié)果集的數(shù)據(jù)進(jìn)行dml或ddl操作都會返回一個錯誤信息或發(fā)生阻塞。
1：對返回結(jié)果集進(jìn)行update或delete操作會發(fā)生阻塞。
2：對該表進(jìn)行ddl操作將會報(bào)：Ora-00054:resource busy and acquire with nowait specified.
原因分析
此時Oracle已經(jīng)對返回的結(jié)果集上加了排它的行級鎖，所有其他對這些數(shù)據(jù)進(jìn)行的修改或刪除操作都必須等待這個鎖的釋放，產(chǎn)生的外在現(xiàn)象就是其它的操作將發(fā)生阻塞，這個這個操作commit或rollback.
同樣這個查詢的事務(wù)將會對該表加表級鎖，不允許對該表的任何ddl操作，否則將會報(bào)出ora-00054錯誤：:resource busy and acquire with nowait specified.
會話1：
|
SYS@lhrdb S1> create table t_lock_lhr as select rownum as id,0 as type from dual connect by rownum <=3;
Table created.
SYS@lhrdb S1> select * from t_lock_lhr where id=2 and type =0 for update nowait;
ID TYPE ---------- ---------- 2 0 |
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait; select * from t_lock_lhr where id=2 and type=0 for update nowait * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
會話1：
|
SYS@lhrdb S1> update t_lock_lhr set type=1 where id=2 and type=0;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete.
SYS@lhrdb S1> select * from t_lock_lhr where id=2;
ID TYPE ---------- ---------- 2 1
|
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait;
no rows selected
|
樂觀的認(rèn)為數(shù)據(jù)在select出來到update進(jìn)取并提交的這段時間數(shù)據(jù)不會被更改。這里面有一種潛在的危險就是由于被選出的結(jié)果集并沒有被鎖定，是存在一種可能被其他用戶更改的可能。因此Oracle仍然建議是用悲觀封鎖，因?yàn)檫@樣會更安全。
|
會話1： SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話2： SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話1： SYS@lhrdb S1> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id = 3;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete. SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591 會話2： SYS@lhrdb S2> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id =3;
0 rows updated.
SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591
|
更新丟失是指多個用戶通過應(yīng)用程序訪問數(shù)據(jù)庫時，由于查詢數(shù)據(jù)并返回到頁面和用戶修改完畢點(diǎn)擊保存按鈕將修改后的結(jié)果保存到數(shù)據(jù)庫這個時間段（即修改數(shù)據(jù)在頁面上停留的時間）在不同用戶之間可能存在偏差，從而最先查詢數(shù)據(jù)并且最后提交數(shù)據(jù)的用戶會把其他用戶所作的修改覆蓋掉。
解決方法如下：
數(shù)據(jù)庫在必要時執(zhí)行鎖轉(zhuǎn)換。在鎖轉(zhuǎn)換中，數(shù)據(jù)庫自動將較低限制的表鎖轉(zhuǎn)換為較高限制的其它鎖定。一個事務(wù)在該事務(wù)中所有執(zhí)行插入、更新、或刪除的行上持有行獨(dú)占鎖。因?yàn)樾墟i是在最高程度限制下獲得的，因此不要求鎖轉(zhuǎn)換，也不執(zhí)行鎖轉(zhuǎn)換。鎖轉(zhuǎn)換不同于鎖升級，鎖升級發(fā)生在當(dāng)某個粒度級別持有許多鎖（例如行），數(shù)據(jù)庫將其提高到更高粒度級別（例如表）。如果一個用戶鎖定了一個表中的許多行，則某些數(shù)據(jù)庫自動將行鎖升級到單個表鎖。鎖的數(shù)量減少了，但被鎖定的東西卻增加了。
Oracle數(shù)據(jù)庫永遠(yuǎn)不會升級鎖。鎖升級極大地增加了死鎖的可能性。假定一個系統(tǒng)嘗試升級事務(wù)1中的鎖，但因?yàn)槭聞?wù)2持有該鎖，故不能成功。如果事務(wù)2在它可以繼續(xù)操作之前也需要在相同的數(shù)據(jù)上進(jìn)行鎖升級，則將發(fā)生一個死鎖。
ORACLE的鎖是block里面實(shí)現(xiàn)的,SQLSERVER,DB2是內(nèi)存里面實(shí)現(xiàn)的.內(nèi)存實(shí)現(xiàn)有資源消耗問題,當(dāng)內(nèi)存不足會引發(fā)鎖升級,但是ORACLE不會發(fā)生鎖升級。
事務(wù)擁有在此事務(wù)內(nèi)被插入（insert），更新（update），刪除（delete）的數(shù)據(jù)行的排它行級鎖（exclusive row lock）。對于數(shù)據(jù)行來說，排它行級鎖已經(jīng)是限制程度最高的鎖，因此無需再進(jìn)行鎖轉(zhuǎn)換（lock conversion）。
Oracle能夠自動地選擇不同類型的鎖對數(shù)據(jù)并發(fā)訪問進(jìn)行控制，防止用戶間破壞性的交互操作。Oracle將自動地為事務(wù)進(jìn)行鎖管理，防止其它事務(wù)對需要排它訪問的資源執(zhí)行操作。當(dāng)事務(wù)不再需要加鎖的資源并觸發(fā)某個事件后，鎖能夠被自動地釋放。
在事務(wù)執(zhí)行期間，Oracle能夠根據(jù)加鎖的資源及需要執(zhí)行的操作自動地決定鎖的類型（types of lock）及對資源的限制級別（level of restrictiveness）。
V$LOCK_TYPE 該視圖是對DML鎖的類型的解釋。
select * from V$LOCK_TYPE vwhere v.IS_USER='YES';
當(dāng)Oracle執(zhí)行DML語句時，系統(tǒng)自動在所要操作的表上申請TM類型的鎖。當(dāng)TM鎖獲得后，系統(tǒng)再自動申請TX類型的鎖，并將實(shí)際鎖定的數(shù)據(jù)行的鎖標(biāo)志位進(jìn)行置位。這樣在事務(wù)加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標(biāo)志，而只需檢查TM鎖模式的相容性即可，大大提高了系統(tǒng)的效率。TM鎖包括了SS、SX、S、X等多種模式，在數(shù)據(jù)庫中用0－6來表示。不同的SQL操作產(chǎn)生不同類型的TM鎖。
在數(shù)據(jù)行上只有X鎖（排它鎖）。在Oracle數(shù)據(jù)庫中，當(dāng)一個事務(wù)首次發(fā)起一個DML語句時就獲得一個TX鎖，該鎖保持到事務(wù)被提交或回滾。當(dāng)兩個或多個會話在表的同一條記錄上執(zhí)行DML語句時，第一個會話在該條記錄上加鎖，其它的會話處于等待狀態(tài)。當(dāng)?shù)谝粋€會話提交后，TX鎖被釋放，其它會話才可以加鎖。
當(dāng)Oracle數(shù)據(jù)庫發(fā)生TX鎖等待時，如果不及時處理常常會引起Oracle數(shù)據(jù)庫掛起，或?qū)е滤梨i的發(fā)生，產(chǎn)生ORA-60的錯誤。這些現(xiàn)象都會對實(shí)際應(yīng)用產(chǎn)生極大的危害，如長時間未響應(yīng)，大量事務(wù)失敗等。
當(dāng)Oracle執(zhí)行DELETE,UPDATE,INSERT,SELECT FOR UPDATE DML語句時，oracle首先自動在所要操作的表上申請TM類型的鎖。當(dāng)TM鎖獲得后，再自動申請TX類型的鎖，并將實(shí)際鎖定的數(shù)據(jù)行的鎖標(biāo)志位(lb即lock bytes)進(jìn)行置位。在記錄被某一會話鎖定后，其它需要訪問被鎖定對象的會話會按先進(jìn)先出的方式等待鎖的釋放，對于select操作而言，并不需要任何鎖，所以即使記錄被鎖定，select語句依然可以執(zhí)行，實(shí)際上，在此情況下，oracle是用到undo的內(nèi)容進(jìn)行一致性讀來實(shí)現(xiàn)的。
當(dāng)Oracle執(zhí)行DML語句時，系統(tǒng)自動在所要操作的表上申請TM類型的鎖。當(dāng)TM鎖獲得后，系統(tǒng)再自動申請TX類型的鎖，并將實(shí)際鎖定的數(shù)據(jù)行的鎖標(biāo)志位進(jìn)行置位。這樣在事務(wù)加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標(biāo)志，而只需檢查TM鎖模式的相容性即可，大大提高了系統(tǒng)的效率。DML語句能夠自動地獲得所需的表級鎖（table-level lock）與行級鎖（row-level lock）。
DML鎖，也稱為數(shù)據(jù)鎖，確保由多個用戶并發(fā)訪問的數(shù)據(jù)的完整性。例如，DML鎖可防止兩個客戶從一個在線書店購買某一本書所剩的最后一個拷貝。DML鎖也可以防止多個相互沖突的DML或DDL操作產(chǎn)生破壞性干擾。
DML語句自動獲取下列類型的鎖：
n 行鎖(TX)
n 表鎖(TM)
行級鎖（row-level lock）的作用是防止兩個事務(wù)同時修改相同的數(shù)據(jù)行。當(dāng)一個事務(wù)需要修改一行數(shù)據(jù)時，就需對此行數(shù)據(jù)加鎖。Oracle對語句或事務(wù)所能獲得的行級鎖的數(shù)量沒有限制，Oracle也不會講行級鎖的粒度升級（lock escalation）。行級鎖是粒度最精細(xì)的鎖，因此行級鎖能夠提供最好的數(shù)據(jù)并發(fā)訪問能力及數(shù)據(jù)處理能力。
Oracle同時支持多版本并發(fā)訪問控制（multiversion concurrency control）及行級鎖技術(shù)（row-level locking），因此用戶只有在訪問相同數(shù)據(jù)行時才會出現(xiàn)競爭，具體來說：
l 讀取操作無需等待對相同數(shù)據(jù)行的寫入操作。
l 寫入操作無需等待對相同數(shù)據(jù)行的讀取操作，除非讀取操作使用了 SELECT ... FOR UPDATE語句，此讀取語句需要對數(shù)據(jù)加鎖。
l 寫入操作只需等待并發(fā)地且針對相同數(shù)據(jù)行的其它寫入操作。
提示：讀取操作可能會等待對相同數(shù)據(jù)塊（data block）的寫入操作，這種情況只會在出現(xiàn)掛起的分布式事務(wù)（pending distributed transaction）時偶爾出現(xiàn)。
在執(zhí)行下列語句時，事務(wù)需要獲得被修改的每一數(shù)據(jù)行的排它行級鎖（exclusive row lock）：INSERT，UPDATE，DELETE，及使用了FOR UPDATE子句的SELECT語句。
在事務(wù)被提交或回滾前，此事務(wù)擁有在其內(nèi)部被修改的所有數(shù)據(jù)行的排它鎖，其它事務(wù)不能對這些數(shù)據(jù)行進(jìn)行修改操作。但是，如果事務(wù)由于實(shí)例故障而終止，在整個事務(wù)被恢復(fù)前，數(shù)據(jù)塊級的恢復(fù)將使數(shù)據(jù)塊內(nèi)數(shù)據(jù)行上的鎖釋放。執(zhí)行前面提到的 4 種SQL語句時，Oracle能自動地對行級鎖進(jìn)行管理。
當(dāng)事務(wù)獲得了某些數(shù)據(jù)行上的行級鎖時，此事務(wù)同時獲得了數(shù)據(jù)行所屬表上的表級鎖（table lock）。表級鎖能夠防止系統(tǒng)中并發(fā)地執(zhí)行有沖突的DDL操作，避免當(dāng)前事務(wù)中的數(shù)據(jù)操作被并發(fā)地DDL操作影響。
行級鎖機(jī)制：
當(dāng)一個事務(wù)開始時，必須申請一個TX鎖，這種鎖保護(hù)的資源是回滾段、回滾數(shù)據(jù)塊。因此申請也就意味著：用戶進(jìn)程必須先申請到回滾段資源后才開始一個事務(wù)，才能執(zhí)行DML操作。申請到回滾段后，用戶事務(wù)就可以修改數(shù)據(jù)了。具體順序如下：
1、首先獲得TM鎖，保護(hù)事務(wù)執(zhí)行時，其他用戶不能修改表結(jié)構(gòu)
2、事務(wù)修改某個數(shù)據(jù)塊中記錄時，該數(shù)據(jù)塊頭部的ITL表中申請一個空閑表項(xiàng)，在其中記錄事務(wù)項(xiàng)號，實(shí)際就是記錄這個事務(wù)要使用的回滾段的地址（應(yīng)該叫包含）
3、事務(wù)修改數(shù)據(jù)塊中的某條記錄時，會設(shè)置記錄頭部的ITL索引指向上一步申請到的表項(xiàng)。然后修改記錄。修改前先在回滾段將記錄之前的狀態(tài)做一個拷貝，然后修改表中數(shù)據(jù)。
4、其他用戶并發(fā)修改這條記錄時，會根據(jù)記錄頭部ITL索引讀取ITL表項(xiàng)內(nèi)容，確認(rèn)是否事務(wù)提交。
5、若沒有提交，必須等待TX鎖釋放
從上面的機(jī)制來看，無論一個事務(wù)修改多少條記錄，都只需要一個TX鎖。所謂的“行級鎖”其實(shí)也就是數(shù)據(jù)塊頭、數(shù)據(jù)記錄頭的一些字段，不會消耗額外的資源。 從另一方面也證明了，當(dāng)用戶被阻塞時，不是被某條記錄阻塞，而是被TX鎖堵塞。也正因?yàn)檫@點(diǎn)，很多人也傾向把TX鎖稱為事務(wù)鎖。這里可通過實(shí)驗(yàn)來驗(yàn)證所說 結(jié)論。
會話1：
|
SQL> select * from test; ID NAME ---------- -------- 1 A 2 B 3 C
SQL> savepoint a; Savepoint created.
SQL> update test set name='ssss' where id=2; 1 row updated.
|
會話2，更新同一行發(fā)生阻塞：
|
SQL> update test set name='ssdsdsds'where id=2;
|
會話1：
|
SQL> rollback to a; Rollback complete.
|
可以看到，雖然會話1已經(jīng)撤銷了對記錄的修改，但是會話2仍然處于等待狀態(tài)這是因?yàn)闀?是被會話1的TX鎖阻塞的，而不是被會話1上的行級鎖 阻塞(rollback to savepoint不會結(jié)束事務(wù))。
|
會話3： SQL> select username,event,sid,blocking_session from v$session where SID IN (146,159); USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR enq: TX - row lock contention 146 159 HR SQL*Net message from client 159 會話1： SQL> rollback; 會話2： SQL> update test set name='ssdsdsds'where id=2; 1 row updated. 會話3： SQL> select username,event,sid,blocking_session from v$session where username='HR'; USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR SQL*Net message from client 159 |
事務(wù)結(jié)束，tx鎖釋放，會話2update執(zhí)行成功。
行鎖，也稱為TX 鎖，是一個表中單個行上的鎖。一個事務(wù)在被INSERT、UPDATE、DELETE、MERGE、或SELECT ... FOR UPDATE等語句所修改的每一行上獲取一個行鎖。行鎖一直存在直到事務(wù)提交或回滾。行鎖主要作為一種排隊(duì)的機(jī)制，以防止兩個事務(wù)修改相同的行。數(shù)據(jù)庫始終以獨(dú)占模式鎖定修改的行，以便其它事務(wù)不能修改該行，直到持有鎖的事務(wù)提交或回滾。行鎖定提供了近乎最細(xì)粒度的鎖定，并因此提供了近乎最佳的并發(fā)性和吞吐量。
如果一個事務(wù)因?yàn)閿?shù)據(jù)庫實(shí)例失效而終止，會先進(jìn)行塊級恢復(fù)以使行可用，之后進(jìn)行整個事務(wù)恢復(fù)。
表級鎖（table-level lock）的作用是對并發(fā)的DDL操作進(jìn)行訪問控制，例如防止在DML語句執(zhí)行期間相關(guān)的表被移除。當(dāng)用戶對表執(zhí)行DDL或DML操作時，將獲取一個此表的表級鎖。表級鎖不會影響其他并發(fā)的DML操作。對于分區(qū)表來說，表級鎖既可以針對整個表，也可以只針對某個分區(qū)。
當(dāng)用戶執(zhí)行以下 DML 語句對表進(jìn)行修改：INSERT，UPDATE，DELETE，及SELECT ... FOR UPDATE，或執(zhí)行LOCK TABLE語句時，事務(wù)將獲取一個表級鎖。這些DML語句獲取表級鎖的目的有兩個：首先保證自身對表的訪問不受其它事務(wù)DML語句的干擾，其次阻止其它事務(wù)中和自身有沖突的DDL操作執(zhí)行。任何類型的表級鎖都將阻止對此表的排它DDL鎖（exclusive DDL lock），從而阻止了必須具備排它DDL鎖才能執(zhí)行的DDL操作。例如，當(dāng)一個未提交的事務(wù)擁有某個表上的鎖時，此表就無法被修改定義或被移除。
表級鎖具有以下幾種模式：行共享（row share，RS），行排它（row exclusive，RX），共享（share，S），共享行排它（share row exclusive，SRX），及排它（exclusive，X）。各種模式的表級鎖具有的限制級別決定了其是否能與其他表級鎖共處于同一數(shù)據(jù)表上。
下表顯示了各種語句所獲得的表級鎖的模式，以及此模式下被允許或禁止的操作。
ORACLE里鎖有以下幾種模式:
鎖的兼容模式如下表所示：
表鎖，也稱為TM鎖，當(dāng)一個表被INSERT、UPDATE、DELETE、MERGE、帶FOR UPDATE子句的SELECT等修改時，由相關(guān)事務(wù)獲取該鎖。DML操作需要表鎖來為事務(wù)保護(hù)DML對表的訪問，并防止可能與事務(wù)沖突的DDL操作。
表鎖可能以下列模式之一持有：
這種鎖也被稱為子共享表鎖(SS，subshare table lock)，表示在表上持有鎖的事務(wù)在表中有被鎖定的行，并打算更新它們。行共享鎖是限制最少的表級鎖模式，提供在表上最高程度的并發(fā)性。
1、 實(shí)驗(yàn)ROW SHARE模式允許同時訪問被鎖定的表，但是禁止用戶以排它方式鎖定整個表。ROW SHARE與SHARE UPDATE相同，只是為了兼容早期的Oracle版本。對應(yīng)lmode2，row-S (SS)。
版本：11.2.0.4
|
會話1： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> select userenv('sid') from dual;
USERENV('SID') -------------- 6
SYS@lhrdb S1> LOCK TABLE SCOTT.EMP IN ROW SHARE MODE;
Table(s) Locked. 會話2： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> select userenv('sid') from dual;
USERENV('SID') -------------- 114
SYS@lhrdb S2> LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE;
====>>>>>產(chǎn)生了阻塞 查詢2個會話的鎖： SYS@lhrdb S1> SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK 2 FROM V$LOCK D 3 WHERE D.SID IN (114, 6) 4 ORDER BY D.SID, D.TYPE; 當(dāng)前題目：【鎖】Oracle鎖系列 網(wǎng)站鏈接：http://weahome.cn/article/gjggid.html 其他資訊 |
重慶分公司
重慶分公司
