1第14章事務(wù)和鎖14.1事務(wù)的基本概念14.2事務(wù)控制語句14.3

事務(wù)的并發(fā)處理14.4管理鎖數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）214.1事務(wù)的基本概念

14.1.1事務(wù)的概念在MySQL環(huán)境中，事務(wù)(Transaction)是由作為一個(gè)邏輯單元的一條或多條SQL語句組成。其結(jié)果是作為整體永久性地修改數(shù)據(jù)庫的內(nèi)容，或者作為整體取消對(duì)數(shù)據(jù)庫的修改。

事務(wù)是數(shù)據(jù)庫程序的基本單位，一般地，一個(gè)程序包含多個(gè)事務(wù)，數(shù)據(jù)存儲(chǔ)的邏輯單位是數(shù)據(jù)塊，數(shù)據(jù)操作的邏輯單位是事務(wù)。現(xiàn)實(shí)生活中的銀行轉(zhuǎn)賬、網(wǎng)上購物、庫存控制、股票交易等，都是事物的例子。例如，將資金從一個(gè)銀行賬戶轉(zhuǎn)到另一個(gè)銀行賬戶，第一個(gè)操作從一個(gè)銀行賬戶中減少一定的資金，第二個(gè)操作向另一個(gè)銀行賬戶中增加相應(yīng)的資金，減少和增加這兩個(gè)操作必須作為整體永久性地記錄到數(shù)據(jù)庫中，否則資金會(huì)丟失。如果轉(zhuǎn)賬發(fā)生問題，必須同時(shí)取消這兩個(gè)操作。一個(gè)事務(wù)可以包括多條INSERT、UPDATE和DELETE語句。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）314.1事務(wù)的基本概念

14.1.2事務(wù)特性事務(wù)定義為一個(gè)邏輯工作單元，即一組不可分割的SQL語句。數(shù)據(jù)庫理論對(duì)事務(wù)有更嚴(yán)格的定義，指明事務(wù)有4個(gè)基本特性，稱為ACID特性，即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability）。（1）原子性事務(wù)必須是原子工作單元，即一個(gè)事務(wù)中包含的所有SQL語句組成一個(gè)工作單元。（2）一致性事務(wù)必須確保數(shù)據(jù)庫的狀態(tài)保持一致，事務(wù)開始時(shí)，數(shù)據(jù)庫的狀態(tài)是一致的，當(dāng)事務(wù)結(jié)束時(shí)，也必須使數(shù)據(jù)庫的狀態(tài)一致。例如，在事務(wù)開始時(shí)，數(shù)據(jù)庫的所有數(shù)據(jù)都滿足已設(shè)置的各種約束條件和業(yè)務(wù)規(guī)則，在事務(wù)結(jié)束時(shí)，數(shù)據(jù)雖然不同，必須仍然滿足先前設(shè)置的各種約束條件和業(yè)務(wù)規(guī)則，事務(wù)把數(shù)據(jù)庫從一個(gè)一致性狀態(tài)帶入另一個(gè)一致性狀態(tài)。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）414.1事務(wù)的基本概念

（3）隔離性多個(gè)事務(wù)可以獨(dú)立運(yùn)行，彼此不會(huì)發(fā)生影響。這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其它事務(wù)。（4）持久性一個(gè)事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫中數(shù)據(jù)的改變永久有效，即使以后系統(tǒng)崩潰也是如此。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）514.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1.

開始事務(wù)開始事務(wù)可以使用STARTTRANSACTION語句來顯式地啟動(dòng)一個(gè)事務(wù)，另外，當(dāng)一個(gè)應(yīng)用程序的第一條SQL語句或者在COMMIT或ROLLBACK語句后的第一條SQL執(zhí)行后，一個(gè)新的事務(wù)也就開始了。語法格式：

STARTTRANSACTION|BEGINWORK

