數(shù)據(jù)庫(kù)原理與MySQL應(yīng)用-6 觸發(fā)器與事務(wù)處理

第6章觸發(fā)器與事務(wù)處理6.1觸發(fā)器數(shù)據(jù)庫(kù)原理與應(yīng)用26.1.1概念觸發(fā)器（TRIGGER）是一種特殊的存儲(chǔ)過(guò)程。

為什么要使用觸發(fā)器？

①加入了新的水果供應(yīng)商，在供應(yīng)商表中添加一條該供應(yīng)商相關(guān)的記錄，供應(yīng)商的總數(shù)就必須同時(shí)改變。

②供應(yīng)商退出時(shí)，在供應(yīng)商表中刪除該供應(yīng)商的記錄，同時(shí)也希望能刪除該供應(yīng)商提供的水果記錄。當(dāng)對(duì)表進(jìn)行INSERT、UPDATE、DELETE操作時(shí)就會(huì)激活相應(yīng)的觸發(fā)器并執(zhí)行。觸發(fā)器經(jīng)常用于加強(qiáng)數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。觸發(fā)器的作用（1）安全性。對(duì)用戶操作數(shù)據(jù)庫(kù)的權(quán)限進(jìn)行控制。比如，基于時(shí)間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫(kù)數(shù)據(jù)（2）審計(jì)?？梢愿櫽脩魧?duì)數(shù)據(jù)庫(kù)的操作，把用戶對(duì)數(shù)據(jù)庫(kù)的更改寫入到審計(jì)表。（3）實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)完整性規(guī)則觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)相關(guān)的表進(jìn)行更新操作。觸發(fā)器可以產(chǎn)生比檢查約束更為復(fù)雜的限制。觸發(fā)器能夠回退那些破壞相關(guān)完整性的操作，取消試圖進(jìn)行數(shù)據(jù)更改的事務(wù)。觸發(fā)器可以自動(dòng)計(jì)算數(shù)據(jù)值。6.1.2創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名

BEFORE|AFTER

INSERT|DELETE|UPDATE

ON表名

FOREACHROW觸發(fā)的SQL語(yǔ)句【例6-1】創(chuàng)建觸發(fā)器del_tri觸發(fā)器，觸發(fā)器將記錄哪些用戶刪除了fruits表中的數(shù)據(jù)，以及刪除的時(shí)間和進(jìn)行的操作類型。首先創(chuàng)建merch_log的日志信息表，用于存儲(chǔ)用戶對(duì)表的操作。CREATETABLEmerch_log(whoVARCHAR(30),oper_dateDATE,operVARCHAR(20));其次在fruits表上創(chuàng)建DELETE類型觸發(fā)器，實(shí)現(xiàn)向merch_log表添加操作的用戶名、日期及操作類型。CREATETRIGGERdel_triAFTERDELETEONfruitsFOREACHROWINSERTINTOmerch_log(who,oper_date,oper)VALUES(USER(),SYSDATE(),'DELETE');最后測(cè)試觸發(fā)器是否正常運(yùn)行，在fruits表中刪除f_id為a1的記錄；并查詢?nèi)罩拘畔⒈韒erch_log。DELETEFROMfruitsWHEREf_id='a1';SELECT*FROMmerch_log;觸發(fā)器如何取得激活觸發(fā)器操作的舊值和新值（1）舊值。在字段名前加上“OLD.”限定詞（2）新值。在字段名前加上“NEW.”限定詞

INSERT觸發(fā)器，只能使用NEW.列名，因?yàn)椴簧婕芭f值行。DELETE觸發(fā)器，只能使用OLD.列名，因?yàn)椴簧婕靶轮敌?。UPDATE觸發(fā)器，可以使用OLD.列名引用更新前某一行的舊值，使用NEW.列名引用更新后行的新值。【例6-2】本例題實(shí)現(xiàn)級(jí)聯(lián)更新。在修改suppliers表中的s_id之后（AFTER）級(jí)聯(lián)地、自動(dòng)地修改fruits表中該供應(yīng)商的s_id。CREATETRIGGERtr_upAFTERUPDATEONsuppliersFOREACHROWUPDATEfruitsSETs_id=NEW.s_idWHEREs_id=OLD.s_id;UPDATEsuppliersSETs_id=110WHEREs_id=101;SELECT*FROMfruitsWHEREs_id=110;6.1.3查看觸發(fā)器【例6-3】查詢觸發(fā)器tr_up的信息。USEinformation_schema;SELECT*FROMtriggersWHEREtrigger_name='tr_up';

