第12章事務(wù)處理與并發(fā)控制

1、Oracle 數(shù)據(jù)數(shù)據(jù)庫管理庫管理與應(yīng)用與應(yīng)用 本章要點(diǎn)事務(wù)的概念與特性事務(wù)的概念與特性 1常見事務(wù)管理命令常見事務(wù)管理命令 2并發(fā)控制與鎖并發(fā)控制與鎖 3 312.1 事務(wù)概述事務(wù)概述 v1事務(wù)的概念事務(wù)的概念 在日常生活和工作中，人們?yōu)榱送瓿梢欢ǖ娜蝿?wù)都會(huì)執(zhí)行一系列邏輯上相關(guān)的操作。這些操作共同影響著任務(wù)的實(shí)現(xiàn)結(jié)果，在Oracle中把這些操作的集操作的集合看作一個(gè)事務(wù)（合看作一個(gè)事務(wù)（TRANSACTION） 。 Oracle中，用戶不能顯示的開始不能顯示的開始一個(gè)事務(wù)，一般在上一個(gè)事務(wù)結(jié)束（被提交或被回滾）后，新事務(wù)會(huì)隱式的在修改數(shù)據(jù)的第一條語句處開始。 vOracle中的事務(wù)結(jié)束會(huì)發(fā)生

2、在： 用戶使用用戶使用COMMIT命令顯示提交事務(wù)。命令顯示提交事務(wù)。 用戶使用用戶使用ROLLBACK命令回滾整個(gè)事務(wù)。命令回滾整個(gè)事務(wù)。 用戶執(zhí)行了一條用戶執(zhí)行了一條DDL語句。語句。 用戶正常斷開了與用戶正常斷開了與Oracle的連接，這時(shí)用戶當(dāng)?shù)倪B接，這時(shí)用戶當(dāng)前的事務(wù)將被自動(dòng)提交。前的事務(wù)將被自動(dòng)提交。 用戶進(jìn)程意外被終止，這時(shí)用戶當(dāng)前的事務(wù)被用戶進(jìn)程意外被終止，這時(shí)用戶當(dāng)前的事務(wù)被回滾?；貪L。 用戶關(guān)閉用戶關(guān)閉SQL*PLUS會(huì)話時(shí)，默認(rèn)使用會(huì)話時(shí)，默認(rèn)使用ROLLBACK回滾事務(wù)。回滾事務(wù)。12.2 事務(wù)的特性事務(wù)的特性 v數(shù)據(jù)庫中的事務(wù)具有ACID屬性： 原子性原子性(Atom

3、icity) 一致性一致性(Consistency) 隔離性隔離性(Isolation) 持久性持久性(Durability)12.2.1 原子性原子性 v事務(wù)的原子性是指構(gòu)成事務(wù)的所有語句要事務(wù)的原子性是指構(gòu)成事務(wù)的所有語句要么都成功執(zhí)行，要么都失敗，不會(huì)有部分么都成功執(zhí)行，要么都失敗，不會(huì)有部分成功部分失敗的情況發(fā)生。成功部分失敗的情況發(fā)生。 事務(wù)是一個(gè)最基本的執(zhí)行單元。 事務(wù)的原子性可以分為以下級(jí)別：事務(wù)的原子性可以分為以下級(jí)別：v1語句級(jí)原子性語句級(jí)原子性 指每條語句本身也是最小級(jí)別的事務(wù)，該語句要么完全執(zhí)行成功，要么完全失敗，并且它不會(huì)影響其他語句的執(zhí)行。 實(shí)際上，Oracle在每條

