第11章 事務(wù)及其并發(fā)控制《數(shù)據(jù)庫(kù)原理及應(yīng)用》教學(xué)課件

《數(shù)據(jù)庫(kù)原理及應(yīng)用》?精品課件合集目錄01

數(shù)據(jù)一致性問題02事務(wù)概念03JDBC事務(wù)04事務(wù)的特性05事務(wù)的并發(fā)問題05事務(wù)的隔離級(jí)別06鎖第11講事務(wù)及其并發(fā)控制數(shù)據(jù)一致性問題數(shù)據(jù)一致性是指關(guān)聯(lián)數(shù)據(jù)之間的邏輯關(guān)系是否正確和完整，當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)從一個(gè)狀態(tài)轉(zhuǎn)變到另一個(gè)狀態(tài)時(shí)，要能保證數(shù)據(jù)的一致性。數(shù)據(jù)庫(kù)系統(tǒng)中的一系列約束可以解決部分一致性需求，如主鍵、外鍵、唯一、非空和檢查約束等。在數(shù)據(jù)庫(kù)中，一個(gè)動(dòng)作是指一個(gè)增、刪、改語句，當(dāng)有多個(gè)動(dòng)作去完成一個(gè)任務(wù)時(shí)（如銀行轉(zhuǎn)帳），如果不加約束就發(fā)產(chǎn)生數(shù)據(jù)不一致性問題。數(shù)據(jù)不一致性舉例

【例】：銀行財(cái)務(wù)轉(zhuǎn)賬，A賬戶轉(zhuǎn)600元到B賬戶。A賬戶B賬戶600400160010001000A賬戶B賬戶60040022004001600第一次轉(zhuǎn)帳第二次轉(zhuǎn)帳數(shù)據(jù)不一致性舉例DELIMITER$$CREATEPROCEDUREp_transfer(INfrom_accountINT,INto_accountINT,INamountDECIMAL(10,2))BEGINUPDATEaccountSETbalance=balance+amountWHEREaccount_id=to_account;UPDATEaccountSETbalance=balance-amountWHEREaccount_id=from_account;END$$

CREATETABLEaccount(

account_idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,

account_nameVARCHAR(20)NOTNULL,balanceDECIMAL(10,2)DEFAULT'0.00'CHECK(balance>=0))AUTO_INCREMENT=202201;事務(wù)概念事務(wù)（Transaction）是指一個(gè)動(dòng)作或一系列動(dòng)作的組合，這些動(dòng)作被當(dāng)作一個(gè)整體或一個(gè)邏輯單元來處理，這些動(dòng)作要么都執(zhí)行，要么都不執(zhí)行。系統(tǒng)變量autocommit控制事務(wù)的提交方式，ON為自動(dòng)提交，即每執(zhí)行完一個(gè)動(dòng)作（一個(gè)動(dòng)作開啟一個(gè)事務(wù)），系統(tǒng)自動(dòng)提交；OFF為手動(dòng)提交，即必須用COMMIT或ROLLBACK結(jié)束當(dāng)前事務(wù)，提交(COMMIT)：這些動(dòng)作或者全部成功地執(zhí)行，即此時(shí)整個(gè)活動(dòng)序列的所有執(zhí)行結(jié)果都被保存到數(shù)據(jù)庫(kù)中?；貪L(ROLLBACK)：這些動(dòng)作都不執(zhí)行并恢復(fù)到執(zhí)行前的狀態(tài)。解決數(shù)據(jù)不一致性舉例DELIMITER$$CREATEPROCEDUREp_transfer1(INfrom_accountINT,INto_accountINT,INamountDECIMAL(10,2))BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK;STARTTRANSACTION;UPDATEaccountSETbalance=balance+amountWHEREaccount_id=to_account;UPDATEaccountSETbalance=balance-amountWHEREaccount_id=from_account;COMMIT;END$$事務(wù)的提交與回退【例】：只撤消事務(wù)的一部分updateaccountsetbalance=balance-10000whereaccountID='A';savepointt;updateaccountsetbalance=balance+10000where

accountID='B';rollbacktot;ROLLBACK說明：

在尚未對(duì)數(shù)據(jù)庫(kù)提交的時(shí)候，可以用事務(wù)回退命令ROLLBACK，將數(shù)據(jù)庫(kù)回退到上次COMMIT后的狀態(tài)。一旦事務(wù)已經(jīng)提交，就不能再使用事務(wù)回退命令進(jìn)行回退了。事務(wù)回退將回退整個(gè)事務(wù)，如果只需回退事務(wù)的一部分時(shí)，可以使用保存點(diǎn)，將整個(gè)事務(wù)劃分為若干部分，這樣就可以回退部分事務(wù)了。JDBC事務(wù)

