99热在线精品一区二区三区_国产伦精品一区二区三区女破破_亚洲一区二区三区无码_精品国产欧美日韩另类一区

這篇文章主要講解了“MySQL鎖與索引的關(guān)系”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MYsql鎖與索引的關(guān)系”吧！

公司主營業(yè)務(wù)：成都網(wǎng)站建設(shè)、網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。成都創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)推出六枝免費做網(wǎng)站回饋大家。

mysql innodb的鎖是通過鎖索引來實現(xiàn)的。

select for update . 排它鎖舉例。

如果字段沒有索引，即使使用wehre條件也會進(jìn)行表級鎖

如果有索引，會鎖定對應(yīng)where條件中索引值的所有行，可理解為對該索引值進(jìn)行了索引（所以即使另一事務(wù)查詢的是其他行，但因為索引值形同們也會被鎖住。）

有索引，而且使用了不同的索引值查數(shù)據(jù)，但是查詢 的結(jié)果是同一行，可以理解為真正的數(shù)據(jù)行鎖。

小結(jié)

本章重點介紹了MySQL中MyISAM表級鎖和InnoDB行級鎖的實現(xiàn)特點，并討論了兩種存儲引擎經(jīng)常遇到的鎖問題和解決辦法。

對于MyISAM的表鎖，主要討論了以下幾點：

（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）與排他寫鎖（X）之間，以及排他寫鎖（X）之間是互斥的，也就是說讀和寫是串行的。

（2）在一定條件下，MyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以利用這一點來解決應(yīng)用中對同一表查詢和插入的鎖爭用問題。

（3）MyISAM默認(rèn)的鎖調(diào)度機(jī)制是寫優(yōu)先，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置LOW_PRIORITY_UPDATES參數(shù)，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調(diào)節(jié)讀寫鎖的爭用。

（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，MyISAM表可能會出現(xiàn)嚴(yán)重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。

對于InnoDB表，本章主要討論了以下幾項內(nèi)容。

l         InnoDB的行鎖是基于鎖引實現(xiàn)的，如果不通過索引訪問數(shù)據(jù)，InnoDB會使用表鎖。

l         介紹了InnoDB間隙鎖（Next-key)機(jī)制，以及InnoDB使用間隙鎖的原因。

l         在不同的隔離級別下，InnoDB的鎖機(jī)制和一致性讀策略不同。

l         MySQL的恢復(fù)和復(fù)制對InnoDB鎖機(jī)制和一致性讀策略也有較大影響。

l         鎖沖突甚至死鎖很難完全避免。

在了解InnoDB鎖特性后，用戶可以通過設(shè)計和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：

l         盡量使用較低的隔離級別；

l         精心設(shè)計索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機(jī)會；

l         選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小；

l         給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖；

l         不同的程序訪問一組表時，應(yīng)盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會；

l         盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響；

l         不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖；

l         對于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能。

鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（如CPU、RAM、I/O等）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。本章我們著重討論MySQL鎖機(jī)制的特點，常見的鎖問題，以及解決MySQL鎖問題的一些方法或建議。

MySQL鎖概述

相對其他數(shù)據(jù)庫而言，MySQL的鎖機(jī)制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機(jī)制。比如，MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；BDB存儲引擎采用的是頁面鎖（page-level locking），但也支持表級鎖；InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認(rèn)情況下是采用行級鎖。

MySQL這3種鎖的特性可大致歸納如下。

開銷、加鎖速度、死鎖、粒度、并發(fā)性能

l         表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。

l         行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。

l         頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。

從上述特點可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點來說哪種鎖更合適！僅從鎖的角度來說：表級鎖更適合于以查詢?yōu)橹鳎挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。這一點在本書的“開發(fā)篇”介紹表類型的選擇時，也曾提到過。下面幾節(jié)我們重點介紹MySQL表鎖和 InnoDB行鎖的問題，由于BDB已經(jīng)被InnoDB取代，即將成為歷史，在此就不做進(jìn)一步的討論了。

MyISAM表鎖

MyISAM存儲引擎只支持表鎖，這也是MySQL開始幾個版本中唯一支持的鎖類型。隨著應(yīng)用對事務(wù)完整性和并發(fā)性要求的不斷提高，MySQL才開始開發(fā)基于事務(wù)的存儲引擎，后來慢慢出現(xiàn)了支持頁鎖的BDB存儲引擎和支持行鎖的InnoDB存儲引擎（實際 InnoDB是單獨的一個公司，現(xiàn)在已經(jīng)被Oracle公司收購）。但是MyISAM的表鎖依然是使用最為廣泛的鎖類型。本節(jié)將詳細(xì)介紹MyISAM表鎖的使用。