4、被執(zhí)行的語句前都隱式設(shè)置了保存點(diǎn)（SAVEPOINT）。如語句INSERT INTO table1 VALUES(1); 可以理解為： SAVEPOINT statement1; INSERT INTO table1 VALUES(1); IF ERROR THEN ROLLBACK TO statement1; v例12.1 建立表t1，并且設(shè)置檢查約束，然后向表中添加3條記錄，其中2條滿足約束條件1條不滿足約束條件，提交后查詢表中的記錄。CREATE TABLE t1(id NUMBER CHECK(id0); INSERT INTO t1 VALUES(1); INSERT INTO t

5、1 VALUES(-1); INSERT INTO t1 VALUES(2);COMMIT; -提交事務(wù)提交事務(wù) SELECT * FROM t1; ID- 1 2v另外，語句級(jí)原子性還表現(xiàn)在該語句的任語句級(jí)原子性還表現(xiàn)在該語句的任何副作用何副作用（如被它觸發(fā)的觸發(fā)器）也被認(rèn)也被認(rèn)為是該語句的一部分。為是該語句的一部分。 也就是說如果該語句成功，受它影響的其他操作也將成功；如果該語句失敗并回滾，受它影響的其他操作也將被自動(dòng)回滾。 如下例所示。 v例12.2 在例12.1的基礎(chǔ)上為表t1創(chuàng)建插入操作的事前行級(jí)觸發(fā)器，并創(chuàng)建表t2用于統(tǒng)計(jì)t1表中的行數(shù)。步驟步驟1 1：創(chuàng)建表：創(chuàng)建表t2t2，并插

6、入初始值，并插入初始值2 2。 CREATE TABLE t2(r NUMBER); INSERT INTO t2 VALUES(2);步驟步驟2 2：為表：為表t1 t1創(chuàng)建插入觸發(fā)器創(chuàng)建插入觸發(fā)器 CREATE OR REPLACE TRIGGER insert_t1 BEFORE INSERT ON t1 FOR EACH ROWBEGIN UPDATE t2 SET r=r+1; dbms_output.put_line(插入觸發(fā)器被觸發(fā)插入觸發(fā)器被觸發(fā));END; 步驟步驟3 3：向：向t1 t1表中插入不滿足約束條件的值表中插入不滿足約束條件的值“-1 -1”INSERT INTO

7、 t1 VALUES(-1); 執(zhí)行結(jié)果如圖12-1所示 ： -查詢表查詢表t2中的記錄的個(gè)數(shù)中的記錄的個(gè)數(shù)SELECT * FROM t2; R- 2修改修改t2表表的的update操作已經(jīng)操作已經(jīng)被執(zhí)行被執(zhí)行修改修改t2表表的的update操作已經(jīng)操作已經(jīng)被回滾被回滾v2過程級(jí)原子性過程級(jí)原子性 過程級(jí)原子性是指Oracle把PL/SQL匿名過程塊也當(dāng)作是語句，當(dāng)作一個(gè)整體，過程中的所有代碼要么都執(zhí)行成功，要么都執(zhí)行失敗，并且不影響過程外的其他語句。 v例12.3 利用INSERT語句和一個(gè)匿名過程塊分別向例12.1創(chuàng)建的表t1中插入新數(shù)據(jù)。-利用INSERT命令向表t1中插入數(shù)據(jù)3INS

8、ERT INTO t1 VALUES(3);-利用匿名塊向表t1中插入數(shù)據(jù)4和-1BEGININSERT INTO t1 VALUES(4);INSERT INTO t1 VALUES(-1);END;-提交COMMIT;-查詢t1表中的數(shù)據(jù)SELECT * FROM t1; ID- 1 2 3過程中被插入過程中被插入的新數(shù)據(jù)均被的新數(shù)據(jù)均被回滾回滾v3事務(wù)級(jí)原子性事務(wù)級(jí)原子性 事務(wù)級(jí)原子性是指事務(wù)級(jí)原子性是指Oracle把整個(gè)事務(wù)中的所有把整個(gè)事務(wù)中的所有語句和匿名塊都當(dāng)作一個(gè)整體，一個(gè)事務(wù)。語句和匿名塊都當(dāng)作一個(gè)整體，一個(gè)事務(wù)。 用戶在提交或回滾事務(wù)時(shí)，要么所有語句都執(zhí)用戶在提交或回滾事務(wù)

9、時(shí)，要么所有語句都執(zhí)行，要么都失敗。行，要么都失敗。 事務(wù)級(jí)原子性中包含了語句級(jí)原子性和過程級(jí)事務(wù)級(jí)原子性中包含了語句級(jí)原子性和過程級(jí)原子性，整個(gè)事務(wù)中的語句或匿名塊首先受語原子性，整個(gè)事務(wù)中的語句或匿名塊首先受語句級(jí)原子性和過程級(jí)原子性的影響。句級(jí)原子性和過程級(jí)原子性的影響。v例12.4 利用例12.1創(chuàng)建的表t1，向表中添加兩行新數(shù)據(jù)4和5，然后提交事務(wù)，最后查詢表中的記錄。INSERT INTO t1 VALUES(4);INSERT INTO t1 VALUES(5);COMMIT;SELECT * FROM t1; ID- 1 2 3 4v 5事務(wù)中的事務(wù)中的所有語句所有語句均執(zhí)行成

10、均執(zhí)行成功功一個(gè)事務(wù)一個(gè)事務(wù)v例12.5 利用例12.1創(chuàng)建的表t1，向表中添加兩行新數(shù)據(jù)6和7，然后回滾事務(wù)，最后查詢表中的記錄。INSERT INTO t1 VALUES(6);INSERT INTO t1 VALUES(7);ROLLBACK;SELECT * FROM t1; ID- 1 2 3 4 5一個(gè)事務(wù)一個(gè)事務(wù)事務(wù)中的事務(wù)中的所有語句所有語句均被回滾均被回滾12.2.2 一致性一致性v事務(wù)的一致性是指數(shù)據(jù)庫在事務(wù)操作前滿事務(wù)的一致性是指數(shù)據(jù)庫在事務(wù)操作前滿足一定的業(yè)務(wù)處理規(guī)則，事務(wù)操作后也要足一定的業(yè)務(wù)處理規(guī)則，事務(wù)操作后也要滿足這樣的業(yè)務(wù)處理規(guī)則。滿足這樣的業(yè)務(wù)處理規(guī)則。 v

11、參見例12.6 。12.2.3 隔離性隔離性 v事務(wù)的隔離性是指：當(dāng)多個(gè)用戶、多個(gè)會(huì)事務(wù)的隔離性是指：當(dāng)多個(gè)用戶、多個(gè)會(huì)話同時(shí)訪問話同時(shí)訪問Oracle數(shù)據(jù)庫的時(shí)候，他們之?dāng)?shù)據(jù)庫的時(shí)候，他們之間是未知的、不可見的、互不影響的。間是未知的、不可見的、互不影響的。 由于事務(wù)具有隔離性才能保證由于事務(wù)具有隔離性才能保證Oracle實(shí)現(xiàn)多用實(shí)現(xiàn)多用戶多會(huì)話的并發(fā)處理。戶多會(huì)話的并發(fā)處理。 v例12.7 打開兩個(gè)SQL*Plus窗口模擬多用戶的并發(fā)操作。使用例12.6中創(chuàng)建的table1表分別在兩個(gè)窗口中執(zhí)行插入操作和查詢操作，在插入操作提交前，兩個(gè)窗口中看到的數(shù)據(jù)不相同。在窗口1中執(zhí)行插入命令和查詢命

