mysql 鎖

鎖概述

　　MySQL的鎖機制，就是資料庫為了保證資料的一致性而設計的面對併發場景的一種規則。

　　最顯著的特點是不同的儲存引擎支援不同的鎖機制，InnoDB支援行鎖和表鎖，MyISAM支援表鎖。

　　表鎖就是把整張表鎖起來，特點是加鎖快，開銷小，不會出現死鎖，鎖粒度大，發生鎖衝突的概率高，併發相對較低。
　　行鎖就是以行為單位把資料鎖起來，特點是加鎖慢，開銷大，會出現死鎖，鎖粒度小，發生鎖衝突的概率低，併發度也相對錶鎖較高。

MyISAM鎖

MyISAM的鎖排程

　　在MyISAM引擎中，讀鎖和寫鎖是互斥的，讀寫操作是序列的，鎖設計方案如下：

　　對於寫操作：如果表上沒有鎖，則在上面加一把寫鎖，否則，把請求放到寫鎖佇列中。
　　對於讀操作：如果表上沒有鎖，則在上面加一把讀鎖，否則，把請求方到讀鎖佇列中。

　　這是什麼意思呢？

　　意思就是說MyISAM在執行查詢語句前，會自動給涉及的所有表加讀鎖，在執行更新語句(增刪改操作)前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預。

　　當一個鎖被釋放時，鎖定權會先被寫鎖佇列中的執行緒得到，當寫鎖佇列中的請求都跑完後，才輪到讀鎖佇列中的請求。（即使讀請求先到鎖等待佇列中，寫請求後到，寫請求也會插入到讀請求之前！這就是MySQL認為寫請求一般比讀請求重要）

　　這就意味著，如果一個表上有很多更新操作，那麼select語句將等待直到別的更新都結束後才能查到東西。這也就是為什麼MyISAM表不適合大量更新操作應用的原因，因為大量更新操作可能導致查詢操作很難獲得讀鎖，從而長久阻塞，致使程式響應超時。

也許你需要顯式加鎖

　　表鎖語句有如下三條（MyISAM和InnoDB都一樣）：

　　LOCK TABLES tb_name READ; 加讀鎖，其他會話可讀，但不能更新。
　　LOCK TABLES tb_name WRITE; 加寫錯，其他會話不可讀，不可寫。
　　UNLOCK TABLES; 釋放鎖

當有連續多表更新的時候，可能會出現頻繁的表鎖競爭，更新資料的速度反而會下降，並且更新這個表的時候另一個表的資料可能被別的執行緒更新了（MyISAM是沒有事務的），這個時候，我們就需要鎖住多張表，再進行更新。

這裡示例，同時上鎖更新兩個表，給id為1的使用者餘額加1：

　　LOCK TABLES tb_1 WRITE,tb_2 WRITE;
　　UPDATE tb_1 SET balance=balance+1 WHERE user_id=1;
　　UPDATE tb_2 SET balance=balance+1 WHERE user_id=1;
　　UNLOCK TABLES;

特別注意：顯式加鎖的時候，必須同時取得所有涉及表的鎖，並且，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表。

（MyISAM的內容就這一章，接下來的章節都是InnDB的了，特此說明哈。）

InnoDB鎖型別

共享鎖（S鎖、讀鎖）

　　SELECT * FROM tb_name LOCK IN SHARE MODE;

　　一個事務獲取了一個資料行的讀鎖，允許其他事務也來獲取讀鎖，但是不允許其他事務來獲取寫鎖。也就是說，我上了讀鎖之後，其他事務也可以來讀，但是不能增刪改。

排他鎖（X鎖、寫鎖）

　　SELECT * FROM tb_name FOR UPDATE;

　　一個事務獲取了一個資料行的寫鎖，其他事務就不能再跑來獲取任何鎖了，所有請求都會被阻塞，直到當前的寫鎖被釋放。

意向鎖與MDL鎖

　　意向共享鎖（IS）：事務在給一個資料行加共享鎖之前必須先取得該表的IS鎖。
　　意向排他鎖（IX）：事務在給一個資料行加共享鎖之前必須先取得該表的IX鎖。
　　MDL鎖：在事務中，InnoDB會給涉及的所有表加上一個MDL鎖，其他事務就不可以執行任何DDL語句的操作。（親測只要在事務中，不管是查詢語句還是更新語句，涉及到的表都會被加上MDL鎖）

