項目7掌握數(shù)據(jù)庫的并發(fā)控制

掌握數(shù)據(jù)庫的并發(fā)控制7.1認(rèn)識事務(wù)7.2事務(wù)的并發(fā)控制

7.1認(rèn)識事務(wù)1

事務(wù)的概念在現(xiàn)實生活中，人們經(jīng)常會進(jìn)行轉(zhuǎn)賬操作，轉(zhuǎn)賬可以分為轉(zhuǎn)入和轉(zhuǎn)出兩個部分，只有這兩個部分都完成才表示轉(zhuǎn)賬成功。這個過程是使用兩條SQL語句來完成的。#A賬戶減少100元mysql>UPDATEaccountSETmoney=money-100WHEREname='A';#B賬戶增加100元mysql>UPDATEaccountSETmoney=money+100WHEREname='B';如果其中任意一條語句出現(xiàn)異常沒有執(zhí)行，則會導(dǎo)致兩個賬戶的金額不同步，造成錯誤。7.1認(rèn)識事務(wù)為了防止這種情況發(fā)生，就需要使用MySQL中的事務(wù)（Transaction）。事務(wù)是針對數(shù)據(jù)庫的一組操作，它可以由一條或多條SQL語句組成。在默認(rèn)情況下，用戶執(zhí)行的每一條SQL語句都會被當(dāng)成單獨(dú)的事務(wù)自動提交。如果要將一組SQL語句作為一個事務(wù)，則需要先執(zhí)行以下語句顯式地開啟一個事務(wù)。STARTTRANSACTION;7.1認(rèn)識事務(wù)原子性：每個事務(wù)都必須被看作一個不可分割的單元。一致性：在事務(wù)處理時，要保證數(shù)據(jù)庫系統(tǒng)處于一致的狀態(tài)。隔離性：當(dāng)一個事務(wù)在執(zhí)行時，不能被其他事務(wù)干擾。持久性：一個事務(wù)一旦提交，它對數(shù)據(jù)庫所做的修改將是持久的。事務(wù)（Transaction）：在MySQL環(huán)境中，事務(wù)由一個作為邏輯單元的一個或多個SQL語句組成。在實際的工作中，一個事務(wù)往往是需要多條語句共同組成，來完成復(fù)雜的數(shù)據(jù)操作。事務(wù)的四個特性：原子性（Atomicity）1

事務(wù)概述事務(wù)中如果有任何一個操作執(zhí)行失敗，已經(jīng)執(zhí)行成功的操作也必須撤銷，數(shù)據(jù)庫的狀態(tài)退回到執(zhí)行事務(wù)前的狀態(tài)。每個事務(wù)必須都必須看作一個不可分割的單元，只有事務(wù)中的所有數(shù)據(jù)庫操作都執(zhí)行成功，才算整個事務(wù)執(zhí)行成功。7.1認(rèn)識事務(wù)一致性（Consistency）1

事務(wù)概述一致性是指在事務(wù)處理時，無論執(zhí)行成功還是失敗，都要保證數(shù)據(jù)庫系統(tǒng)處于一致狀態(tài)，即數(shù)據(jù)庫從一個一致狀態(tài)變到另一個一致狀態(tài)。當(dāng)數(shù)據(jù)庫只包含成功事務(wù)提交的結(jié)果時，就說數(shù)據(jù)庫處于一致狀態(tài)。例如銀行的轉(zhuǎn)賬操作，全做或全不做，數(shù)據(jù)庫都處于一致狀態(tài)。7.1認(rèn)識事務(wù)隔離性（Isolation）1

事務(wù)概述該機(jī)制是通過對事務(wù)的數(shù)據(jù)訪問對象加適當(dāng)?shù)逆i，排斥其他事務(wù)對同一數(shù)據(jù)庫對象的并發(fā)操作來實現(xiàn)的。隔離性是指當(dāng)一個事務(wù)在執(zhí)行時，不能被其他事務(wù)干擾，即一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)，對并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能互相干擾。7.1認(rèn)識事務(wù)持久性（Durability）1

事務(wù)概述大多數(shù)DBMS產(chǎn)品通過保存所有行為的日志來保證數(shù)據(jù)的永久性，這些行為是指在數(shù)據(jù)庫中以任何方法更改數(shù)據(jù)。數(shù)據(jù)庫日志記錄了所有對于表的更新、查詢、報表等行為。持久性是指事務(wù)一旦提交，一個事務(wù)一旦提交，它對數(shù)據(jù)庫所做的修改將是持久的。接下來的其他操作或故障不應(yīng)該對其有任何影響。7.1認(rèn)識事務(wù)7.1認(rèn)識事務(wù)2