12、令，代碼和執(zhí)行結(jié)果如下：INSERT INTO table1 VALUES(4);SELECT * FROM table1; ID- 2 3 4在窗口在窗口2中執(zhí)行查詢命令，執(zhí)行結(jié)果中執(zhí)行查詢命令，執(zhí)行結(jié)果如下：如下：SELECT * FROM table1; ID- 2 3數(shù)據(jù)不同，數(shù)據(jù)不同，事務(wù)隔離，事務(wù)隔離，互不影響互不影響v事務(wù)的隔離性不允許多個(gè)會(huì)話同時(shí)修改相同的數(shù)事務(wù)的隔離性不允許多個(gè)會(huì)話同時(shí)修改相同的數(shù)據(jù)，除非較早的修改命令提交結(jié)束后，其他窗口據(jù)，除非較早的修改命令提交結(jié)束后，其他窗口才允許修改。才允許修改。 12.2.4 持久性持久性 v事務(wù)的持久性是指：事務(wù)一旦提交，它的改變事

13、務(wù)的持久性是指：事務(wù)一旦提交，它的改變就會(huì)永久生效，不能再被撤銷（回滾），即使就會(huì)永久生效，不能再被撤銷（回滾），即使出現(xiàn)系統(tǒng)故障或錯(cuò)誤，改變也不會(huì)消失。出現(xiàn)系統(tǒng)故障或錯(cuò)誤，改變也不會(huì)消失。 12.3 事務(wù)管理命令事務(wù)管理命令 v對(duì)事務(wù)執(zhí)行各種操作的常見命令如下： COMMIT事務(wù)提交命令 ROLLBACK事務(wù)回滾命令 SAVEPOINT 設(shè)置保存點(diǎn)命令 ROLLBACK TO 回滾到保存點(diǎn)命令 SET TRANSACTION設(shè)置事務(wù)屬性命令 SET CONSTRAINT設(shè)置約束的可延遲性命令12.3.1 COMMIT命令命令 vCOMMIT是事務(wù)提交命令，表明該事務(wù)對(duì)數(shù)據(jù)庫所做的修改操作將永