查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪：

mysql> show status like 'table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| Table_locks_immediate | 2979  |

| Table_locks_waited    | 0     |

+-----------------------+-------+

2 rows in set (0.00 sec))

如果Table_locks_waited的值比較高，則說明存在著較嚴(yán)重的表級鎖爭用情況。

MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。鎖模式的兼容性如表20-1所示。

表20-1                                            MySQL中的表鎖兼容性                

可見，對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！根據(jù)如表20-2所示的例子可以知道，當(dāng)一個線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

表20-2                          MyISAM存儲引擎的寫阻塞讀例子

如何加表鎖

MyISAM在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預(yù)，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，并非必須如此。

給MyISAM表顯示加鎖，一般是為了在一定程度模擬事務(wù)操作，實現(xiàn)對某一時間點多個表的一致性讀取。例如，有一個訂單表orders，其中記錄有各訂單的總金額total，同時還有一個訂單明細(xì)表order_detail，其中記錄有各訂單每一產(chǎn)品的金額小計 subtotal，假設(shè)我們需要檢查這兩個表的金額合計是否相符，可能就需要執(zhí)行如下兩條SQL：

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

這時，如果不先給兩個表加鎖，就可能產(chǎn)生錯誤的結(jié)果，因為第一條語句執(zhí)行過程中，order_detail表可能已經(jīng)發(fā)生了改變。因此，正確的方法應(yīng)該是：

Lock tables orders read local, order_detail read local;

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;

要特別說明以下兩點內(nèi)容。

?  上面的例子在LOCK TABLES時加了“l(fā)ocal”選項，其作用就是在滿足MyISAM表并發(fā)插入條件的情況下，允許其他用戶在表尾并發(fā)插入記錄，有關(guān)MyISAM表的并發(fā)插入問題，在后面的章節(jié)中還會進(jìn)一步介紹。

?  在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且MySQL不支持鎖升級。也就是說，在執(zhí)行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執(zhí)行查詢操作，而不能執(zhí)行更新操作。其實，在自動加鎖的情況下也基本如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現(xiàn)死鎖（Deadlock Free）的原因。

在如表20-3所示的例子中，一個session使用LOCK TABLE命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現(xiàn)鎖等待。

表20-3                     MyISAM存儲引擎的讀阻塞寫例子

當(dāng)使用LOCK TABLES時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現(xiàn)多少次，就要通過與SQL語句中相同的別名鎖定多少次，否則也會出錯！舉例說明如下。

（1）對actor表獲得讀鎖：

mysql> lock table actor read;

Query OK, 0 rows affected (0.00 sec)

（2）但是通過別名訪問會提示錯誤：

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;

ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

（3）需要對別名分別鎖定：

mysql> lock table actor as a read,actor as b read;

Query OK, 0 rows affected (0.00 sec)

（4）按照別名的查詢可以正確執(zhí)行：

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;

+------------+-----------+------------+-----------+

| first_name | last_name | first_name | last_name |

+------------+-----------+------------+-----------+

| Lisa       | Tom       | LISA       | MONROE    |

+------------+-----------+------------+-----------+

1 row in set (0.00 sec)

并發(fā)插入（Concurrent Inserts）

上文提到過MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進(jìn)行。

MyISAM存儲引擎有一個系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。

l         當(dāng)concurrent_insert設(shè)置為0時，不允許并發(fā)插入。

l         當(dāng)concurrent_insert設(shè)置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進(jìn)程讀表的同時，另一個進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。

l         當(dāng)concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。

在如表20-4所示的例子中，session_1獲得了一個表的READ LOCAL鎖，該線程可以對表進(jìn)行查詢操作，但不能對表進(jìn)行更新操作；其他的線程（session_2），雖然不能對表進(jìn)行刪除和更新操作，但卻可以對該表進(jìn)行并發(fā)插入操作，這里假設(shè)該表中間不存在空洞。

表20-4              MyISAM存儲引擎的讀寫（INSERT）并發(fā)例子

可以利用MyISAM存儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同一表查詢和插入的鎖爭用。例如，將concurrent_insert系統(tǒng)變量設(shè)為2，總是允許并發(fā)插入；同時，通過定期在系統(tǒng)空閑時段執(zhí)行 OPTIMIZE TABLE語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞。有關(guān)OPTIMIZE TABLE語句的詳細(xì)介紹，可以參見第18章中“兩個簡單實用的優(yōu)化方法”一節(jié)的內(nèi)容。

MyISAM的鎖調(diào)度

