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

第11章數(shù)據(jù)庫的安全性控制本章介紹數(shù)據(jù)庫的權(quán)限系統(tǒng)、訪問控制實現(xiàn)原理，以及用戶管理、角色管理及權(quán)限管理的實現(xiàn)方法。數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用11.1訪問控制實現(xiàn)原理11.2用戶管理11.3權(quán)限管理11.4角色管理主要內(nèi)容訪問控制實現(xiàn)原理11.111.1訪問控制實現(xiàn)原理不同的數(shù)據(jù)庫管理系統(tǒng)提供的安全機制，不論是權(quán)限劃分還是實現(xiàn)方式可能不太一樣。MySQL權(quán)限系統(tǒng)通過對連接的用戶進行身份認(rèn)證、對通過認(rèn)證的合法用戶賦予權(quán)限兩個階段進行安全性控制。MySQL的安全機制涉及到三個方面：權(quán)限表用戶管理權(quán)限管理11.1.1對連接的用戶進行身份認(rèn)證“mysql”數(shù)據(jù)庫（安裝MySQL時被創(chuàng)建，數(shù)據(jù)庫名稱為“mysql”）中包含user、db、table_priv、columns_priv等重要的權(quán)限表。當(dāng)用戶通過用戶名和密碼與服務(wù)器進行連接的時候，服務(wù)器先查看user表。對于身份認(rèn)證，MySQL是通過IP地址、用戶名、密碼聯(lián)合進行確認(rèn)的。同一個用戶名在不同的IP地址登陸系統(tǒng)，會被MySQL視為不同的用戶。例如root@localhost表示用戶root只能從本地（localhost）進行連接且密碼正確才可以通過認(rèn)證，此用戶從其他任何主機對數(shù)據(jù)庫進行的連接都將被拒絕。系統(tǒng)判斷連接的IP地址、用戶名和密碼是否存在于user表中，如果存在，則通過身份驗證，該用戶便可以連接服務(wù)器并進入到第二個階段，否則拒絕連接。11.1.2對通過認(rèn)證的合法用戶賦予相應(yīng)的權(quán)限MySQL通過權(quán)限表控制用戶對數(shù)據(jù)庫的訪問，權(quán)限表user、db、table_priv、columns_priv的權(quán)限范圍依次遞減。user表不僅能限制用戶連接服務(wù)器，還存儲了所有用戶的全局權(quán)限信息。db表存儲的是所有用戶在數(shù)據(jù)庫層的權(quán)限信息。tables_priv表存儲的是所有用戶在表層的權(quán)限信息。columns_priv表存儲的是所有用戶在列層的權(quán)限信息。按照全局權(quán)限覆蓋局部權(quán)限的原則，用戶如果通過身份驗證登錄系統(tǒng)后，按照user、db、table_priv、columns_priv的順序得到數(shù)據(jù)庫權(quán)限。用戶管理11.211.2.1創(chuàng)建用戶CREATEUSER語句用來創(chuàng)建用戶，其基本的語法格式為：CREATEUSER[IFNOTEXISTS]'username'@'client_host'IDENTIFIEDBY'password';參數(shù)：（1）username：創(chuàng)建的用戶名。（2）client_host：允許用戶連接的主機。（3）password：用戶的登錄密碼。[例11-1]：創(chuàng)建用戶user1，密碼為123456。CREATEUSER'user1'@'localhost'IDENTIFIEDBY'123456';11.2.2修改用戶密碼ALTERUSER語句用于修改普通用戶的密碼，其基本的語法格式為：ALTERUSER[IFEXISTS]'username'@'client_host'IDENTIFIEDBY'newpassword';[例11-2]：把user1的密碼改為123。ALTERUSER'user1'@'localhost'IDENTIFIEDBY'123';11.2.3修改用戶名RENAMEUSER語句用于修改普通用戶名，其基本的語法格式為：RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...[例11-3]：把user1的用戶名改為Zhouping。RENAMEUSER'user1'@'localhost'TO'Zhouping'@'localhost';11.2.4刪除用戶DROPUSER語句用于刪除用戶，其基本的語法格式為：DROPUSER[IFEXISTS]user[,user]...[例11-4]：例11-5：刪除用戶Zhouping。DROPUSER'Zhouping'@'localhost';權(quán)限管理11.311.3.1授權(quán)GRANT語句用于向用戶授權(quán)，其基本的語法格式為：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)限，同時賦予多個權(quán)限時權(quán)限之間使用逗號分開。CREATE、ALTER、DROP、INSERT、DELETE、UPDATE，ALL，USAGE（2）priv_level：MySQL支持三種級別的權(quán)限管理：全局性權(quán)限、數(shù)據(jù)庫級別的權(quán)限、數(shù)據(jù)庫對象級別的權(quán)限。（3）WITHGRANTOPTION：該帳戶可以為其他帳戶分配權(quán)限。11.3.2收回權(quán)限REVOKE語句用于收回權(quán)限，其基本的語法格式為：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMuser[,user]...修改完權(quán)限以后需要重啟服務(wù)或用FLUSHPRIVILEGES語句刷新服務(wù)。11.3.3實例[例11-5]：已知某用戶的用戶名為user1，host為localhost，密碼為123456。對其進行授權(quán)和權(quán)限收回。GRANTALLON*.*TOuser1@'localhost';REVOKEALLON*.*FROMuser1@'localhost';[例11-6]：授予用戶user1校園卡管理數(shù)據(jù)庫中salebill表的INSERT、UPDATE、SELECT權(quán)限，隨后收回該用戶對salebill表的UPDATE權(quán)限。GRANTINSERT,UPDATE,SELECTONcardmanagement.salebillTO'user1'@'localhost';REVOKEUPDATEoncardmanagement.salebillFROM'user1'@'localhost';GRANT語句執(zhí)行后，以user1身份連接服務(wù)器，對salebill表進行查詢、插入、修改、刪除操作。觀察結(jié)果。REVOKE語句執(zhí)行成功后，user1再次修改salebill表。觀察結(jié)果角色管理11.411.4.1創(chuàng)建角色CREATEROLE語句用于創(chuàng)建角色，其基本的語法格式為：CREATEROLE[IFNOTEXISTS]role[,role]...參數(shù)：（1）role：角色名。角色名和帳戶名相同，也是名字+host，如果沒有寫host，默認(rèn)為'%'（2）IFNOTEXISTS：創(chuàng)建一個已經(jīng)存在的角色，默認(rèn)情況下會發(fā)生錯誤；如果提供了IFNOTEXISTS子句則不會報錯。[例11-7]：創(chuàng)建三個角色，角色名分別為admin、creator和reader。CREATEROLE'admin','creator','reader';11.4.2為角色授權(quán)給角色授權(quán)也是使用GRANT語句，其基本的語法格式如下：GRANTpriv_type[(column_list)][,priv_type[(column_list)]]...ONpriv_levelTOrole[,role]...[WITHGRANTOPTION]參數(shù)同給用戶授權(quán)的GRANT語句。[例11-8]：給cardmanagement數(shù)據(jù)庫中所有表的查詢權(quán)限。GRANTSELECTONcardmanagement.*TO'reader';11.4.3收回角色權(quán)限收回角色權(quán)限也是使用REVOKE語句，其基本的語法格式如下：REVOKEpriv_type[(column_list)][,priv_type[(column_list)]]...ON[object_type]priv_levelFROMrole[,role]...參數(shù)同給用戶權(quán)限收回的REVOKE語句。[例11-9]：收回creator角色的權(quán)限。REVOKEINSERT,UPDATE,DELETE

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

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

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

讀A=16

A←A-1寫回A=15COMMIT①讀A=16

②

③A←A-1

寫回A=15COMMIT

④T2T1(a)丟失修改

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

讀B=200COMMIT

①讀B=100B←B*2

寫回B②

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

讀C=100C←C*2寫回C=200COMMIT

①

讀C=100

②

③

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

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

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

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

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

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

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

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

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

SlockAX=A=3UnlockAXlockBB=X+1寫回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寫回A(=4)UnlockA

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

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

UnlockB

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

UnlockA

SlockAX=A=2

UnlockA

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

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