通過(guò)數(shù)據(jù)庫(kù)information_schema中的系統(tǒng)表triggers，查詢指定觸發(fā)器的定義、狀態(tài)和語(yǔ)法信息等。6.1.4刪除觸發(fā)器【例6-4】刪除觸發(fā)器tr_up。DROPTRIGGERtr_up;

DROPTRIGGER觸發(fā)器名;6.2事務(wù)數(shù)據(jù)庫(kù)原理與應(yīng)用13updatebanksetsal=sal-1000wherename='張三';updatebanksetsale=sale+1000wherename='李四';如何在銀行表（bank）中實(shí)現(xiàn)客戶（name）張三給李四轉(zhuǎn)賬1000元存款（sal）的操作？updatebanksetsal=sal-1000wherename='張三';updatebanksetsal=sal+1000wherename='李四';下面的SQL語(yǔ)句執(zhí)行后的結(jié)果？張三賬戶減少了1000元，但李四賬戶卻沒(méi)有加錢。是否有一種方法使得一個(gè)業(yè)務(wù)對(duì)應(yīng)的SQL語(yǔ)句要么都執(zhí)行，要么里面有一句沒(méi)有執(zhí)行，就全部不執(zhí)行？

事務(wù)通常包含一系列INSERT、DELETE、UPDATE等更新操作語(yǔ)句，這些更新操作是一個(gè)不可分割的邏輯工作單元。

每個(gè)事務(wù)的處理必須要滿足ACID的4個(gè)特性，即原子性（A）、一致性（C）、隔離性（I）和持久性（D）。6.2.1事務(wù)的概述1.原子性（Atomicity）

原子性意味著每個(gè)事務(wù)都必須作為一個(gè)不可分割的單元，事務(wù)中包含的所有操作要么全做，要么全不做。6.2.2事務(wù)的ACID特性如何實(shí)現(xiàn)事務(wù)的原子性呢？

使用DBMS的事務(wù)日志文件，把那些未成功執(zhí)行的事務(wù)中已執(zhí)行的操作對(duì)數(shù)據(jù)產(chǎn)生的影響“抹掉”。

事務(wù)日志文件記錄了每個(gè)事務(wù)對(duì)數(shù)據(jù)庫(kù)所作變更的“舊值”和“新值”，當(dāng)一個(gè)事務(wù)不能完成時(shí)，將這些變更了的“新值”恢復(fù)到它的“舊值”（即抹掉了該變更）。

一致性是指事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)從一種一致性狀態(tài)變更為另外一種一致性狀態(tài)，確保數(shù)據(jù)的完整性。如銀行轉(zhuǎn)賬事務(wù)：updatebanksetsal=sal-1000wherename='張三';updatebanksetsal=sal+1000wherename='李四';

轉(zhuǎn)賬事務(wù)必須保證兩個(gè)賬戶的總錢數(shù)不變（這就是一種一致性的限制），轉(zhuǎn)賬前總數(shù)是多少，轉(zhuǎn)賬后的總還是多少。2.一致性（Consistency）

事務(wù)的隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，由于它們的操作命令交叉執(zhí)行而導(dǎo)致的數(shù)據(jù)不一致?tīng)顟B(tài)。發(fā)生過(guò)的事件：

要求：一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。3.隔離性（Isolation）

事務(wù)完成之后，所做的修改對(duì)數(shù)據(jù)的影響是永久的，即使出現(xiàn)系統(tǒng)故障，數(shù)據(jù)仍可以恢復(fù)。4.持久性（Durability）

InnoDB存儲(chǔ)引擎引入了與事務(wù)處理相關(guān)的REDO（重做）日志和UNDO（撤消）日志。

當(dāng)每條SQL語(yǔ)句進(jìn)行數(shù)據(jù)更新操作時(shí)，將寫入REDO日志文件，在MySQL崩潰恢復(fù)時(shí)會(huì)重新執(zhí)行REDO日志中的記錄。REDO日志對(duì)應(yīng)磁盤上的ib_logfileN文件

