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

史勝利

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

0 引言

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

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

1 程序案例

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

下面程序中用到的測(cè)試表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 事務(wù)的原子性

事務(wù)是并發(fā)控制的基本單位，要理解并發(fā)控制首先要理解事務(wù)的原子性和事務(wù)處理命令。事務(wù)的原子性是事務(wù)中包括的諸操作要么都做，要么都不做。可以編寫程序演示撤銷事務(wù)部分操作的功能。下面的程序代碼2演示事務(wù)的部分代碼執(zhí)行后出現(xiàn)錯(cuò)誤，使用撤銷語句恢復(fù)了數(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ù)為零錯(cuò)誤，執(zhí)行失敗

commit tran

end try

begin catch

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

begin

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

end

end catch

end

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

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

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

--第一步：用戶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í)行時(shí)，用戶A在修改操作之前有一個(gè)30秒的等待，所以用戶B先修改數(shù)據(jù)，然后用戶A后修改，用戶A把用戶B修改的數(shù)據(jù)覆蓋掉了。

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

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

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

--第一步：用戶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í)行后會(huì)出現(xiàn)不可重復(fù)讀問題，如果把第一步中語句（1）中的“COMMITTED”換成“REPEATABLE READ”就可避免不可重復(fù)讀的錯(cuò)誤

1.5 幻讀

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

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

1.6 死鎖

下面代碼7演示出現(xiàn)死鎖問題。有table1和ta?ble2兩個(gè)表，用戶A先修改了table1的數(shù)據(jù)，這樣就持有table1的更新鎖，接著用戶B修改了table2的數(shù)據(jù)，又試圖修改table1，但用戶A還沒有釋放table1的更新鎖，然后用戶A又試圖修改table2，但用戶B這時(shí)持有table2的更新鎖，這樣用戶A和用戶B都會(huì)因等待對(duì)方占有的資源而陷入長(zhǎng)期的等待狀態(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 結(jié)語

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

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

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

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

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