oracle 數(shù)據(jù)庫事務(wù),提交,回滾,保存點,表的鎖定,隱式鎖,顯示鎖,寫鎖,讀鎖,排他鎖,共享鎖

瞻云軒 2015-04-17

展開全文

數(shù)據(jù)庫事務(wù)的概念
事務(wù)是由相關(guān)操作構(gòu)成的一個完整的操作單元。兩次連續(xù)成功的COMMIT或ROLLBACK之間的操作，稱為一個事務(wù)。在一個事務(wù)內(nèi)，數(shù)據(jù)的修改一起提交或撤銷，如果發(fā)生故障或系統(tǒng)錯誤，整個事務(wù)也會自動撤銷。
比如，我們?nèi)ャy行轉(zhuǎn)賬，操作可以分為下面兩個環(huán)節(jié)：
(1) 從第一個賬戶劃出款項。
(2) 將款項存入第二個賬戶。
整個交易過程，可以看作是一個事物，成功則全部成功，失敗則需要全部撤消，這樣可以避免當(dāng)操作的中間環(huán)節(jié)出現(xiàn)問題時，產(chǎn)生數(shù)據(jù)不一致的問題。
數(shù)據(jù)庫事務(wù)是一個邏輯上的劃分，有的時候并不是很明顯，它可以是一個操作步驟，也可以是多個操作步驟。
我們可以這樣理解數(shù)據(jù)庫事物：對數(shù)據(jù)庫所做的一系列修改，在修改過程中，暫時不寫入數(shù)據(jù)庫，而是緩存起來，用戶在自己的終端可以預(yù)覽變化，直到全部修改完成，并經(jīng)過檢查確認(rèn)無誤后，一次性提交并寫入數(shù)據(jù)庫，在提交之前，必要的話所做的修改都可以取消。提交之后，就不能撤銷，提交成功后其他用戶才可以通過查詢?yōu)g覽數(shù)據(jù)的變化。
以事務(wù)的方式對數(shù)據(jù)庫進(jìn)行訪問，有如下的優(yōu)點：
* 把邏輯相關(guān)的操作分成了一個組。
* 在數(shù)據(jù)永久改變前，可以預(yù)覽數(shù)據(jù)變化。
* 能夠保證數(shù)據(jù)的讀一致性。

數(shù)據(jù)庫事務(wù)的應(yīng)用
數(shù)據(jù)庫事務(wù)處理可分為隱式和顯式兩種。顯式事務(wù)操作通過命令實現(xiàn)，隱式事務(wù)由系統(tǒng)自動完成提交或撤銷(回退)工作，無需用戶的干預(yù)。
隱式提交的情況包括：當(dāng)用戶正常退出SQL*Plus或執(zhí)行CREATE、DROP、GRANT、REVOKE等命令時會發(fā)生事務(wù)的自動提交。

還有一種情況，如果把系統(tǒng)的環(huán)境變量AUTOCOMMIT設(shè)置為ON(默認(rèn)狀態(tài)為OFF)，則每當(dāng)執(zhí)行一條INSERT、DELETE或UPDATE命令對數(shù)據(jù)進(jìn)行修改后，就會馬上自動提交。設(shè)置命令格式如下：
SET AUTOCOMMIT ON/OFF
隱式回退的情況包括：當(dāng)異常結(jié)束SQL*Plus或系統(tǒng)故障發(fā)生時，會發(fā)生事務(wù)的自動回退。
顯式事務(wù)處理的數(shù)據(jù)庫事務(wù)操作語句有3條，
COMMIT:數(shù)據(jù)庫事務(wù)提交，將變化寫入數(shù)據(jù)庫
ROLLBACK:數(shù)據(jù)庫事務(wù)回退，撤銷對數(shù)據(jù)的修改
SAVEPOINT:創(chuàng)建保存點，用于事務(wù)的階段回退

COMMIT操作把多個步驟對數(shù)據(jù)庫的修改，一次性地永久寫入數(shù)據(jù)庫，代表數(shù)據(jù)庫事務(wù)的成功執(zhí)行。ROLLBACK操作在發(fā)生問題時，把對數(shù)據(jù)庫已經(jīng)作出的修改撤消，回退到修改前的狀態(tài)。在操作過程中，一旦發(fā)生問題，如果還沒有提交操作，則隨時可以使用ROLLBACK來撤消前面的操作。SAVEPOINT則用于在事務(wù)中間建立一些保存點，ROLLBACK可以使操作回退到這些點上邊，而不必撤銷全部的操作。一旦COMMIT完成，就不能用ROLLBACK來取消已經(jīng)提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必須重新執(zhí)行相關(guān)操作語句。