實(shí)際上，一個(gè)Java應(yīng)用系統(tǒng)，通常分前端與后端兩個(gè)部分，前端和后端都可以實(shí)現(xiàn)事務(wù)管理，應(yīng)用系統(tǒng)對(duì)事務(wù)的控制通常放在前端來實(shí)現(xiàn)。如果前端使用JAVA編程，前端事務(wù)就稱為JDBC事務(wù)。數(shù)據(jù)庫(kù)客戶端工具（SQLyog）DBA數(shù)據(jù)庫(kù)開發(fā)工具（Java）程序員JDBCmysql存儲(chǔ)過程JDBC事務(wù)JDBCConnection接口提供了相應(yīng)的方法來管理事務(wù):publicvoidsetAutoCommit(boolean)//設(shè)置提交方式publicboolean

getAutoCommit()//獲取當(dāng)前的提交方式publicvoidcommit()//提交publicvoidrollback()//回滾JDBC事務(wù)

【例】：用JDBC事務(wù)來實(shí)現(xiàn)銀行財(cái)務(wù)轉(zhuǎn)賬。try{conn.setAutoCommit(false);stmt=conn.createStatement();Stringsql1="updateaccountsetbalance=balance-600whereaccount_id=202201";stmt.executeUpdate(sql1);Stringsql2="updateaccountsetbalance=balance+600whereaccount_id=202202";stmt.executeUpdate(sql2);

mit();}catch(SQLExceptione){ e.printStackTrace();try{

conn.rollback();}} JDBC事務(wù)思考題：（1）在數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)中，事務(wù)處理放在前端和后端，哪個(gè)更好？答：從數(shù)據(jù)庫(kù)系統(tǒng)的執(zhí)行效率來說，放在前端比放在后端好。數(shù)據(jù)在前端直接過濾即可，放在后端需要數(shù)據(jù)庫(kù)去做判斷，會(huì)消耗系統(tǒng)效率。答：從數(shù)據(jù)庫(kù)系統(tǒng)的可移植性來說，放在前端比放在后端好。因?yàn)楹蠖艘话阃ㄟ^數(shù)據(jù)庫(kù)編程來進(jìn)行控制，而不同工具如MySQL、SQLServer、Orale等編程語法差別較大，可移植性差。（2）在數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)中，檢查約束放在前端和后端，哪個(gè)更好？事務(wù)的特性①原子性（Atomicity）:事務(wù)是一個(gè)不可分隔的整體，事務(wù)中的語句要么都執(zhí)行，要么都不執(zhí)行；（通過COMMIT、ROLLBACK實(shí)現(xiàn)）②一致性（Consistency）：事務(wù)執(zhí)行的結(jié)果必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變?yōu)榱硗庖粋€(gè)一致性狀態(tài)；（通過事務(wù)其他三個(gè)特性來實(shí)現(xiàn)）③隔離性（Isolation）：指并發(fā)事務(wù)之間的干擾性，有具體的隔離級(jí)別決定；（通過事務(wù)隔離級(jí)別來實(shí)現(xiàn)）④持續(xù)性（Durability）：事務(wù)對(duì)數(shù)據(jù)庫(kù)的更改是永久的，能夠保證已經(jīng)成功提交的事務(wù)持久化到磁盤中。（通過redolog來實(shí)現(xiàn)）數(shù)據(jù)庫(kù)的內(nèi)存結(jié)構(gòu)數(shù)據(jù)庫(kù)的內(nèi)存結(jié)構(gòu)128128BufferPoolUndolog命中區(qū)臟區(qū)空閑區(qū)redologbinlogDBWRLGWRLGWRLGWR18210202201110052022011000220202201150010202201110052022011000事務(wù)并發(fā)引發(fā)的數(shù)據(jù)不一致性并發(fā)操作：在多用戶共享系統(tǒng)中，如果多個(gè)用戶同時(shí)對(duì)同一個(gè)數(shù)據(jù)進(jìn)行操作，稱為多事務(wù)并發(fā)操作。A事務(wù)B事務(wù)（1）丟失更新（3）不可重復(fù)讀（2）讀“臟”數(shù)據(jù)（4）幻讀事務(wù)并發(fā)引發(fā)的數(shù)據(jù)不一致性（1）丟失更新事務(wù)并發(fā)引發(fā)的數(shù)據(jù)不一致性（2）讀“臟”數(shù)據(jù)事務(wù)的并發(fā)引發(fā)的數(shù)據(jù)不一致性（3）不可重復(fù)讀事務(wù)并發(fā)引發(fā)的數(shù)據(jù)不一致性（3）不可重復(fù)讀事務(wù)并發(fā)引發(fā)的數(shù)據(jù)不一致性（4）幻讀事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別（TransactionIsolationLevel）：指一個(gè)事務(wù)對(duì)并行的另一個(gè)事務(wù)的隔離程度。為避免并發(fā)事務(wù)執(zhí)行中可能出現(xiàn)丟失更新、臟讀、不可重復(fù)讀和幻讀等現(xiàn)象，數(shù)據(jù)庫(kù)系統(tǒng)提供了不同級(jí)別的事務(wù)隔離，防止事務(wù)之間的相互影響：（1）讀未提交（READUNCOMMITTED）；（2）讀已提交（READCOMMITTED）；（3）可重復(fù)讀（REPEATABLEREAD）；（4）串行化（SERIALIZBALE）。設(shè)置事務(wù)隔離級(jí)別：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別舉例事務(wù)1Readuncommitted事務(wù)2Readcommitted事務(wù)3RepeatableRead事務(wù)0事務(wù)隔離級(jí)別思考題：不同的事務(wù)隔離級(jí)別在數(shù)據(jù)庫(kù)系統(tǒng)該如何選擇？答：數(shù)據(jù)庫(kù)管理系統(tǒng)一般都把事務(wù)的隔離級(jí)別設(shè)置為ReadCommitted;MySQL使用ReadCommited隔離級(jí)別進(jìn)行主從復(fù)制時(shí)會(huì)出現(xiàn)數(shù)據(jù)不一致問題，因此MySQL將RepeatableRead作為默認(rèn)的隔離級(jí)別。鎖鎖：數(shù)據(jù)庫(kù)的鎖機(jī)制主要目的是為了使用戶對(duì)數(shù)據(jù)的訪問變得有序，保證數(shù)據(jù)的一致性?；镜逆i類型有兩種：排它鎖（Exclusivelocks),簡(jiǎn)稱X鎖。

