通過程序案例講解《數(shù)據(jù)庫原理》課程中的并發(fā)控制

史勝利

（包頭師范學院信息科學與技術學院，內(nèi)蒙古 014030）

0 引言

并發(fā)控制是《數(shù)據(jù)庫原理》課程的重要概念，并發(fā)控制技術涉及到多用戶共享和事務處理技術，對于深入理解事務的特性、數(shù)據(jù)的共享、多用戶并發(fā)操作都有重要的意義。同時并發(fā)控制教學對教師來說也是一個難點，并發(fā)操作帶來的數(shù)據(jù)不一致性有多種情況，要理解這些不一致情況需要多方面知識，不僅僅涉及到一些SQL語句，還可能涉及到多用戶訪問、事務的隔離性和一致性等，往往需要一些具體的案例才能理解。

現(xiàn)有的《數(shù)據(jù)庫原理》教材中都有并發(fā)控制的介紹，但內(nèi)容上存在一些問題，例如都以理論講解為主，使學生閱讀起來困難；介紹的方法和概念很多，顯得雜亂。這些問題可能在教師授課時也存在。教學中如果教師只講理論，對于剛接觸數(shù)據(jù)庫的學生來說會有很大的難度，很難對并發(fā)操作出現(xiàn)的問題和并發(fā)控制方法留有深刻的印象。

1 程序案例

教師在講解時可以使用程序案例把并發(fā)操作的整個過程都呈現(xiàn)出來，通過程序可以鮮活、生動的展示出現(xiàn)的問題，并很容易分析出產(chǎn)生問題的原因，既能引起學生的興趣，又能使學生加深認識。下面給出一些講解并發(fā)控制時的程序案例，代碼是在MS SQL Server 2008中執(zhí)行。

下面程序中用到的測試表Orders生成代碼如下代碼1：

CREATE TABLE Orders

（ID INT NOT NULL,Price FLOAT NOT NULL）;

INSERT INTO Orders VALUES（10,10.00）,（11,11.00）,（12,12.00）,（13,13.00）,（14,14.00）;

1.1 事務的原子性

事務是并發(fā)控制的基本單位，要理解并發(fā)控制首先要理解事務的原子性和事務處理命令。事務的原子性是事務中包括的諸操作要么都做，要么都不做?？梢跃帉懗绦蜓菔境蜂N事務部分操作的功能。下面的程序代碼2演示事務的部分代碼執(zhí)行后出現(xiàn)錯誤，使用撤銷語句恢復了數(shù)據(jù)。

begin

set xact_abort on;

begin try

begin tran

update orders set Price=Price+Price/10

where id=10;--執(zhí)行成功

update orders set Price=Price+Price/0

where id=11;--除數(shù)為零錯誤，執(zhí)行失敗

commit tran

end try

begin catch

if xact_state（）=-1--判斷修改數(shù)據(jù)有沒有出錯

begin

rollbacktran;--事務執(zhí)行中出錯就撤銷事務的所有操作

end

end catch

end

代碼 2在 MS SQL Server 2008中執(zhí)行，BEGIN TRAN、END TRAN、ROLLBACK TRAN語句分別用于標識事務開始、事務結束和撤銷事務。上面的程序中事務的開始語句沒問題，中間的操作會出現(xiàn)錯誤，這時使用回滾語句撤銷了已執(zhí)行的操作。

1.2 并發(fā)控制的必要性

并發(fā)控制講解時需要讓學生明白不實行并發(fā)控制會出現(xiàn)的問題。下面的程序代碼3演示沒有施行并發(fā)控制導致出現(xiàn)丟失更新問題。案例中使用兩個用戶同時連接數(shù)據(jù)庫，然后兩用戶同時修改一個表中的數(shù)據(jù)時會引起丟失更新錯誤。

--第一步：用戶A執(zhí)行

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Begin tran

Declare@n float=0

Select@n=price from orders where id=10

Set@n=@n-5

waitfor delay'00:00:30'

Update orders set price=@n where id=10;

Commit tran

--第二步：用戶B執(zhí)行

SET TRANSACTION ISOLATION LEVEL READ unCOMMIT?TED

Begin tran

Declare@n int;

Select@n=price from orders where id=10;

Set@n=@n-5;

Update orders set price=@n where id=10;Commit tran

按上面先后步驟執(zhí)行時，用戶A在修改操作之前有一個30秒的等待，所以用戶B先修改數(shù)據(jù)，然后用戶A后修改，用戶A把用戶B修改的數(shù)據(jù)覆蓋掉了。

1.3 讀臟數(shù)據(jù)

下面代碼4演示出現(xiàn)讀臟數(shù)據(jù)問題。下面代碼中用戶A讀取被用戶B修改但未提交的數(shù)據(jù)，而用戶B在后續(xù)操作中又撤消了修改，這就會使用戶A讀取的數(shù)據(jù)成為臟數(shù)據(jù)。