前面講過，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。那么，一個進(jìn)程請求某個 MyISAM表的讀鎖，同時另一個進(jìn)程也請求同一表的寫鎖，MySQL如何處理呢？答案是寫進(jìn)程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認(rèn)為寫請求一般比讀請求要重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應(yīng)用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。這種情況有時可能會變得非常糟糕！幸好我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM 的調(diào)度行為。

?  通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請求以優(yōu)先的權(quán)利。

?  通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級降低。

?  通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級。

雖然上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法，但還是可以用其來解決查詢相對重要的應(yīng)用（如用戶登錄系統(tǒng)）中，讀鎖等待嚴(yán)重的問題。

另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達(dá)到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機(jī)會。

上面已經(jīng)討論了寫優(yōu)先調(diào)度機(jī)制帶來的問題和解決辦法。這里還要強(qiáng)調(diào)一點：一些需要長時間運行的查詢操作，也會使寫進(jìn)程“餓死”！因此，應(yīng)用中應(yīng)盡量避免出現(xiàn)長時間運行的查詢操作，不要總想用一條SELECT語句來解決問題，因為這種看似巧妙的SQL語句，往往比較復(fù)雜，執(zhí)行時間較長，在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解”，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時段執(zhí)行，比如一些定期統(tǒng)計可以安排在夜間執(zhí)行。

InnoDB鎖問題

InnoDB與MyISAM的最大不同有兩點：一是支持事務(wù)（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務(wù)的引入也帶來了一些新問題。下面我們先介紹一點背景知識，然后詳細(xì)討論InnoDB的鎖問題。

背景知識

1．事務(wù)（Transaction）及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有以下4個屬性，通常簡稱為事務(wù)的ACID屬性。

l         原子性（Atomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。

l         一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持?jǐn)?shù)據(jù)的完整性；事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的。

l         隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。這意味著事務(wù)處理過程中的中間狀態(tài)對外部是不可見的，反之亦然。

l         持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

銀行轉(zhuǎn)帳就是事務(wù)的一個典型例子。

2．并發(fā)事務(wù)處理帶來的問題

相對于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持更多的用戶。但并發(fā)事務(wù)處理也會帶來一些問題，主要包括以下幾種情況。

l  更新丟失（Lost Update）：當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，由于每個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題－－最后的更新覆蓋了由其他事務(wù)所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成并提交事務(wù)之前，另一個編輯人員不能訪問同一文件，則可避免此問題。

l  臟讀（Dirty Reads）：一個事務(wù)正在對一條記錄做修改，在這個事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做"臟讀"。

l  不可重復(fù)讀（Non-Repeatable Reads）：一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀”。

l  幻讀（Phantom Reads）：一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。

3．事務(wù)隔離級別

在上面講到的并發(fā)事務(wù)處理帶來的問題中，“更新丟失”通常是應(yīng)該完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。

“臟讀”、“不可重復(fù)讀”和“幻讀”，其實都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決。數(shù)據(jù)庫實現(xiàn)事務(wù)隔離的方式，基本上可分為以下兩種。

l  一種是在讀取數(shù)據(jù)前，對其加鎖，阻止其他事務(wù)對數(shù)據(jù)進(jìn)行修改。

l  另一種是不用加任何鎖，通過一定機(jī)制生成一個數(shù)據(jù)請求時間點的一致性數(shù)據(jù)快照（Snapshot)，并用這個快照來提供一定級別（語句級或事務(wù)級）的一致性讀取。從用戶的角度來看，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個版本，因此，這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經(jīng)常稱為多版本數(shù)據(jù)庫。

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價也就越大，因為事務(wù)隔離實質(zhì)上就是使事務(wù)在一定程度上 “串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的。同時，不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。

為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSI SQL92定義了4個事務(wù)隔離級別，每個級別的隔離程度不同，允許出現(xiàn)的副作用也不同，應(yīng)用可以根據(jù)自己的業(yè)務(wù)邏輯要求，通過選擇不同的隔離級別來平衡 “隔離”與“并發(fā)”的矛盾。表20-5很好地概括了這4個隔離級別的特性。

表20-5                                             4種隔離級別比較

最后要說明的是：各具體數(shù)據(jù)庫并不一定完全實現(xiàn)了上述4個隔離級別，例如，Oracle只提供Read committed和Serializable兩個標(biāo)準(zhǔn)隔離級別，另外還提供自己定義的Read only隔離級別；SQL Server除支持上述ISO/ANSI SQL92定義的4個隔離級別外，還支持一個叫做“快照”的隔離級別，但嚴(yán)格來說它是一個用MVCC實現(xiàn)的Serializable隔離級別。MySQL 支持全部4個隔離級別，但在具體實現(xiàn)時，有一些特點，比如在一些隔離級別下是采用MVCC一致性讀，但某些情況下又不是，這些內(nèi)容在后面的章節(jié)中將會做進(jìn)一步介紹。