UNDO日志主要用于事務(wù)異常時(shí)的數(shù)據(jù)回滾。磁盤上不存在單獨(dú)的UNDO日志文件，所有的UNDO日志均存放在表空間對(duì)應(yīng)的.ibd數(shù)據(jù)文件中。1．一個(gè)事務(wù)執(zhí)行過(guò)程中，其正在訪問(wèn)的數(shù)據(jù)被其他事務(wù)所修改，導(dǎo)致處理結(jié)果不正確，這是由于違背了事務(wù)的

A）原子性B）一致性

C）隔離性D）持久性2．“一旦事務(wù)成功提交，其對(duì)數(shù)據(jù)庫(kù)的更新操作將永久有效，即使數(shù)據(jù)庫(kù)發(fā)生故障”，這一性質(zhì)是指事務(wù)的

A）原子性B）一致性

C）隔離性D）持久性練習(xí)：CD（1）自動(dòng)提交事務(wù)模式。每條單獨(dú)的語(yǔ)句都是一個(gè)事務(wù)，是MySQL默認(rèn)的事務(wù)管理模式。

（2）顯式事務(wù)模式。

由用戶定義事務(wù)的啟動(dòng)和結(jié)束。6.2.3MySQL事務(wù)控制語(yǔ)句1.事務(wù)模式

（3）隱性事務(wù)模式。在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動(dòng)啟動(dòng)。

修改提交方式：SETAUTOCOMMIT=0|1;①SETAUTOCOMMIT=1是默認(rèn)的，為自動(dòng)提交事務(wù)模式。②SETAUTOCOMMIT=0，設(shè)置之后的所有事務(wù)都需要通過(guò)明確的命令進(jìn)行提交和回滾。STARTTRANSACTION;或

BEGINWORK;【說(shuō)明】在存儲(chǔ)過(guò)程中只能使用STARTTRANSACTION語(yǔ)句來(lái)開(kāi)啟一個(gè)事務(wù)，因?yàn)镸ySQL數(shù)據(jù)庫(kù)分析器會(huì)自動(dòng)將BEGIN識(shí)別為BEGIN…END語(yǔ)句。2.開(kāi)始事務(wù)COMMIT[WORK][AND[NO]CHAIN][[NO]RELEASE];【說(shuō)明】①提交事務(wù)的最簡(jiǎn)單形式，只需要給出COMMIT命令。②ANDCHAIN子句會(huì)在當(dāng)前事務(wù)結(jié)束時(shí)，立刻啟動(dòng)一個(gè)新事務(wù)，并且新事務(wù)與剛結(jié)束的事務(wù)有相同的隔離等級(jí)。③RELEASE子句在終止了當(dāng)前事務(wù)后，會(huì)讓服務(wù)器斷開(kāi)與當(dāng)前客戶端的連接。④NO關(guān)鍵字可以抑制CHAIN或RELEASE完成。3.提交事務(wù)ROLLBACK[WORK][AND[NO]CHAIN][[NO]RELEASE];

回滾會(huì)結(jié)束用戶的事務(wù)，并撤消正在進(jìn)行的所有未提交的修改（即BEGINWORK或STARTTRANSACTIO后的所有修改）。4.回滾事務(wù)【例6-5】假設(shè)銀行存在兩個(gè)借記卡賬戶（account）'李三'與'王五'，要求這兩個(gè)借記卡賬戶不能用于透支，即兩個(gè)賬戶的余額（balance）不能小于0。創(chuàng)建存儲(chǔ)過(guò)程tran_proc()，實(shí)現(xiàn)兩個(gè)賬戶的轉(zhuǎn)賬業(yè)務(wù)。

創(chuàng)建保存點(diǎn)：SAVEPOINT保存點(diǎn)名稱;5.設(shè)置保存點(diǎn)