14、久記錄到數(shù)據(jù)庫中，不能被回滾。 v用戶執(zhí)行修改數(shù)據(jù)庫的操作但未提交時(shí)，Oracle已經(jīng)完成了對(duì)數(shù)據(jù)庫的實(shí)際操作，主要包括： Oracle生成了回滾信息。生成了回滾信息。 Oracle在在 SGA 中生成了重做日志條目。中生成了重做日志條目。 修改后的新數(shù)據(jù)已經(jīng)被寫入修改后的新數(shù)據(jù)已經(jīng)被寫入 SGA 中的數(shù)據(jù)緩沖區(qū)中的數(shù)據(jù)緩沖區(qū)。 v事務(wù)被提交時(shí)，Oracle進(jìn)行以下操作： 為此事務(wù)分配一個(gè)唯一的系統(tǒng)變化編號(hào)（SYSTEM CHANGE NUMBER，簡稱SCN，又稱 Oracle的內(nèi)部時(shí)鐘）。 重做日志寫進(jìn)程（LGWR）將SGA 內(nèi)重做日志緩沖區(qū)中的重做日志條目寫入重做日志文件中。同時(shí)還將此事

15、務(wù)的 SCN 也寫入重做日志文件。 釋放事務(wù)操作中占用的數(shù)據(jù)，即解除添加到表或數(shù)據(jù)行上的各種鎖。 通知用戶事務(wù)已經(jīng)成功提交。 從以上得知，提交事務(wù)的時(shí)間和事務(wù)的從以上得知，提交事務(wù)的時(shí)間和事務(wù)的大小，沒有關(guān)系大小，沒有關(guān)系。vOracle中事務(wù)的提交方式：中事務(wù)的提交方式： 顯示執(zhí)行COMMIT命令提交 事務(wù)隱式提交 執(zhí)行DDL語句時(shí)它前后的事務(wù)被隱式提交（DDL語句自己是一個(gè)獨(dú)立的事務(wù)） 正常結(jié)束會(huì)話時(shí)事務(wù)隱式提交 設(shè)置set autocommit on事務(wù)可自動(dòng)提交v參見例12.9 12.3.2 ROLLBACK命令命令 vROLLBACK是事務(wù)回滾命令，表明撤銷未提交的事務(wù)所做的各種修改