獲取InnoDB行鎖爭用情況    

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| InnoDB_row_lock_current_waits | 0     |

| InnoDB_row_lock_time          | 0     |

| InnoDB_row_lock_time_avg      | 0     |

| InnoDB_row_lock_time_max      | 0     |

| InnoDB_row_lock_waits         | 0     |

+-------------------------------+-------+

5 rows in set (0.01 sec)

如果發(fā)現(xiàn)鎖爭用比較嚴(yán)重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以通過設(shè)置InnoDB Monitors來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭用的原因。

具體方法如下：

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

Query OK, 0 rows affected (0.14 sec)

然后就可以用下面的語句來進(jìn)行查看：

mysql> Show innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name:

Status:

…

…

------------

TRANSACTIONS

------------

Trx id counter 0 117472192

Purge done for trx's n:o < 0 117472190 undo n:o < 0 0

History list length 17

Total number of lock structs in row lock hash table 0

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456

MySQL thread id 200610, query id 291197 localhost root

---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936

MySQL thread id 199285, query id 291199 localhost root

Show innodb status

…

監(jiān)視器可以通過發(fā)出下列語句來停止查看：

mysql> DROP TABLE innodb_monitor;

Query OK, 0 rows affected (0.05 sec)

設(shè)置監(jiān)視器后，在SHOW INNODB STATUS的顯示內(nèi)容中，會有詳細(xì)的當(dāng)前鎖等待的信息，包括表名、鎖類型、鎖定記錄的情況等，便于進(jìn)行進(jìn)一步的分析和問題的確定。打開監(jiān)視器以后，默認(rèn)情況下每15秒會向日志中記錄監(jiān)控的內(nèi)容，如果長時間打開會導(dǎo)致.err文件變得非常的巨大，所以用戶在確認(rèn)問題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器，或者通過使用“--console”選項來啟動服務(wù)器以關(guān)閉寫日志文件。

InnoDB的行鎖模式及加鎖方法

InnoDB實現(xiàn)了以下兩種類型的行鎖。

l  共享鎖（S）：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

l  排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

l  意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。

l  意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

上述鎖模式的兼容情況具體如表20-6所示。

表20-6                                            InnoDB行鎖模式兼容性列表

如果一個事務(wù)請求的鎖模式與當(dāng)前的鎖兼容，InnoDB就將請求的鎖授予該事務(wù)；反之，如果兩者不兼容，該事務(wù)就要等待鎖釋放。

意向鎖是InnoDB自動加的，不需用戶干預(yù)。對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

?  共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

?  排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時來確認(rèn)某行記錄是否存在，并確保沒有人對這個記錄進(jìn)行UPDATE或者DELETE操作。但是如果當(dāng)前事務(wù)也需要對該記錄進(jìn)行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用SELECT... FOR UPDATE方式獲得排他鎖。

在如表20-7所示的例子中，使用了SELECT ... IN SHARE MODE加鎖后再更新記錄，看看會出現(xiàn)什么情況，其中actor表的actor_id字段為主鍵。

表20-7  InnoDB存儲引擎的共享鎖例子

    當(dāng)使用SELECT...FOR UPDATE加鎖后再更新記錄，出現(xiàn)如表20-8所示的情況。

表20-8 InnoDB存儲引擎的排他鎖例子

InnoDB行鎖實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。下面通過一些實際例子來加以說明。

（1）在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。

在如表20-9所示的例子中，開始tab_no_index表沒有索引：

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

表20-9   InnoDB存儲引擎的表在不使用索引時使用表鎖例子

在如表20 -9所示的例子中，看起來session_1只給一行加了排他鎖，但session_2在請求其他行的排他鎖時，卻出現(xiàn)了鎖等待！原因就是在沒有索引的情況下，InnoDB只能使用表鎖。當(dāng)我們給其增加一個索引后，InnoDB就只鎖定了符合條件的行，如表20-10所示。

創(chuàng)建tab_with_index表，id字段有普通索引：

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> alter table tab_with_index add index id(id);

Query OK, 4 rows affected (0.24 sec)

Records: 4  Duplicates: 0  Warnings: 0

表20-10   InnoDB存儲引擎的表在使用索引時使用行鎖例子

（2）由于MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現(xiàn)鎖沖突的。應(yīng)用設(shè)計的時候要注意這一點。

