文章目錄

• • 1.概述
  • • 1.1 定義
    • 1.2 鎖的分類
  • 2.三鎖
  • • 2.1 表鎖(偏讀)
    • 2.2 行鎖(偏寫)
    • 2.3 頁鎖

1.概述

1.1 定義

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

1.2 鎖的分類

從對數據操作的類型（讀\寫）分:

讀鎖(共享鎖)：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

從對數據操作的粒度分:

為了盡可能提高數據庫的并發度，每次鎖定的數據范圍越小越好, 理論上每次只鎖定當前操作的數據的方案會得到最大的并發度， 但是管理鎖是很耗資源的事情（涉及獲取，檢查，釋放鎖等動作）， 因此數據庫系統需要在高并發響應和系統性能兩方面進行平衡， 這樣就產生了“鎖粒度（Lock granularity）”的概念。一種提高共享資源并發發性的方式是讓鎖定對象更有選擇性。 盡量只鎖定需要修改的部分數據，而不是所有的資源。 更理想的方式是，只對會修改的數據片進行精確的鎖定。 任何時候，在給定的資源上，鎖定的數據量越少，則系統的并發程度越高，只要相互之間不發生沖突即可。

表鎖
行鎖

2.三鎖

2.1 表鎖(偏讀)

1.特點

偏向MyISAM存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大，發生鎖沖突的概率最高,并發度最低。

2.案例分析

【表級鎖分析--建表SQL】create table mylock(id int not null primary key auto_increment,name varchar(20) )engine myisam;insert into mylock(name) values('a'); insert into mylock(name) values('b'); insert into mylock(name) values('c'); insert into mylock(name) values('d'); insert into mylock(name) values('e');select * from mylock;【手動增加表鎖】lock table 表名字1 read(write)，表名字2 read(write)，其它; 【查看表上加過的鎖】 show open tables;【釋放表鎖】 unlock tables;

3.結論

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖， 在執行增刪改操作前，會自動給涉及的表加寫鎖。 MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）表獨占寫鎖（Table Write Lock） 鎖類型他人可讀他人可寫

讀鎖	是	否
寫鎖	否	否

結論： 結合上表，所以對MyISAM表進行操作，會有以下情況： 1、對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其它進程的寫操作。 2、對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其它進程的讀寫操作。 簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞

2.2 行鎖(偏寫)

1.特點

偏向InnoDB存儲引擎，開銷大，加鎖慢； 會出現死鎖； 鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。 InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）；二是采用了行級鎖

2.由于行鎖支持事務，復習老知識

事務筆記

事務（Transaction）及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。 l 原子性（Atomicity）： 事務是一個原子操作單元，其對數據的修改，要么全都執行，要么全都不執行。 l 一致性（Consistent）： 在事務開始和完成時，數據都必須保持一致狀態。 這意味著所有相關的數據規則都必須應用于事務的修改，以保持數據的完整性； 事務結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的。 l 隔離性（Isolation）： 數據庫系統提供一定的隔離機制， 保證事務在不受外部并發操作影響的“獨立”環境執行。 這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。l 持久性（Durable）： 事務完成之后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。

并發事務處理帶來的問題

更新丟失(Lost Update)

當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時， 由于每個事務都不知道其他事務的存在，就會發生丟失更新問題－－最后的更新覆蓋了由其他事務所做的更新。例如，兩個程序員修改同一java文件。每程序員獨立地更改其副本，然后保存更改后的副本， 這樣就覆蓋了原始文檔。最后保存其更改副本的編輯人員覆蓋前一個程序員所做的更改。如果在一個程序員完成并提交事務之前，另一個程序員不能訪問同一文件，則可避免此問題。

臟讀(Dirty Reads)

一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致狀態； 這時，另一個事務也來讀取同一條記錄，如果不加控制， 第二個事務讀取了這些“臟”數據，并據此做進一步的處理，就會產生未提交的數據依賴關系。 這種現象被形象地叫做”臟讀”。 一句話：事務A讀取到了事務B已修改但尚未提交的的數據，還在這個數據基礎上做了操作。 此時，如果B事務回滾，A讀取的數據無效，不符合一致性要求。

不可重復讀(Non-Repeatable Reads)

在一個事務內，多次讀同一個數據。 在這個事務還沒有結束時，另一個事務也訪問該同一數據。 那么，在第一個事務的兩次讀數據之間。 由于第二個事務的修改，那么第一個事務讀到的數據可能不一樣， 這樣就發生了在一個事務內兩次讀到的數據是不一樣的，因此稱為不可重復讀，即原始讀取不可重復。一句話：一個事務范圍內兩個相同的查詢卻返回了不同數據。

幻讀(Phantom Reads)

一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀”。一句話：事務A 讀取到了事務B提交的新增數據，不符合隔離性。

事務隔離級別

臟讀”、“不可重復讀”和“幻讀”，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決。

數據庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大， 因為事務隔離實質上就是使事務在一定程度上 “串行化”進行，這顯然與“并發”是矛盾的。 同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的， 比如許多應用對“不可重復讀”和“幻讀”并不敏感，可能更關心數據并發訪問的能力。 常看當前數據庫的事務隔離級別：show variables like 'transaction_isolation'; mysql> show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.01 sec)

