第7章 事務與并發(fā)控制

1、1第7章 事務與并發(fā)控制 當用戶建立與數據庫的會話后，用戶就可以對數據庫進行操作，而用戶對數據庫的操作是通過一個個事務來進行的。事務確保用戶對數據庫邏輯操作的完整性和一致性，這里的邏輯操作是指用戶根據業(yè)務邏輯而進行的一系列操作。那什么是事務？ 2本章學習目標： 事務的概念，主要了解事務的ACID特性、處理過程。在Oracle中設置事務的隔性層事務處理語句事務的并發(fā)控制最基本鎖的作用和使用死鎖的發(fā)生鎖定的多粒度性Oracle中的多粒度意向鎖 37.1 了解事務 在介紹Oracle 10g的事務處理之前，首先需要理解什么是數據庫中的事務。事務其實是一個很簡單的概念，用戶每天都會遇到許多現實生活中類

2、似事務的示例。例如，商業(yè)活動的中的交易，對于任何一筆交易來說，都涉及兩個基本動作：一手交錢和一手交貨。這兩個動作構成了一個完整的商業(yè)交易，缺一不可。也就是說，這兩個動作都成功發(fā)生，說明交易完成；如果只發(fā)生一個動作，則交易失敗。所以，為了保證交易能夠正常完成，需要某種方法來保證這些操作的整體性，即這些操作要么都成功，要么都失敗。 事務是數據庫中工作的最小單位，一個事務可以由一個或多個完成一組相關行為的SQL語句組成，通過相應機制保證這組語句所作的操作要么全做，要么全部都不做。是一個不可分割的整體，如果一個操作出錯，整個事務會結束，保證完整性。數據庫事務 事務是由相關操作構成的一個完整的操作單元。

3、兩次連續(xù)成功的COMMIT或ROLLBACK之間的操作，稱為一個事務。在一個事務內，數據的修改一起提交或撤銷，如果發(fā)生故障或系統錯誤，整個事務也會自動撤銷。我們去銀行轉賬，操作可以分為下面兩個環(huán)節(jié)：(1) 從第一個賬戶劃出款項。(2) 將款項存入第二個賬戶。在這個過程中，兩個環(huán)節(jié)是關聯的。第一個賬戶劃出款項必須保證正確的存入第二個賬戶，如果第二個環(huán)節(jié)沒有完成，整個的過程都應該取消，否則就會發(fā)生丟失款項的問題。整個交易過程，可以看作是一個事物，成功則全部成功，失敗則需要全部撤消，這樣可以避免當操作的中間環(huán)節(jié)出現問題時，產生數據不一致的問題。數據庫事務是一個邏輯上的劃分，有的時候并不是很明顯，它可

4、以是一個操作步驟，也可以是多個操作步驟。我們可以這樣理解數據庫事務：對數據庫所做的一系列修改，在修改過程中，暫時不寫入數據庫，而是緩存起來，用戶在自己的終端可以預覽變化，直到全部修改完成，并經過檢查確認無誤后，一次性提交并寫入數據庫，在提交之前，必要的話所做的修改都可以取消。提交之后，就不能撤銷，提交成功后其他用戶才可以通過查詢?yōu)g覽數據的變化。以事務的方式對數據庫進行訪問，有如下的優(yōu)點：把邏輯相關的操作分成了一個組。在數據永久改變前，可以預覽數據變化。能夠保證數據的讀一致性。77.2 事務的ACID特性 一組SQL語句操作要成為事務，數據庫管理系統必須保證這組操作的原子性（Atomicity）

5、、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這就是事務的ACID特性。 原子性指事務中包含操作要么全做要么全不做，作為一個不可分割的整體存在。銀行轉賬過程：A：2000 取1000沒存入B時，A+B=4000少了1000，但事務結束后應該繼續(xù)保持5000，只是A成為1000，B成為4000.整體是完整的。B：3000 示例：兩個更新語句更新數據后，如果提交，作為一個完整整體，不能分割的。 一致性指事務執(zhí)行前后，數據從一種一致性狀態(tài)變?yōu)榱硗庖环N一致性狀態(tài)，即必須滿足業(yè)務規(guī)則約束。比如轉賬前后總金額保持不變。事務處理中間，數據可能會產生短暫的不