共享鎖(ShareLocks),簡(jiǎn)稱S鎖。鎖

X鎖：也稱排它鎖或?qū)戞i。如果事務(wù)T對(duì)數(shù)據(jù)對(duì)象R實(shí)現(xiàn)X封鎖，則只允許T讀取和修改R，其它任何事務(wù)都不能再對(duì)R加任何類型的鎖，直到T釋放R上的鎖。只有獲準(zhǔn)X封鎖的事務(wù)，才能對(duì)被封鎖的數(shù)據(jù)進(jìn)行修改。

S鎖：也稱讀鎖。如果事務(wù)T對(duì)數(shù)據(jù)對(duì)象R實(shí)現(xiàn)S封鎖，則事務(wù)T只可以讀取R但不能修改R，其它事務(wù)只能再對(duì)R加S鎖，而不能加X鎖，直到事務(wù)T釋放R上的S鎖之前不能對(duì)R做任何修改。注意：只能通過COMMIT和ROLLBACK來解鎖MVCC原理MVCC(Multi-Version-Concurrency-Control)：多版本并發(fā)控制是指MySQL在使用READCOMMITTED、REPEATABLEREAD這兩種隔離級(jí)別執(zhí)行普通SELECT操作時(shí)，訪問版本鏈的過程。版本鏈：當(dāng)對(duì)同一條記錄多次更新后，在undolog中就會(huì)形成一條版本鏈。ReadView(一致視圖或快照)：當(dāng)使用READCOMMITTED、REPEATABLEREAD這兩種隔離的事務(wù)來說，必須保證讀取的是已提交的記錄。思考題：如何才能讀取已提交的記錄呢？如果這條記錄沒有提交，去讀取什么內(nèi)容呢？MVCC原理redolog

insertintoaccountvalues(202201,’a’,1000)

5202201,’a’,1000,insT1:updateaccountsetbalance=balance+100whereaccout_id=202201)

8202201,’a’,1100,udp

5202201,’a’,1000,ins

8202201,’a’,1100,udp

5202201,’a’,1000,ins

10202201,’a’,1500,udp

T3:updateaccountsetbalance=balance+400whereaccout_id=202201)T2事務(wù)狀態(tài)Readcommitted（T2）Repeatableread（T2）T2讀T1未提交快照5快照5T2讀T1已提交快照8快照5T2讀T3未提交快照8快照5T2讀T3已提交快照10快照5InnoDB解決數(shù)據(jù)不一致性的方法（1）一致性讀：事務(wù)利用MVCC進(jìn)行讀取記錄，又稱無鎖讀或快照讀。在讀取記錄時(shí)不加鎖，不影響其他事務(wù)對(duì)該記錄的修改，執(zhí)行效率高。（2）鎖定讀：在不同的隔離級(jí)別下加不同的鎖。SELECT…LOCKINSHAREMODE;SELECT…FORUPDATE;GapLock：又稱Gap鎖或間隙鎖，將鍵值在搜索范圍內(nèi)的間隙鎖定，不能插入記錄。Next-KeyLock：將鍵值在搜索范圍內(nèi)的間隙鎖定，不能插入記錄，也不能修改當(dāng)