回滾事務(wù)到保存點(diǎn)：ROLLBACK[WORK]TOSAVEPOINT保存點(diǎn)名稱;【例6-6】創(chuàng)建save_p1_proc存儲(chǔ)過(guò)程，僅僅撤消第二條insert語(yǔ)句，但提交了第一條insert語(yǔ)句?！纠?-6】創(chuàng)建save_p2_proc存儲(chǔ)過(guò)程，先撤消第二條insert語(yǔ)句，然后撤消了所有的insert語(yǔ)句。6.3并發(fā)控制數(shù)據(jù)庫(kù)原理與應(yīng)用34事務(wù)并發(fā)執(zhí)行：DBMS同時(shí)執(zhí)行多個(gè)事務(wù)對(duì)同一數(shù)據(jù)的操作（并發(fā)操作），為此，DBMS需要對(duì)各事務(wù)中的操作順序進(jìn)行安排，以達(dá)到同時(shí)運(yùn)行多個(gè)事務(wù)的目的。6.3.1理解什么是并發(fā)控制在單處理機(jī)系統(tǒng)中，事務(wù)的并發(fā)執(zhí)行實(shí)際上是這些并發(fā)事務(wù)輪流交叉進(jìn)行的，這種并發(fā)方式稱為交叉并發(fā)方式。在多處理機(jī)系統(tǒng)中，每個(gè)處理機(jī)可以運(yùn)行一個(gè)事務(wù)，多個(gè)處理機(jī)可以同時(shí)運(yùn)行多個(gè)事務(wù)，實(shí)現(xiàn)事務(wù)真正的并發(fā)運(yùn)行，這種并發(fā)執(zhí)行方式稱為同時(shí)并發(fā)方式。為什么出現(xiàn)一票兩賣？分析：售票處A讀車票數(shù)據(jù)庫(kù)余票數(shù)為x;售票處B讀車票數(shù)據(jù)庫(kù)余票數(shù)為x;售票處A售出一張火車票，更新數(shù)據(jù)庫(kù)中余票數(shù)為x-1；

售票處B售出一張火車票，更新數(shù)據(jù)庫(kù)中余票數(shù)為x-1；本賣出2張票，但數(shù)據(jù)庫(kù)只減了1張票。原因：兩個(gè)售票過(guò)程（事務(wù)）交叉進(jìn)行，發(fā)生了相互干擾。并發(fā)執(zhí)行的事務(wù)，可能會(huì)同時(shí)讀寫數(shù)據(jù)庫(kù)中同一數(shù)據(jù)的情況，如果不加以控制，可能會(huì)引起讀寫數(shù)據(jù)的沖突，對(duì)數(shù)據(jù)庫(kù)的一致性會(huì)造成破壞。事務(wù)對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)可以進(jìn)行哪些操作？

讀操作和寫操作讀和寫，哪個(gè)可能會(huì)導(dǎo)致數(shù)據(jù)不正確？讀不會(huì)破壞數(shù)據(jù)，但寫可能導(dǎo)致數(shù)據(jù)不正確。事務(wù)并發(fā)執(zhí)行可能引發(fā)的問(wèn)題？讀-讀讀-寫寫-讀寫-寫保持?jǐn)?shù)據(jù)一致性不可重復(fù)讀讀臟數(shù)據(jù)丟失更新6.3.2并發(fā)執(zhí)行可能引起的問(wèn)題1.丟失更新又稱為覆蓋未提交的數(shù)據(jù)。原因：由于兩個(gè)(或多個(gè))事務(wù)對(duì)同一數(shù)據(jù)并發(fā)地寫入引起，稱為寫—寫沖突。結(jié)果：與串行地執(zhí)行兩個(gè)(或多個(gè))事務(wù)的結(jié)果不一致。2、不可重復(fù)讀

又稱為讀值不可復(fù)現(xiàn)。原因：該問(wèn)題因讀—寫沖突引起。結(jié)果：第二次讀的值與前次讀的值不同。

幻影讀（phantomred）也屬于不可重復(fù)讀的問(wèn)題。幻影讀

與不可重復(fù)的區(qū)別是：不可重復(fù)讀的操作對(duì)象是數(shù)據(jù)，而幻影讀的操作對(duì)象是表中的記錄。3、讀臟數(shù)據(jù)又稱為讀未提交的數(shù)據(jù)。原因：由于后一事務(wù)讀了前一個(gè)事務(wù)寫了但尚未提交的數(shù)據(jù)引起，稱為寫—讀沖突。結(jié)果：讀到有可能要回退的更新數(shù)據(jù)。事務(wù)并發(fā)操作引發(fā)問(wèn)題的解決方法方法一：設(shè)置事務(wù)隔離級(jí)別方法二：封鎖6.3.3事務(wù)隔離級(jí)別隔離級(jí)別定義了一個(gè)事務(wù)與其他事務(wù)的隔離程度。并發(fā)事務(wù)發(fā)生的4種異常情況丟失更新讀臟數(shù)據(jù)不可重復(fù)讀