6、一致。隔離性指數據庫允許多個并發(fā)事務同時對其中數據進行讀寫和修改的能力，可以防止多個事務并發(fā)執(zhí)行時，由于命令交叉執(zhí)行而導致的數據不一致性。A向B轉賬中間，A又轉入金額，違背了一致性，隔離性保證不會出現這種情況。持久性指事務結束后，對數據的修改應該是永久的，即便遇到故障也不會丟失。127.3 事務控制語句 在Oracle中沒有提供開始事務處理語句，所有的事務都是隱式開始的。也就是說，在Oracle中用戶不可以顯式使用命令來開始一個事務。Oracle認為第一條修改數據庫的語句，或者一些要求事務處理的場合都是事務隱式的開始。但是，當用戶想要終止一個事務處理時，必須顯式使用COMMIT和ROLLBAC

7、K語句結束。Oracle中事務相關操作：Set transactionSet constraintSavepointRollbackcommit數據異常丟失修改（錯讀）丟失修改（錯讀）不可重讀讀讀臟臟數據數據(假讀)選擇隔離層SQL92隔離級別。4個Oracle支持兩個：Read COMMITTED （預防錯讀）-默認級別SERIALIZABLE（串行讀）Oracle支持的其他級別Read onlyRead write設置事務屬性必須位于事務第一個語句。事務結束設置自動消失?？稍O置的屬性：指定事務隔離層規(guī)定回滾事務時使用的存儲空間（很少使用）命名事務（分布式事務中使用）建立set transa

8、ction語句Set transaction read only避免非重復讀和假讀Set transaction read write（默認）Set transaction isolation level read committed（適合并發(fā)用戶多情況）Set transaction isolation level serializable（適合用戶少情況）幾個選項是互斥的。結束事務結束事務的幾種情況：CommitRollback斷開了連接（exit）commit;進程意外終止（rollback）Commit提交事務需要完成如下任務：記錄事務提交到回滾段，產生scn啟動lgwr，日志和scn

9、號保存到日志服務器進程釋放事務處理進程中所使用的資源通知用戶事務已經成功提交回滾事務Rollback語句?；貪L需要很大系統開銷，有必要時才回滾。存儲點事務非常龐大時，可以使用存儲點，從而使回滾操作可以實現部分事務的回滾。Savepoint 存儲點名字數據庫事務處理可分為隱式和顯式兩種。顯式事務操作通過命令實現，隱式事務由系統自動完成提交或撤銷(回退)工作，無需用戶的干預。隱式提交的情況包括：當用戶正常退出SQL*Plus或執(zhí)行CREATE、DROP、GRANT、REVOKE等命令時會發(fā)生事務的自動提交。2 數據庫事務的應用還 有 一 種 情 況 ， 如 果 把 系 統 的 環(huán) 境 變 量AUT

10、OCOMMIT設置為ON(默認狀態(tài)為OFF)，則每當執(zhí)行一條INSERT、DELETE或UPDATE命令對數據進行修改后，就會馬上自動提交。設置命令格式如下：SET AUTOCOMMIT ON/OFF隱式回退的情況包括：當異常結束SQL*Plus或系統故障發(fā)生時，會發(fā)生事務的自動回退。顯式事務處理的數據庫事務操作語句有3條，如表3-2所示。數據在修改的時候會對記錄進行鎖定，其他會話不能對鎖定的記錄進行修改或加鎖，只有當前會話提交或撤銷后，記錄的鎖定才會釋放。詳細內容見下一節(jié)。我們通過以下的訓練來為雇員SCOTT增加工資，SCOTT的雇員號為7788。 表2 事務控制語句語 句 描 述 COMM

