數(shù)據(jù)庫(kù)原理與應(yīng)用 課件 張千帆 第11、12章 數(shù)據(jù)庫(kù)的安全性控制、數(shù)據(jù)庫(kù)的并發(fā)控制

第11章數(shù)據(jù)庫(kù)的安全性控制本章介紹數(shù)據(jù)庫(kù)的權(quán)限系統(tǒng)、訪(fǎng)問(wèn)控制實(shí)現(xiàn)原理，以及用戶(hù)管理、角色管理及權(quán)限管理的實(shí)現(xiàn)方法。數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用11.1訪(fǎng)問(wèn)控制實(shí)現(xiàn)原理11.2用戶(hù)管理11.3權(quán)限管理11.4角色管理主要內(nèi)容訪(fǎng)問(wèn)控制實(shí)現(xiàn)原理11.111.1訪(fǎng)問(wèn)控制實(shí)現(xiàn)原理不同的數(shù)據(jù)庫(kù)管理系統(tǒng)提供的安全機(jī)制，不論是權(quán)限劃分還是實(shí)現(xiàn)方式可能不太一樣。MySQL權(quán)限系統(tǒng)通過(guò)對(duì)連接的用戶(hù)進(jìn)行身份認(rèn)證、對(duì)通過(guò)認(rèn)證的合法用戶(hù)賦予權(quán)限兩個(gè)階段進(jìn)行安全性控制。MySQL的安全機(jī)制涉及到三個(gè)方面：權(quán)限表用戶(hù)管理權(quán)限管理11.1.1對(duì)連接的用戶(hù)進(jìn)行身份認(rèn)證“mysql”數(shù)據(jù)庫(kù)（安裝MySQL時(shí)被創(chuàng)建，數(shù)據(jù)庫(kù)名稱(chēng)為“mysql”）中包含user、db、table_priv、columns_priv等重要的權(quán)限表。當(dāng)用戶(hù)通過(guò)用戶(hù)名和密碼與服務(wù)器進(jìn)行連接的時(shí)候，服務(wù)器先查看user表。對(duì)于身份認(rèn)證，MySQL是通過(guò)IP地址、用戶(hù)名、密碼聯(lián)合進(jìn)行確認(rèn)的。同一個(gè)用戶(hù)名在不同的IP地址登陸系統(tǒng)，會(huì)被MySQL視為不同的用戶(hù)。例如root@localhost表示用戶(hù)root只能從本地（localhost）進(jìn)行連接且密碼正確才可以通過(guò)認(rèn)證，此用戶(hù)從其他任何主機(jī)對(duì)數(shù)據(jù)庫(kù)進(jìn)行的連接都將被拒絕。系統(tǒng)判斷連接的IP地址、用戶(hù)名和密碼是否存在于user表中，如果存在，則通過(guò)身份驗(yàn)證，該用戶(hù)便可以連接服務(wù)器并進(jìn)入到第二個(gè)階段，否則拒絕連接。11.1.2對(duì)通過(guò)認(rèn)證的合法用戶(hù)賦予相應(yīng)的權(quán)限MySQL通過(guò)權(quán)限表控制用戶(hù)對(duì)數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)，權(quán)限表user、db、table_priv、columns_priv的權(quán)限范圍依次遞減。user表不僅能限制用戶(hù)連接服務(wù)器，還存儲(chǔ)了所有用戶(hù)的全局權(quán)限信息。db表存儲(chǔ)的是所有用戶(hù)在數(shù)據(jù)庫(kù)層的權(quán)限信息。tables_priv表存儲(chǔ)的是所有用戶(hù)在表層的權(quán)限信息。columns_priv表存儲(chǔ)的是所有用戶(hù)在列層的權(quán)限信息。按照全局權(quán)限覆蓋局部權(quán)限的原則，用戶(hù)如果通過(guò)身份驗(yàn)證登錄系統(tǒng)后，按照user、db、table_priv、columns_priv的順序得到數(shù)據(jù)庫(kù)權(quán)限。用戶(hù)管理11.211.2.1創(chuàng)建用戶(hù)CREATEUSER語(yǔ)句用來(lái)創(chuàng)建用戶(hù)，其基本的語(yǔ)法格式為：CREATEUSER[IFNOTEXISTS]'username'@'client_host'IDENTIFIEDBY'password';參數(shù)：（1）username：創(chuàng)建的用戶(hù)名。（2）client_host：允許用戶(hù)連接的主機(jī)。（3）password：用戶(hù)的登錄密碼。[例11-1]：創(chuàng)建用戶(hù)user1，密碼為123456。CREATEUSER'user1'@'localhost'IDENTIFIEDBY'123456';11.2.2修改用戶(hù)密碼ALTERUSER語(yǔ)句用于修改普通用戶(hù)的密碼，其基本的語(yǔ)法格式為：ALTERUSER[IFEXISTS]'username'@'client_host'IDENTIFIEDBY'newpassword';[例11-2]：把user1的密碼改為123。ALTERUSER'user1'@'localhost'IDENTIFIEDBY'123';11.2.3修改用戶(hù)名RENAMEUSER語(yǔ)句用于修改普通用戶(hù)名，其基本的語(yǔ)法格式為：RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...[例11-3]：把user1的用戶(hù)名改為Zhouping。RENAMEUSER'user1'@'localhost'TO'Zhouping'@'localhost';11.2.4刪除用戶(hù)DROPUSER語(yǔ)句用于刪除用戶(hù)，其基本的語(yǔ)法格式為：DROPUSER[IFEXISTS]user[,user]...[例11-4]：例11-5：刪除用戶(hù)Zhouping。DROPUSER'Zhouping'@'localhost';權(quán)限管理11.311.3.1授權(quán)GRANT語(yǔ)句用于向用戶(hù)授權(quán)，其基本的語(yǔ)法格式為：GRANTpriv_type[(column_list)]