如何開始一個新的事務(wù)呢？一般情況下，開始一個會話(即連接數(shù)據(jù)庫)，執(zhí)行第一條SQL語句將開始一個新的事務(wù)，或執(zhí)行COMMIT提交或ROLLBACK撤銷事務(wù)，也標(biāo)志新的事務(wù)的開始。另外，執(zhí)行DDL(如CREATE)或DCL命令也將自動提交前一個事務(wù)而開始一個新的事務(wù)。

數(shù)據(jù)在修改的時候會對記錄進(jìn)行鎖定，其他會話不能對鎖定的記錄進(jìn)行修改或加鎖，只有當(dāng)前會話提交或撤銷后，記錄的鎖定才會釋放。

觀察數(shù)據(jù)的讀一致性
步驟1：顯示剛插入的雇員小馬:

Sql代碼

SELECT empno,ename FROM emp WHERE empno=3000;

執(zhí)行結(jié)果：

Sql代碼

EMPNO ENAME
------------------ -----------
3000 小馬

步驟2：刪除雇員小馬:

Sql代碼

DELETE FROM emp WHERE empno=3000;

執(zhí)行結(jié)果：
已刪除 1 行。
步驟3：再次顯示該雇員，顯示結(jié)果為該雇員不存在：

Sql代碼

SELECT empno,ename FROM emp WHERE empno=3000;

執(zhí)行結(jié)果：
未選定行
步驟4：另外啟動第2個SQL*Plus，并以SCOTT身份連接。執(zhí)行以下命令，結(jié)果為該記錄依舊存在。

Sql代碼

SELECT empno,ename FROM emp WHERE empno=3000;

執(zhí)行結(jié)果：

Sql代碼

EMPNO ENAME
------------------ -----------
3000 小馬

步驟5：在第1個SQL*Plus中提交刪除：

Sql代碼

COMMIT;

執(zhí)行結(jié)果：
提交完成。
步驟6：在第2個SQL*Plus中再次顯示該雇員，顯示結(jié)果與步驟3的結(jié)果一致:

Sql代碼

SELECT empno,ename FROM emp WHERE empno=3000;

執(zhí)行結(jié)果：
未選定行
說明：在以上訓(xùn)練中，當(dāng)?shù)?個SQL*Plus會話刪除小馬后，第2個SQL*Plus會話仍然可以看到該雇員，直到第1個SQL*Plus會話提交該刪除操作后，兩個會話看到的才是一致的數(shù)據(jù)。

鎖的概念
鎖出現(xiàn)在數(shù)據(jù)共享的場合，用來保證數(shù)據(jù)的一致性。當(dāng)多個會話同時修改一個表時，需要對數(shù)據(jù)進(jìn)行相應(yīng)的鎖定。
鎖有“只讀鎖”、“排它鎖”，“共享排它鎖”等多種類型，而且每種類型又有“行級鎖”(一次鎖住一條記錄)，“頁級鎖”(一次鎖住一頁，即數(shù)據(jù)庫中存儲記錄的最小可分配單元)，“表級鎖”(鎖住整個表)。

排它鎖和共享鎖

排它鎖又稱為寫鎖（(Exclusive lock,簡記為X鎖)），若事務(wù)T對數(shù)據(jù)對象A加上X鎖，則只允許T讀取和修改A，其它任何事務(wù)都不能再對A加任何類型的鎖，直到T釋放A上的鎖。它防止任何其它事務(wù)獲取資源上的鎖，直到在事務(wù)的末尾將資源上的原始鎖釋放為止。在更新操作（INSERT、UPDATE 或 DELETE）過程中始終應(yīng)用排它鎖。
共享鎖又稱為讀鎖（Share lock,簡記為S鎖），若事務(wù)T對數(shù)據(jù)對象A加上S鎖，則其它事務(wù)只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。
共享鎖：由非更新（讀?。┎僮鲃?chuàng)建的鎖。其他用戶可以并發(fā)讀取數(shù)據(jù)，但任何事務(wù)都不能獲取數(shù)據(jù)上的排它鎖，直到已釋放所有共享鎖。

若為“行級排它鎖”，則除被鎖住的行外，該表中其他行均可被其他的用戶進(jìn)行修改(Update)或刪除(delete)。若為“表級排它鎖”，則所有其他用戶只能對該表進(jìn)行查詢(select)操作，而無法對其中的任何記錄進(jìn)行修改或刪除。當(dāng)程序?qū)λ龅男薷倪M(jìn)行提交(commit)或回滾(rollback)后，鎖住的資源便會得到釋放，從而允許其他用戶進(jìn)行操作。
有時，由于程序的原因，鎖住資源后長時間未對其工作進(jìn)行提交；或是由于用戶的原因，調(diào)出需要修改的數(shù)據(jù)后，未及時修改并提交，而是放置于一旁；或是由于客戶服務(wù)器方式中客戶端出現(xiàn)“死機(jī)”，而服務(wù)器端卻并未檢測到，從而造成鎖定的資源未被及時釋放，影響到其他用戶的操作。
如果兩個事務(wù)，分別鎖定一部分?jǐn)?shù)據(jù)，而都在等待對方釋放鎖才能完成事務(wù)操作，這種情況下就會發(fā)生死鎖。

