北京工業(yè)大學(xué)研究生數(shù)據(jù)庫復(fù)試筆試課件chap6控制(0512)-72

第6章SQL系統(tǒng)控制

6.1事務(wù)

6.1.1問題的提出：

像銀行業(yè)務(wù)或機(jī)票預(yù)定這樣的應(yīng)用每秒鐘都會有上百個操作在數(shù)據(jù)庫上執(zhí)行。

如果DBMS完全沒有約束對數(shù)據(jù)庫操作的順序，將會出現(xiàn)錯誤。

1

例C語言嵌套SQL寫一個函數(shù)chooseSeat()EXECSQLBEGINDECLARESECTION;

intflight;chardate[10];charseat[3];intocc;EXECSQLENDDECLARESECTION;voidchooseSeat(){

EXECSQLSELECToccupiedINTO:occFROMFlightsWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;內(nèi)存：以變量的形式存儲Sql：以列的形式2

if(!occ){

EXECSQLUPDATEFlightsSEToccupid=TRUEWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;}else……/*已經(jīng)占用另選*/}3用戶1發(fā)現(xiàn)座位空用戶2發(fā)現(xiàn)座位空

用戶1占用用戶2占用不同用戶在不同售票點購票,調(diào)用chooseSeat()問題：不同用戶占同一個座位4解決方法：用戶1的操作執(zhí)行完，再執(zhí)行用戶2的操作將一組操作組成一個事務(wù)，事務(wù)中進(jìn)行封鎖保證事務(wù)的可串行性。5

例：銀行轉(zhuǎn)帳函數(shù)transfer():如果賬戶1余額夠用，轉(zhuǎn)帳到賬戶2EXECSQLBEGINDECLARESECTION;