[,priv_type[(column_list)]]...

ONpriv_level

TOuser[,user]...

[WITHGRANTOPTION]

priv_level:{*.*|db_name.*|db_name.tbl_name|tbl_name|

db_name.routine_name}參數(shù)：（1）priv_type：權(quán)限，同時(shí)賦予多個(gè)權(quán)限時(shí)權(quán)限之間使用逗號(hào)分開(kāi)。CREATE、ALTER、DROP、INSERT、DELETE、UPDATE，ALL，USAGE（2）priv_level：MySQL支持三種級(jí)別的權(quán)限管理：全局性權(quán)限、數(shù)據(jù)庫(kù)級(jí)別的權(quán)限、數(shù)據(jù)庫(kù)對(duì)象級(jí)別的權(quán)限。（3）WITHGRANTOPTION：該帳戶(hù)可以為其他帳戶(hù)分配權(quán)限。11.3.2收回權(quán)限REVOKE語(yǔ)句用于收回權(quán)限，其基本的語(yǔ)法格式為：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser[,user]...修改完權(quán)限以后需要重啟服務(wù)或用FLUSHPRIVILEGES語(yǔ)句刷新服務(wù)。11.3.3實(shí)例[例11-5]：已知某用戶(hù)的用戶(hù)名為user1，host為localhost，密碼為123456。對(duì)其進(jìn)行授權(quán)和權(quán)限收回。GRANTALLON*.*TOuser1@'localhost';REVOKEALLON*.*FROMuser1@'localhost';[例11-6]：授予用戶(hù)user1校園卡管理數(shù)據(jù)庫(kù)中salebill表的INSERT、UPDATE、SELECT權(quán)限，隨后收回該用戶(hù)對(duì)salebill表的UPDATE權(quán)限。GRANTINSERT,UPDATE,SELECTONcardmanagement.salebillTO'user1'@'localhost';REVOKEUPDATEoncardmanagement.salebillFROM'user1'@'localhost';GRANT語(yǔ)句執(zhí)行后，以u(píng)ser1身份連接服務(wù)器，對(duì)salebill表進(jìn)行查詢(xún)、插入、修改、刪除操作。觀(guān)察結(jié)果。REVOKE語(yǔ)句執(zhí)行成功后，user1再次修改salebill表。觀(guān)察結(jié)果角色管理11.411.4.1創(chuàng)建角色CREATEROLE語(yǔ)句用于創(chuàng)建角色，其基本的語(yǔ)法格式為：CREATEROLE[IFNOTEXISTS]role[,role]...參數(shù)：（1）role：角色名。角色名和帳戶(hù)名相同，也是名字+host，如果沒(méi)有寫(xiě)host，默認(rèn)為'%'（2）IFNOTEXISTS：創(chuàng)建一個(gè)已經(jīng)存在的角色，默認(rèn)情況下會(huì)發(fā)生錯(cuò)誤；如果提供了IFNOTEXISTS子句則不會(huì)報(bào)錯(cuò)。[例11-7]：創(chuàng)建三個(gè)角色，角色名分別為admin、creator和reader。CREATEROLE'admin','creator','reader';11.4.2為角色授權(quán)給角色授權(quán)也是使用GRANT語(yǔ)句，其基本的語(yǔ)法格式如下：GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ONpriv_levelTOrole[,role]...[WITHGRANTOPTION]參數(shù)同給用戶(hù)授權(quán)的GRANT語(yǔ)句。[例11-8]：給cardmanagement數(shù)據(jù)庫(kù)中所有表的查詢(xún)權(quán)限。GRANTSELECTONcardmanagement.*TO'reader';11.4.3收回角色權(quán)限收回角色權(quán)限也是使用REVOKE語(yǔ)句，其基本的語(yǔ)法格式如下：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMrole[,role]...參數(shù)同給用戶(hù)權(quán)限收回的REVOKE語(yǔ)句。[例11-9]：收回creator角色的權(quán)限。REVOKEINSERT,UPDATE,DELETE

