第10章

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

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

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