11、IT 數據庫事務提交，將變化寫入數據庫 ROLLBACK 數據庫事務回退，撤銷對數據的修改 SAVEPOINT 創(chuàng)建保存點，用于事務的階段回退 COMMIT操作把多個步驟對數據庫的修改，一次性地永久寫入數據庫，代表數據庫事務的成功執(zhí)行。ROLLBACK操作在發(fā)生問題時，把對數據庫已經作出的修改撤消，回退到修改前的狀態(tài)。在操作過程中，一旦發(fā)生問題，如果還沒有提交操作，則隨時可以使用ROLLBACK來撤消前面的操作。SAVEPOINT則用于在事務中間建立一些保存點，ROLLBACK可以使操作回退到這些點上邊，而不必撤銷全部的操作。一旦COMMIT完成，就不能用ROLLBACK來取消已經提交的操作。

12、一旦ROLLBACK完成，被撤消的操作要重做，必須重新執(zhí)行相關操作語句。如何開始一個新的事務呢？一般情況下，開始一個會話(即連接數據庫)，執(zhí)行第一條SQL語句將開始一 個 新 的 事 務 ， 或 執(zhí) 行 C O M M I T 提 交 或ROLLBACK撤銷事務，也標志新的事務的開始。另外，執(zhí)行DDL(如CREATE)或DCL命令也將自動提交前一個事務而開始一個新的事務?！居柧?】 學習使用COMMIT和ROLLBACK。步驟1：執(zhí)行以下命令，提交尚未提交的操作：COMMIT;執(zhí)行結果：提交完成。顯示SCOTT的現有工資：SELECT ename,sal FROM emp WHERE empn

13、o=7788;執(zhí)行結果：ENAME SAL- -SCOTT 3000步驟2：修改雇員SCOTT的工資：UPDATE emp SET sal=sal+100 WHERE empno=7788;執(zhí)行結果：已更新1行。顯示修改后的SCOTT的工資：SELECT ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL- -SCOTT 3100步驟3：假定修改操作后發(fā)現增加的工資應該為1000而不是100，為了取消剛做的操作，可以執(zhí)行以下命令：ROLLBACK;執(zhí)行結果：回退已完成。顯示回退后SCOTT的工資恢復為3000：SELECT ename,sal

14、FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL- -SCOTT 3000步驟4：重新修改雇員SCOTT的工資，工資在原有基礎上增加1000：UPDATE emp SET sal=sal+1000 WHERE empno=7788;執(zhí)行結果：已更新 1 行。顯示修改后SCOTT的工資：SELECT ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：ENAME SAL- -SCOTT 4000步驟5：經查看修改結果正確，提交所做的修改：COMMIT;執(zhí)行結果：提交完成。 說明：在執(zhí)行COMMIT后，工資的修改被永久寫入數據庫。本訓練

15、的第1步，先使用COMMIT命令提交原來的操作，同時標志一個新的事務的開始。注意：在事務執(zhí)行過程中，隨時可以預覽數據的變化。對于比較大的事務，可以使用SAVEPOINT命令在事務中間劃分一些斷點，用來作為回退點?！居柧?】 學習使用SAVEPOINT命令。步驟1：插入一個雇員：INSERT INTO emp(empno, ename, job)VALUES (3000, 小馬,STUDENT);執(zhí)行結果：已創(chuàng)建 1 行。步驟2：插入保存點，檢查點的名稱為PA：SAVEPOINT pa;執(zhí)行結果：保存點已創(chuàng)建。步驟3：插入另一個雇員：INSERT INTO emp(empno, ename, j

16、ob)VALUES (3001, 小黃,STUDENT);執(zhí)行結果：已創(chuàng)建 1 行。 步驟4：回退到保存點PA，則后插入的小黃被取消，而小馬仍然保留。ROLLBACK TOpa;執(zhí)行結果：回退已完成。步驟5: 提交所做的修改：COMMIT;執(zhí)行結果：提交完成。說明：第4步的回退，將回退到保存點PA，即第3步被撤銷。所以最后的COMMIT只提交了對小馬的插入。請自行檢查插入的雇員?！揪毩?】對emp表進行修改，然后退出SQL*Plus，重新啟動SQL*Plus，檢查所做的修改是否生效。在Oracle數據庫中，有一個叫回滾段的特殊的存儲區(qū)域。在提交一個事物之前，如果用戶進行了數據的修改，在所謂的回