ONcardmanagement.*FROM'creator';11.4.4賦予用戶(hù)角色使用GRANT語(yǔ)句賦予用戶(hù)角色，其基本的語(yǔ)法格式為：GRANTrole[,role]...TOuser[,user]...[WITHADMINOPTION]賦予角色和賦予權(quán)限的語(yǔ)句都是GRANT開(kāi)頭，區(qū)別在于是否有ON子句。所以角色和權(quán)限需要分成不同的語(yǔ)句來(lái)授予。[例11-10]：先創(chuàng)建1個(gè)用戶(hù)，然后給用戶(hù)賦予角色。CREATEUSER'adm1'@'localhost'IDENTIFIEDBY'adm1123456';GRANT'admin'TO'adm1'@'localhost';11.4.5查看角色的權(quán)限使用SHOWGRANTS語(yǔ)句查看用戶(hù)或角色的權(quán)限，其基本的語(yǔ)法格式為：SHOWGRANTS[FORuser_or_role[USINGrole[,role]...]][例11-11]：查看用戶(hù)adm1的權(quán)限。SHOWGRANTSFOR'adm1'@'localhost';[例11-12]：查看角色creator的權(quán)限。SHOWGRANTSFOR'creator';[例11-13]：查看用戶(hù)adm1及其擁有的角色對(duì)應(yīng)的權(quán)限。SHOWGRANTSFOR'adm1'@'localhost'USING'admin';11.4.6收回用戶(hù)的角色使用REVOKE語(yǔ)句收回用戶(hù)的角色，其基本的語(yǔ)法格式為：REVOKErole[,role]...FROMuser[,user]...[例11-14]：收回用戶(hù)rw_user1的creator角色。REVOKE'creator'FROM'rw_user1'@'localhost';用戶(hù)rw_user1的creator角色收回后，通過(guò)該角色賦予用戶(hù)的權(quán)限一并收回了，其它角色賦予該帳戶(hù)的權(quán)限不變。11.4.7角色和用戶(hù)的權(quán)限互換MySQL中角色和用戶(hù)的權(quán)限是可以互換的，除了可以把角色的權(quán)限賦予用戶(hù)外，還可以把角色的權(quán)限賦予其它角色、用戶(hù)的權(quán)限賦予其它用戶(hù)，用戶(hù)的權(quán)限賦予角色。[例11-15]：舉例說(shuō)明角色和用戶(hù)權(quán)限互換。創(chuàng)建新用戶(hù)user1和新角色role1，把用戶(hù)read_user1和角色creator的權(quán)限賦予新用戶(hù)或者新角色。CREATEUSER'user1';CREATEROLE'role1';GRANT'read_user1'@'localhost','creator'TO'user1','role1';11.4.8激活角色1）設(shè)置默認(rèn)激活的角色使用SETDEFAULTROLE設(shè)置默認(rèn)激活的角色，語(yǔ)法格式為：SETDEFAULTROLE{NONE|ALL|role[,role]...}TOuser[,user]...參數(shù)：（1）NONE：該帳戶(hù)沒(méi)有角色被默認(rèn)激活。（2）ALL：該帳戶(hù)的所有角色被默認(rèn)激活。（3）role：該帳戶(hù)中指定的角色被默認(rèn)激活。（4）user：帳戶(hù)名。[例11-16]：為指定帳戶(hù)默認(rèn)激活所有已擁有的角色。SETDEFAULTROLEALLTO'adm1'@'localhost','read_user1'@'localhost','read_user2'@'localhost','rw_user1'@'localhost','rw_user2'@'localhost';11.4.8激活角色2）運(yùn)行時(shí)激活使用SETROLE設(shè)置當(dāng)前帳戶(hù)的角色，語(yǔ)法格式為：SETROLE{DEFAULT|NONE|ALL|ALLEXCEPTrole[,role]...|role[,role]...}參數(shù)：（1）DEFAULT：將當(dāng)前帳戶(hù)中的活動(dòng)角色設(shè)置為當(dāng)前帳戶(hù)的默認(rèn)角色。（2）NONE：當(dāng)前帳戶(hù)無(wú)角色被激活。（3）ALL：當(dāng)前帳戶(hù)的所有角色被激活。（4）ALLEXCEPTrole[,role]...：除指定的角色外，該帳戶(hù)的其他角色都被激活。（5）role[,role]...：指定當(dāng)前帳戶(hù)被激活的角色。[例11-17]：激活除admin以外的所有角色。SETROLEALLEXCEPT'admin';11.4.9刪除角色DROPROLE語(yǔ)句用于刪除角色，其基本的語(yǔ)法格式為：DROPROLE[IFEXISTS]role[,role]...[例11-18]：刪除角色reader和creator。DROPROLE'reader','creator';第12章數(shù)據(jù)庫(kù)的并發(fā)控制本章介紹事務(wù)與事務(wù)調(diào)度，并發(fā)控制技術(shù)及死鎖的預(yù)防與處理方法。數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用12.1事務(wù)概述12.2事務(wù)并發(fā)與數(shù)據(jù)不一致12.3基于鎖的并發(fā)控制技術(shù)12.4并發(fā)調(diào)度的可串行性主要內(nèi)容事務(wù)概述12.112.1.1事務(wù)的概念事務(wù)不是單行語(yǔ)句，是實(shí)現(xiàn)數(shù)據(jù)操作的一個(gè)最基本的單位。這些操作要么都做，要么都不做，是一個(gè)不可分割的工作單位。事務(wù)的執(zhí)行是分成眾多元操作逐步進(jìn)行的，元操作一般分為READ和WRITE兩種。這兩個(gè)元操作的含義如下：1）READ（X）：將數(shù)據(jù)項(xiàng)X從數(shù)據(jù)庫(kù)磁盤(pán)文件中讀入事務(wù)所在的內(nèi)存的緩沖區(qū)內(nèi)。2）WRITE（X）：將數(shù)據(jù)項(xiàng)X從事務(wù)的內(nèi)存緩沖中寫(xiě)回?cái)?shù)據(jù)庫(kù)磁盤(pán)文件中，代表了事務(wù)對(duì)數(shù)據(jù)庫(kù)的更新操作。12.1.1事務(wù)的概念例如，如下事務(wù)的具體含義是學(xué)生持飯卡A在售飯機(jī)B上消費(fèi)5元。STARTTRANSACTIONREAD(Balance_A)Balance_A=Balance_A-5;IFBalance_A<0THENPRINT(‘飯卡余額不足’)ROLLBACKT1ELSEWRITE(Balance_A)READ(Balance_B)MONEY_B=MONEY_B+5WRITE(MONEY_B)COMMIT12.1.2事務(wù)的特性原子性(Atomicity)事務(wù)中包含的所有操作要么全做，要么全不做原子性由恢復(fù)機(jī)制實(shí)現(xiàn)一致性(Consistency)事務(wù)開(kāi)始前，數(shù)據(jù)庫(kù)處于一致性的狀態(tài)；

