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

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

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

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

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

BEFORE|AFTER

INSERT|DELETE|UPDATE

ON表名

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

INSERT觸發(fā)器，只能使用NEW.列名，因為不涉及舊值行。DELETE觸發(fā)器，只能使用OLD.列名，因為不涉及新值行。UPDATE觸發(fā)器，可以使用OLD.列名引用更新前某一行的舊值，使用NEW.列名引用更新后行的新值。【例6-2】本例題實現(xiàn)級聯(lián)更新。在修改suppliers表中的s_id之后（AFTER）級聯(lián)地、自動地修改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';

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A）原子性B）一致性

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

A）原子性B）一致性

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

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

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

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

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

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

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

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

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

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

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

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

幻影讀（phantomred）也屬于不可重復(fù)讀的問題?；糜白x

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

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

ISOLATIONLEVEL

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

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

（1）打開MySQL客戶機AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

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

（3）打開MySQL客戶機ASELECT*FROMaccount;

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

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

（1）打開MySQL客戶機AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

（2）打開MySQL客戶機BSETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;STARTTRANSACTION;UPDATEaccountSETbalance=balance+1000WHEREaccount_no=1;COMMIT;

（3）打開MySQL客戶機ASELECT*FROMaccount;

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

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

（1）打開MySQL客戶機AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

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

（3）打開MySQL客戶機ASELECT*FROMaccount;

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

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

是最高的隔離級別，它通過強制事務(wù)排序，使之不可能相互沖突。【例6-10】避免幻影讀現(xiàn)象示例。

（1）打開MySQL客戶機AUSEtest;SETSESSIONTRANSACTIONISOLATIONLEVELSERIALIZABLE;SELECT@@transaction_isolation;STARTTRANSACTION;SELECT*FROMaccount;

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

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

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

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

并發(fā)操作帶來的問題？

丟失更新

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

不可重復(fù)讀

封鎖協(xié)議一級封鎖協(xié)議二級封鎖協(xié)議三級封鎖協(xié)議封鎖協(xié)議：在運用X鎖和S鎖對數(shù)據(jù)對象加鎖時，還需要約定一些規(guī)則，如：何時申請X鎖或S鎖、持鎖時間、何時釋放等，這些規(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)致“丟失更新”問題如何加鎖？寫操作前加X鎖。1．一級封鎖協(xié)議不同級別的封鎖協(xié)議和一致性保證封鎖協(xié)議X鎖S鎖一致性保證事務(wù)結(jié)束釋放操作結(jié)束釋放事務(wù)結(jié)束釋放防止丟失更新防止讀“臟”數(shù)據(jù)防止不可重復(fù)讀一級封鎖協(xié)議

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

二級封鎖協(xié)議三級封鎖協(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ù)讀”問題如何加鎖？寫操作前加X鎖讀操作前加S鎖不能數(shù)據(jù)對象加了S鎖，還能再加X鎖？事務(wù)T1事務(wù)T2Slock(A)R(A)：5R(A)：5CommitUnlock(A)Xlock(A)等待等待等待Xlock(A)W(A)：6→ACommitUnlock(A)3．三級封鎖協(xié)議不同級別的封鎖協(xié)議和一致性保證封鎖協(xié)議X鎖S鎖一致性保證事務(wù)結(jié)束釋放操作結(jié)束釋放事務(wù)結(jié)束釋放防止丟失更新防止讀“臟”數(shù)據(jù)防止不可重復(fù)讀一級封鎖協(xié)議

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