幻影讀。（1）readuncommitted（未提交讀）用戶可以對(duì)數(shù)據(jù)執(zhí)行未提交讀；在事務(wù)結(jié)束前可以更改數(shù)據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。它是4個(gè)級(jí)別中限制最小的級(jí)別。（2）readcommitted（提交讀）此隔離級(jí)別不允許用戶讀一些未提交的數(shù)據(jù)，因此不會(huì)出現(xiàn)讀臟數(shù)據(jù)的情況，但數(shù)據(jù)可以在事務(wù)結(jié)束前被修改，從而產(chǎn)生不可重復(fù)讀或幻影數(shù)據(jù)。（3）repeatableread（重復(fù)讀）此隔離級(jí)別保證在一個(gè)事務(wù)中重復(fù)讀到的數(shù)據(jù)會(huì)保持同樣的值，而不會(huì)出現(xiàn)讀臟數(shù)據(jù)、不可重復(fù)讀的問(wèn)題。但允許其他用戶將新的幻影行插入數(shù)據(jù)集，且幻影行包括在當(dāng)前事務(wù)的后續(xù)讀取中。（4）serializable（串行讀）此隔離級(jí)別是4種隔離級(jí)別中限制最大的級(jí)別，稱為可串行讀，不允許其它用戶在事務(wù)完成之前更新數(shù)據(jù)集或?qū)⑿胁迦霐?shù)據(jù)集內(nèi)。事務(wù)的4種隔離級(jí)別隔離級(jí)別丟失更新讀臟數(shù)據(jù)不可重復(fù)讀幻影讀未提交讀（readuncommitted）是是是是提交讀（readcommitted）否否是是可重復(fù)讀（repeatableread）否否否是可串行讀（serializable）否否否否1．MySQL隔離級(jí)別的設(shè)置6.3.4MySQL事務(wù)隔離級(jí)別設(shè)置SET[GLOBAL|SESSION]TRANSACTION

ISOLATIONLEVEL

SERIALIZABLE|REPEATABLEREAD|READCOMMITED|READUNCOMMITED;GLOBAL，定義的隔離級(jí)別適用于所有的SQL用戶。SESSION，定義的隔離級(jí)別只適用于當(dāng)前運(yùn)行的會(huì)話和連接。MySQL默認(rèn)的事務(wù)隔離級(jí)別是REPEATABLEREAD。系統(tǒng)變量@@TRANSACTION_ISOLATION存儲(chǔ)了事務(wù)的隔離級(jí)別。2.READUNCOMMITED隔離級(jí)別

所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。該隔離級(jí)別很少用于實(shí)際應(yīng)用?！纠?-7】臟讀現(xiàn)象示例。

（1）打開(kāi)MySQL客戶機(jī)AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

（2）打開(kāi)MySQL客戶機(jī)BUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;STARTTRANSACTION;UPDATEaccountSETbalance=balance+1000WHEREaccount_no=1;##未提交事務(wù)

（3）打開(kāi)MySQL客戶機(jī)ASELECT*FROMaccount;

（4）關(guān)閉MySQL客戶機(jī)A與MySQL客戶機(jī)B，由于兩個(gè)客戶機(jī)的事務(wù)都沒(méi)有提交，所以，account表中的數(shù)據(jù)沒(méi)有變化，'李三'賬戶的余額仍然是200。3.READCOMMITED隔離級(jí)別

一個(gè)事務(wù)只能看見(jiàn)已提交事務(wù)所做的改變。避免臟讀現(xiàn)象，但可能出現(xiàn)不可重復(fù)讀和幻影讀。【例6-8】不可重復(fù)讀現(xiàn)象示例。

（1）打開(kāi)MySQL客戶機(jī)AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

（2）打開(kāi)MySQL客戶機(jī)BSETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;STARTTRANSACTION;UPDATEaccountSETbalance=balance+1000WHEREaccount_no=1;COMMIT;

