第10章 MySQL多用戶(hù)事務(wù)管理

第10章

MySQL多用戶(hù)事務(wù)管理事務(wù)管理10.110.2多用戶(hù)訪(fǎng)問(wèn)10.1事務(wù)管理10.1.1事務(wù)的概念使用一個(gè)簡(jiǎn)單的例子來(lái)幫助理解事務(wù)：向公司添加一名新的雇員（見(jiàn)圖10.1）。10.1.2ACID屬性1．原子性原子性意味著每個(gè)事務(wù)都必須被看作是一個(gè)不可分割的單元。假設(shè)一個(gè)事務(wù)由兩個(gè)或者多個(gè)任務(wù)組成，其中的語(yǔ)句必須同時(shí)成功才能認(rèn)為整個(gè)事務(wù)是成功的。如果事務(wù)失敗，系統(tǒng)將會(huì)返回到該事務(wù)以前的狀態(tài)。2．一致性不管事務(wù)是完全成功完成還是中途失敗，當(dāng)事務(wù)使系統(tǒng)處于一致的狀態(tài)時(shí)存在一致性。參照前面的例子，一致性是指如果從系統(tǒng)中刪除了一個(gè)雇員，則所有和該雇員相關(guān)的數(shù)據(jù)，包括工資數(shù)據(jù)和組的成員資格也要被刪除。3．隔離性隔離性是指每個(gè)事務(wù)在它自己的空間發(fā)生，和其他發(fā)生在系統(tǒng)中的事務(wù)隔離，而且事務(wù)的結(jié)果只有在它完全被執(zhí)行時(shí)才能看到。即使在這樣的一個(gè)系統(tǒng)中同時(shí)發(fā)生了多個(gè)事務(wù)，隔離性原則保證某個(gè)特定事務(wù)在完全完成之前，其結(jié)果是看不見(jiàn)的。10.1.2ACID屬性4．持久性持久性是指即使系統(tǒng)崩潰，一個(gè)提交的事務(wù)仍然存在。當(dāng)一個(gè)事務(wù)完成，數(shù)據(jù)庫(kù)的日志已經(jīng)被更新時(shí)，持久性就開(kāi)始發(fā)生作用。大多數(shù)RDBMS產(chǎn)品通過(guò)保存所有行為的日志來(lái)保證數(shù)據(jù)的持久性，這些行為是指在數(shù)據(jù)庫(kù)中以任何方法更改數(shù)據(jù)。數(shù)據(jù)庫(kù)日志記錄了所有對(duì)于表的更新、查詢(xún)、報(bào)表等。10.1.3事務(wù)處理在MySQL中，當(dāng)一個(gè)會(huì)話(huà)開(kāi)始時(shí)，系統(tǒng)變量AUTOCOMMIT值為1，即自動(dòng)提交功能是打開(kāi)的，當(dāng)用戶(hù)每執(zhí)行一條SQL語(yǔ)句后，該語(yǔ)句對(duì)數(shù)據(jù)庫(kù)的修改就立即被提交成為持久性修改保存到磁盤(pán)上，一個(gè)事務(wù)也就結(jié)束了。因此，用戶(hù)必須關(guān)閉自動(dòng)提交，事務(wù)才能由多條SQL語(yǔ)句組成，使用如下語(yǔ)句：SET@@AUTOCOMMIT=0;執(zhí)行此語(yǔ)句后，必須明確地指示每個(gè)事務(wù)的終止，事務(wù)中的SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)所做的修改才能成為持久化修改。例如，執(zhí)行如下語(yǔ)句：deletefromxswhere學(xué)號(hào)='081101';select*fromxs;10.1.3事務(wù)處理1．開(kāi)始事務(wù)當(dāng)一個(gè)應(yīng)用程序的第一條SQL語(yǔ)句或者在COMMIT或ROLLBACK語(yǔ)句（后面介紹）后的第一條SQL執(zhí)行后，一個(gè)新的事務(wù)也就開(kāi)始了。另外還可以使用一條STARTTRANSACTION語(yǔ)句來(lái)顯式地啟動(dòng)一個(gè)事務(wù)。語(yǔ)法格式：STARTTRANSACTION|BEGINWORK2．結(jié)束事務(wù)COMMIT語(yǔ)句是提交語(yǔ)句，它使得自從事務(wù)開(kāi)始以來(lái)所執(zhí)行的所有數(shù)據(jù)修改成為數(shù)據(jù)庫(kù)的永久部分，也標(biāo)志一個(gè)事務(wù)的結(jié)束，其語(yǔ)法格式為：COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]10.1.3事務(wù)處理3．撤銷(xiāo)事務(wù)ROLLBACK語(yǔ)句是撤銷(xiāo)語(yǔ)句，它撤銷(xiāo)事務(wù)所做的修改，并結(jié)束當(dāng)前這個(gè)事務(wù)。

