MySQL數(shù)據(jù)庫(kù)教程課件

第9章事務(wù)學(xué)習(xí)目標(biāo)了解事務(wù)的概念和事務(wù)的基本特性。掌握事務(wù)控制語句。了解事務(wù)并發(fā)操作會(huì)引起的問題。了解事務(wù)隔離級(jí)別與鎖機(jī)制之間的關(guān)系9.1事務(wù)概述在MySQL數(shù)據(jù)庫(kù)中，只有使用了InnoDB存儲(chǔ)引擎的數(shù)據(jù)表才支持事務(wù)。9.1.1事務(wù)的概念事務(wù)是一個(gè)不可分割的程序執(zhí)行單元，由一條或多條數(shù)據(jù)庫(kù)操作命令語句組成。這些數(shù)據(jù)庫(kù)操作命令語句要么全部執(zhí)行，要么全部不執(zhí)行。只要有一條操作命令語句執(zhí)行失敗，整個(gè)事務(wù)都將撤銷（回滾），所有被影響的數(shù)據(jù)都將恢復(fù)到事務(wù)開始前的狀態(tài)。與事務(wù)相關(guān)的數(shù)據(jù)庫(kù)操作命令語句包括INSERT、UPDATE和DELETE語句。在MySQL中，通過日志文件來記錄數(shù)據(jù)庫(kù)的所有變化，為事務(wù)回滾提供依據(jù)。

