mysql讀數據時怎么加寫鎖

加鎖情況與死鎖原因分析

成都創新互聯堅持“要么做到，要么別承諾”的工作理念，服務領域包括：成都網站設計、成都做網站、外貿網站建設、企業官網、英文網站、手機端網站、網站推廣等服務，滿足客戶于互聯網時代的冊亨網站設計、移動媒體設計的需求，幫助企業找到有效的互聯網解決方案。努力成為您成熟可靠的網絡建設合作伙伴！

為方便大家復現，完整表結構和數據如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 執行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產生的：

1.?session1 執行 delete ?會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內部觀測到的：X Lock but not gap）；

2.?session2 和 session3 在執行 insert 的時候，由于唯一約束檢測發生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進入等待；

3.?session1 在執行 commit 后，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；

4.?session2 和 session3 繼續執行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現了，并且由于插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。

死鎖日志如下：

請點擊輸入圖片描述

INSERT INTENTION LOCK

在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。

但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務不會互相沖突等待。

當插入一條記錄時，會去檢查當前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進入等待狀態（插入意向鎖之間并不互斥）。總結一下這把鎖的屬性：

1. 它不會阻塞其他任何鎖；

2. 它本身僅會被 gap lock 阻塞。

在學習 MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一個重要的點就是 gap lock，通常情況下我們說到 gap lock 都只會聯想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。

通過下面這個例子就能驗證：

請點擊輸入圖片描述

這里 session1 插入數據遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 并不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當 session2 插入遇到唯一沖突但是因為被 X Lock 阻塞，并不會立刻報錯 “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。

有個困惑很久的疑問：出現唯一沖突需要加 S Next-Key Lock 是事實，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實現的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點希望能與大家一起討論得到好的解釋。

如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：

普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到后1條記錄的左閉右開區間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區間。

對于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：

1. 在絕大部分的業務場景下，都可以把 MySQL 的隔離界別設置為 READ-COMMITTED；

2. 在業務方便控制字段值唯一的情況下，盡量減少表中唯一索引的數量。

鎖沖突矩陣

前面我們說的 GAP LOCK 其實是鎖的屬性，另外我們知道 InnoDB 常規鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對我們分析死鎖很有幫助：

請點擊輸入圖片描述

mysql 的鎖以及間隙鎖

mysql 為并發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的并發讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數據，還有一種是“當前讀”，一般加鎖實現“當前讀”，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低于后面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果后面事務插入數據并提交，那么在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

mysql update是加什么鎖

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

MySQL鎖概述

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

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

開銷、加鎖速度、死鎖、粒度、并發性能

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

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

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

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

MyISAM表鎖

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

查詢表級鎖爭用情況

可以通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪：

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的值比較高，則說明存在著較嚴重的表級鎖爭用情況。

MySQL表級鎖的鎖模式

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

表20-1 MySQL中的表鎖兼容性請求鎖模式

是否兼容

當前鎖模式

None

讀鎖

寫鎖

讀鎖

是

是

否

寫鎖

是

否

否

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

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

session_2

獲得表film_text的WRITE鎖定

mysql lock table film_text write;

Query OK, 0 rows affected (0.00 sec)

當前session對鎖定表的查詢、更新、插入操作都可以執行：

mysql select film_id,title from film_text where film_id = 1001;+---------+-------------+

| film_id | title |

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

| 1001 | Update Test |

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

1 row in set (0.00 sec)

mysql insert into film_text (film_id,title) values(1003,'Test');Query OK, 1 row affected (0.00 sec)

mysql update film_text set title = 'Test' where film_id = 1001;Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

其他session對鎖定表的查詢被阻塞，需要等待鎖被釋放：

mysql select film_id,title from film_text where film_id = 1001;等待

釋放鎖：

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

Session2獲得鎖，查詢返回：

mysql select film_id,title from film_text where film_id = 1001;+---------+-------+

| film_id | title |

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

| 1001 | Test |

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

1 row in set (57.59 sec)

如何加表鎖

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

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

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

這時，如果不先給兩個表加鎖，就可能產生錯誤的結果，因為第一條語句執行過程中，order_detail表可能已經發生了改變。因此，正確的方法應該是：

Lock tables orders read local, order_detail read local;Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;

要特別說明以下兩點內容。

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

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

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

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

session_2

獲得表film_text的READ鎖定

mysql lock table film_text read;

Query OK, 0 rows affected (0.00 sec)

當前session可以查詢該表記錄

mysql select film_id,title from film_text where film_id = 1001;+---------+------------------+

| film_id | title |

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

| 1001 | ACADEMY DINOSAUR |

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

1 row in set (0.00 sec)

其他session也可以查詢該表的記錄

mysql select film_id,title from film_text where film_id = 1001;+---------+------------------+

| film_id | title |

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

| 1001 | ACADEMY DINOSAUR |

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

1 row in set (0.00 sec)

當前session不能查詢沒有鎖定的表

mysql select film_id,title from film where film_id = 1001;ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES其他session可以查詢或者更新未鎖定的表

mysql select film_id,title from film where film_id = 1001;+---------+---------------+

| film_id | title |

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

| 1001 | update record |

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

1 row in set (0.00 sec)

mysql update film set title = 'Test' where film_id = 1001;Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

當前session中插入或者更新鎖定的表都會提示錯誤：

mysql insert into film_text (film_id,title) values(1002,'Test');ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql update film_text set title = 'Test' where film_id = 1001;ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session更新鎖定表會等待獲得鎖：

mysql update film_text set title = 'Test' where film_id = 1001;等待

釋放鎖

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

Session獲得鎖，更新操作完成：

mysql update film_text set title = 'Test' where film_id = 1001;Query OK, 1 row affected (1 min 0.71 sec)Rows matched: 1 Changed: 1 Warnings: 0

當使用LOCK TABLES時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與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）按照別名的查詢可以正確執行：

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)

并發插入（Concurrent Inserts）

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

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

l 當concurrent_insert設置為0時，不允許并發插入。

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

l 當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發插入記錄。

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

表20-4 MyISAM存儲引擎的讀寫（INSERT）并發例子session_1

session_2

獲得表film_text的READ LOCAL鎖定

mysql lock table film_text read local;

Query OK, 0 rows affected (0.00 sec)

當前session不能對鎖定表進行更新或者插入操作：

mysql insert into film_text (film_id,title) values(1002,'Test');ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updatedmysql update film_text set title = 'Test' where film_id = 1001;ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated其他session可以進行插入操作，但是更新會等待：

mysql insert into film_text (film_id,title) values(1002,'Test');Query OK, 1 row affected (0.00 sec)

mysql update film_text set title = 'Update Test' where film_id = 1001;等待

當前session不能訪問其他session插入的記錄：

mysql select film_id,title from film_text where film_id = 1002;Empty set (0.00 sec)

釋放鎖：

mysql unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

當前session解鎖后可以獲得其他session插入的記錄：

mysql select film_id,title from film_text where film_id = 1002;+---------+-------+

| film_id | title |

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

| 1002 | Test |

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

1 row in set (0.00 sec)

Session2獲得鎖，更新操作完成：

mysql update film_text set title = 'Update Test' where film_id = 1001;Query OK, 1 row affected (1 min 17.75 sec)Rows matched: 1 Changed: 1 Warnings: 0

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

分享標題：mysql更新時怎么加鎖 mysql更新是否加鎖
當前路徑：http://www.yijiale78.com/article46/ddgcseg.html

成都網站建設公司_創新互聯，為您提供Google、網站建設、自適應網站、網站設計、服務器托管、建站公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