16、操作。 回滾事務(wù)的時(shí)間和事務(wù)中修改的數(shù)據(jù)量成正比 。v當(dāng)事務(wù)被回滾時(shí)，Oracle將執(zhí)行以下操作： 使用回滾表空間內(nèi)存儲(chǔ)的相關(guān)信息撤銷事務(wù)中所有 SQL 語句對(duì)數(shù)據(jù)的修改。 釋放事務(wù)中占用的各種資源，即解除該事務(wù)對(duì)表或行施加的各種鎖。 通知用戶事務(wù)回滾操作已經(jīng)完成。 v例12.10 向scott方案下的emp表中插入職工編號(hào)為1113的記錄并用UPDATE命令將該記錄的職工姓名修改為張三，然后用ROLLBACK命令回滾整個(gè)事務(wù)，代碼如下：INSERT INTO scott.emp(empno) VALUES(1113); -插入職工編號(hào)為1113的記錄SELECT * FROM scott.e

17、mp WHERE empno=1113; -執(zhí)行查詢命令查看該員工的姓名為nullUPDATE scott.emp SET ename=張三 WHERE empno=1113; -修改姓名為張三SELECT * FROM scott.emp WHERE empno=1113; -該員工姓名從null改為張三ROLLBACK; -執(zhí)行回滾操作，撤銷整個(gè)事務(wù)SELECT * FROM scott.emp WHERE empno=1113; -找不到該記錄12.3.3 SAVEPOINT 和 ROLLBACK TO SAVEPOINT 命令 vSAVEPOINT命令可以在事務(wù)中的某個(gè)地方設(shè)置保存點(diǎn)，

18、將一個(gè)大的事務(wù)劃分為幾個(gè)片段。 當(dāng)某個(gè)保存點(diǎn)后面的命令出現(xiàn)錯(cuò)誤需要回滾時(shí)，只需回滾到該保存點(diǎn)，不影響保存點(diǎn)前面操作的執(zhí)行，也不影響該回滾命令之后的操作。 v當(dāng)事務(wù)被回滾到某個(gè)保存點(diǎn)時(shí)，Oracle將執(zhí)行以下操作： 回滾指定保存點(diǎn)之后的所有語句。回滾指定保存點(diǎn)之后的所有語句。 保留指定的保存點(diǎn)，但其后創(chuàng)建的保存點(diǎn)都將被保留指定的保存點(diǎn)，但其后創(chuàng)建的保存點(diǎn)都將被清除。清除。 釋放此保存點(diǎn)后面獲得的表級(jí)鎖與行級(jí)鎖，但它釋放此保存點(diǎn)后面獲得的表級(jí)鎖與行級(jí)鎖，但它之前的數(shù)據(jù)鎖依然保留。之前的數(shù)據(jù)鎖依然保留。 v因此，回滾到保存點(diǎn)時(shí)，并不結(jié)束整個(gè)事務(wù)。v定義保存點(diǎn)的格式：定義保存點(diǎn)的格式：SAVEPOIN

19、T 保存點(diǎn)名稱;v回滾到指定保存點(diǎn)的格式：回滾到指定保存點(diǎn)的格式：ROLLBACK TO 保存點(diǎn)名稱; 參見例12.11 12.3.4 SET TRANSACTION命令命令 v用戶可以使用SET TRANSACTION命令設(shè)置當(dāng)前事務(wù)的屬性。 如設(shè)置事務(wù)的隔離級(jí)別、設(shè)置事務(wù)回滾時(shí)用的存儲(chǔ)空間以及為事務(wù)命名等操作。 v使用該命令時(shí)需要注意的事項(xiàng)使用該命令時(shí)需要注意的事項(xiàng): 該語句必須是當(dāng)前事務(wù)中的第一條語句 。 命令設(shè)置的事務(wù)屬性只對(duì)當(dāng)前事務(wù)生效，當(dāng)該事務(wù)結(jié)束后，設(shè)置的屬性也將失效。 v事務(wù)的隔離級(jí)別是指事務(wù)與事務(wù)之間的隔離程度，能夠有效解決并發(fā)事務(wù)之間的干擾，主要包括： v1READ ONL