在如表20-11所示的例子中，表tab_with_index的id字段有索引，name字段沒有索引：

mysql> alter table tab_with_index drop index name;

Query OK, 4 rows affected (0.22 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into tab_with_index  values(1,'4');

Query OK, 1 row affected (0.00 sec)

mysql> select * from tab_with_index where id = 1;

+------+------+

| id   | name |

+------+------+

| 1    | 1    |

| 1    | 4    |

+------+------+

2 rows in set (0.00 sec)

表20-11 InnoDB存儲引擎使用相同索引鍵的阻塞例子       

（3）當(dāng)表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖。

在如表20-12所示的例子中，表tab_with_index的id字段有主鍵索引，name字段有普通索引：

mysql> alter table tab_with_index add index name(name);

Query OK, 5 rows affected (0.23 sec)

Records: 5  Duplicates: 0  Warnings: 0

表20-12  InnoDB存儲引擎的表使用不同索引的阻塞例子

（4）即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計劃的代價來決定的，如果MySQL認(rèn)為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執(zhí)行計劃，以確認(rèn)是否真正使用了索引。關(guān)于MySQL在什么情況下不使用索引的詳細(xì)討論，參見本章“索引問題”一節(jié)的介紹。

在下面的例子中，檢索值的數(shù)據(jù)類型與索引字段不同，雖然MySQL能夠進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，但卻不會使用索引，從而導(dǎo)致InnoDB使用表鎖。通過用explain檢查兩條SQL的執(zhí)行計劃，我們可以清楚地看到了這一點。

例子中tab_with_index表的name字段有索引，但是name字段是varchar類型的，如果where條件中不是和varchar類型進(jìn)行比較，則會對name進(jìn)行類型轉(zhuǎn)換，而執(zhí)行的全表掃描。

mysql> alter table tab_no_index add index name(name);

Query OK, 4 rows affected (8.06 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from tab_with_index where name = 1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ALL

possible_keys: name

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from tab_with_index where name = '1' \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: tab_with_index

         type: ref

possible_keys: name

          key: name

      key_len: 23

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

間隙鎖（Next-Key鎖）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一個范圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關(guān)隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務(wù)插入了empid大于100的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句，就會發(fā)生幻讀；另外一方面，是為了滿足其恢復(fù)和復(fù)制的需要。有關(guān)其恢復(fù)和復(fù)制對鎖機(jī)制的影響，以及不同隔離級別下InnoDB使用間隙鎖的情況，在后續(xù)的章節(jié)中會做進(jìn)一步介紹。

很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機(jī)制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會造成嚴(yán)重的鎖等待。因此，在實際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。

還要特別說明的是，InnoDB除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

在如表20-13所示的例子中，假如emp表中只有101條記錄，其empid的值分別是1,2,......,100,101。

表20-13                InnoDB存儲引擎的間隙鎖阻塞例子

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | 分享文章：MYsql鎖與索引的關(guān)系 新聞來源：http://www.ef60e0e.cn/article/geoegp.html 其他資訊 vb.net中定義數(shù)組 vbs定義數(shù)組 wordpress辦公 wordpress 2021 Linux命令運行run linux命令運行腳本 gis技術(shù)關(guān)鍵 gis的技術(shù)瓶頸 智能推薦的java代碼 java智能推薦系統(tǒng)源碼 快速導(dǎo)航 無紡布袋定制 都江堰做網(wǎng)站 云服務(wù)器 域名注冊 成都廣告扇定制 廣告品牌策劃設(shè)計 成都手提袋制作 友情鏈接 網(wǎng)站制作 網(wǎng)站建設(shè) 網(wǎng)絡(luò)推廣 網(wǎng)站推廣 小程序開發(fā) 網(wǎng)站導(dǎo)航 網(wǎng)站建設(shè) 四川平武建站 網(wǎng)站導(dǎo)航 聯(lián)系方式 企業(yè)：四川綿陽平武網(wǎng)站建設(shè)工作室 地址：成都市青羊區(qū)太升南路288號 電話：18980820575 網(wǎng)址：www.ef60e0e.cn 公司名稱：四川綿陽平武網(wǎng)站建設(shè)工作室 聯(lián)系電話：18980820575 網(wǎng)站備案號：蜀ICP備2024061352號-3 四川平武建站 四川平武網(wǎng)站建設(shè) 四川平武網(wǎng)站設(shè)計 四川平武網(wǎng)站制作 成都做網(wǎng)站 99热在线精品一区二区三区_国产伦精品一区二区三区女破破_亚洲一区二区三区无码_精品国产欧美日韩另类一区

<ul id="0c1fb"></ul>