　　這三種鎖，是InnoDB內部使用的鎖，是自動實現的，不需要使用者干預。

幾種行鎖技術

記錄鎖（record lock）

　　這是一個索引記錄鎖，它是建立在索引記錄上的鎖（主鍵和唯一索引都算），很多時候，鎖定一條資料，由於無索引，往往會導致整個表被鎖住，建立合適的索引可以防止掃描整個表。

　　如：開兩個會話，兩個事務，並且都不commit，該表有主鍵，兩個會話修改同一條資料，第一個會話update執行後，第二個會話的update是無法執行成功的，會進入等待狀態，但是如果update別的資料行就可以成功。

　　再例如：開兩個會話，兩個事務，並且都不commit，並且該表無主鍵無索引，那麼第二個會話不管改什麼都會進入等待狀態。因為無索引的話，整個表的資料都被第一個會話鎖定了。

間隙鎖（gap lock）

　　MySQL預設隔離級別是可重複讀，這個隔離級別為了避免幻讀現象，引入了這個間隙鎖，對索引項之間的間隙上鎖。

　　示例：

　　(會話1)
　　START TRANSACTION;
　　SELECT * FROM tb_name WHERE id>10 LOCK IN SHARE MODE;
　　（會話2）
　　START TRANSACTION;
　　INSERT INTO tb_name(id,name) VLUES(11,"張三")

　　結果怎樣？會話2會進入執行等待狀態，直至會話1的鎖釋放或者鎖超時。

next-key鎖（記錄所和間隙鎖的組合）

　　當InnoDB掃描索引記錄時，會先對選中的索引記錄加上記錄鎖（record Lock），再對索引記錄兩遍的間隙加上間隙鎖（gap lock）。

　　還是以間隙鎖的例子說，假如表中沒有id=10的這行資料，會話2新增的id該為10，會成功嗎？

　　答案是不會，因為它不止鎖了id>10的間隙，連id=10也一起鎖了。

表鎖

　　在InnoDB中絕大部分都應該使用行鎖，因為事務和行鎖往往是我們選擇InnoDB表的理由，但是在個別特殊事務中，也可以考慮使用表鎖。

　　情況1：事務需要更新大部分或者全部資料，表又比較大，如果使用預設的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖衝突，這種情況下可以考慮使用表鎖來提高事務的執行速度。

　　情況2：事務涉及多個表，比較複雜，很可能引起死鎖，造成大量事務回滾，這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖，減少資料庫因事務回滾帶來的開銷。

　　當然，這兩種情況不能太多，否則就應該從業務和程式設計上進行拆分處理，而不是由資料庫來承擔這個事情。

　　例子如下：

　　LOCK TABLES tb_name WRITE;
　　UNLOCK TABLES;

注意：在事務中鎖表時，在事務結束前不要釋放鎖，因為unlock tables會隱含提交事務，所以正確的做法是結束事務後再釋放鎖。

鎖等待和死鎖

　　鎖等待是指一個事務過程中產生的鎖，其他事務需要等待上一個事務釋放它的鎖，才能佔用該資源，如果該事務一直不釋放，就需要繼續等待下去，直到超過了鎖等待時間，會報一個超時錯誤。

　　檢視鎖等待允許時間：

　　SHOW VARIABLES LIKE "innodb_lock_wait_timeout"

 

　　死鎖是指兩個或兩個以上的程式在執行過程中，因爭奪資源而造成的一種互相等待的現象，就是所謂的死迴圈。

　　典型的實驗過程就是兩個事務併發，互相修改自己的一條資料，緊接著又修改對方的鎖定的那條資料，都要等待對方的鎖，死鎖就產生了。

 

　　出現死鎖的問題並不可怕，解決死鎖通常有如下辦法：

　　1.不要把無關的操作放到事務裡，小事務發生衝突的概率較低。
　　2.如果不同的程式會併發存取多個表，應儘量約定以相同的順序來訪問表，這樣事務就會形成定義良好的查詢並且沒有死鎖。
　　3.儘量按照索引去查資料，範圍查詢增加了鎖衝突的可能性。
　　4.對於非常容易產生死鎖的業務部分，可以嘗試升級鎖粒度，通過表鎖定來減少死鎖產生的概率。

鎖監控

表鎖監控

　　獲取表鎖爭用情況：

    SHOW STATUS LIKE "table%"