事務(wù)舉例以銀行轉(zhuǎn)賬為例，該事務(wù)主要包括以下3個(gè)步驟。①查詢A賬戶的余額是否足夠。②A賬戶減去1000元。③B賬戶增加1000元。如果A賬戶中的資金已經(jīng)轉(zhuǎn)出，但B賬戶由于發(fā)生故障未轉(zhuǎn)入資金；或者A賬戶中的資金由于網(wǎng)絡(luò)故障未轉(zhuǎn)出，而B賬戶卻已經(jīng)完成了資金轉(zhuǎn)入，這會(huì)給個(gè)人和銀行帶來重大經(jīng)濟(jì)損失。采用事務(wù)處理機(jī)制后，一旦在轉(zhuǎn)賬過程中發(fā)生意外，整個(gè)轉(zhuǎn)賬業(yè)務(wù)將全部撤銷，使A賬戶和B賬戶都恢復(fù)到轉(zhuǎn)賬前的狀態(tài)，從而保證數(shù)據(jù)的完整性。9.1.2事務(wù)的基本特性——ACID1．原子性（Atomicity）原子性是指事務(wù)中的所有數(shù)據(jù)庫(kù)操作命令語句，要么全部執(zhí)行，要么全部不執(zhí)行。以銀行轉(zhuǎn)賬為例，一旦在轉(zhuǎn)賬過程中發(fā)生意外，整個(gè)轉(zhuǎn)賬業(yè)務(wù)將回滾到轉(zhuǎn)賬前。2．一致性（Consistency）一致性是指事務(wù)必須保證數(shù)據(jù)庫(kù)的狀態(tài)保持一致。以銀行轉(zhuǎn)賬為例，A賬戶和B賬戶的金額總計(jì)不會(huì)發(fā)生改變。3．隔離性（Isolation）隔離性是指多個(gè)并發(fā)事務(wù)可以獨(dú)立執(zhí)行，彼此不會(huì)產(chǎn)生影響。例如，對(duì)任意兩個(gè)并發(fā)的事務(wù)1和事務(wù)2，在事務(wù)1看來，事務(wù)2要么在事務(wù)1開始之前已經(jīng)結(jié)束，要么在事務(wù)1結(jié)束后才開始。隔離性使得每個(gè)事務(wù)都感覺不到有其他事務(wù)在并發(fā)執(zhí)行。4．持久性（Durability）持久性是指事務(wù)完成后，對(duì)數(shù)據(jù)的修改是永久的，即便系統(tǒng)發(fā)生故障也不會(huì)丟失。例如，提交一個(gè)事務(wù)后，計(jì)算機(jī)癱瘓或者數(shù)據(jù)庫(kù)因故障受到破壞，重啟計(jì)算機(jī)后，該事務(wù)的執(zhí)行結(jié)果依然存在9.2單個(gè)事務(wù)控制1．開始事務(wù)STARTTRANSACTION|BEGINWORK;在MySQL中，事務(wù)是不允許嵌套的。在第1個(gè)事務(wù)開始后，如果使用STARTTRANSACTION或BEGINWORK語句開始新事務(wù)，則系統(tǒng)會(huì)自動(dòng)提交并結(jié)束第1個(gè)事務(wù)。9.2單個(gè)事務(wù)控制2．完成數(shù)據(jù)庫(kù)操作命令語句【例9-1】開始一個(gè)事務(wù)，使用每次插入一條記錄的方式向課程表course中插入兩門課程（10600150，軟件工程，40）和（10600710，數(shù)據(jù)結(jié)構(gòu)，64）。①開始事務(wù)。STARTTRANSACTION;②插入第1門課程。INSERTINTOcourseVALUES("10600150","軟件工程",40);③插入第2門課程。INSERTINTOcourseVALUES("10600710","數(shù)據(jù)結(jié)構(gòu)",64);④使用SELECT語句查看記錄是否插入成功。SELECT*FROMcourseWHEREcno="10600150"ORcno="10600710";只有當(dāng)前用戶可以看到新記錄插入成功mysql>STARTTRANSACTION;QueryOK,0rowsaffected(0.01sec)mysql>INSERTINTOcourseVALUES("10600150","軟件工程",40);QueryOK,1rowaffected(0.01sec)mysql>INSERTINTOcourseVALUES("10600710","數(shù)據(jù)結(jié)構(gòu)",64);QueryOK,1rowaffected(0.01sec)mysql>SELECT*FROMcourseWHEREcno="10600150"ORcno="10600710";+----------+----------+-------+|cno|cname|hours|+----------+----------+-------+|10600150|軟件工程|40||10600710|數(shù)據(jù)結(jié)構(gòu)|64|+----------+----------+-------+9.2單個(gè)事務(wù)控制3．結(jié)束事務(wù)提交事務(wù)或撤銷事務(wù)都標(biāo)志著一個(gè)事務(wù)的結(jié)束。（1）提交事務(wù)COMMIT;（2）撤銷事務(wù)ROLLBACK;單個(gè)事務(wù)控制舉例【例9-2】開始一個(gè)事務(wù)，使用每次插入一條記錄的方式向課程表course中插入兩門課程（10600151，軟件工程1，40）和（10600711，數(shù)據(jù)結(jié)構(gòu)1，64），最后提交事務(wù)。STARTTRANSACTION;INSERTINTOcourseVALUES("10600151","軟件工程1",40);INSERTINTOcourseVALUES("10600711","數(shù)據(jù)結(jié)構(gòu)1",64);COMMIT;其他用戶就可以使用SELECT語句查詢到相同結(jié)果。SELECT*FROMcourseWHEREcno="10600151"ORcno="10600711";單個(gè)事務(wù)控制舉例【例9-3】開始一個(gè)事務(wù)，使用每次插入一條記錄的方式向課程表course中插入兩門課程（10600152，軟件工程2，40）和（10600712，數(shù)據(jù)結(jié)構(gòu)2，64），最后撤銷事務(wù)。STARTTRANSACTION;INSERTINTOcourseVALUES("10600152","軟件工程2",40);INSERTINTOcourseVALUES("10600712","數(shù)據(jù)結(jié)構(gòu)2",64);ROLLBACK;當(dāng)前事務(wù)回滾后，使用SELECT語句查詢，可以看到新記錄不存在，數(shù)據(jù)庫(kù)恢復(fù)到事務(wù)開始前的狀態(tài)。mysql>SELECT*FROMcourseWHEREcno="10600152"ORcno="10600712";Emptyset(0.01sec)單個(gè)事務(wù)控制舉例【例9-4】開始一個(gè)事務(wù)，向課程表course中插入一門課程（10600153，軟件工程3，40）后設(shè)置一個(gè)保存點(diǎn)sp1，然后向課程表course中插入另一門課程（10600713，數(shù)據(jù)結(jié)構(gòu)3，64），最后將事務(wù)回滾到保存點(diǎn)sp1并提交。STARTTRANSACTION;INSERTINTOcourseVALUES("10600153","軟件工程3",40);SAVEPOINTsp1;INSERTINTOcourseVALUES("10600713","數(shù)據(jù)結(jié)構(gòu)3",64);ROLLBACKTOSAVEPOINTsp1;COMMIT;除了撤銷整個(gè)事務(wù)，用戶還可以使事務(wù)回滾到某個(gè)點(diǎn)，使保存點(diǎn)sp1后的INSERT語句被撤銷了。mysql>SELECT*FROMcourseWHEREcno="10600153"ORcno="10600713";+----------+-------------+-------+|cno|cname|hours|+----------+-------------+-------+|10600153|軟件工程3

