mysql 鎖機制介紹

一、鎖機制和Mysql鎖介紹

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

Mysql用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。這些鎖統稱為悲觀鎖(Pessimistic Lock)。

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

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

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

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

從上述特點可見，很難籠統地說哪種鎖更好，隻能就具體應用的特點來說哪種鎖更合适！僅從鎖的角度來說：表級鎖更适合于以查詢為主，隻有少量按索引條件更新資料的應用，如Web應用；而行級鎖則更适合于有大量按索引條件并發更新少量不同資料，同時又有 并發查詢的應用，如一些線上事務處理（OLTP）系統。

二、MyISAM表鎖

MySQL的表級鎖有兩種模式：

表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。

執行select語句對MyISAM表的讀操作會自動加讀鎖，不會阻塞其他使用者對同一表的讀請求，但會阻塞；在執行更新操作 (UPDATE、DELETE、INSERT 等)前，會自動給涉及的表加寫鎖，對同一表的寫請求,對 MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作；這個過程并不需要使用者幹預，是以，使用者一般不需要直接用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 table film_text write;

select * from film_text where film_id=999\G;

insert into film_text(film_id,title) value (1001,'Test');

update film_text set title='Test1' where film_id='1001';

UNLOCK TABLES；

注意：

在執行 LOCK TABLES 後，隻能通路顯式加鎖的這些表，不能通路未加鎖的表;

四、InnoDB鎖

InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來了一些新問題。

1、事務（Transaction）及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有4屬性，通常稱為事務的ACID屬性。

原子性（Actomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。

一緻性（Consistent）：在事務開始和完成時，資料都必須保持一緻狀态。這意味着所有相關的資料規則都必須應用于事務的修改，以操持完整性；事務結束時，所有的内部資料結構（如B樹索引或雙向連結清單）也都必須是正确的。

隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的"獨立"環境執行。這意味着事務處理過程中的中間狀态對外部是不可見的，反之亦然。

持久性（Durable）：事務完成之後，它對于資料的修改是永久性的，即使出現系統故障也能夠保持。

2、并發事務帶來的問題

相對于串行處理來說，并發事務處理能大大增加資料庫資源的使用率，提高資料庫系統的事務吞吐量，進而可以支援可以支援更多的使用者。但并發事務處理也會帶來一些問題，主要包括以下幾種情況。

更新丢失（Lost Update）：當兩個或多個事務選擇同一行，然後基于最初標明的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丢失更新問題——最後的更新覆寫了其他事務所做的更新。例如，兩個編輯人員制作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後儲存更改後的副本，這樣就覆寫了原始文檔。最後儲存其更改儲存其更改副本的編輯人員覆寫另一個編輯人員所做的修改。如果在一個編輯人員完成并送出事務之前，另一個編輯人員不能通路同一檔案，則可避免此問題。

髒讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務并送出前，這條記錄的資料就處于不一緻狀态；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些"髒"的資料，并據此做進一步的處理，就會産生未送出的資料依賴關系。這種現象被形象地叫做"髒讀"。

不可重複讀（Non-Repeatable Reads）：一個事務在讀取某些資料已經發生了改變、或某些記錄已經被删除了！這種現象叫做"不可重複讀"。

幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為"幻讀"。

3、事務隔離級别

在并發事務處理帶來的問題中，"更新丢失"通常應該是完全避免的。但防止更新丢失，并不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖來解決，是以，防止更新丢失應該是應用的責任。

"髒讀"、"不可重複讀"和"幻讀"，其實都是資料庫讀一緻性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實作事務隔離的方式，基本可以分為以下兩種。

一種是在讀取資料前，對其加鎖，阻止其他事務對資料進行修改。

另一種是不用加任何鎖，通過一定機制生成一個資料請求時間點的一緻性資料快照（Snapshot），并用這個快照來提供一定級别（語句級或事務級）的一緻性讀取。從使用者的角度，好像是資料庫可以提供同一資料的多個版本，是以，這種技術叫做資料多版本并發控制（ＭultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。

五、InnoDB的行鎖模式及加鎖方法

InnoDB共有七種類型的鎖：

共享鎖(Shared Locks)：又稱讀鎖，允許其他事務讀一行，組織其他事務為這一行增加排他鎖

select語句預設不加鎖，加共享鎖可以使用select … lock in share mode語句

排它鎖(Exclusive Locks)：又稱寫鎖，阻止其他事務為這一回加讀鎖和寫鎖

update,delete,insert都會自動給涉及到的資料加上排他鎖

加排他鎖可以使用select …for update。

意向鎖(Intention Locks)：InnoDB為了支援多粒度鎖機制(multiple granularity locking)，即允許行級鎖與表級鎖共存，而引入了意向鎖(intention locks)。意向鎖是指，未來的某個時刻，事務可能要加共享/排它鎖了，先提前聲明一個意向。