　　查了很多資料，確實是這個獲取方法，但是我自己沒測出來它的用處，試了兩臺資料庫都不行，很奇怪。

 

　　查詢哪些表正在被鎖定：

    SHOW OPEN TABLES WHERE In_use > 0;

　　這個命令監控的是被表鎖鎖住的表，親測如果用行鎖，這個命令是沒有反應的，真的得自己動手實踐才能發現真相。

行鎖監控

　　獲取行鎖爭用情況：

　　SHOW STATUS LIKE "innodb_row_lock%"

　　下面介紹幾張表，可以幫助我們監控當前的事務並分析可能存在的鎖問題。

　　select * from information_schema.innodb_trx;

　　主要欄位如下：

　　trx_id：唯一的事務id號
　　trx_state：當前事務的狀態，lock wait鎖等待狀態，running執行中狀態。
　　trx_started：事務開始時間
　　trx_wait_started：事務開始等待時間
　　trx_mysql_thread_id：執行緒id
　　trx_query：事務執行的SQL語句

 

　　持有鎖的物件：

　　select * from information_schema.innodb_locks;

　　鎖等待的物件：

　　select * from information_schema.innodb_lock_waits;

解密

為什麼鎖一行資料，速度就變得這麼慢？

　　實驗內容：兩個會話兩個事務，會話1鎖，會話2改，目標是不同的行資料。

　　會話1的where條件必須是索引，才能鎖住這一行，否則就會鎖住整張表的資料，讓會話2上不了鎖。

　　會話2的where條件也必須是索引，才能鎖住這一行，否則會試圖去鎖整張表的資料，而整張表的資料已經有一行被會話1鎖了，所以會話2鎖不上。

為什麼我要鎖一行，MySQL給我鎖全表？

　　即使在條件中使用了索引，但是是否使用索引來檢索資料是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB也會對全表記錄上鎖（申明一點，行鎖不會升級成表鎖，它實際上是把所有行都上了鎖）。

事務中混合使用儲存引擎會怎樣？

　　MySQL的服務層不管理事務，事務是由下層的儲存引擎實現的（表鎖是由MySQL的服務層實現的），所以在同一個事務中，使用多種儲存引擎的表是有風險的。

　　比如在事務中同時操作innodb和myisam的表，正常提交不會有問題,但是如果要回滾，myisam的表是不會被回滾的。

　　因此，在一個事務中，最好不要使用不同儲存引擎的表。

先開事務再鎖表？還是鎖了表再開事務？

　　答案是先開事務再鎖表，因為START TRANSACTION語句會隱含了UNLOCK TABLES，一開事務就等於釋放了之前的表鎖。

我就是開一個事務執行SQL,算不算上鎖？

　　InnoDB採用的是兩階段鎖定協議。

　　在事務執行過程中，隨時都可以執行鎖定，鎖只有在commit或者rollback的時候才會釋放（這裡說的是行鎖哈^_^，表鎖是不在儲存引擎這層的），並且所有的鎖是在同一時刻釋放。

　　innodb會根據隔離級別在需要的時候自動加鎖，優先走隔離級別的規則，然後才是行鎖，如果資料確實隔離了，那麼是不會上鎖的（不信小夥伴們可以親測，開事務改資料會自動上鎖，但是開事務查資料不會上鎖）。

　　顯式加鎖語句是LOCK IN SHARE MODE 和 FOR UPDATE了。

（隔離級別的內容請往這裡跳：https://www.cnblogs.com/fengyumeng/p/9852735.html）

怎麼測試它到底有沒有上鎖呢？

　　兩種辦法：

　　第一種，在事務中使用顯式加鎖語句，不在事務中使用你是感覺不到它上了鎖的。

　　第二種，關閉自動提交模式 

　　SET autocommit=0

　　關閉之後就可以不開事務直接顯式上鎖，直到你執行commit或者rollback它才會釋放鎖。

　　這其實就證明了一個很多人都不知道的事情：每一條SQL都是一個事務。只不過都是自動提交的，所以人們感覺不到事務的存在而已，當關閉了自動提交後，就必須手動提交事務才可以讓SQL生效。

　　查詢自動提交是否開啟：

　　SHOW VARIABLES LIKE "autocommit"

（這裡有一個我還沒弄明白的問題：我只能確定每個更新語句是開了事務的，但我不知道每一個查詢是不是開了事務，沒辦法去證明，也沒想出來該怎麼去證明，有知道的小夥伴可以交流一下哦）