其中，BEGINWORK語句可以用來替代STARTTRANSACTION語句，但是STARTTRANSACTION更為常用。2.提交事務(wù)COMMIT語句是提交語句，它使得自從事務(wù)開始以來所執(zhí)行的所有數(shù)據(jù)修改成為數(shù)據(jù)庫的永久部分，也標(biāo)志一個(gè)事務(wù)的結(jié)束.語法格式：COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE]614.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）其中，可選的ANDCHAIN子句會(huì)在當(dāng)前事務(wù)結(jié)束時(shí)，立刻啟動(dòng)一個(gè)新事務(wù)，并且新事務(wù)與剛結(jié)束的事務(wù)有相同的隔離等級(jí)。3.撤消事務(wù)撤銷事務(wù)語句使用ROLLBACK，它撤銷事務(wù)所做的修改，并結(jié)束當(dāng)前這個(gè)事務(wù)。語法格式：ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE]4.設(shè)置保存點(diǎn)ROLLBACK語句除了撤銷整個(gè)事務(wù)外，還可以用來使事務(wù)回滾到某個(gè)點(diǎn)，在這之前需要使用SAVEPOINT語句來設(shè)置一個(gè)保存點(diǎn)。語法格式：SAVEPOINT保存點(diǎn)名714.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）ROLLBACKTOSAVEPOINT語句會(huì)向已命名的保存點(diǎn)回滾一個(gè)事務(wù)。如果在保存點(diǎn)被設(shè)置后，當(dāng)前事務(wù)對(duì)數(shù)據(jù)進(jìn)行了更改，則這些更改會(huì)在回滾中被撤銷。語法格式：ROLLBACK[WORK]TOSAVEPOINT保存點(diǎn)名當(dāng)事務(wù)回滾到某個(gè)保存點(diǎn)后，在該保存點(diǎn)之后設(shè)置的保存點(diǎn)將被刪除。814.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）【例14.1】創(chuàng)建數(shù)據(jù)庫test和表usr，在表中插入記錄后，開始第1個(gè)事務(wù)，更新表的記錄，提交第1個(gè)事務(wù)；開始第2個(gè)事務(wù)，更新表的記錄，回滾第2個(gè)事務(wù)。執(zhí)行過程如下：（1）查看MySQL隔離級(jí)別(隔離級(jí)別下一節(jié)介紹)mysql>SHOWVARIABLESLIKE'transaction_isolation';+-------------------------+----------------------------+|Variable_name|Value|+-------------------------+----------------------------+|transaction_isolation|REPEATABLE-READ|+-------------------------+----------------------------+1rowinset,1warning(0.一00sec)可以看出，MySQL默認(rèn)隔離級(jí)別為REPEATABLE-READ（可重復(fù)讀）。914.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）（2）創(chuàng)建數(shù)據(jù)庫test和表usr，在表中插入記錄創(chuàng)建并選擇數(shù)據(jù)庫test：mysql>CREATEDATABASEtest;QueryOK,1rowaffected(0.07sec)mysql>USEtest;Databasechanged創(chuàng)建表usr：mysql>CREATETABLEusr->(->usridint,->namevarchar(12)->);QueryOK,0rowsaffected(0.48sec)在表usr中插入記錄：1014.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）mysql>INSERTINTOusr->VALUES(1,'David'),->(2,'Mary'),->(3,'ben'),->(4,'Iris');QueryOK,4rowsaffected(0.10sec)Records:4Duplicates:0Warnings:0查詢usr表：mysql>SELECT*FROMusr;+--------+------------+|usrid|name|+--------+------------+|1|David||2|Mary||3|ben||4|Iris|+--------+------------+4rowsinset(0.00sec)1114.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）（3）開始第1個(gè)事務(wù)，更新表的記錄，提交第1個(gè)事務(wù)開始第1個(gè)事務(wù)：mysql>BEGINWORK;QueryOK,0rowsaffected(0.00sec)將usr表的第1條記錄的用戶名更新為L(zhǎng)ee：mysql>UPDATEusrSETname='Lee'WHEREusrid=1;QueryOK,1rowaffected(0.06sec)Rowsmatched:1Changed:1Warnings:0提交第1個(gè)事務(wù)：mysql>COMMIT;QueryOK,0rowsaffected(0.00sec)查詢usr表，更新的第1條記錄的用戶名Lee已永久性地保存：mysql>SELECT*FROMusr;1214.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）+--------+---------+|usrid|name|+--------+---------+|1|Lee||2|Mary||3|ben||4|Iris|+--------+---------+（4）開始第2個(gè)事務(wù)，更新表的記錄，回滾第2個(gè)事務(wù)開始第2個(gè)事務(wù)：mysql>STARTTRANSACTION;QueryOK,0rowsaffected(0.00sec)將usr表的第1條記錄的用戶名更新為Qian：mysql>UPDATEusrSETname='Qian'WHEREusrid=1;QueryOK,1rowaffected(0.06sec)Rowsmatched:1Changed:1Warnings:01314.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）查詢usr表：mysql>SELECT*FROMusr;+--------+-------+|usrid|name|+--------+-------+|1|Qian||2|Mary||3|ben||4|Iris|+--------+-------+4rowsinset(0.00sec)回滾第2個(gè)事務(wù)：mysql>ROLLBACK;QueryOK,0rowsaffected(0.06sec)1414.2事務(wù)控制語句

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）查詢usr表，更新的第1條記錄的用戶名Qian已撤銷，恢復(fù)為L(zhǎng)ee：mysql>SELECT*FROMusr;+--------+---------+|usrid|name|+--------+---------+|1|Lee||2|Mary||3|ben||4|Iris|+--------+---------+4rowsinset(0.00sec)數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1514.3