3.案例分析

建表SQL

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;insert into test_innodb_lock values(1,'b2'); insert into test_innodb_lock values(3,'3'); insert into test_innodb_lock values(4,'4000'); insert into test_innodb_lock values(5,'5000'); insert into test_innodb_lock values(6,'6000'); insert into test_innodb_lock values(7,'7000'); insert into test_innodb_lock values(8,'8000'); insert into test_innodb_lock values(9,'9000'); insert into test_innodb_lock values(1,'b1');create index test_innodb_a_ind on test_innodb_lock(a);create index test_innodb_lock_b_ind on test_innodb_lock(b);select * from test_innodb_lock;

行鎖定基本演示

查看時候自動提交：

mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec)

窗口1：

mysql> set autocommit=0; Query OK, 0 rows affected (0.03 sec)

窗口2：

mysql> set autocommit=0; Query OK, 0 rows affected (0.03 sec)

窗口1：

mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)mysql> update test_innodb_lock set b="4001" where a=4; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4001 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)

窗口2：

mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)

窗口1：

mysql> commit; Query OK, 0 rows affected (0.07 sec)

窗口2：

mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)mysql> commit; Query OK, 0 rows affected (0.00 sec)mysql> select * from test_innodb_lock; +------+------+ | a | b | +------+------+ | 1 | b2 | | 3 | 3 | | 4 | 4001 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | b1 | +------+------+ 9 rows in set (0.00 sec)

無索引行鎖升級為表鎖

Select也可以加鎖

讀鎖：

共享鎖(Share Lock)共享鎖又稱讀鎖，是讀取操作創建的鎖。其他用戶可以并發讀取數據， 但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。 如果事務T對數據A加上共享鎖后，則其他事務只能對A再加共享鎖，不能加排他鎖。 獲準共享鎖的事務只能讀數據，不能修改數據。用法 SELECT ... LOCK IN SHARE MODE;在查詢語句后面增加 LOCK IN SHARE MODE ， Mysql會對查詢結果中的每行都加共享鎖， 當沒有其他線程對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。 其他線程也可以讀取使用了共享鎖的表（行？），而且這些線程讀取的是同一個版本的數據。

寫鎖：

排他鎖（eXclusive Lock）共享鎖又稱寫鎖，如果事務T對數據A加上排他鎖后，則其他事務不能再對A加任任何類型的封鎖。 獲準排他鎖的事務既能讀數據，又能修改數據。用法 SELECT ... FOR UPDATE;在查詢語句后面增加 FOR UPDATE ，Mysql會對查詢結果中的每行都加排他鎖， 當沒有其他線程對查詢結果集中的任何一行使用排他鎖時，可以成功申請排他鎖，否則會被阻塞。

間隙鎖危害

【什么是間隙鎖】 當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時， InnoDB會給符合條件的已有數據記錄的索引項加鎖； 對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（GAP)”， InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（GAP Lock）。【危害】 因為Query執行過程中通過過范圍查找的話，他會鎖定整個范圍內所有的索引鍵值，即使這個鍵值并不存在。 間隙鎖有一個比較致命的弱點，就是當鎖定一個范圍鍵值之后，即使某些不存在的鍵值也會被無辜的鎖定， 而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害

4.案列結論

Innodb存儲引擎由于實現了行級鎖定， 雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些， 但是在整體并發處理能力方面要遠遠優于MyISAM的表級鎖定的。 當系統并發量較高的時候，Innodb的整體性能和MyISAM相比就會有比較明顯的優勢了。但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現不僅不能比MyISAM高，甚至可能會更差。

5.行鎖分析

【如何分析行鎖定】 通過檢查InnoDB_row_lock狀態變量來分析系統上的行鎖的爭奪情況 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.00 sec)對各個狀態量的說明如下：Innodb_row_lock_current_waits：當前正在等待鎖定的數量； Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度； Innodb_row_lock_time_avg：每次等待所花平均時間； Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間； Innodb_row_lock_waits：系統啟動后到現在總共等待的次數；對于這5個狀態變量，比較重要的主要是Innodb_row_lock_time_avg（等待平均時長），Innodb_row_lock_waits（等待總次數）Innodb_row_lock_time（等待總時長）這三項。 尤其是當等待次數很高，而且每次等待時長也不小的時候， 我們就需要分析系統中為什么會有如此多的等待，然后根據分析結果著手指定優化計劃。最后可以通過 SELECT * FROM information_schema.INNODB_TRX\G; 來查詢正在被鎖阻塞的sql語句。

6.優化建議

盡可能讓所有數據檢索都通過索引來完成，避免無索引行鎖升級為表鎖。 盡可能較少檢索條件，避免間隙鎖 盡量控制事務大小，減少鎖定資源量和時間長度 鎖住某行后，盡量不要去調別的行或表，趕緊處理被鎖住的行然后釋放掉鎖。 涉及相同表的事務，對于調用表的順序盡量保持一致。 在業務環境允許的情況下,盡可能低級別事務隔離

2.3 頁鎖

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

總結

以上是生活随笔為你收集整理的数据库高级知识——MySql锁机制的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。