（3）打開(kāi)MySQL客戶機(jī)ASELECT*FROMaccount;

MySQL客戶機(jī)A在同一個(gè)事務(wù)中兩次執(zhí)行“SELECT*FROMaccount;”的結(jié)果不相同，造成不可重復(fù)讀現(xiàn)象。4.REPEATABLEREAD隔離級(jí)別

是MySQL的默認(rèn)事務(wù)隔離級(jí)別，它確保在同一事務(wù)內(nèi)相同的查詢語(yǔ)句的執(zhí)行結(jié)果一致。避免臟讀及不可重復(fù)讀的現(xiàn)象，但可能出現(xiàn)幻影讀現(xiàn)象?！纠?-9】幻影讀現(xiàn)象示例。

（1）打開(kāi)MySQL客戶機(jī)AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

（2）打開(kāi)MySQL客戶機(jī)BSETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;STARTTRANSACTION;INSERTINTOaccountVALUES(10,'趙六',3000);COMMIT;SELECT*FROMaccount;

（3）打開(kāi)MySQL客戶機(jī)ASELECT*FROMaccount;

查詢結(jié)果顯示account表中不存在account_no=10的賬戶信息。

（4）由于MySQL客戶機(jī)A檢測(cè)到account表中不存在account_no=10的賬戶信息，在MySQL客戶機(jī)A繼續(xù)執(zhí)行下面INSERT語(yǔ)句。INSERTINTOaccountVALUES(10,'趙六',3000);運(yùn)行結(jié)果顯示account表中確實(shí)存在account_no=10的賬戶信息，但由于REPEATABLEREAD（可重復(fù)讀）隔離級(jí)別使用了“障眼法”，使得MySQL客戶機(jī)A無(wú)法查詢到account_no=10的賬戶信息，這種現(xiàn)象稱為幻影讀現(xiàn)象。5.SERIALIZABLE隔離級(jí)別

是最高的隔離級(jí)別，它通過(guò)強(qiáng)制事務(wù)排序，使之不可能相互沖突?！纠?-10】避免幻影讀現(xiàn)象示例。

（1）打開(kāi)MySQL客戶機(jī)AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

（2）打開(kāi)MySQL客戶機(jī)BSETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE;STARTTRANSACTION;INSERTINTOaccountVALUES(20,'馬七',5000);SELECT*FROMaccount;

由于發(fā)生了鎖等待超時(shí)引發(fā)的錯(cuò)誤異常，事務(wù)被回滾，所以account_no=20的賬戶信息并沒(méi)有添加到account表中。

對(duì)于大部分應(yīng)用來(lái)說(shuō)，READCOMMITTED是最合適的隔離級(jí)別。如果所處的數(shù)據(jù)庫(kù)中具有大量的并發(fā)事務(wù)，并且對(duì)事務(wù)的處理和響應(yīng)速度要求較高，則使用READCOMMITTED隔離級(jí)別比較合適。如果所連接的數(shù)據(jù)庫(kù)用戶比較少，多個(gè)事務(wù)并發(fā)地訪問(wèn)同一資源的概率比較小，并且用戶的事務(wù)可能會(huì)執(zhí)行很長(zhǎng)一段時(shí)間，在這種情況下使用REPEATABLEREAD或SERIALIZABLE隔離級(jí)別較合適6.4封鎖機(jī)制數(shù)據(jù)庫(kù)原理與應(yīng)用57一個(gè)鎖實(shí)質(zhì)上就是允許（或阻止）一個(gè)事務(wù)對(duì)一個(gè)數(shù)據(jù)對(duì)象的存取特權(quán)。一個(gè)事務(wù)對(duì)一個(gè)對(duì)象加鎖的結(jié)果是將其它事務(wù)“封鎖”在該對(duì)象之外，特別是防止了其他事務(wù)對(duì)該對(duì)象的更改，而加鎖的事務(wù)則可以執(zhí)行它所希望的處理并維持該對(duì)象的正確狀態(tài)。6.4.1鎖（1）排它鎖（X鎖、寫鎖）事務(wù)更新數(shù)據(jù)前必須先加上X鎖；數(shù)據(jù)對(duì)象加上X鎖，其它事務(wù)對(duì)該對(duì)象即不能加S鎖也不能加X(jué)鎖事務(wù)對(duì)數(shù)據(jù)加X(jué)鎖后，對(duì)鎖定數(shù)據(jù)即能讀取也能修改。（2）共享鎖（S鎖、讀鎖）事務(wù)讀取數(shù)據(jù)前必須先加上S鎖；數(shù)據(jù)對(duì)象加上S鎖后，其它事務(wù)只能對(duì)該對(duì)象加S鎖不能加X(jué)鎖事務(wù)對(duì)數(shù)據(jù)加S鎖后，對(duì)鎖定數(shù)據(jù)只能讀取。1．鎖的類型（3）意向鎖意向鎖分為意向共享鎖（IS）和意向排他鎖（IX）兩類。意向鎖表示一個(gè)事務(wù)有意向在某些數(shù)據(jù)上加共享鎖或者排他鎖。2．鎖的相容矩陣封鎖對(duì)象的大小稱為封鎖粒度。