事務(wù)的并發(fā)處理在并發(fā)事務(wù)中，可能發(fā)生以下3種異常情況：

?臟讀(DirtyRead)：讀取未提交的數(shù)據(jù)。

?不可重復(fù)讀(Non-repeatableRead)：同一個(gè)事務(wù)前后兩次讀取的數(shù)據(jù)不同。

?幻讀(PhantomRead)：例如，同一個(gè)事務(wù)前后兩條相同的查詢語句查詢結(jié)果應(yīng)相同，在此期間另一事務(wù)插入并提交了新記錄，當(dāng)本事務(wù)更新時(shí)，會(huì)發(fā)現(xiàn)新插入的記錄，好像以前讀到的數(shù)據(jù)是幻覺。為了處理并發(fā)事務(wù)中可能出現(xiàn)的幻想讀、不可重復(fù)讀、臟讀等問題，數(shù)據(jù)庫實(shí)現(xiàn)了不同級(jí)別的事務(wù)隔離，以防止事務(wù)的相互影響?；贏NSI/ISOSQL規(guī)范，MySQL提供了4種事務(wù)隔離級(jí)別，隔離級(jí)別從低到高依次為：未提交讀(READUNCOMMITTED)、提交讀(READCOMMITTED)、可重復(fù)讀(REPEATABLEREAD)、可串行化(SERIALIZABLE)。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1614.3

事務(wù)的并發(fā)處理（1）未提交讀(READUNCOMMITTED)提供了事務(wù)之間最小限度的隔離，所有事務(wù)都可看到其他未提交事務(wù)的執(zhí)行結(jié)果。臟讀、不可重復(fù)讀和幻讀都允許，該隔離級(jí)別很少用于實(shí)際應(yīng)用。（2）提交讀(READCOMMITTED)該級(jí)別滿足了隔離的簡(jiǎn)單定義，即一個(gè)事務(wù)只能看見已提交事務(wù)所做的改變。該級(jí)別不允許臟讀，但允許不可重復(fù)讀、幻讀。（3）可重復(fù)讀(REPEATABLEREAD)這是MySQL默認(rèn)的事務(wù)隔離級(jí)別，它確保同一事務(wù)內(nèi)相同的查詢語句，執(zhí)行結(jié)果一致。該級(jí)別不允許不可重復(fù)讀和臟讀，但允許幻讀。（4）可串行化(SERIALIZABLE)如果隔離級(jí)別為可串行化，用戶之間通過一個(gè)接一個(gè)順序地執(zhí)行當(dāng)前的事務(wù)提供了事務(wù)之間最大限度的隔離。臟讀、不可重復(fù)讀和幻讀在該級(jí)別都不允許。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1714.3