事務(wù)結(jié)束后，數(shù)據(jù)庫(kù)必須仍處于一致性狀態(tài)一致性通過(guò)并發(fā)控制機(jī)制實(shí)現(xiàn)隔離性(Isolation)對(duì)任何一對(duì)事務(wù)T1，T2，在T1看來(lái)，T2要么在T1開(kāi)始之前已經(jīng)結(jié)束，要么在T1完成之后再開(kāi)始執(zhí)行隔離性通過(guò)并發(fā)控制機(jī)制實(shí)現(xiàn)持久性(Durability)一個(gè)事務(wù)一旦提交之后，它對(duì)數(shù)據(jù)庫(kù)的影響必須是永久的系統(tǒng)發(fā)生故障不能改變事務(wù)的持久性持久性通過(guò)恢復(fù)機(jī)制實(shí)現(xiàn)12.1.3事務(wù)模式（1）自動(dòng)提交事務(wù)模式每條單獨(dú)的語(yǔ)句都是一個(gè)事務(wù)。每條SQL語(yǔ)句在成功執(zhí)行完成后，都被自動(dòng)提交，如果遇到錯(cuò)誤，則自動(dòng)回滾該語(yǔ)句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。（2）顯式事務(wù)模式應(yīng)用程序通過(guò)指定事務(wù)啟動(dòng)和結(jié)束的時(shí)間來(lái)控制事務(wù)。MySQL使用STARTTRANSACTION、COMMIT、ROLLBACK、SETAUTOCOMMIT等語(yǔ)句管理本地事務(wù)（3）隱式事務(wù)模式數(shù)據(jù)定義語(yǔ)言中的CREATE、ALTER、DROP、RENAME、權(quán)限管理中的GRANT、REVOKE、SETPASSWORD等語(yǔ)句會(huì)產(chǎn)生隱式提交操作。即在事務(wù)中執(zhí)行完這些語(yǔ)句后會(huì)有一個(gè)隱式的COMMIT操作，直接提交該語(yǔ)句及其之前的語(yǔ)句。即使當(dāng)這些語(yǔ)句出現(xiàn)在STARTTRANSACTION與ROLLBACK之間，ROLLBACK也無(wú)法撤銷(xiāo)該語(yǔ)句及其之前的語(yǔ)句的操作結(jié)果。12.1.3事務(wù)模式基本的語(yǔ)法格式為：{STARTTRANSACTION|BEGIN[WORK]}[transaction_characteristic[,transaction_characteristic]...][WITHCONSISTENTSNAPSHOT]{COMMIT|ROLLBACK}SETAUTOCOMMIT={0|1}參數(shù)：（1）STARTTRANSACTION|BEGIN[WORK]：?jiǎn)?dòng)事務(wù)。BEGIN或BEGINWORK與STARTTRANSACTION的作用一樣，都可以用來(lái)啟動(dòng)事務(wù)。（2）WITHCONSISTENTSNAPSHOT：有該子句是將STARTTRANSACTION作為事務(wù)開(kāi)始的時(shí)間點(diǎn)。沒(méi)有該子句的情況下，執(zhí)行STARTTRANSACTION之后的第一條語(yǔ)句時(shí)事務(wù)才真正開(kāi)始。（3）COMMIT：提交當(dāng)前事務(wù)，使事務(wù)所做的數(shù)據(jù)更新永久生效。（4）ROLLBACK：事務(wù)執(zhí)行過(guò)程中遇到錯(cuò)誤時(shí)，撤銷(xiāo)事務(wù)中已經(jīng)執(zhí)行的操作，把數(shù)據(jù)庫(kù)中的數(shù)據(jù)回滾到事務(wù)執(zhí)行之前的狀態(tài)或回滾到某一個(gè)指定位置。（5）AUTOCOMMIT：會(huì)話(huà)變量，可以為每個(gè)事務(wù)設(shè)置提交模式。默認(rèn)情況下AUTOCOMMIT=1，MySQL啟用自動(dòng)提交模式。這意味著事務(wù)中的每個(gè)語(yǔ)句都不能用ROLLBACK撤消語(yǔ)句執(zhí)行的效果，就像每個(gè)語(yǔ)句都被STARTTRANSACTION和COMMIT包裹起來(lái)一樣；如果在語(yǔ)句執(zhí)行期間發(fā)生錯(cuò)誤，則會(huì)回滾該語(yǔ)句。（6）SETAUTOCOMMIT語(yǔ)句用來(lái)改變當(dāng)前會(huì)話(huà)的提交模式。執(zhí)行SETAUTOCOMMIT=0后，系統(tǒng)將禁用語(yǔ)句的自動(dòng)提交模式，必須使用COMMIT提交事務(wù)或使用ROLLBACK撤銷(xiāo)事務(wù)。12.1.3事務(wù)模式例[12-1]：設(shè)更新前C00001校園卡的余額是500元，下列兩個(gè)SELECT語(yǔ)句的查詢(xún)結(jié)果分別是什么？STARTTRANSACTION;UPDATEcardSETbalance=balance-80WHERECID='C00001';SELECTbalanceFROMcardWHERECID='C00001';ROLLBACK;SELECTbalanceFROMcardWHERECID='C00001';第一個(gè)SELECE語(yǔ)句查看到的是UPDATE語(yǔ)句執(zhí)行后的數(shù)據(jù),為420第二個(gè)SELECE語(yǔ)句查看到的是ROLLBACK語(yǔ)句回滾事務(wù)后的結(jié)果，為50012.1.3事務(wù)模式[例12-2]：分析下列SELECT語(yǔ)句的執(zhí)行結(jié)果。SHOWVARIABLESLIKE'AUTOCOMMIT';SETAUTOCOMMIT=0;STARTTRANSACTION;UPDATEsalebillSETpayamount=30WHEREnumber='1';DELETEFROMsalebillWHEREnumber='2';DROPTABLEsalebill; //隱式提交ROLLBACK;SELECT*FROMsalebill;DROPTABLE語(yǔ)句執(zhí)行之后，該語(yǔ)句及其之前的語(yǔ)句已經(jīng)隱式提交，ROLLBACK失效。ROLLBACK語(yǔ)句之后的SELECT語(yǔ)句的執(zhí)行結(jié)果如圖所示，系統(tǒng)提示不存在salebill表。12.1.4保存點(diǎn)保存點(diǎn)(SAVEPOINT)是事務(wù)中的一個(gè)邏輯點(diǎn)，用于指定事務(wù)回滾的位置。結(jié)束事務(wù)時(shí)，系統(tǒng)自動(dòng)刪除該事務(wù)中定義的所有保存點(diǎn)。（1）定義保存點(diǎn)SAVEPOINT語(yǔ)句用于定義保存點(diǎn)，其語(yǔ)法格式為：SAVEPOINTsavepoint_name參數(shù)：