封鎖的對(duì)象可以是字段、記錄、表等邏輯單元；也可以是頁(yè)（數(shù)據(jù)頁(yè)或索引頁(yè)）、塊等物理單元。3．鎖的粒度封鎖粒度越小，系統(tǒng)中能夠被封鎖的對(duì)象就越多，但封鎖機(jī)構(gòu)復(fù)雜，系統(tǒng)開(kāi)銷也就越大。封鎖粒度越大，系統(tǒng)中能夠被封鎖的對(duì)象就越少，并發(fā)度越小，封鎖機(jī)構(gòu)簡(jiǎn)單，相應(yīng)系統(tǒng)開(kāi)銷也就越小。實(shí)際應(yīng)用中，選擇封鎖粒度應(yīng)同時(shí)考慮封鎖開(kāi)銷和并發(fā)度兩個(gè)因素，對(duì)系統(tǒng)開(kāi)銷與并發(fā)度進(jìn)行權(quán)衡，以求得最優(yōu)的效果。需要處理大量元組的用戶事務(wù)可以以關(guān)系為封鎖單元；對(duì)于一個(gè)處理少量元組的用戶事務(wù)，可以以元組為封鎖單位

并發(fā)操作帶來(lái)的問(wèn)題？

丟失更新

讀“臟”數(shù)據(jù)

不可重復(fù)讀

封鎖協(xié)議一級(jí)封鎖協(xié)議二級(jí)封鎖協(xié)議三級(jí)封鎖協(xié)議封鎖協(xié)議：在運(yùn)用X鎖和S鎖對(duì)數(shù)據(jù)對(duì)象加鎖時(shí)，還需要約定一些規(guī)則，如：何時(shí)申請(qǐng)X鎖或S鎖、持鎖時(shí)間、何時(shí)釋放等，這些規(guī)則稱為封鎖協(xié)議。6.4.2封鎖協(xié)議事務(wù)T1事務(wù)T2R(A)：5W(A)：6→AR(A)：7？W(A)：7→A事務(wù)T1事務(wù)T2Xlock(A)R(A)：5W(A)：6→AR(A)：6CommitUnlock(A)Xlock(A)等待等待等待等待Xlock(A)R(A)W(A)：7→A寫-寫操作導(dǎo)致“丟失更新”問(wèn)題如何加鎖？寫操作前加X(jué)鎖。1．一級(jí)封鎖協(xié)議不同級(jí)別的封鎖協(xié)議和一致性保證封鎖協(xié)議X鎖S鎖一致性保證事務(wù)結(jié)束釋放操作結(jié)束釋放事務(wù)結(jié)束釋放防止丟失更新防止讀“臟”數(shù)據(jù)防止不可重復(fù)讀一級(jí)封鎖協(xié)議