事務(wù)的并發(fā)處理定義隔離級(jí)可以使用SETTRANSACTION語句，只有支持事務(wù)的存儲(chǔ)引擎才可以定義一個(gè)隔離級(jí)。語法格式：SET[GLOBAL|SESSION]TRANSACTIONISOLATIONLEVEL(READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE)說明：如果指定GLOBAL，那么定義的隔離級(jí)將適用于所有的SQL用戶；如果指定SESSION，則隔離級(jí)只適用于當(dāng)前運(yùn)行的會(huì)話和連接。MySQL默認(rèn)為REPEATABLEREAD隔離級(jí)。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1814.4管理鎖

14.4.1

鎖機(jī)制鎖機(jī)制中的基本概念如下：（1）鎖的粒度鎖的粒度是指鎖的作用范圍。鎖的粒度可以分為服務(wù)器級(jí)鎖（server-levellocking）和存儲(chǔ)引擎級(jí)鎖（storage-engine-levellocking）。InnoDB存儲(chǔ)引擎支持表級(jí)鎖以及行級(jí)鎖，MyISAM存儲(chǔ)引擎支持表級(jí)鎖。（2）隱式鎖與顯式鎖MySQL自動(dòng)加鎖稱為隱式鎖，數(shù)據(jù)庫開發(fā)人員手動(dòng)加鎖稱為顯式鎖。（3）鎖的類型鎖的類型包括讀鎖(readlock)和寫鎖(writelock)，其中讀鎖也稱為共享鎖，寫鎖也稱為排他鎖或者獨(dú)占鎖。讀鎖允許其他MySQL客戶機(jī)對(duì)數(shù)據(jù)同時(shí)“讀”，但不允許其他MySQL客戶機(jī)對(duì)數(shù)據(jù)任何“寫”。寫鎖不允許其他MySQL客戶機(jī)對(duì)數(shù)據(jù)同時(shí)讀，也不允許其他MySQL客戶機(jī)對(duì)數(shù)據(jù)同時(shí)寫。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）1914.4管理鎖

14.4.2鎖的級(jí)別1.表級(jí)鎖表級(jí)鎖指整個(gè)表被客戶鎖定。根據(jù)鎖定的類型，其他客戶不能向表中插入記錄，甚至從中讀數(shù)據(jù)也受到限制。表級(jí)鎖包括讀鎖(readlock)和寫鎖(writelock)兩種。LOCKTABLES語句用于鎖定當(dāng)前線程的表。語法格式：LOCKTABLEStable_name[ASalias]{READ[LOCAL]|[LOS_PRIORITY]WRITE}說明；（1）表鎖定支持以下類型的鎖定：

READ：讀鎖定，確保用戶可以讀取表，但是不能修改表。

WRITE：寫鎖定，只有鎖定該表的用戶可以修改表，其他用戶無法訪問該表。

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）2014.4管理鎖（2）在鎖定表時(shí)會(huì)隱式地提交所有事務(wù)，在開始一個(gè)事務(wù)時(shí)，如STARTTRANSACTION，會(huì)隱式解開所有表鎖定。（3）在事務(wù)表中，系統(tǒng)變量@@AUTOCOMMIT值必須設(shè)為0。否則，MySQL會(huì)在調(diào)用locktables之后立刻釋放表鎖定，并且很容易形成死鎖。例如，在student表上設(shè)置一個(gè)只讀鎖定。LOCKTABLESstudentREAD;在score表上設(shè)置一個(gè)寫鎖定。LOCKTABLESscoreWRITE;在鎖定表以后，可以使用UNLOCKTABLES命令解除鎖定，該命令不需要指出解除鎖定的表的名字。語法格式：UNLOCKTABLES;

數(shù)據(jù)庫原理與應(yīng)用（基于MySQL）2114.4管理鎖2.行級(jí)鎖行級(jí)鎖的類型包括共