意向共享鎖(intention shared lock, IS)，它預示着，事務有意向對表中的某些行加共享S鎖；

意向排它鎖(intention exclusive lock, IX)，它預示着，事務有意向對表中的某些行加排它X鎖；

加鎖的文法為：

select ... lock in share mode;　　要設定IS鎖；

select ... for update;　　　　　　 要設定IX鎖；

記錄鎖(Record Locks)：記錄鎖，它封鎖索引記錄

例如(其中id為pk)：

create table lock_example(id smallint(10),name varchar(20),primary key id)engine=innodb;

select * from t where id=1 for update;

其實這裡是先擷取該表的意向排他鎖(IX)，再擷取這行記錄的排他鎖(我的了解是因為這裡直接命中索引了)，以阻止其他事務插入，更新，删除id=1的這一行

間隙鎖(Gap Locks)：間隙鎖，它封鎖索引記錄中的間隔，或者第一條索引記錄之前的範圍，又或者最後一條索引記錄之後的範圍。

select * from lock_example

where id between 8 and 15

for update;

間隙鎖的主要目的，就是為了防止其他事務在間隔中插入資料，以導緻"不可重複讀"。如果把事務的隔離級别降級為讀送出(Read Committed, RC)，間隙鎖則會自動失效。

臨鍵鎖(Next-key Locks)：臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖範圍，既包含索引記錄，又包含索引區間。

事務A執行如下語句，未送出：

select * from lock_example where id = 20 for update;

事務B開始，執行如下語句，會阻塞：

insert into lock_example values('zhang',15);

插入意向鎖(Insert Intention Locks)：是間隙鎖(Gap Locks)的一種（是以，也是實施在索引上的），它是專門針對insert操作的。多個事務，在同一個索引，同一個範圍區間插入記錄時，如果插入的位置不沖突，不會阻塞彼此。

　　舉個例子(表依然是如上的例子lock_example，資料依然是如上)，事務A先執行，在10與20兩條記錄中插入了一行，還未送出：

insert into t values(11, xxx);

　　事務B後執行，也在10與20兩條記錄中插入了一行：

insert into t values(12, ooo);

　　因為是插入操作，雖然是插入同一個區間，但是插入的記錄并不沖突，是以使用的是插入意向鎖，此處A事務并不會阻塞B事務。

自增鎖(Auto-inc Locks)：是一種特殊的表級别鎖（table-level lock），專門針對事務插入AUTO_INCREMENT類型的列。最簡單的情況，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

舉個例子(表依然是如上的例子lock_example)，但是id為AUTO_INCREMENT，資料庫表中資料為：

1, zhangsan

2, lisi

3, wangwu

事務A先執行，還未送出：insert into t(name) values(xxx);

事務B後執行：insert into t(name) values(ooo);

此時事務B插入操作會阻塞，直到事務A送出。

六、InnoDB鎖狀态檢視

可以通過檢查InnoDB_row_lock狀态變量來分析系統上的行鎖的争奪情況：

mysql> show status like '%lock%';+------------------------------------------+---------+| Variable_name | Value |+------------------------------------------+---------+| Com_lock_tables | 0 || Com_unlock_tables | 0 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 3946985 || Innodb_row_lock_time_avg | 1558 || Innodb_row_lock_time_max | 121788 || Innodb_row_lock_waits | 2532 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 26716 || Key_blocks_used | 2660 || Performance_schema_locker_lost | 0 || Performance_schema_rwlock_classes_lost | 0 || Performance_schema_rwlock_instances_lost | 0 || Qcache_free_blocks | 27343 || Qcache_total_blocks | 61962 || Table_locks_immediate | 8504599 || Table_locks_waited | 2 |+------------------------------------------+---------+

nnoDB 的行級鎖定狀态變量不僅記錄了鎖定等待次數，還記錄了鎖定總時長，每次平均時長，以及最大時長，此外還有一個非累積狀态量顯示了目前正在等待鎖定的等待數量。對各個狀态量的說明如下：

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（等待總時長）這三項。尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果着手指定優化計劃。

如果發現鎖争用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高，還可以通過設定InnoDB Monitors 來進一步觀察發生鎖沖突的表、資料行等，并分析鎖争用的原因。

七、mysql檢視死鎖和解除鎖

解除正在死鎖的狀态有兩種方法：

第一種：

1.查詢是否鎖表

show OPEN TABLES where In_use > 0;

2.查詢程序（如果您有SUPER權限，您可以看到所有線程。否則，您隻能看到您自己的線程）

show processlist

3.殺死程序id（就是上面指令的id列）

kill id

第二種：

1.檢視下在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.殺死程序id（就是上面指令的trx_mysql_thread_id列）

kill 線程ID

例子：

查出死鎖程序：SHOW PROCESSLIST

殺掉程序 KILL 420821;

其它關于檢視死鎖的指令：

1：檢視目前的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：檢視目前鎖定的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;