17、滾段中將保存變化前的數據。有了回滾段才能在必要時使用ROLLBACK命令或自動地進行數據撤銷。在提交事物之前，用戶自己可以看到修改的數據，但因為修改還沒有最終提交，其他用戶看到的應該是原來的數據，也就是回滾段中的數據，這時用戶自己看到的數據和其他用戶看到的數據是不同的，只有提交發(fā)生后，變化的數據才會被寫入數據庫，此時用戶自己看到的數據和其他用戶看到的數據才是一致的，這叫做數據的讀一致性。【訓練3】 觀察數據的讀一致性。步驟1：顯示剛插入的雇員小馬:SELECT empno,ename FROM emp WHERE empno=3000;執(zhí)行結果： EMPNO ENAME- - 3000 小馬步

18、驟2：刪除雇員小馬:DELETE FROM emp WHERE empno=3000;執(zhí)行結果：已刪除 1 行。步驟3：再次顯示該雇員，顯示結果為該雇員不存在：SELECT empno,ename FROM emp WHERE empno=3000;執(zhí)行結果：未選定行 步驟4：另外啟動第2個SQL*Plus，并以SCOTT身份連接。執(zhí)行以下命令，結果為該記錄依舊存在。SELECT empno,ename FROM emp WHERE empno=3000;執(zhí)行結果： EMPNO ENAME- - 3000 小馬步驟5：在第1個SQL*Plus中提交刪除：COMMIT;執(zhí)行結果：提交完成。步驟6

19、：在第2個SQL*Plus中再次顯示該雇員，顯示結果與步驟3的結果一致:SELECT empno,ename FROM emp WHERE empno=3000;執(zhí)行結果：未選定行 說明：在以上訓練中，當第1個SQL*Plus會話刪除小馬后，第2個SQL*Plus會話仍然可以看到該雇員，直到第1個SQL*Plus會話提交該刪除操作后，兩個會話看到的才是一致的數據。397.4 并發(fā)控制 對于多用戶數據庫系統而言，當多個用戶并發(fā)地操作時，會產生多個事務同時操作同一數據的情況。若對并發(fā)操作不加控制就可能會發(fā)生讀取和寫入不正確的數據，破壞數據庫的一致性。所以數據庫管理系統必須提供并發(fā)控制機制。因此，一

20、個數據庫管理系統性能的優(yōu)劣，很大一部分取決于并發(fā)控制。并發(fā)控制是指使用正確的方式實現事務的并發(fā)操作，避免數據的不一致性。主要通過鎖機制保證并發(fā)控制的。鎖是控制共享資源并發(fā)訪問的一種機制。由oracle系統自動管理。用戶也可以給資源手工加鎖，封鎖開始于事務開始，結束于事務結束。鎖的分類：DDL鎖，自動發(fā)布和釋放DML鎖：事務開始時施加，結束時釋放內部鎖（保護內部數據庫結構的鎖，oracle來自動管理）注：前兩者可以由用戶直接或間接控制鎖模式Oracle中鎖的模式：S鎖共享鎖X鎖排他鎖RS鎖行級共享鎖RX鎖行級排他鎖SRX鎖共享行級排他鎖鎖的相容性：兩個鎖能否同時作用于同一內容：p328所示DML

21、語句自動使用RX鎖，DDLcreate使用S鎖，alter使用X鎖Lock table語句 +表名+in row share mode鎖模式的驗證7.5 鎖粒度 鎖粒度是指被鎖定的數據對象的大小。鎖粒度與數據庫系統的并發(fā)度和并發(fā)控制的開銷密切相關。鎖粒度越大，數據庫中所能夠使用的資源也就越少，并發(fā)度也就越小，系統開銷也就越??；反之，鎖的粒度越小，并發(fā)度也就越大，但系統的開銷也就越大。 選擇鎖粒度時要對并發(fā)度和系統開銷進行權衡。一般情況下，事務中要處理大量記錄時，鎖粒度應為表級。要處理多個表中大量記錄時，一般應為數據庫級。對于少量記錄的處理，為行級。分四個級別：數據庫級表級行級列級TX鎖和TM鎖