隱式鎖和顯式鎖
在Oracle數(shù)據(jù)庫中，修改數(shù)據(jù)操作時需要一個隱式的獨占鎖，以鎖定修改的行，直到修改被提交或撤銷為止。如果一個會話鎖定了數(shù)據(jù)，那么第二個會話要想對數(shù)據(jù)進(jìn)行修改，只能等到第一個會話對修改使用COMMIT命令進(jìn)行提交或使用ROLLBACK命令進(jìn)行回滾撤銷后，才開始執(zhí)行。因此應(yīng)養(yǎng)成一個良好的習(xí)慣：執(zhí)行修改操作后，要盡早地提交或撤銷，以免影響其他會話對數(shù)據(jù)的修改。

對emp表的SCOTT雇員記錄進(jìn)行修改，測試隱式鎖。
步驟1：啟動第一個SQL*Plus，以SCOTT賬戶登錄數(shù)據(jù)庫(第一個會話)，修改SCOTT記錄，隱式加鎖。

Sql代碼

UPDATE emp SET sal=3500 where empno=7788;

執(zhí)行結(jié)果：
已更新 1 行。
步驟2：啟動第二個SQL*Plus，以SCOTT賬戶登錄數(shù)據(jù)庫(第二個會話)，進(jìn)行記錄修改操作。

Sql代碼

UPDATE emp SET sal=4000 where empno=7788;

執(zhí)行結(jié)果，沒有任何輸出(處于等待解鎖狀態(tài))。

步驟3：對第一個會話進(jìn)行解鎖操作：

Sql代碼

COMMIT;

步驟4：查看第二個會話，此時有輸出結(jié)果：
已更新 1 行。
步驟5：提交第二個會話，防止長時間鎖定。
說明：兩個會話對同一表的同一條記錄進(jìn)行修改。步驟1修改SCOTT工資為3500，沒有提交或回滾之前，SCOTT記錄處于加鎖狀態(tài)。步驟2的第二個會話對SCOTT進(jìn)行修改處于等待狀態(tài)。
步驟3解鎖之后(即第一個會話對SCOTT的修改已經(jīng)完成)，第二個會話掛起的修改此時可以執(zhí)行。最后結(jié)果為第二個會話的修改結(jié)果，即SCOTT的工資修改為4000。讀者可以使用查詢語句檢查。
以上是隱式加鎖，用戶也可以使用如下兩種方式主動鎖定行或表，防止其他會話對數(shù)據(jù)的修改。

SELECT FOR UPDATE:鎖定表行，防止其他會話對行的修改
LOCK TABLE:鎖定表，防止其他會話對表的修改

對emp表的部門10的雇員記錄加顯式鎖，并測試。
步驟1：對部門10加顯式鎖：

Sql代碼

SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;

結(jié)果為：

Sql代碼

EMPNO ENAME JOB SAL
------------------ -------------- ------------------ ------------------
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7934 MILLER CLERK 1300

步驟2：啟動第二個SQL*Plus(第二個會話)，以SCOTT賬戶登錄數(shù)據(jù)庫，對部門10的雇員CLARK進(jìn)行修改操作。

Sql代碼

UPDATE emp SET sal=sal+100 where empno=7782;

執(zhí)行結(jié)果：
沒有任何輸出(處于等待解鎖狀態(tài))。
步驟3：在第一個會話進(jìn)行解鎖操作：

Sql代碼

COMMIT;

步驟4：查看第二個會話，有輸出結(jié)果：
已更新 1 行。
說明：步驟1對選定的部門10的雇員加鎖，之后其他會話不能對部門10的雇員數(shù)據(jù)進(jìn)行修改或刪除。如果此時要進(jìn)行修改或刪除，則會處于等待狀態(tài)。使用COMMIT語句進(jìn)行解鎖之后，如果有掛起的修改或刪除操作，則等待的操作此時可以執(zhí)行。
鎖定表
LOCK語句用于對整張表進(jìn)行鎖定。語法如下：
LOCK TABLE 表名 IN {SHARE|EXCLUSIVE} MODE
對表的鎖定可以是共享(SHARE)或獨占(EXCLUSIVE)模式。共享模式下，其他會話可以加共享鎖，但不能加獨占鎖。在獨占模式下，其他會話不能加共享或獨占鎖。
【訓(xùn)練1】對emp表添加獨占鎖。
步驟1：對emp表加獨占鎖：

Sql代碼