intacct1,acct2;intbalance1;intamount;EXECSQLENDDECLARESECTION;voidtransfer(){

EXECSQLSELECTbalanceINTO:balance1FROMAccountsWHEREacctNo=:acct1;6

if(balance1>=amount){

EXECSQLUPDATEAccountsSETbalance=balance+:amountWHEREacctNo=:acct2;

EXECSQLUPDATEAccountsSETbalance=balance-:amountWHEREacctNo=:acct1;}else/*余額不足告知客戶*/

｝7

設(shè)想：金額轉(zhuǎn)入賬戶2之后，系統(tǒng)故障。

數(shù)據(jù)庫狀態(tài)：賬戶2增加金額賬戶1未減金額對銀行造成損失

8

問題：數(shù)據(jù)處于不一致狀態(tài)

結(jié)論：

數(shù)據(jù)庫操作的某些組合，需要原子地完成。即要么都做要么都不做。

96.1.2事務(wù)的定義與特性事務(wù):

必須被原子地執(zhí)行的一個或一組操作。

如:一組SQL語句,一條SQL語句或一段程序。

BEGINTRANSACTION

開始

COMMITTRANSACTION

提交

ROLLBACKTRANSACTION

撤消夭折、回滾10保證事務(wù)正確執(zhí)行的ACID特性。

A原子性

AtomicityC一致性

ConsistencyI隔離性

IsolationD持續(xù)性

Durability11

原子性:

事務(wù)中的操作要么都執(zhí)行,要么都不執(zhí)行。一致性:

以保證數(shù)據(jù)庫從一個一致性狀態(tài)到另一個一致性狀態(tài)。

12

隔離性:

一個事務(wù)的執(zhí)行不能被其他事務(wù)干擾。

持續(xù)性:

事務(wù)提交后對數(shù)據(jù)庫的改變是永久性的。即使出現(xiàn)故障,也不受影響。136.1.3只讀事務(wù)

例

讀數(shù)據(jù)判定座位是否有效

EXECSQLSELECToccupiedINTO:occFROMFlightsWHEREfltNum=:flightANDfltDate=:dateANDfltSeat=:seat;

只讀、讀寫的設(shè)置

SETTRANSACTIONREADONLYSETTRANSACTIONREADWRITE

只讀事務(wù)可以更自由地與其他事務(wù)并行146.1.4讀臟數(shù)據(jù)

臟數(shù)據(jù)（dirtydata）：未提交事務(wù)所寫的數(shù)據(jù)

臟讀取（dirtyread）：對臟數(shù)據(jù)的讀取

讀臟數(shù)據(jù)的風(fēng)險：

寫數(shù)據(jù)的事務(wù)夭折，臟數(shù)據(jù)被移走，像不曾存在。如果別的事務(wù)讀了這個臟數(shù)據(jù)，并提交對臟數(shù)據(jù)的計算或使用。15

讀臟數(shù)據(jù)有時出問題，有時無關(guān)緊要

例

以下述步驟進(jìn)行賬戶轉(zhuǎn)移

1.將錢加到賬戶22.檢測賬戶1是否有足夠的錢沒有：從賬戶2減去此金額（相當(dāng)于回滾）有：從賬戶1減去此金額

假設(shè)有可能讀臟數(shù)據(jù)。賬戶：A1($100)A2($200)A3($300)

T1$150

T2$250

16

可能的執(zhí)行順序：

T2將$250 加到A3；A3有$550T1將$150 加到A2；A2有$350

T2測試，A2有足夠金額（$350，臟數(shù)據(jù)）；允許轉(zhuǎn)賬T1測試，A1沒有足夠金額（$100）；不允許轉(zhuǎn)賬T2：從A2減去$250A2有$100結(jié)束T1：從A2減去$150A2有-$50結(jié)束（T1回滾）17例座位選擇新方法

1.發(fā)現(xiàn)有效座位，occ=TRUE占用。無有效座位，夭折。

2.問顧客是否要此座位,要，提交。不要，occ=FALSE,重復(fù)步驟1，再選。

18

假設(shè)可能讀臟數(shù)據(jù)T2只是因讀臟數(shù)據(jù)，失去選喜歡的座位的機(jī)會……T1顧客放棄座位1occ=FALSET2發(fā)現(xiàn)座位1占用不能選T1occ=TRUE占用座位1可能的事務(wù)執(zhí)行順序：19#當(dāng)讀臟數(shù)據(jù)無關(guān)緊要時，允許讀臟數(shù)據(jù)可以避免：

1.DBMS用來預(yù)防讀臟數(shù)據(jù)所做的費時的工作

2.因等待而未去讀臟數(shù)據(jù)造成的并發(fā)性的丟失#允許明確指定一個給定的事務(wù)可以讀臟數(shù)據(jù)

ISOLATIONLEVELREADUNCOMMITED206.1.5隔離級別

四種隔離級別

可串行化

SERIALIZABLE(缺省)

讀未提交

READUNCOMMITED

讀提交

READCOMMITED

可重復(fù)讀

REPEATABLEREAD216.2并發(fā)控制數(shù)據(jù)庫系統(tǒng)是多用戶環(huán)境。當(dāng)多個用戶同時使用數(shù)據(jù)庫中相同數(shù)據(jù)時，稱為并發(fā)操作。

DBMS必須采取措施，對并發(fā)操作加以控制。事務(wù)＋封鎖機(jī)制226.2.1事務(wù)并發(fā)執(zhí)行可能帶來的不一致

不加控制時，可能帶來下面三種數(shù)據(jù)庫狀態(tài)不一致的情況。

#丟失修改

#不可重復(fù)讀

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

231.丟失修改事務(wù)1讀(x=3)

事務(wù)2讀(x=3)

事務(wù)1改(x=x+2=5)

事務(wù)2改(x=x+1=4)

時間事務(wù)1的修改丟失24

2.讀臟數(shù)據(jù)事務(wù)1更新(x=3

x=9)

事務(wù)2讀(x=9)

事務(wù)1撤消(x=3)時間事務(wù)2讀臟數(shù)據(jù)25

3.不可重復(fù)讀事務(wù)1讀(x=1)

事務(wù)2改(x=3)

事務(wù)1再讀(x=3)時間事務(wù)1不可重復(fù)讀266.2.2調(diào)度調(diào)度（scheduling）主要是指對一組可能來自多個不同事務(wù)操作流的一種偏序序列安排。通常要求來自同一個事務(wù)的兩個操作保持原來的次序。所謂偏序：來自不同事務(wù)的某些操作相對順序并不重要。串行調(diào)度:每個事物內(nèi)部連著做，事務(wù)間互相不交替

非串行調(diào)度27

6.2.3可串行化調(diào)度

并行事務(wù)正確性的準(zhǔn)則-------可串行性

幾個事務(wù)并行執(zhí)行是正確的當(dāng)且僅當(dāng)其結(jié)果與某一次序串行執(zhí)行它們的結(jié)果相同。

28可串行化調(diào)度：包含一組提交事務(wù)的調(diào)度S執(zhí)行時，如果對于數(shù)據(jù)庫一致性的影響與同樣一組事務(wù)的某一串行調(diào)度的影響相同，稱S為可串行化調(diào)度。沖突：兩個操作滿足如下兩個條件，稱為沖突的

1)屬于同一個事務(wù)，順序由事務(wù)本身邏輯決定

2)屬于不同事務(wù)，對同一數(shù)據(jù)元素操作，其中之一是寫操作。29沖突等價如果兩個調(diào)度S1和S2滿足以下兩個條件，則稱S1和S2沖突等價

1）包含了同一組事務(wù)以及其中的操作集

2）任意兩個提交事務(wù)的每一個沖突操作對，在S1和S2中出現(xiàn)的次序相同。30沖突可串行化調(diào)度一定是可串行化調(diào)度。反之，不然。視等價調(diào)度也是可串行化調(diào)度。沖突可串行化：如果S與某一個串行調(diào)度沖突等價，則S是沖突可串行化的。31

視等價：調(diào)度S1和S2包含同樣的事務(wù)集，如果滿足：

1)