22、TX-事務鎖（行級鎖），事務執(zhí)行數據更改操作獲得TX鎖，直至事務結束，釋放，可以鎖定多行。行級別沒有S鎖，只有X鎖TM鎖表級鎖，五種模式數據庫級鎖Alter system enable restricted session；-其他用戶不允許登陸。 sysdba權限取消限制： Alter system disable restricted sessionAlter database open read only；-先執(zhí)行shutdown immediate，然后執(zhí)行startup mount已經連接的用戶不會受到前者語句的影響?？梢允褂胊lter system quiesce restricte

23、d(靜默模式)語句從用戶活動中鎖定數據庫。此模式下，其他用戶登錄和執(zhí)行sql語句都不提示錯誤，而是一直等待。在命令行表現為語句或登錄行為停著不動了，不被執(zhí)行 .取消靜默：ALTER SYSTEM UNQUIESCE ；sysdba權限7.6 查詢鎖 Oracle將當前鎖的信息存儲在數據字典的動態(tài)性能視圖V$LOCK和V$LOCKED_OBJECT中。其中，V$LOCK視圖列出當前系統持有的，或者正在申請的所有鎖的情況，其主要字段說明如表7-6所示： V$locked_object列出哪些對象正被鎖定。OEM工具查看鎖：主目錄性能其他監(jiān)視鏈接-數據庫鎖7.7 死鎖 如果對數據對象加鎖不當，就會發(fā)

24、生死鎖。死鎖是指在兩個以上的事務中，每個事務都因為試圖加鎖當前已被另一個事務加鎖的數據項，從而造成的相互等待現象。 3 表的鎖定1 鎖的概念鎖出現在數據共享的場合，用來保證數據的一致性。當多個會話同時修改一個表時，需要對數據進行相應的鎖定。鎖有“只讀鎖”、“排它鎖”，“共享排它鎖”等多種類型，而且每種類型又有“行級鎖”(一次鎖住一條記錄)，“頁級鎖”(一次鎖住一頁，即數據庫中存儲記錄的最小可分配單元)，“表級鎖”(鎖住整個表)。 若為“行級排它鎖”，則除被鎖住的行外，該表中其他行均可被其他的用戶進行修改(Update)或刪除(delete)。若為“表級排它鎖”，則所有其他用戶只能對該表進行查詢

25、(select)操作，而無法對其中的任何記錄進行修改或刪除。當程序對所做的修改進行提交(commit)或回滾(rollback)后，鎖住的資源便會得到釋放，從而允許其他用戶進行操作。 有時，由于程序的原因，鎖住資源后長時間未對其工作進行提交；或是由于用戶的原因，調出需要修改的數據后，未及時修改并提交，而是放置于一旁；或是由于客戶服務器方式中客戶端出現“死機”，而服務器端卻并未檢測到，從而造成鎖定的資源未被及時釋放，影響到其他用戶的操作。如果兩個事務，分別鎖定一部分數據，而都在等待對方釋放鎖才能完成事務操作，這種情況下就會發(fā)生死鎖。2 隱式鎖和顯式鎖在Oracle數據庫中，修改數據操作時需要一個

26、隱式的獨占鎖，以鎖定修改的行，直到修改被提交或撤銷為止。如果一個會話鎖定了數據，那么第二個會話要想對數據進行修改，只能等到第一個會話對修改使用COMMIT命令進行提交或使用ROLLBACK命令進行回滾撤銷后，才開始執(zhí)行。因此應養(yǎng)成一個良好的習慣：執(zhí)行修改操作后，要盡早地提交或撤銷，以免影響其他會話對數據的修改?！居柧?】 對emp表的SCOTT雇員記錄進行修改，測試隱式鎖。步驟1：啟動第一個SQL*Plus，以SCOTT賬戶登錄數據庫(第一個會話)，修改SCOTT記錄，隱式加鎖。UPDATE emp SET sal=3500 where empno=7788;執(zhí)行結果：已更新 1 行。步驟2：