savepoint_name：保存點(diǎn)的名稱(chēng)。12.1.4保存點(diǎn)ROLLBACKTO[SAVEPOINT]savepoint_name（2）回滾到保存點(diǎn)ROLLBACK語(yǔ)句除了可以將事務(wù)回滾到事務(wù)執(zhí)行之前的狀態(tài)并終止事務(wù)，還可以與保存點(diǎn)結(jié)合使用，將事務(wù)回滾到指定的保存點(diǎn)且不終止該事務(wù)的執(zhí)行，其基本的語(yǔ)法格式為：（3）刪除保存點(diǎn)RELEASESAVEPOINT語(yǔ)句的作用是從當(dāng)前事務(wù)中刪除指定名稱(chēng)的保存點(diǎn)，而不會(huì)引發(fā)事務(wù)的提交或回滾，其基本的語(yǔ)法格式為：RELEASESAVEPOINTsavepoint_name12.1.4保存點(diǎn)[例12-4]：分析下列SELECT語(yǔ)句的查詢(xún)結(jié)果。DELETEFROMbusiness;INSERTINTObusinessVALUES('B001','第一食堂');INSERTINTObusinessVALUES('B002','第二食堂');SELECT*FROMbusiness; //第一個(gè)SELECT語(yǔ)句STARTTRANSACTION;INSERTINTObusinessVALUES('B003','百景園餐廳');SAVEPOINTa1;INSERTINTObusinessVALUES('B004','學(xué)一超市');SAVEPOINTa2;INSERTINTObusinessVALUES('B005','學(xué)二超市');SAVEPOINTa3;INSERTINTObusinessVALUES('B006','車(chē)隊(duì)');SELECT*FROMbusiness;//第二個(gè)SELECT語(yǔ)句ROLLBACKTOa3;SELECT*FROMbusiness; //第三個(gè)SELECT語(yǔ)句ROLLBACKTOa1;SELECT*FROMbusiness; //第四個(gè)SELECT語(yǔ)句ROLLBACK;SELECT*FROMbusiness; //第五個(gè)SELECT語(yǔ)句第一個(gè)SELECT語(yǔ)句顯示事務(wù)開(kāi)始時(shí)business表的數(shù)據(jù)第二個(gè)SELECT語(yǔ)句顯示六個(gè)INSERT語(yǔ)句執(zhí)行后的結(jié)果第三個(gè)SELECT語(yǔ)句顯示的是回滾到保存點(diǎn)a3、第六個(gè)INSERT語(yǔ)句回滾后第四個(gè)SELECT語(yǔ)句顯示的是回滾到保存點(diǎn)a1，保存點(diǎn)a1之后的第四個(gè)、第五個(gè)INSERT也回滾后第五個(gè)SELECT語(yǔ)句顯示的是回滾整個(gè)事務(wù)后、business表恢復(fù)到初始事務(wù)開(kāi)始時(shí)的狀態(tài)事務(wù)并發(fā)與數(shù)據(jù)不一致12.212.2事務(wù)并發(fā)與數(shù)據(jù)不一致數(shù)據(jù)庫(kù)系統(tǒng)也支持事務(wù)的并發(fā)執(zhí)行，即多個(gè)事務(wù)同一時(shí)間操作同一數(shù)據(jù)對(duì)象。事務(wù)并發(fā)執(zhí)行可以有效提高數(shù)據(jù)庫(kù)系統(tǒng)的性能。但是如果不對(duì)并發(fā)事務(wù)進(jìn)行控制，有可能產(chǎn)生數(shù)據(jù)不一致。常見(jiàn)的數(shù)據(jù)不一致問(wèn)題:丟失修改或覆蓋更新（LostUpdate）臟讀（DirtyRead）不可重復(fù)讀（Non-repeatableRead）幻影讀（PhantomRow）12.2.1丟失修改或覆蓋更新

讀A=16

A←A-1寫(xiě)回A=15COMMIT①讀A=16

②

③A←A-1