二級(jí)封鎖協(xié)議三級(jí)封鎖協(xié)議√√事務(wù)T1事務(wù)T2R(A)：5W(A)：6→AROLLBACKA的值恢復(fù)為5R(A)：6讀的6為臟數(shù)據(jù)寫-讀操作導(dǎo)致“讀臟數(shù)據(jù)”問(wèn)題如何加鎖？寫操作前加X(jué)鎖讀操作前加S鎖數(shù)據(jù)對(duì)象加了X鎖，還能再加S鎖？不能事務(wù)T1事務(wù)T2Xlock(A)R(A)：5W(A)：6→AROLLBACKUnlock(A)Slock(A)等待等待Slock(A)R(A)：5commitUlock(A)2．二級(jí)封鎖協(xié)議不同級(jí)別的封鎖協(xié)議和一致性保證封鎖協(xié)議X鎖S鎖一致性保證事務(wù)結(jié)束釋放操作結(jié)束釋放事務(wù)結(jié)束釋放防止丟失更新防止讀“臟”數(shù)據(jù)防止不可重復(fù)讀一級(jí)封鎖協(xié)議

二級(jí)封鎖協(xié)議三級(jí)封鎖協(xié)議√√√√√√事務(wù)T1事務(wù)T2Xlock(A)R(A)：5W(A)：6→AROLLBACKUnlock(A)Xlock(A)W(A):7→AUlock(A)Slock(A)等待等待Slock(A)R(A)：5commitUlock(A)Slock(A)R(A):7(與前面讀到的5不同，發(fā)生不可重復(fù)讀)原因：S鎖操作結(jié)束即被釋放事務(wù)T1事務(wù)T2R(A)：5R(A)：6？W(A)：6→A讀-寫操作導(dǎo)致“不可重復(fù)讀”問(wèn)題如何加鎖？寫操作前加X(jué)鎖讀操作前加S鎖不能數(shù)據(jù)對(duì)象加了S鎖，還能再加X(jué)鎖？事務(wù)T1事務(wù)T2Slock(A)R(A)：5R(A)：5CommitUnlock(A)Xlock(A)等待等待等待Xlock(A)W(A)：6→ACommitUnlock(A)3．三級(jí)封鎖協(xié)議不同級(jí)別的封鎖協(xié)議和一致性保證封鎖協(xié)議X鎖S鎖一致性保證事務(wù)結(jié)束釋放操作結(jié)束釋放事務(wù)結(jié)束釋放防止丟失更新防止讀“臟”數(shù)據(jù)防止不可重復(fù)讀一級(jí)封鎖協(xié)議

二級(jí)封鎖協(xié)議三級(jí)封鎖協(xié)議√√√√√√√√√√√出現(xiàn)這種T1在等待T2，T2又在等待T1的局面，致使T1和T2兩個(gè)事務(wù)永遠(yuǎn)不能結(jié)束，形成“死鎖”。事務(wù)T1事務(wù)T2Lock(R1)┊lock(R2)等待等待等待等待等待等待等待等待┊Lock(R2)┊┊Lock(R1)等待等待等待等待等待等待1、死鎖6.4.3“死鎖”問(wèn)題在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來(lái)訪問(wèn)表。2、避免死鎖的常用方法在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來(lái)處理記錄。在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的排他鎖，而不應(yīng)先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖。在REPEATABLEREAD隔離級(jí)別下，如果兩個(gè)線程同時(shí)對(duì)相同條件記錄加排他鎖，在沒(méi)有符合該條件記錄情況下，兩個(gè)線程都會(huì)加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就會(huì)試圖插入一條新記錄，如果兩個(gè)線程都這么做就會(huì)出現(xiàn)死鎖。這種情況下，將隔離級(jí)別改為READCOMMITTED，就可以避免問(wèn)題。當(dāng)隔離級(jí)別為READCOMMITTED時(shí)，如果兩個(gè)線程都先執(zhí)行SELECT…FORUPDATE，判斷是否存在符合條件的記錄，如果沒(méi)有，就插入記錄。此時(shí)，只有一個(gè)線程能插入成功，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)?個(gè)線程提交后，第2個(gè)線程會(huì)因主鍵值重復(fù)而出錯(cuò)，雖然這個(gè)線程出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖，這時(shí)如果有第3個(gè)線程又來(lái)申請(qǐng)排他鎖，也會(huì)出現(xiàn)死鎖。對(duì)于這種情況，可以直接做插入操作，然后再捕獲主鍵值重復(fù)的異常情況，或者在遇到主鍵值重復(fù)錯(cuò)誤時(shí)，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。6.5MySQL的并發(fā)控制數(shù)據(jù)庫(kù)原理與設(shè)計(jì)73LOCKTABLES表名R