電工與電子技術(shù)電子商務(wù)電子課件數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）第14章ppt

1、第14章 事務(wù)與MySQL的多用戶并發(fā)控制數(shù)據(jù)庫原理及應(yīng)用教程（基于Linux的MySQL和NoSQL應(yīng)用）CONTENTS事務(wù)1MySQL的并發(fā)控制2小結(jié)3訪問控制114.1.1 事務(wù)的概念對于一般簡單的業(yè)務(wù)邏輯或中小型程序而言，無須考慮應(yīng)用MySQL事務(wù)。在現(xiàn)實生活中，事務(wù)就在我們周圍銀行交易、股票交易、網(wǎng)上購物、庫存品控制，到處都有這種情況發(fā)生。在所有這些例子中，事務(wù)的成功取決于這些相互依賴的行為是否能夠被成功地執(zhí)行，是否互相協(xié)調(diào)。其中的任何一個失敗將取消整個事務(wù)，系統(tǒng)返回到事務(wù)處理以前的狀態(tài)。事務(wù)的原子性事務(wù)通常包含一系列更新操作（update、insert和delete等操作語句），

2、這些更新操作是一個不可分割的邏輯工作單元。14.1.2 事務(wù)的ACID特性，每個事務(wù)的處理必須滿足ACID原則，即原子性(A)、一致性(C)、隔離性(I)和持久性(D)。每個事務(wù)都必須被認為是一個不可分割的單元。假設(shè)一個事務(wù)由兩個或者多個任務(wù)組成，其中的語句必須同時成功才能認為事務(wù)是成功的。如果事務(wù)失敗，系統(tǒng)將會返回到事務(wù)以前的狀態(tài)。原子性(A)不管事務(wù)是完全成功完成還是中途失敗，當(dāng)事務(wù)使系統(tǒng)處于一致的狀態(tài)時存在一致性。在MySQL中，一致性主要由MySQL的日志機制處理，它記錄了數(shù)據(jù)庫的所有變化，為事務(wù)恢復(fù)提供了跟蹤記錄。一致性(C)14.1.2 事務(wù)的ACID特性指每個事務(wù)在它自己的空間發(fā)

3、生，和其他發(fā)生在系統(tǒng)中的事務(wù)隔離，而且事務(wù)的結(jié)果只有在它完全被執(zhí)行時才能看到。隔離性(I)指即使系統(tǒng)崩潰，一個提交的事務(wù)仍然存在。當(dāng)一個事務(wù)完成，數(shù)據(jù)庫的日志已經(jīng)被更新時，持久性就開始發(fā)生作用。MySQL通過保存一條記錄事務(wù)過程中系統(tǒng)變化的二進制事務(wù)日志文件來實現(xiàn)持久性。持久性(D)14.1.2 事務(wù)的ACID特性假設(shè)數(shù)據(jù)有兩個域，A和B，在兩個記錄里。一個完整約束需要A值和B值必須相加得100。下面以SQL代碼創(chuàng)建上面描述的表：create table acidtest (A integer b integer check(A十 B = 100);考慮這兩個事務(wù)，T1從A轉(zhuǎn)移10到B。T2從

4、B轉(zhuǎn)移10到A。為完成這兩個事物，一共有4個步驟：(l)從A減IO;(2)加10到B；(3)從B減10;(4)加10到A。如果T1在一半的時候失敗，那么數(shù)據(jù)庫會消除了T1的效果，并且T2只能看見有效數(shù)據(jù)。事務(wù)的執(zhí)行可能交叉，實際執(zhí)行順序可能是：A-10，B-10，B+10，A+10。如果T1失敗，T2不能看到T1的中間值，因此T1必須回滾。14.1.3 MySQL事務(wù)控制語句MySQL中可以使用begin 開始事務(wù)，使用commint結(jié)束事務(wù)，中間可以使用rollback回滾事務(wù)。MySQL通過set autocommint 、start transaction 、commit和rollbac

5、k等語句支持本地事務(wù)。start transaction | begin work commit work and no chain no release rollback work and no chain no release set autocommit = 0 | 1 14.1.4 事務(wù)的隔離性級別每一個事務(wù)都有一個所謂的隔離級，它定義了用戶彼此之間隔離和交互的程度。如果沒有事務(wù)的隔離性，不同的SELECT語句將會在同一個事務(wù)的環(huán)境中檢索到不同的結(jié)果，因為在這期間，基本上數(shù)據(jù)已經(jīng)被其他事務(wù)所修改。這將導(dǎo)致不一致性，同時很難相信結(jié)果集，從而不能利用查詢結(jié)果作為計算的基礎(chǔ)。MySQL提供了

6、下面4種隔離級：序列化（serializable）、可重復(fù)讀(repeatable read)、提交讀(read committed)、未提交讀（read uncommitted)只有支持事務(wù)的和存儲引擎（比如，InnoDB）才可以定義一個隔離級。14.1.4 事務(wù)的隔離性級別定義隔離級可以使用set transaction 語句。其中語句中如果指定global，那么定義的隔離級將適用于所有的sql用戶；如果指定session，則隔離級只適用于當(dāng)前運行的會話和連接。MySQL默認為repeatable read隔離級。set global|session transaction isolati