20、Y（只讀）（只讀） 當(dāng)前事務(wù)只能對(duì)數(shù)據(jù)庫執(zhí)行當(dāng)前事務(wù)只能對(duì)數(shù)據(jù)庫執(zhí)行SELECT操作不能執(zhí)操作不能執(zhí)行行INSERT、UPDATE、DELETE操作。操作。 數(shù)據(jù)庫被凍結(jié)為事務(wù)剛開始的狀態(tài)，其他事務(wù)后數(shù)據(jù)庫被凍結(jié)為事務(wù)剛開始的狀態(tài)，其他事務(wù)后來對(duì)數(shù)據(jù)庫的修改操作在當(dāng)前事務(wù)中不可見。來對(duì)數(shù)據(jù)庫的修改操作在當(dāng)前事務(wù)中不可見。 v2READ WRITE（讀寫）（讀寫） 當(dāng)前事務(wù)可以對(duì)數(shù)據(jù)庫執(zhí)行增、刪、改、查的全當(dāng)前事務(wù)可以對(duì)數(shù)據(jù)庫執(zhí)行增、刪、改、查的全部操作。部操作。 并不凍結(jié)數(shù)據(jù)庫狀態(tài)。當(dāng)前事務(wù)能夠看到其他事并不凍結(jié)數(shù)據(jù)庫狀態(tài)。當(dāng)前事務(wù)能夠看到其他事務(wù)提交后的數(shù)據(jù)庫修改操作。務(wù)提交后的數(shù)據(jù)庫修改操

21、作。 這是事務(wù)的默認(rèn)隔離級(jí)別，一般不用單獨(dú)設(shè)置。這是事務(wù)的默認(rèn)隔離級(jí)別，一般不用單獨(dú)設(shè)置。v3ISOLATION LEVEL SERIALIZABLE（串行讀）（串行讀） 當(dāng)前事務(wù)可以對(duì)數(shù)據(jù)庫執(zhí)行增、刪、改、查的當(dāng)前事務(wù)可以對(duì)數(shù)據(jù)庫執(zhí)行增、刪、改、查的全部操作。全部操作。 并且凍結(jié)數(shù)據(jù)庫的狀態(tài)。該事務(wù)能夠看到自己并且凍結(jié)數(shù)據(jù)庫的狀態(tài)。該事務(wù)能夠看到自己對(duì)數(shù)據(jù)庫的修改操作，但是看不到其他事務(wù)對(duì)對(duì)數(shù)據(jù)庫的修改操作，但是看不到其他事務(wù)對(duì)數(shù)據(jù)庫的修改操作數(shù)據(jù)庫的修改操作 。v4ISOLATION LEVEL READ COMMITTED（提交讀）（提交讀） 該隔離級(jí)別的作用和該隔離級(jí)別的作用和READ WRITE一致，當(dāng)前一致，當(dāng)前事務(wù)可以讀取其他事務(wù)提交后的數(shù)據(jù)，看不到事務(wù)可以讀取其他事務(wù)提交后的數(shù)據(jù)，看不到未提交的數(shù)據(jù)。這也是默認(rèn)級(jí)別，不需單獨(dú)設(shè)未提交的數(shù)據(jù)。這也是默認(rèn)級(jí)別，不需單獨(dú)設(shè)置。置。12.3.5 SET CONSTRAINT 命令命令 vSET CONSTRAINT 命令用來設(shè)置數(shù)據(jù)庫中的約束在事務(wù)中是立即生效還是延遲到事務(wù)提交時(shí)再生效。v另外，另外，Ora