|40|+----------+-------------+-------+9.3事務(wù)并發(fā)控制當(dāng)多個(gè)用戶同時(shí)訪問同一個(gè)數(shù)據(jù)庫(kù)對(duì)象時(shí)，在一個(gè)用戶修改數(shù)據(jù)的過程中，可能其他用戶也要修改該數(shù)據(jù)，因此，為了保證數(shù)據(jù)的一致性，需要對(duì)事務(wù)并發(fā)操作進(jìn)行控制。9.3.1事務(wù)并發(fā)會(huì)引起的問題1．丟失更新（lostupdate）分為兩類：一類是回滾丟失更新，另一類是覆蓋丟失更新?；貪L丟失更新是指后一個(gè)事務(wù)回滾時(shí)覆蓋了前一個(gè)事務(wù)提交的數(shù)據(jù)更新所造成的數(shù)據(jù)丟失。9.3.1事務(wù)并發(fā)會(huì)引起的問題1．丟失更新（lostupdate）覆蓋丟失更新是指后一個(gè)事務(wù)提交的數(shù)據(jù)更新覆蓋了前一個(gè)事務(wù)提交的數(shù)據(jù)更新所造成的數(shù)據(jù)丟失。9.3.1事務(wù)并發(fā)會(huì)引起的問題2．臟讀（dirtyread）臟讀是指一個(gè)事務(wù)讀取了另一個(gè)事務(wù)未提交的數(shù)據(jù)。這里將未提交且隨后又被撤銷的數(shù)據(jù)稱為臟數(shù)據(jù)。9.3.1事務(wù)并發(fā)會(huì)引起的問題3．不可重復(fù)讀（non-repeatableread）不可重復(fù)讀是指同一個(gè)事務(wù)前后兩次讀取的數(shù)據(jù)不同。9.3.1事務(wù)并發(fā)會(huì)引起的問題4．幻讀（phantomread）幻讀是指同一個(gè)事務(wù)前后兩次使用相同查詢統(tǒng)計(jì)語句的執(zhí)行結(jié)果不同。9.3.2事務(wù)隔離級(jí)別為了解決事務(wù)并發(fā)操作可能引起的丟失更新、臟讀、不可重復(fù)讀和幻讀問題，MySQL提供了4種事務(wù)隔離級(jí)別。1．未提交讀（READUNCOMMITTED）所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。不能解決丟失更新、臟讀、不可重復(fù)讀和幻讀問題，在實(shí)際中很少應(yīng)用。2．提交讀（READCOMMITTED）一個(gè)事務(wù)只能看到已提交事務(wù)所做的數(shù)據(jù)修改?？梢越鉀Q回滾丟失更新和臟讀問題，但不能解決不可重復(fù)讀、覆蓋丟失更新和幻讀問題。3．可重復(fù)讀（REPEATABLEREAD）MySQL默認(rèn)的事務(wù)隔離級(jí)別，可以確保同一個(gè)事務(wù)內(nèi)相同的查詢語句的查詢結(jié)果一致。可以解決回滾丟失更新、臟讀、不可重復(fù)讀和覆蓋丟失更新問題，但不能解決幻讀問題。4．序列化（SERIALIZABLE）事務(wù)只能一個(gè)接著一個(gè)地執(zhí)行，不能并發(fā)執(zhí)行?？梢越鉀Q丟失更新、臟讀、不可重復(fù)讀和幻讀問題。9.3.2事務(wù)隔離級(jí)別低級(jí)別的事務(wù)隔離可以提高事務(wù)的并發(fā)執(zhí)行性能，但會(huì)導(dǎo)致丟失更新、臟讀、不可重復(fù)讀和幻讀等并發(fā)問題；高級(jí)別的事務(wù)隔離可以有效避免并發(fā)問題，但會(huì)降低事務(wù)的并發(fā)執(zhí)行性能。9.3.3鎖機(jī)制鎖機(jī)制是實(shí)現(xiàn)事務(wù)并發(fā)控制的主要方法和重要手段。在MySQL中，不同的事務(wù)隔離級(jí)別是通過鎖機(jī)制來實(shí)現(xiàn)的。9.3.3鎖機(jī)制1．鎖的類型按照鎖的共享策略，可將鎖劃分為2種：（1）共享鎖：S鎖，也稱讀鎖（READLOCK）。一個(gè)事務(wù)對(duì)數(shù)據(jù)加讀鎖后，其他事務(wù)只能讀取這些數(shù)據(jù)，不能修改，可以避免不可重復(fù)讀問題。（2）排他鎖：X鎖，也稱寫鎖（WRITELOCK）。一個(gè)事務(wù)對(duì)數(shù)據(jù)加寫鎖后，只有該事務(wù)可以操作這些數(shù)據(jù)，其他的事務(wù)既不能讀，也不能修改，可以避免臟讀問題。9.3.3鎖機(jī)制1．鎖的類型按照對(duì)數(shù)據(jù)操作的粒度，可將鎖劃分為3種：（1）表級(jí)鎖：鎖定整張表，其他事務(wù)訪問同一張表時(shí)將受到限制。（2）行級(jí)鎖：只鎖定所使用的一條或多條記錄行，當(dāng)其他事務(wù)訪問同一張表時(shí)，只有被鎖定的記錄不能訪問，其他的記錄可以正常訪問。（3）頁級(jí)鎖：介于表級(jí)鎖和行級(jí)鎖之間，是MySQL獨(dú)特的一種鎖機(jī)制，它鎖定表中相鄰的一組記錄行作為頁來實(shí)現(xiàn)控制。9.3.3鎖機(jī)制2．隔離級(jí)別與鎖的關(guān)系（1）在未提交讀級(jí)別下，在讀取數(shù)據(jù)時(shí)不加鎖；在修改數(shù)據(jù)的過程中，對(duì)需要更新的數(shù)據(jù)加行級(jí)共享鎖，使其他事務(wù)不能修改，但是可以讀取數(shù)據(jù)，直到事務(wù)結(jié)束。因此，其他事務(wù)可以讀取到未提交的數(shù)據(jù)，不能避免臟讀問題。（2）在提交讀級(jí)別下，在讀取數(shù)據(jù)時(shí)加行級(jí)共享鎖，讀取結(jié)束就釋放；在修改數(shù)據(jù)的過程中，對(duì)需要更新的數(shù)據(jù)加行級(jí)排他鎖，直到事務(wù)結(jié)束。這樣可有效防止其他事務(wù)讀取到未提交的數(shù)據(jù)。但由于讀取數(shù)據(jù)后就立即釋放了行級(jí)共享鎖，因此可能會(huì)出現(xiàn)從同一事務(wù)中讀取到的數(shù)據(jù)前后不一致的情況，不能避免不可重復(fù)讀問題。（3）在可重復(fù)讀級(jí)別下，在讀取數(shù)據(jù)時(shí)加行級(jí)共享鎖，直到事務(wù)結(jié)束；在修改數(shù)據(jù)的過程中，對(duì)需要更新的數(shù)據(jù)加行級(jí)排他鎖，直到事務(wù)結(jié)束。因此不會(huì)出現(xiàn)從同一事務(wù)中讀取到的數(shù)據(jù)前后不一致的情況。（4）在序列化級(jí)別下，在讀取數(shù)據(jù)時(shí)加表級(jí)共享鎖，直到事務(wù)結(jié)束；在修改數(shù)據(jù)時(shí)加表級(jí)排他鎖，直到事務(wù)結(jié)束。因此其他事務(wù)不能讀寫該表中的任何數(shù)據(jù)，可以避免由事務(wù)并發(fā)操作引起的任何問題。9.4課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的事務(wù)控制1．新開課程事務(wù)控制要求編寫存儲(chǔ)過程，使用每次插入一條記錄的方式將兩門新課程（10500260，國(guó)際金融學(xué)，48）和（10400350，電路理論，56）插入課程表course中。（1）不使用事務(wù)控制的存儲(chǔ)過程。（2）使用事務(wù)控制的存儲(chǔ)過程。9.4課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的事務(wù)控制1．新開課程事務(wù)控制（1）不使用事務(wù)控制的存儲(chǔ)過程。DELIMITER$$CREATEPROCEDUREp_insert_course()BEGININSERTINTOcourseVALUES("10500260","國(guó)際金融學(xué)",48);INSERTINTOcourseVALUES("10400350","電路理論",56);END$$DELIMITER;課程編號(hào)為10400350的課程已經(jīng)存在，插入第2條記錄時(shí)違反了課程編號(hào)的主鍵約束，導(dǎo)致錯(cuò)誤。最終只有第1條記錄插入成功了。9.4課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的事務(wù)控制1．新開課程事務(wù)控制（2）使用事務(wù)控制的存儲(chǔ)過程。DELIMITER$$CREATEPROCEDUREp_insert_course()BEGINDECLAREhasErrorINTDEFAULTFALSE;DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=TRUE;STARTTRANSACTION;INSERTINTOcourseVALUES("10500260","國(guó)際金融學(xué)",48);INSERTINTOcourseVALUES("10400350","電路理論",56);IFhasError=TRUETHENROLLBACK;SELECT"失敗"AS插入結(jié)果;ELSECOMMIT;SELECT"成功"AS插入結(jié)果;ENDIF;END$$DELIMITER;執(zhí)行結(jié)果兩條INSERT語句都回滾了，新課程插入失敗。9.4課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的事務(wù)控制2．修改院系代碼事務(wù)控制要求在存儲(chǔ)過程中定義事務(wù)，修改院系表department中某個(gè)學(xué)院的院系代碼deptno，將原來的old_deptno修改為新的new_deptno。為了保證數(shù)據(jù)的一致性，還需要同步修改學(xué)生表student和教師表teacher中與該學(xué)院相關(guān)的學(xué)生和教師的院系代碼deptno。只有這些修改操作都成功，才提交，否則回滾。9.4課堂案例：學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)的事務(wù)控制2．修改院系代碼事務(wù)控制要求在存儲(chǔ)過程中定義事務(wù)，修改院系表department中某個(gè)學(xué)院的院系代碼deptno，將原來的old_deptno修改為新的new_deptno。為了保證數(shù)據(jù)的一致性，還需要同步修改學(xué)生表student和教師表teacher中與該學(xué)院相關(guān)的學(xué)生和教師的院系代碼deptno。只有這些修改操作都成功，才提交，否則回滾。DELIMITER$$

CREATEPROCEDUREp_deptno(INold_deptnoCHAR(3),INnew_deptnoCHAR(3))BEGINDECLAREhasErrorINTDEFAULTFALSE;DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThasError=TRUE;STARTTRANSACTION;UPDATEdepartmentSETdeptno=new_deptnoWHEREdeptno=old_deptno;UPDATEstudentSETdeptno=new_deptnoWHEREdeptno=old_deptno;UPDATEteacherSETdeptno=new_deptnoWHEREdeptno=old_deptno;IFhasError=TRUETHENROLLBACK;SELECT"失敗"AS修改結(jié)果;ELSECOMMIT;SELECT"成功"AS修改結(jié)果;ENDIF;END$$DELIMITER;9.4