27、啟動第二個SQL*Plus，以SCOTT賬戶登錄數據庫(第二個會話)，進行記錄修改操作。UPDATE emp SET sal=4000 where empno=7788;執(zhí)行結果，沒有任何輸出(處于等待解鎖狀態(tài))。步驟3：對第一個會話進行解鎖操作：COMMIT;步驟4：查看第二個會話，此時有輸出結果：已更新 1 行。步驟5：提交第二個會話，防止長時間鎖定。說明：兩個會話對同一表的同一條記錄進行修改。步驟1修改SCOTT工資為3500，沒有提交或回滾之前，SCOTT記錄處于加鎖狀態(tài)。步驟2的第二個會話對SCOTT進行修改處于等待狀態(tài)。步驟3解鎖之后(即第一個會話對SCOTT的修改已經完成)，第二

28、個會話掛起的修改此時可以執(zhí)行。最后結果為第二個會話的修改結果，即SCOTT的工資修改為4000。讀者可以使用查詢語句檢查。以上是隱式加鎖，用戶也可以使用如下兩種方式主動鎖定行或表，防止其他會話對數據的修改。表3-3是對行或表進行鎖定的語句。表3 表的顯式鎖定操作語句語 句 描 述 SELECT FOR UPDATE 鎖定表行，防止其他會話對行的修改 LOCK TABLE 鎖定表，防止其他會話對表的修改 3 鎖定行 【訓練1】 對emp表的部門10的雇員記錄加顯式鎖，并測試。步驟1：對部門10加顯式鎖：SELECT empno,ename,job,sal FROM emp WHERE deptn

29、o=10 FOR UPDATE;結果為： EMPNO ENAME JOB SAL- - - - 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7934 MILLER CLERK 700步驟2：啟動第二個SQL*Plus(第二個會話)，以SCOTT賬戶登錄數據庫，對部門10的雇員CLARK進行修改操作。UPDATE emp SET sal=sal+100 where empno=7782;執(zhí)行結果：沒有任何輸出(處于等待解鎖狀態(tài))。步驟3：在第一個會話進行解鎖操作：COMMIT;步驟4：查看第二個會話，有輸出結果：已更新 1 行。說明：步驟1對

30、選定的部門10的雇員加鎖，之后其他會話不能對部門10的雇員數據進行修改或刪除。如果此時要進行修改或刪除，則會處于等待狀態(tài)。使用COMMIT語句進行解鎖之后，如果有掛起的修改或刪除操作，則等待的操作此時可以執(zhí)行。4 鎖定表LOCK語句用于對整張表進行鎖定。語法如下：LOCK TABLE 表名 IN SHARE|EXCLUSIVE MODE對表的鎖定可以是共享(SHARE)或獨占(EXCLUSIVE)模式。共享模式下，其他會話可以加共享鎖，但不能加獨占鎖。在獨占模式下，其他會話不能加共享或獨占鎖?！居柧?】 對emp表添加獨占鎖。步驟1：對emp表加獨占鎖：LOCK TABLE emp IN EX

31、CLUSIVE MODE;結果為：表已鎖定。步驟2：對表進行解鎖操作：COMMIT;說明：當使用LOCK語句顯式鎖定一張表時，死鎖的概率就會增加。同樣地，使用COMMIT或ROLLBACK命令可以釋放鎖。 注意：必須沒有其他會話對該表的任何記錄加鎖，此操作才能成功?！揪毩?】通過兩個會話以共享方式鎖定dept表，然后分別釋放。階段訓練【訓練1】 以數據庫事務方式將SCOTT從emp表轉入manager表，再將SCOTT的工資改成和emp表的KING的工資一樣。步驟1：復制emp表的SCOTT到manager表：INSERT INTO manager SELECT empno,ename,sal FROM emp WHERE empno=7788;執(zhí)行結果：已創(chuàng)建 1 行。步驟2：刪除emp表的SCOTT：DELETE FROM emp WHERE empno=7788;執(zhí)行結果：已刪除 1 行。步驟3：修改SCOTT的工資：UPD