事務(wù)的基本操作1.開始事務(wù)STARTTRANSACTION2.提交事務(wù)COMMIT;3.回滾事務(wù)ROLLBACK;4.事務(wù)的保存點(diǎn)SAVEPOINT保存點(diǎn)名;ROLLBACKTOSAVEPOINT保存點(diǎn)名;RELEASESAVEPOINT保存點(diǎn)名;7.1認(rèn)識事務(wù)2

事務(wù)的基本操作MySQL默認(rèn)是自動提交模式。如果沒有顯式開啟事務(wù)（STARTTRANSACTION），每一條SQL語句都會自動提交（COMMIT）。5.事務(wù)的自動提交mysql>SELECT@@autocommit;+--------------+|@@autocommit|+--------------+|1|+--------------+1rowinset(0.00sec)查看事務(wù)是否自動提交：1：開啟自動提交0：關(guān)閉自動提交7.1認(rèn)識事務(wù)2

事務(wù)的基本操作5.事務(wù)的自動提交關(guān)閉自動提交：關(guān)閉后：用戶需要手動執(zhí)行提交（COMMIT）操作。若直接終止MySQL會話，MySQL會自動進(jìn)行回滾。SETAUTOCOMMIT=0;

掌握數(shù)據(jù)庫的并發(fā)控制7.1認(rèn)識事務(wù)7.2事務(wù)的并發(fā)控制

7.2事務(wù)的并發(fā)操作1

事務(wù)并發(fā)操作引起的問題1.丟失修改

（LostUpdate）2.讀“臟”數(shù)據(jù)（DirtyRead）3.

不可重復(fù)讀（Non-repeatableRead）4.

死鎖（Deadlock）T1T2R(A)=30R(A)=30AA-1A=29AA-1A=297.2事務(wù)的并發(fā)操作1

并發(fā)操作引起的問題1.丟失修改

（LostUpdate）7.2事務(wù)的并發(fā)操作1

并發(fā)操作引起的問題T1T2R(C)=100CC*2W(C)=200R(C)=200ROLLBACKC恢復(fù)為1002.讀“臟”數(shù)據(jù)（DirtyRead）7.2事務(wù)的并發(fā)操作1

并發(fā)操作引起的問題T1T2R(A)=30R(A)=30AA*2W(A)=60R(A)=603.

不可重復(fù)讀（Non-repeatableRead）7.2事務(wù)的并發(fā)操作1

并發(fā)操作引起的問題T1STARTTRANSACTION;SELECT*FROMtable1WHEREid=1FORUPDATE;--鎖定table1SELECT*FROMtable2WHEREid=1FORUPDATE;--鎖定table2COMMIT;T2STARTTRANSACTION;SELECT*FROMtable2WHEREid=1FORUPDATE;--鎖定table2SELECT*FROMtable1WHEREid=1FORUPDATE;--鎖定table1COMMIT;如果這兩個事務(wù)幾乎同時執(zhí)行，它們可能會因為互相等待對方持有的鎖而發(fā)生死鎖4.

死鎖（Deadlock）7.2事務(wù)的并發(fā)操作2

事務(wù)的隔離級別1．readuncommitted

在該級別下的事務(wù)可以讀取到其他事務(wù)中未提交的數(shù)據(jù)，即臟讀（DirtyRead）。

2．readcommitted在該隔離級別下只能讀取其他事務(wù)已經(jīng)提交的數(shù)據(jù)，雖避免了臟讀現(xiàn)象，但會出現(xiàn)不可重復(fù)讀（Non-repeatableRead）的問題。3．repeatableread是MySQL默認(rèn)的事務(wù)隔離級別，它解決了臟讀和不可重復(fù)讀的問題，也解決了幻讀的問題。4．serializable最高的隔離級別，它在每條數(shù)據(jù)行上加鎖，使之不會發(fā)生沖突，從而解決了臟讀、不可重讀和幻讀的問題，但是性能最低。7.2.3MySQL的鎖機(jī)制1

鎖機(jī)制中的基本概念（1）鎖的粒度。鎖的粒度是指鎖的作用范圍。鎖的粒度可以分為服務(wù)器級鎖和存儲引擎級鎖。MyISAM存儲引擎支持表級鎖，InnoDB存儲引擎支持表級鎖和行級鎖。

（2）隱式鎖與顯式鎖。MySQL鎖分為隱式鎖和顯式鎖。MySQL自動加鎖稱為隱式鎖，數(shù)據(jù)庫開發(fā)人員手動加鎖稱為顯式鎖。

（3）鎖的類型。鎖的類型包括讀鎖（readlock）和寫鎖（writelock），其中，讀鎖也稱為共享鎖，寫鎖也稱為排他鎖或者獨(dú)占鎖。讀鎖允許其他MySQL端對數(shù)據(jù)同時“讀”，但不允許其他MySQL客戶端對數(shù)據(jù)“寫”。寫鎖不允許其他MySQL客戶端對數(shù)據(jù)同時“讀”，也不允許其他客戶端對數(shù)據(jù)同時“寫”。