語(yǔ)法格式：ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]在前面的舉例中，若在最后加上以下這條語(yǔ)句：rollbackwork;10.1.3事務(wù)處理4．回滾事務(wù)除了撤銷(xiāo)整個(gè)事務(wù)，用戶(hù)還可以使用ROLLBACKTO語(yǔ)句使事務(wù)回滾到某個(gè)點(diǎn)，在這之前需要使用SAVEPOINT語(yǔ)句來(lái)設(shè)置一個(gè)保存點(diǎn)。SAVEPOINT語(yǔ)法格式為：SAVEPOINTidentifier其中，identifier為保存點(diǎn)的名稱(chēng)。ROLLBACKTOSAVEPOINT語(yǔ)句會(huì)向已命名的保存點(diǎn)回滾一個(gè)事務(wù)。如果在保存點(diǎn)被設(shè)置后，當(dāng)前事務(wù)對(duì)數(shù)據(jù)進(jìn)行了更改，則這些更改會(huì)在回滾中被撤銷(xiāo)，語(yǔ)法格式為：ROLLBACK[WORK]TOSAVEPOINTidentifierRELEASESAVEPOINT語(yǔ)句會(huì)從當(dāng)前事務(wù)的一組保存點(diǎn)中刪除已命名的保存點(diǎn)。不出現(xiàn)提交或回滾。如果保存點(diǎn)不存在，會(huì)出現(xiàn)錯(cuò)誤。語(yǔ)法格式為：RELEASESAVEPOINTidentifier10.1.3事務(wù)處理下面幾個(gè)語(yǔ)句說(shuō)明了有關(guān)事務(wù)的處理過(guò)程：1.STARTTRANSACTION2.UPDATE…3.DELETE…4.SAVEPOINTS1;5.DELETE…6.ROLLBACKWORKTOSAVEPOINTS1;7.INSERT…8.COMMITWORK;10.1.4事務(wù)隔離級(jí)基于A(yíng)NSI/ISOSQL規(guī)范，MySQL提供了下面4種隔離級(jí)：序列化（SERIALIZABLE）、可重復(fù)讀（REPEATABLEREAD）、提交讀（READCOMMITTED）、未提交讀（READUNCOMMITTED）。只有支持事務(wù)的存儲(chǔ)引擎才可以定義一個(gè)隔離級(jí)。定義隔離級(jí)可以使用SETTRANSACTION語(yǔ)句。語(yǔ)法格式：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVELSERIALIZABLE|REPEATABLEREAD|READCOMMITTED|READUNCOMMITTED10.1.4事務(wù)隔離級(jí)（1）序列化如果隔離級(jí)為序列化，用戶(hù)之間通過(guò)一個(gè)接一個(gè)順序地執(zhí)行當(dāng)前的事務(wù)提供了事務(wù)之間最大限度的隔離。