7、on level seriaizable (1)序列化如果隔離級為序列化，用戶之間通過一個接一個順序地執(zhí)行當(dāng)前的事務(wù)提供了事務(wù)之間最大限度的隔離。14.1.4 事務(wù)的隔離性級別set global|session transaction isolation level repeatable read (2)可重復(fù)讀如果用戶在同一個事務(wù)中執(zhí)行同條select語句數(shù)次，結(jié)果總是相同的set global|session transaction isolation level read committed (3)提交讀read committed隔離級的安全性比repeatable read隔離級的

8、安全性要差。在事務(wù)處理期間，如果其他事務(wù)修改了相應(yīng)的表，那么同一個事務(wù)的多個select語句可能返回不同的結(jié)果.14.1.4 事務(wù)的隔離性級別set global|session transaction isolation level read uncommitted (4)未提交讀提供了事務(wù)之間最小限度的隔離。select tx_isolation; 結(jié)果：tx_isolation repeatable-read系統(tǒng)變量tx_isolation中存儲了事務(wù)的隔離級，可以使用select隨時獲得當(dāng)前隔離級的值： MySQL的并發(fā)控制2在單處理機系統(tǒng)中，事務(wù)的并行執(zhí)行實際上是這些并行事務(wù)輪流交叉

9、進行，這種并行執(zhí)行方式稱為交叉并發(fā)方式在多處理機系統(tǒng)中，每個處理機可以運行一個事務(wù)，多個處理機可以同時運行多個事務(wù)，實現(xiàn)事務(wù)真正的并發(fā)運行，這種并發(fā)執(zhí)行方式稱為同時并發(fā)方式14.2.1 并發(fā)概述當(dāng)多個用戶并發(fā)地存取數(shù)據(jù)庫時就會產(chǎn)生多個事務(wù)同時存取同一數(shù)據(jù)的情況。若對并發(fā)操作不加控制可能會存取和存儲不正確的數(shù)據(jù)，就會出現(xiàn)數(shù)據(jù)的不一致問題。當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題最后的更新覆蓋了由其他事務(wù)所做的更新。1、丟失更新(lost update) 問題一個事務(wù)正在對一條記錄做修改，在這個事務(wù)完成并提交前，這條記錄的

10、數(shù)據(jù)就處于不一致狀態(tài)；這時，另-個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進一步的處理，就會產(chǎn)生未交的數(shù)據(jù)依賴關(guān)系2、臟讀(dirty read)問題14.2.1 并發(fā)概述當(dāng)一個事務(wù)多次訪問同一行而且每次讀取不同的數(shù)據(jù)時，會發(fā)生不可重復(fù)讀( unrepeatableread)問題。3、不可重復(fù)讀( unrepeatableread) 問題當(dāng)一個事務(wù)對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時，會發(fā)生幻讀(phantom read)問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因為該行已被其他事務(wù)刪除。同樣，由于

11、其他事務(wù)的插入操作，事務(wù)的第二次讀或后續(xù)讀顯示有一行已不存在于原始讀中。4、幻讀(phantom read) 問題14.1.2 鎖的概述當(dāng)用戶對數(shù)據(jù)庫并發(fā)訪問時，為了確保事務(wù)完整性和數(shù)據(jù)庫一致性，需要使用鎖定，它是實現(xiàn)數(shù)據(jù)庫并發(fā)控制的主要手段。鎖一種用來防止多個客戶端同時訪問數(shù)據(jù)而產(chǎn)生問題的機制。1)表級鎖：一個特殊類型的訪問，整個表被客戶鎖定。根據(jù)鎖定的類型，其他客戶不能向表中插入記錄，甚至從中讀數(shù)據(jù)也受到限制。其特點是：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定力度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。1、MySQL3種鎖的特點如下：14.1.2 鎖的概述2)頁面鎖：MySQL將鎖定表中的某些行稱