寫(xiě)回A=15COMMIT

④T2T1(a)丟失修改

丟失修改是指事務(wù)1與事務(wù)2從數(shù)據(jù)庫(kù)中讀入同一數(shù)據(jù)并修改，事務(wù)2的提交結(jié)果破壞了事務(wù)1提交的結(jié)果，導(dǎo)致事務(wù)1的修改被丟失。12.2.2臟讀

讀B=200COMMIT

①讀B=100B←B*2

寫(xiě)回B②

③ROLLBACKB恢復(fù)為100T2T1(b)讀“臟”數(shù)據(jù)事務(wù)1修改某一數(shù)據(jù)，并將其寫(xiě)回磁盤(pán)事務(wù)2讀取同一數(shù)據(jù)后事務(wù)1由于某種原因被撤消，這時(shí)事務(wù)1已修改過(guò)的數(shù)據(jù)恢復(fù)原值事務(wù)2讀到的數(shù)據(jù)就與數(shù)據(jù)庫(kù)中的數(shù)據(jù)不一致，是不正確的數(shù)據(jù)，又稱(chēng)為“臟”數(shù)據(jù)。12.2.3不可重復(fù)讀

讀C=100C←C*2寫(xiě)回C=200COMMIT

①

讀C=100

②

③

讀C=200COMMITT2T1(c)不可重復(fù)讀

不可重復(fù)讀是指事務(wù)1讀取數(shù)據(jù)后，事務(wù)2執(zhí)行更新操作，使事務(wù)1無(wú)法再現(xiàn)前一次讀取結(jié)果。12.2.4幻影讀

插入D=200COMMIT

①

讀D={100}

②

③

讀D={100，200}COMMITT2T1(d)幻影讀

幻影讀是指事務(wù)1讀取數(shù)據(jù)后，事務(wù)2插入或刪除了其中部分記錄，當(dāng)事務(wù)1再次讀取數(shù)據(jù)時(shí)，發(fā)現(xiàn)某些記錄神密地出現(xiàn)或消失了。使事務(wù)1無(wú)法再現(xiàn)前一次讀取結(jié)果。基于鎖的并發(fā)控制技術(shù)12.312.3基于鎖的并發(fā)控制技術(shù)封鎖就是事務(wù)T在對(duì)某個(gè)數(shù)據(jù)對(duì)象（例如表、記錄等）操作之前，先向系統(tǒng)發(fā)出請(qǐng)求，對(duì)其加鎖;加鎖后事務(wù)T就對(duì)該數(shù)據(jù)對(duì)象有了一定的控制，直到事務(wù)T釋放它的鎖之前。封鎖是實(shí)現(xiàn)并發(fā)控制的一個(gè)非常重要的技術(shù)。12.3.1鎖的基本類(lèi)型鎖代表了對(duì)該數(shù)據(jù)項(xiàng)的訪(fǎng)問(wèn)權(quán)限。事務(wù)對(duì)數(shù)據(jù)的操作有SELECT、UPDATE、INSERT、DELETE操作，其中SELECT不改變數(shù)據(jù)的值，稱(chēng)為讀操作，后三者會(huì)改變數(shù)據(jù)的值，稱(chēng)為寫(xiě)操作。根據(jù)讀寫(xiě)數(shù)據(jù)的權(quán)限不同，鎖分為共享鎖和排他鎖兩種類(lèi)型。1）共享鎖共享鎖(ShareLock)，又稱(chēng)讀鎖，S鎖，是指如果事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加上共享鎖且沒(méi)有釋放共享鎖之前，事務(wù)T可以讀A但不可以寫(xiě)A，其它事務(wù)只能再對(duì)A加共享鎖但不能加排他鎖，直到事務(wù)T釋放A上的共享鎖。2）排他鎖排他鎖(ExclusiveLock)，又稱(chēng)寫(xiě)鎖，獨(dú)占鎖，X鎖，是指如果事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加上排他鎖且沒(méi)有釋放排他鎖之前，事務(wù)T可以讀A也可以寫(xiě)A，其它事務(wù)不能再對(duì)A加任何鎖，直到事務(wù)T釋放A上的鎖。12.3.1鎖的基本類(lèi)型鎖類(lèi)型S鎖X鎖無(wú)鎖S鎖√×√X鎖××√無(wú)鎖√√√鎖的相容矩陣“√”表示相容的請(qǐng)求，“×”表示互斥的請(qǐng)求。12.3.1鎖的基本類(lèi)型關(guān)系數(shù)據(jù)庫(kù)并發(fā)控制的原理是當(dāng)事務(wù)訪(fǎng)問(wèn)某一個(gè)數(shù)據(jù)對(duì)象時(shí)需要先向數(shù)據(jù)庫(kù)管理系統(tǒng)申請(qǐng)對(duì)該數(shù)據(jù)對(duì)象加鎖。如果事務(wù)只對(duì)該數(shù)據(jù)進(jìn)行讀操作就申請(qǐng)對(duì)該數(shù)據(jù)對(duì)象加S鎖，如果需要對(duì)該數(shù)據(jù)進(jìn)行寫(xiě)操作就申請(qǐng)對(duì)該數(shù)據(jù)對(duì)象加X(jué)鎖。如果申請(qǐng)成功，則事務(wù)獲得了對(duì)該數(shù)據(jù)相應(yīng)的操作權(quán)限；當(dāng)事務(wù)對(duì)數(shù)據(jù)的操作完成以后，需要釋放它所占用的鎖，解鎖后的數(shù)據(jù)允許其他事務(wù)加鎖并訪(fǎng)問(wèn)。12.3.2MySQL的隔離級(jí)別隔離級(jí)別是一個(gè)事務(wù)必須與其它事務(wù)進(jìn)行隔離的程度。較低的隔離級(jí)別可以增加并發(fā)，但代價(jià)是降低數(shù)據(jù)的正確性。相反，較高的隔離級(jí)別可以確保數(shù)據(jù)的正確性，但可能對(duì)并發(fā)產(chǎn)生負(fù)面影響。按照隔離程度由低到高，MySQL中事務(wù)的隔離級(jí)別分為：