（2）可重復(fù)讀在這一級(jí)上，事務(wù)不會(huì)被看成是一個(gè)序列。不過(guò)，當(dāng)前在執(zhí)行事務(wù)的變化仍然不能看到，也就是說(shuō)，如果用戶(hù)在同一個(gè)事務(wù)中執(zhí)行同條SELECT語(yǔ)句數(shù)次，結(jié)果總是相同的。（3）提交讀READCOMMITTED隔離級(jí)的安全性比REPEATABLEREAD隔離級(jí)的安全性要差。不僅處于這一級(jí)的事務(wù)可以看到其他事務(wù)添加的新記錄，而且其他事務(wù)對(duì)現(xiàn)存記錄做出的修改一旦被提交，也可以看到。10.1.4事務(wù)隔離級(jí)（4）未提交讀提供了事務(wù)之間最小限度的隔離。除了容易產(chǎn)生虛幻的讀操作和不能重復(fù)的讀操作外，處于這個(gè)隔離級(jí)的事務(wù)可以讀到其他事務(wù)還沒(méi)有提交的數(shù)據(jù)，如果這個(gè)事務(wù)使用其他事務(wù)不提交的變化作為計(jì)算的基礎(chǔ)，然后那些未提交的變化被它們的父事務(wù)撤銷(xiāo)，這就導(dǎo)致了大量的數(shù)據(jù)變化。系統(tǒng)變量TX_ISOLATION中存儲(chǔ)了事務(wù)的隔離級(jí)，可以使用SELECT隨時(shí)獲得當(dāng)前隔離級(jí)的值，如下所示：10.1.4事務(wù)隔離級(jí)沒(méi)有一個(gè)標(biāo)準(zhǔn)公式來(lái)決定哪個(gè)隔離級(jí)適用于應(yīng)用程序—大多數(shù)情況下，這是一個(gè)主觀(guān)的決定，它是基于應(yīng)用程序的容錯(cuò)能力和應(yīng)用程序開(kāi)發(fā)者對(duì)于潛在數(shù)據(jù)錯(cuò)誤的影響的判斷。隔離級(jí)的選擇對(duì)于每個(gè)應(yīng)用程序也是沒(méi)有標(biāo)準(zhǔn)的。例如，同一個(gè)應(yīng)用程序的不同事務(wù)基于執(zhí)行的任務(wù)需要不同的隔離級(jí)。圖10.2中列出了事務(wù)隔離級(jí)和性能之間的關(guān)系。10.2多用戶(hù)訪(fǎng)問(wèn)10.2.1鎖定的級(jí)別表鎖定：一個(gè)特殊類(lèi)型的訪(fǎng)問(wèn)，整個(gè)表被客戶(hù)鎖定。根據(jù)鎖定的類(lèi)型，其他客戶(hù)不能向表中插入記錄，甚至從中讀數(shù)據(jù)也受到限制。頁(yè)鎖定：MySQL將鎖定表中的某些行（稱(chēng)作頁(yè)）。被鎖定的行只對(duì)鎖定最初的線(xiàn)程是可行的。行鎖定：行級(jí)的鎖定比表級(jí)鎖定或頁(yè)級(jí)鎖定對(duì)鎖定過(guò)程提供了更精細(xì)的控制。死鎖：如果很多用戶(hù)同時(shí)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的話(huà)，一個(gè)常見(jiàn)的現(xiàn)象就是死鎖。10.2.2鎖定與解鎖MySQL提供了LOCKTABLES語(yǔ)句來(lái)鎖定當(dāng)前線(xiàn)程的表，語(yǔ)法格式如下：LOCKTABLEStbl_name[ASalias]{READ[LOCAL]|[LOW_PRIORITY]WRITE}說(shuō)明：表鎖定支持以下類(lèi)型的鎖定。READ：讀鎖定，確保用戶(hù)可以讀取表，但是不能修改表。WRITE：寫(xiě)鎖定，只有鎖定該表的用戶(hù)可以修改表，其他用戶(hù)無(wú)法訪(fǎng)問(wèn)該表。10.2.2鎖定與解鎖【例10.1】在xs表上設(shè)置一個(gè)只讀鎖定。locktablesxsread;【例10.2】在kc表上設(shè)置一個(gè)寫(xiě)鎖定。locktableskcwrite;在鎖定表以后，可以使用UNLOCKTABLES命令解除鎖定，語(yǔ)法格式為：unlocktables;10.2.3并發(fā)訪(fǎng)問(wèn)的問(wèn)題丟失更新（LostUpdate），指當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，因此最后的更新將重寫(xiě)由其他事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。臟讀（DirtyRead）指一個(gè)事務(wù)正在訪(fǎng)問(wèn)數(shù)據(jù)，而其他事務(wù)正在更新該數(shù)據(jù)，但尚未提交，此時(shí)就會(huì)發(fā)生臟讀問(wèn)題，即第一個(gè)事務(wù)所讀取的數(shù)據(jù)是“臟”（不正確）數(shù)據(jù)，它可能會(huì)引起錯(cuò)誤。10.2.3并發(fā)訪(fǎng)問(wèn)的問(wèn)題不可重復(fù)讀（UnrepeatableRead）是當(dāng)一個(gè)事務(wù)多次訪(fǎng)問(wèn)同一行而且每次讀取不同的數(shù)據(jù)時(shí)會(huì)發(fā)生的問(wèn)題。它與臟讀有相似之處，因?yàn)樵撌?/p>