12、為頁。被鎖定的行只對鎖定最初的線程是可行的。如果另外一個線程想要向這些行寫數(shù)據(jù)，它必須等到鎖被釋放。不過，其他頁的行仍然可以使用。其特點是：開銷和加鎖時間界于表級鎖和行級鎖之間；會出現(xiàn)死鎖；鎖定力度界于表級鎖和行級鎖之間，并發(fā)度一般。1、MySQL3種鎖的特點如下：3)行級鎖：行級鎖比表級鎖或頁面鎖對鎖定過程提供了更精細的控制。在這種情況下，只有線程使用的行是被鎖定的。表中的其他行對于其他線程都是可用的。在多用戶的環(huán)境中，行級鎖降低了線程間的沖突，可以使多個用戶同時從一個相同表讀數(shù)據(jù)甚至寫數(shù)據(jù)。其特點是：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定力度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。14.2.3

13、 MyISAM表的表級鎖MyISAM在執(zhí)行查詢語句（select）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（update、delete、insert等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預(yù)。對MyISAM表進行操作，會有以下情況：（1）對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當(dāng)讀鎖釋放后，才會執(zhí)行其他進程的寫操作。（2）對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會執(zhí)行其他進程的讀寫操作。14.2.3 MyISAM表的表級鎖1、查詢表級鎖爭用情況show statu

14、s liketable% 2、MySQL表級鎖的鎖模式表共享讀鎖（table read lock）和表獨占寫鎖（tablewrite i.ock）。3、表級鎖的加鎖方法在變化的過程中阻止其他用戶訪問正在變化的表（通過鎖定表），只在變化完成后才允許訪問14.2.3 MyISAM表的表級鎖MySQL提供了LOCK TABLES語句來鎖定當(dāng)前線程lock tables tbl_nameas alias readlocal| low_priority write , tbl_name as alias readlocal| low_priority write . unlock tables 說明：表

15、鎖定支持以下類型的鎖定。read：讀鎖定，確保用戶可以讀取表，但是不能修改表。加上local后允許表鎖定后用戶可以進行非沖突的insert語句，只適用于MyISAM類型的表。write：寫鎖定，只有鎖定該表的用戶可以修改表，其他用戶無法訪問該表。加上low_priority后允許其他用戶讀取表，但是不能修改它。14.2.3 MyISAM表的表級鎖在對一個事務(wù)表使用表鎖定的時候需要注意以下幾點：(1)在鎖定表時會隱式地提交所有事務(wù)，在開始一個事務(wù)時，如start transaction ，會隱式解開所有表鎖定。(2)在事務(wù)表中，系統(tǒng)變量autocommit值必須設(shè)為0。否則，MySQL會在調(diào)用l

16、ock tables之后立刻釋放表鎖定，并且很容易形成死鎖。14.2.4 InnoDB表的行級鎖InnoDB與MyISAM的最大不同有兩點：一是支持事務(wù)（transaction ）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務(wù)的引人也帶來了一些新問題。show status like innoDB_rowiock%; 1)獲取InnoDB行鎖爭用情況可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭用情況。如果發(fā)現(xiàn)innoDB_row_lock_waits和innoDB_row_lock_time_avg的值比較高，則說明鎖爭用比較嚴重。14.2.4

17、InnoDB表的行級鎖2)InnoDB的行級鎖的鎖模式行鎖:共享鎖(S)：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。排他鎖(X)：允許獲得排他事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機制，InnoDB還有兩種內(nèi)部使用的意向鎖(Intention Locks)，這兩種意向鎖都是表鎖。意向共享鎖(IS)：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。意向排他鎖(IX)：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。14.2.4 InnoDB表的行級鎖意

18、向鎖是InnoDB自動加的，不需用戶干預(yù)。對于update、delete和insert語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖(X)；對于普通select語句，InnoDB不會加任何鎖，共享鎖(S): select * from table_name where lock in share mode。 排他鎖(X): select * from table_name where for update。 3)InnoDB行級鎖的加鎖方法InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的.14.2.5 死鎖如果事務(wù)T1封鎖了數(shù)據(jù)R1，T2封鎖了數(shù)據(jù)R2，然后T1又請求封鎖R2，因T2已封鎖了R2，于是T1等待T2釋放R2上的鎖。接著T2又申請封鎖R1，因T1己封鎖了R1，T2也只能等待T1釋放R1上的鎖。這樣就出現(xiàn)了T1在等待T2，而T2又在等待T1的局面，T1和T2兩個事務(wù)永遠不能結(jié)束，形成死鎖。幾種避免死鎖的常用方法：1）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序來訪表，這樣可以大大降低產(chǎn)生死鎖的機會：2）在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的