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

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

6.1事務(wù)

6.1.1問(wèn)題的提出：

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

如果DBMS完全沒(méi)有約束對(duì)數(shù)據(jù)庫(kù)操作的順序，將會(huì)出現(xiàn)錯(cuò)誤。

1

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

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

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

if(!occ){

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

用戶1占用用戶2占用不同用戶在不同售票點(diǎn)購(gòu)票,調(diào)用chooseSeat()問(wèn)題：不同用戶占同一個(gè)座位4解決方法：用戶1的操作執(zhí)行完，再執(zhí)行用戶2的操作將一組操作組成一個(gè)事務(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ù)庫(kù)狀態(tài)：賬戶2增加金額賬戶1未減金額對(duì)銀行造成損失

8

問(wèn)題：數(shù)據(jù)處于不一致?tīng)顟B(tài)

結(jié)論：

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

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

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

如:一組SQL語(yǔ)句,一條SQL語(yǔ)句或一段程序。

BEGINTRANSACTION

開(kāi)始

COMMITTRANSACTION

提交

ROLLBACKTRANSACTION

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

A原子性

AtomicityC一致性

ConsistencyI隔離性

IsolationD持續(xù)性

Durability11

原子性:

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

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

12

隔離性:

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

持續(xù)性:

事務(wù)提交后對(duì)數(shù)據(jù)庫(kù)的改變是永久性的。即使出現(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ù)

臟讀?。╠irtyread）：對(duì)臟數(shù)據(jù)的讀取

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

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

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

例

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

1.將錢加到賬戶22.檢測(cè)賬戶1是否有足夠的錢沒(méi)有：從賬戶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測(cè)試，A2有足夠金額（$350，臟數(shù)據(jù)）；允許轉(zhuǎn)賬T1測(cè)試，A1沒(méi)有足夠金額（$100）；不允許轉(zhuǎn)賬T2：從A2減去$250A2有$100結(jié)束T1：從A2減去$150A2有-$50結(jié)束（T1回滾）17例座位選擇新方法

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

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

18

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

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

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

ISOLATIONLEVELREADUNCOMMITED206.1.5隔離級(jí)別

四種隔離級(jí)別

可串行化

SERIALIZABLE(缺省)

讀未提交

READUNCOMMITED

讀提交

READCOMMITED

可重復(fù)讀

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

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

不加控制時(shí)，可能帶來(lái)下面三種數(shù)據(jù)庫(kù)狀態(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)

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

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

x=9)

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

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

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

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

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

非串行調(diào)度27

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

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

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

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

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

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

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

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

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

1)

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

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

共享鎖(S)：

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

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

一級(jí):

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

解決“丟失修改”的問(wèn)題

34

二級(jí):

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

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

三級(jí):

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

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

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

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

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

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

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

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

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

2）當(dāng)一個(gè)事務(wù)已經(jīng)釋放了一個(gè)鎖之后，就不能再請(qǐng)求其他的鎖。第一階段申請(qǐ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的一個(gè)不可串行化調(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èn)題在包含兩個(gè)或兩個(gè)以上的事務(wù)的集合中，當(dāng)其中每個(gè)事務(wù)都在等待使用其他事務(wù)鎖定的數(shù)據(jù)項(xiàng)時(shí)，就會(huì)發(fā)生死鎖。

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

46解決方法:

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