MySQL讀寫問題(鎖)

• 2022 年 2 月 1 日
• 筆記
• MySQL

一.概述

• 讀-讀：並發不存在問題,不需要加鎖
• 寫-寫：並發存在問題,可能會造成臟寫(一個事務沒有寫完,另一個事務也對相同的數據進行寫),但是這種情況,任何一種隔離級別都不允許發生,在隔離級別的時候就解決了。
• 讀-寫/寫-讀：會造成臟讀,幻讀,不可重複讀的問題。每個數據廠商對它的支持也是不相同的
  • 解決方案：
    • 方案一：讀操作利用MVCC,寫進行加鎖。
    • 方案二：讀寫都加鎖。

怎麼加鎖：數據庫自己就進行加鎖，不需要手動加鎖。除非想演示效果，就自己開事務自己加鎖

• 表鎖：
  • lock tables 表名 read      //表級讀鎖
  • lock tables 表名 write     //表級寫鎖
  • unlock //釋放鎖
• 行鎖：
  • SQL語句 for share         //行級讀鎖
  • SQL語句 for update       //行級寫鎖
  • commit //釋放鎖

二.鎖

基於鎖的屬性分類:

• 共享鎖(S):讀鎖,可以並發的讀數據
• 排他鎖(X):寫鎖,獨佔鎖

基於鎖的粒度分類：(MyISAM使用表鎖，innodb一般使用行鎖)

• 表級鎖
  • 表級讀鎖：在執行select 之前，會給涉及的所有表加讀鎖。
  • 表級寫鎖：在執行增刪改之前，會給涉及的所有表加寫鎖。
  • 意向鎖：就是一個標誌，某一行加上鎖之後，就給整個表加一個意向鎖，有新的鎖加入表鎖 ，就需要等待獲得意向鎖。
  • 自增鎖：就是表中的主鍵自增(auto-increment)也需要加鎖。
  • 元數據鎖(MDL)：就是對錶進行DDL的時候，需要進行加鎖。
• 行級鎖
  • 記錄鎖：鎖住每一條記錄，就是行級讀鎖/行級寫鎖。
  • 間隙鎖(Gap)：就是為了解決幻讀而提出的。(幻讀除了使用間隙鎖解決，還可以使用MVCC解決)，間隙鎖還可能造成死鎖。

• • 臨界鎖：是記錄鎖和gap鎖的合體，即鎖住了某條記錄，又阻止在該記錄前插入新紀錄。
  • 插入意向鎖：是一種間隙鎖，專門針對的是數據行的插入操作，多個事務插入相同的索引間隙時，只要不是插入到相同的位置，則不需要進行鎖等待。插入意向鎖鎖定了索引之間的間隙，但是插入意向鎖之間沒有互相阻塞。(間隙鎖讓範圍之間不能插入，插入意向鎖雖然也鎖住了一個範圍，但是不是同一個位置的就可以插入)　
• 頁鎖：鎖着一頁，是介於表鎖和行鎖之間，並發度一般。會出現死鎖。

加鎖方式分類

• 隱式鎖：自動添加的，在insert的時候就會添加一個隱式鎖。比如事務A在insert一條數據後,還沒有提交,此時另一個事務B要修改這條新添加的數據,就不會成功。

• 顯示鎖：就是我們上面學的鎖

其他的鎖

• 死鎖：兩個或者多個事務在同一資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性循環。
  • 導致死鎖的場景：
    • 間隙鎖導致的死鎖：比如兩個事務A,B，都鎖了(3,8)之間的範圍，事務A進行插入數據到(3,8)發生阻塞，同理事務B進行插入數據到(3,8)也發生阻塞。但是它倆都僵持着，互相進行阻塞，就發生死鎖
      
    • 頁鎖導致的死鎖：事務A,B。A先鎖住頁1然後鎖了頁2，B先鎖了頁2然後鎖了頁1。
  • 出現死鎖，有2種策略
    • 方式一：直接進入等待，直到超時。這個超時時間可以通過innodb_lock_wait_timeout來設置。
    • 方式二：死鎖檢測，發現死鎖，主動回滾死鎖鏈中的某一個事務，讓其他事務繼續執行。通過innodb_deadlock_detect設置為on來開啟。
  • 如何避免出現死鎖：(了解)
    • 如果不同的程序並發存取多個表，盡量以相同的順序訪問表。
    • 在程序以批量方式處理數據的時候，如果已經對數據排序，盡量保證每個線程按照固定的順序來處理記錄。
    • 在事務中，如果需要更新記錄，應直接申請足夠級別的排他鎖，而不應該先申請共享鎖，更新時在申請排他鎖，因為在當前用戶申請排他鎖時，其他事務可能已經獲得了相同記錄的共享鎖，從而造成鎖衝突或者死鎖。
    • 盡量使用較低的隔離級別。
    • 盡量使用索引訪問數據，使加鎖更加準確，從而減少鎖衝突的機會。
    • 合理選擇事務的大小，小事務發生鎖衝突的概率更低。
    • 盡量用相等的條件訪問數據，可以避免Next-Key鎖對並發插入的影響。
    • 不要申請超過實際需要的鎖級別，查詢時盡量不要顯示加鎖。
    • 對於一些特定的事務，可以表鎖來提高處理速度或減少死鎖的概率。
• 全局鎖：讓整個數據庫實例都加鎖，不管是DDL還是DML都加鎖。
  • 使用場景：全庫邏輯備份。
  • 命令Flush tables with read lock；開啟

三.MVCC

MVCC:多版本並發控制：多版本：undo鏈實現；控制：readView。主要針對於RC和RR兩種事務。

為了提高數據庫並發性能，用更好的方式去處理讀-寫衝突。讀就是採用快照讀，寫就是採用當前讀(加鎖)。
前提知識：

• 快照讀：不加鎖的非阻塞讀，比如select * from …
• 當前讀：讀取時還要保證其他並發事務不能修改當前記錄，會對讀取的記錄進行加鎖。不僅限於讀數據，修改數據也屬於當前讀。
• 行格式的隱藏列

• undo 版本鏈：每一個數據行的隱藏指針指向自己undo版本鏈，undo版本鏈中應該存儲的之前的逆操作，但是為了好理解，下圖就畫成數據原來的樣子

• ReadView：就是每一個事務在使用MVCC機制進行快照讀操作時產生的讀視圖。每一個事務都會產生一個ReadView。

MVCC的實現依賴於隱藏字段，undo Log，ReadView

說白了，就是事務生成的ReadView與要修改的這條記錄的隱藏列的tri_id進行比較，看是讀取undo鏈中的歷史呢，還是讀取當前最新的數據呢。

MVCC如何解決幻讀？

• A事務先進行讀，然後B事務中途插入一條數據後提交。A事務再次讀，根據MVCC，發現trx_id大於low_limit_id，說明實在readView生成之後，才出現的事務B，所以不能讀取該數據。就不會出現幻讀。

 寄語：生活像一隻蝴蝶，沒有破繭的勇氣，哪來飛舞的美麗