7.2.3MySQL的鎖機(jī)制1

鎖機(jī)制中的基本概念（4）鎖的鑰匙。多個MySQL客戶端并發(fā)訪問同一個數(shù)據(jù)時，如果MySQL客戶端A對該數(shù)據(jù)成功施加了鎖，那么只有客戶端A擁有這把鎖的“鑰匙”，也就是說，只有客戶端A能夠?qū)υ撴i進(jìn)行解鎖操作。

（5）鎖的生命周期。鎖的生命周期是指在同一個MySQL服務(wù)器連接內(nèi)，對數(shù)據(jù)加鎖到解鎖之間的時間間隔。7.2.3MySQL的鎖機(jī)制2

鎖的分類1.表級鎖粒度最大，它實現(xiàn)簡單，資源消耗少，就是整個表被鎖定，分為讀鎖和寫鎖。①

讀鎖：如果表沒有加寫鎖，那么就加一個讀鎖；否則，將請求放到讀鎖隊列中。

②

寫鎖：如果表沒有加任何鎖，那么就加一個寫鎖；否則，將請求放到寫鎖隊列中。7.2.3MySQL的鎖機(jī)制2

鎖的分類2.行級鎖

只有線程使用的行是被鎖定的，表中的其他行對于其他線程都是可用的。InnoDB表的鎖定機(jī)制就是行級鎖。①

排他鎖（eXclusiveLocks）：又稱為X鎖。如果事務(wù)T1獲得了數(shù)據(jù)行D上的排他鎖，則T1對數(shù)據(jù)行既可讀又可寫。事務(wù)T1對數(shù)據(jù)行D加上排他鎖，則其他事務(wù)對數(shù)據(jù)行D的任務(wù)封鎖請求都不會成功，直至事務(wù)T1釋放數(shù)據(jù)行D上的排他鎖。

②

共享鎖（ShareLocks）：又稱為S鎖。如果事務(wù)T1獲得了數(shù)據(jù)行D上的共享鎖，則T1對數(shù)據(jù)項D可以讀但不可以寫。事務(wù)T1對數(shù)據(jù)行D加上共享鎖，則其他事務(wù)對數(shù)據(jù)行D的排他鎖請求不會成功，而對數(shù)據(jù)行D的共享鎖請求會成功。7.2.3MySQL的鎖機(jī)制2

鎖的分類3.頁級鎖定頁級鎖定將鎖定表中的某些行（稱作頁）。被鎖定的行只對鎖定最初的線程是可行的。如果另外一個線程想要向這些行寫數(shù)據(jù)，必須等到鎖被釋放。7.2.3MySQL的鎖機(jī)制3

死鎖的管理兩個或兩個以上的事務(wù)分別申請鎖定對方已經(jīng)鎖定的數(shù)據(jù)對象，導(dǎo)致長期等待而無法繼續(xù)運(yùn)行下去的現(xiàn)象稱為死鎖。死鎖的原因1.兩個事務(wù)分別鎖定了兩個單獨(dú)的對象，這時每一個事務(wù)都要求在另外一個事務(wù)鎖定的對象上獲得一個鎖，結(jié)果是每一個事務(wù)都必須等待另一個事務(wù)釋放占有的鎖，此時就發(fā)生了死鎖。這種死鎖是最典型的死鎖形式。2.在一個數(shù)據(jù)庫中，有若干長時間運(yùn)行的事務(wù)并行的執(zhí)行操作，查詢分析器處理非常復(fù)雜的查詢時，例如連接查詢，由于不能控制處理的順序，有可能發(fā)生死鎖。MySQL的InnoDB存儲引擎會自動檢測死鎖循環(huán)，并選擇一個會話作為死鎖中放棄的一方，通過終止該事務(wù)來打斷死鎖。被終止的事務(wù)發(fā)生回滾，并返回給連接一個錯誤消息。7.2.3MySQL的鎖機(jī)制3

死鎖的管理事務(wù)與鎖機(jī)制注意事項在處理數(shù)據(jù)庫并發(fā)操作時，為避免死鎖，需注意以下幾點(diǎn)。①鎖的粒度越小，應(yīng)用系統(tǒng)的并發(fā)性能就越高，由于InnoDB存儲引擎支持行鎖，建議使用InnoDB存儲引擎表以提高系統(tǒng)的可靠性。②使用事務(wù)時，盡量避免一個事務(wù)中使用不同存儲引擎的表。③處理事務(wù)時盡量設(shè)置和使用較低的隔離級別。④盡量使用基于行鎖控制的隔離級別。必要時使用表鎖，可以避免死鎖現(xiàn)象。⑤對于InnoDB存儲引擎支持的行鎖，設(shè)置合理的超時參數(shù)范圍，編寫鎖等待超時異常處理程序，可以解