MySQL鎖總結與詳解

本文主要給大家介紹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.linuxidc.com/Linux/2018-11/155273.htm）

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

　　兩種辦法：

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

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

　　SET autocommit=0

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

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

　　查詢自動提交是否開啟：

　　SHOW VARIABLES LIKE "autocommit"

看了以上關于MySQL鎖總結與詳解，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。