S1中的事務(wù)Ti讀數(shù)據(jù)庫元素A的初值，S2中的事務(wù)Ti也讀元素A的初值；2)S1中的事務(wù)Ti讀被事務(wù)Tj寫過的元素A的值，S2中的事務(wù)Ti也讀被事務(wù)Tj寫過的元素A的值；3)對于每一個數(shù)據(jù)庫元素A，S1中Tk執(zhí)行對A的最后的寫操作，S2中也是Tk執(zhí)行對A的最后的寫操作。稱為視等價的。一個調(diào)度視等價于某串行調(diào)度，該調(diào)度是視可串行化的。326.3基于封鎖的并發(fā)控制

排它鎖(X)：拒絕其它事務(wù)任何類型的鎖請求。

共享鎖(S)：

拒絕其它事務(wù)的X鎖請求，但允許其它事務(wù)的S鎖請求。336.3.1封鎖協(xié)議

保證一致性的三級封鎖協(xié)議:

一級:

寫之前加排它鎖直至事務(wù)結(jié)束

解決“丟失修改”的問題

34

二級:

寫之前加排它鎖直至事務(wù)結(jié)束讀之前加共享鎖,讀完釋放

解決“讀臟數(shù)據(jù)”問題35

三級:

寫之前加排它鎖直至事務(wù)結(jié)束讀之前加共享鎖,直至事務(wù)結(jié)束

解決“不可重復(fù)讀”問題36實際應(yīng)用中，測試調(diào)度的可串行性是十分困難的。并發(fā)事務(wù)操作的交替是由操作系統(tǒng)調(diào)度程序來實現(xiàn)的，它為所有進(jìn)程分配資源，操作系統(tǒng)通常把并發(fā)事務(wù)當(dāng)作進(jìn)程來執(zhí)行。如：系統(tǒng)裝載、事務(wù)提交的時間、進(jìn)程的優(yōu)先級等因素將有助于對調(diào)度中的操作排序。很難為確?？纱行远孪却_定調(diào)度的操作如何交替。6.3.2兩段鎖協(xié)議37在大多數(shù)商業(yè)DBMS中采取的方法是設(shè)計協(xié)議以保證可串行性，而不必測試調(diào)度本身。

如果每個單獨事務(wù)都遵循協(xié)議，或者協(xié)議被一個DBMS并發(fā)控制子系統(tǒng)執(zhí)行，那么將確保事務(wù)參與的所有調(diào)度都是可串行性的。38有多種保證可串行性的并發(fā)控制協(xié)議，最常用的是兩階段封鎖（two-phase

locking）協(xié)議。該協(xié)議基于對數(shù)據(jù)項加鎖以阻止并發(fā)事務(wù)相互干擾。其他協(xié)議：

時間戳排序按照事務(wù)的時間戳順序執(zhí)行

多版本協(xié)議維護(hù)數(shù)據(jù)項的多個版本

最優(yōu)化協(xié)議（驗證）提交前驗證是否違反可串行性39嚴(yán)格兩段鎖協(xié)議：

1)事務(wù)在讀元素前，必須獲得共享鎖；寫元素前必須獲得排他鎖。2)一個事務(wù)所持有的所有鎖只有在事務(wù)完成時才釋放。40一般兩段鎖協(xié)議

1）同嚴(yán)格2PL

2）當(dāng)一個事務(wù)已經(jīng)釋放了一個鎖之后，就不能再請求其他的鎖。第一階段申請鎖第二階段釋放鎖41T1T2read_lock(Y);read_item(Y);unlock(Y);write_lock(X);read_item(X);X:=X+Y;write_item(X);unlock(X);read_lock(X);read_item(X);unlock(X);write_lock(Y);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);不遵循一般2PL協(xié)議的事務(wù)3-1423-2初始值:X=20,Y=30串行調(diào)度T1，T2（T2在T1之后進(jìn)行）的結(jié)果：

X=50，Y=80串行調(diào)度T2，T1（T1在T2之后進(jìn)行）的結(jié)果：

X=70，Y=50T1，T2可能的串行調(diào)度的結(jié)果433-3T1T2read_lock(Y);read_item(Y);unlock(Y);write_lock(X);read_item(X);X:=X+Y;write_item(X);unlock(X);read_lock(X);read_item(X);unlock(X);write_lock(Y);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);T1T2的一個不可串行化調(diào)度X=50Y=5044遵守一般2PL協(xié)議的事務(wù)T1read_lock(Y);read_item(Y);write_lock(X);unlock(Y);read_item(X);X:=X+Y;write_item(X);unlock(X);T2read_lock(X);read_item(X);write_lock(Y);unlock(X);read_item(Y);Y:=Y+X;write_item(Y);unlock(Y);456.3.3死鎖問題在包含兩個或兩個以上的事務(wù)的集合中，當(dāng)其中每個事務(wù)都在等待使用其他事務(wù)鎖定的數(shù)據(jù)項時，就會發(fā)生死鎖。

集合中的每個事務(wù)都處于等待隊列中，等待集合中另一事務(wù)釋放對某一數(shù)據(jù)項的鎖。

46解決方法:

1）預(yù)防死鎖如果給每