串行讀(Serializable)未提交讀(ReadUncommitted,RU)提交讀(ReadCommitted,RC)可重復(fù)讀(RepeatableRead,RR)。其中，可重復(fù)讀是默認(rèn)的隔離級(jí)別。SETTRANSACTION語(yǔ)句用于指定隔離級(jí)別，并一直保持有效直到事務(wù)終止或者重新指定隔離級(jí)別。1）串行讀(Serializable)12.3.2MySQL的隔離級(jí)別使用以下語(yǔ)句將系統(tǒng)的隔離級(jí)別設(shè)置為串行讀級(jí)別：

SETTRANSACTIONISOLATIONLEVELSERIALIZABLE;串行讀是限制性最強(qiáng)的隔離級(jí)別，使用悲觀(guān)鎖。悲觀(guān)鎖，是指對(duì)數(shù)據(jù)被外界（包括本系統(tǒng)當(dāng)前的其他事務(wù)，以及來(lái)自外部系統(tǒng)的事務(wù)處理）修改持保守態(tài)度，為了保證事務(wù)的隔離性，在整個(gè)數(shù)據(jù)處理過(guò)程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀(guān)鎖要求對(duì)寫(xiě)操作加X(jué)鎖，對(duì)讀操作加S鎖，讀寫(xiě)互斥，以保證操作最大程度的獨(dú)占性，以此實(shí)現(xiàn)插入、修改、刪除數(shù)據(jù)時(shí)其它事務(wù)無(wú)法修改、也無(wú)法讀取這些數(shù)據(jù)，讀取數(shù)據(jù)時(shí)其它事務(wù)無(wú)法修改這些數(shù)據(jù)。MySQL中，除了串行讀隔離級(jí)別之外的其它三種隔離級(jí)別都采用更加寬松的加鎖機(jī)制，稱(chēng)為樂(lè)觀(guān)鎖。樂(lè)觀(guān)鎖機(jī)制下，只需要對(duì)寫(xiě)操作加X(jué)鎖，讀取數(shù)據(jù)不需要加鎖。

2）未提交讀(ReadUncommitted,RU)12.3.2MySQL的隔離級(jí)別使用以下語(yǔ)句將系統(tǒng)的隔離級(jí)別設(shè)置為未提交讀級(jí)別：

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;未提交讀是限制性最弱的隔離級(jí)別，只要求對(duì)寫(xiě)操作加X(jué)鎖，讀取數(shù)據(jù)不需要加鎖。3）提交讀(ReadCommitted,RC)使用以下語(yǔ)句將系統(tǒng)的隔離級(jí)別設(shè)置為提交讀級(jí)別：

SETTRANSACTIONISOLATIONLEVELREADCOMMITTED;提交讀隔離級(jí)別中，數(shù)據(jù)的寫(xiě)操作需要加X(jué)鎖，讀操作不需要S鎖，并通過(guò)多版本并發(fā)控制(Multi-versionConcurrencyControl,MVCC)，進(jìn)行快照讀(SnapshotRead)，以讀取最新提交的歷史數(shù)據(jù)的方式避免臟讀。

4）可重復(fù)讀(RepeatableRead,RR)12.3.2MySQL的隔離級(jí)別使用以下語(yǔ)句將系統(tǒng)的隔離級(jí)別設(shè)置為可重復(fù)讀級(jí)別：

SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;可重復(fù)讀隔離級(jí)別中，數(shù)據(jù)的寫(xiě)操作需要加X(jué)鎖，讀操作不需要加鎖，也是通過(guò)MVCC機(jī)制進(jìn)行快照讀。與提交讀隔離級(jí)別不同的是，可重復(fù)讀隔離級(jí)別中快照在事務(wù)第一次執(zhí)行SELECT語(yǔ)句時(shí)生成，只有本事務(wù)更新數(shù)據(jù)時(shí)才更新快照，本事務(wù)執(zhí)行過(guò)程中其它事務(wù)提交的數(shù)據(jù)變更是讀取不到的。即無(wú)論事務(wù)執(zhí)行過(guò)程中是否有其它事務(wù)提交了新的版本，該事務(wù)沒(méi)有提交之前讀到的都是該事務(wù)開(kāi)始時(shí)的版本，以此種方式實(shí)現(xiàn)了數(shù)據(jù)的可重復(fù)讀和無(wú)幻影讀。12.3.2MySQL的隔離級(jí)別隔離級(jí)別丟失修改臟讀不可重復(fù)讀幻影讀未提交讀×√√√提交讀××√√可重復(fù)讀×××√串行讀××××這四種隔離級(jí)別，分別有可能產(chǎn)生問(wèn)題如下所示：12.3.2MySQL的隔離級(jí)別[例12-5]：設(shè)事務(wù)T1讀取card表的數(shù)據(jù)，事務(wù)T1未提交之前事務(wù)T2申請(qǐng)向該表中插入行，如表所示因?yàn)槭聞?wù)T1對(duì)card表加S鎖，事務(wù)T2無(wú)法加X(jué)鎖而等待。直到事務(wù)T1提交之后釋放對(duì)card表的封鎖，事務(wù)T2才加鎖成功并執(zhí)行插入操作。如果事務(wù)T2等待時(shí)間超時(shí)（這個(gè)時(shí)間可以進(jìn)行配置），系統(tǒng)會(huì)提示Lockwaittimeout，并把事務(wù)T2掛起。12.3.2MySQL的隔離級(jí)別[例12-6]：設(shè)事務(wù)T1向C00002校園卡充值200元，事務(wù)T2在事務(wù)T1提交之前用該卡消費(fèi)10元，也申請(qǐng)修改該卡的余額，如表所示。card表中CID是主鍵，該列上有主鍵索引，事務(wù)T1只對(duì)CID='C00002'的行加X(jué)鎖，事務(wù)T2陷入等待狀態(tài)。事務(wù)T1結(jié)束并釋放鎖后，事務(wù)T2對(duì)CID='C00002'的行加鎖并修改數(shù)據(jù)，保證了事務(wù)T1的修改不會(huì)丟失。12.3.2MySQL的隔離級(jí)別[例12-7]：設(shè)事務(wù)T1向C00002號(hào)卡充值200元還沒(méi)有提交之前，事務(wù)T2讀取該卡余額，并在事務(wù)T1提交后再次讀取該卡余額，如表所示。假設(shè)事務(wù)T1執(zhí)行前，C00002卡的余額是276.5元；事務(wù)T1執(zhí)行UPDATE語(yǔ)句向該卡充值200元但沒(méi)有提交之前，事務(wù)T2查看該卡余額，讀到的結(jié)果是快照讀的歷史數(shù)據(jù)276.5元，而不是未提交的數(shù)據(jù)476.5元；事務(wù)T1提交后，事務(wù)T2再次讀取該卡的余額，讀到的結(jié)果是476.5元。事務(wù)T2前后兩次讀的結(jié)果是不一樣的。這是因?yàn)樽x取數(shù)據(jù)不加鎖，事務(wù)T2的兩次讀操作之間，事務(wù)T1進(jìn)行寫(xiě)操作并提交，事務(wù)T2每次執(zhí)行SELECT語(yǔ)句都會(huì)重新生成一個(gè)快照，讀取到的是SELECT語(yǔ)句啟動(dòng)前就已經(jīng)提交的數(shù)據(jù)，就出現(xiàn)了兩次讀取到的數(shù)據(jù)不一致的現(xiàn)象。12.3.2MySQL的隔離級(jí)別[例12-8]：設(shè)事務(wù)T1多次讀取card表的數(shù)據(jù)，事務(wù)T2在事務(wù)T1執(zhí)行期間，對(duì)card表進(jìn)行了數(shù)據(jù)的插入、修改和刪除操作，如表所示。12.3.2MySQL的隔離級(jí)別如果事務(wù)T1中第一個(gè)SELECT語(yǔ)句先對(duì)card表做了一次讀取操作續(xù)：事務(wù)T1是讀事務(wù)，對(duì)數(shù)據(jù)不加鎖，事務(wù)T2是寫(xiě)事務(wù)，成功加X(jué)鎖后對(duì)card表做了插入、修改、刪除操作。事務(wù)T1的第二、三、四個(gè)SELECT語(yǔ)句讀到的結(jié)果仍然是事務(wù)T1的第一個(gè)SELECT語(yǔ)句讀到的歷史版本。當(dāng)事務(wù)T1提交之后，第四個(gè)SELECT語(yǔ)句讀到的就是事務(wù)T2提交后的數(shù)據(jù)，12.3.3事務(wù)開(kāi)始的時(shí)間點(diǎn)STARTTRANSACTION和STARTTRANSACTIONWITHCONSISTENTSNAPSHOT情況下事務(wù)開(kāi)始的時(shí)間點(diǎn)是不同的。有WITHCONSISTENTSNAPSHOT子句時(shí)，STARTTRANSACTION作為事務(wù)開(kāi)始的時(shí)間點(diǎn)。沒(méi)有WITHCONSISTENTSNAPSHOT子句的情況下，執(zhí)行STARTTRANSACTION之后的第一條語(yǔ)句時(shí)事務(wù)才真正開(kāi)始。并發(fā)調(diào)度的可串行性12.4SlockBY=B=2UnlockBXlockAA=Y+Z1寫(xiě)回A(=3)UnlockA

SlockAX=A=3UnlockAXlockBB=X+1寫(xiě)回B(=4)UnlockB

T1T212.4.1串行調(diào)度和可串行性1）串行(Serial)調(diào)度，正確的調(diào)度A=3，B=4A=4，B=3

SlockBY=B=3UnlockBXlockAA=Y+1寫(xiě)回A(=4)UnlockA

SlockAX=A=2UnlockAXlockBB=X+1寫(xiě)回B(=3)UnlockB

T1T212.4.1串行調(diào)度和可串行性2）不可串行化的調(diào)度A=3，B=3,由于其執(zhí)行結(jié)果與1)兩個(gè)結(jié)果都不同，所以是錯(cuò)誤的調(diào)度。SlockBY=B=2

UnlockB

XlockAA=Y+1寫(xiě)回A(=3)

UnlockA

SlockAX=A=2

UnlockA

XlockBB=X+1寫(xiě)回B(=3)

UnlockBT1T212.4.1串行調(diào)度和可串行性3）可串行性(Serializability)A=3，B=4,由于