--第一步：用戶A中執(zhí)行修改orders表數(shù)據(jù)

BEGIN TRANSACTION

UPDATE Orders SET Price=Price+1 WHERE ID=10

--第二步：用戶B中執(zhí)行讀取orders表數(shù)據(jù)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMIT?

TED

SELECT ID,Price FROM Orders WHERE ID=10;

--第三步：用戶A中執(zhí)行撤銷修改操作

ROLLBACK TRANSACTION

按上面代碼4先后步驟執(zhí)行后，用戶B中讀到的數(shù)據(jù)就成為臟數(shù)據(jù)。

1.4 不可重復讀

下面代碼演示并發(fā)操作時出現(xiàn)不可重復讀問題。下面代碼5中用戶A先讀取了數(shù)據(jù)，之后數(shù)據(jù)被用戶B修改，然后用戶A重新讀取數(shù)據(jù)時和先前讀取的就不一致了。

--第一步：用戶A中執(zhí)行讀取orders表數(shù)據(jù)

SET TRANSACTION ISOLATION LEVEL COMMITTED--（1）

BEGIN TRANSACTION

SELECT ID,Price FROM Orders WHERE ID=10

--第二步：用戶B中執(zhí)行修改orders表數(shù)據(jù)

UPDATE Orders SET Price=Price+1 WHERE ID=10

--第三步：用戶A中執(zhí)行讀取orders表數(shù)據(jù)

SELECT ID,Price FROM Orders WHERE ID=10

COMMIT TRANSACTION

按照上面代碼5的步驟執(zhí)行后會出現(xiàn)不可重復讀問題，如果把第一步中語句（1）中的“COMMITTED”換成“REPEATABLE READ”就可避免不可重復讀的錯誤

1.5 幻讀

下面代碼6演示并發(fā)操作時出現(xiàn)的幻讀問題。用戶A先讀取了表數(shù)據(jù)，之后用戶B在表中插入了數(shù)據(jù)，然后用戶A重新讀取表時得到的數(shù)據(jù)比以前多了。

--第一步：用戶A中執(zhí)行讀取orders表數(shù)據(jù)

SET TRANSACTION ISOLATION LEVEL COMMITTED--（2）

BEGIN TRANSACTION

SELECT ID,Price FROM Orders

--第二步：用戶B中執(zhí)行向orders表插入數(shù)據(jù)INSERT INTO Orders VALUES（15,15.00）

--第三步：用戶A中執(zhí)行讀取orders表數(shù)據(jù)

SELECT ID,Price FROM Orders

COMMIT TRANSACTION

按照上面步驟操作會出現(xiàn)幻讀問題。上面代碼如果把第一步中（2）的“COMMITTED”修改為“SERIALIZ?ABLE”，可避免出現(xiàn)幻讀問題。

1.6 死鎖

下面代碼7演示出現(xiàn)死鎖問題。有table1和ta?ble2兩個表，用戶A先修改了table1的數(shù)據(jù)，這樣就持有table1的更新鎖，接著用戶B修改了table2的數(shù)據(jù)，又試圖修改table1，但用戶A還沒有釋放table1的更新鎖，然后用戶A又試圖修改table2，但用戶B這時持有table2的更新鎖，這樣用戶A和用戶B都會因等待對方占有的資源而陷入長期的等待狀態(tài)。

--步驟1：用戶A執(zhí)行鎖定了資源1

Begin tran

Update table1 set col1=2

--步驟2：用戶B執(zhí)行鎖定了資源2，試圖鎖資源1

Begin tran

Update table2 set col1=2

Update table1 set col1=2

--步驟3：用戶A執(zhí)行試圖鎖資源2，發(fā)生死鎖

Update table2 set col1=2

2 結語

并發(fā)控制是《數(shù)據(jù)庫原理》課程中的重要內(nèi)容，通過一些程序演示可展示事務并發(fā)時出現(xiàn)的問題和并發(fā)處理過程，可以使學生加深對事務原子性、隔離性、一致性的認識，對并發(fā)操作會出現(xiàn)的讀臟數(shù)據(jù)、不可重復讀、幻讀、丟失更新有一個具體直觀的印象，使抽象的概念具體化，教學效果會好得多。

[1]王珊，薩師煊等.數(shù)據(jù)庫系統(tǒng)概論（第5版）[M].北京：高等教育出版社，2014.9

[2]David M.Kroenke、David J.Auer著,朱居正譯.數(shù)據(jù)庫原理（第7版）：使用Access 2013演示與實踐[M].北京：清華大學出版社，2015.6.

[3]安博教育集團.Oracle數(shù)據(jù)庫系統(tǒng)[M].北京：電子工業(yè)出版社,2012.2.

[4]孟憲虎，馬雪英等.大型數(shù)據(jù)庫管理系統(tǒng)技術應用與實例分析—SQL Server 2005（第二版）[M].北京：電子工業(yè)出版社,2011.5.