InnoDB 中不同SQL語句設置的鎖

来源:https://www.cnblogs.com/cjsblog/archive/2022/08/03/16544690.html
-Advertisement-
Play Games

鎖定讀、UPDATE 或 DELETE 通常會給在SQL語句處理過程掃描到的每個索引記錄上設置記錄鎖。語句中是否存在排除該行的WHERE條件並不重要。InnoDB不記得確切的WHERE條件,但只知道哪些索引範圍被掃描了。鎖通常是next-key鎖,它也阻止插入到緊挨著記錄之前的“間隙”中。然而,間隙 ...


鎖定讀、UPDATE 或 DELETE 通常會給在SQL語句處理過程掃描到的每個索引記錄上設置記錄鎖。語句中是否存在排除該行的WHERE條件並不重要。InnoDB不記得確切的WHERE條件,但只知道哪些索引範圍被掃描了。鎖通常是next-key鎖,它也阻止插入到緊挨著記錄之前的“間隙”中。然而,間隙鎖定可以顯式禁用,這會導致next-key鎖無法使用。事務隔離級別也會影響到鎖的設置。

如果在搜索中使用了二級索引,並且要設置的索引記錄鎖是互斥的,InnoDB也會檢索相應的聚集索引記錄並對它們設置鎖。

如果沒有適合語句的索引,MySQL必須掃描整個表來處理該語句,那麼表的每一行都將被鎖定,從而阻止其他用戶對錶的所有插入。創建良好的索引非常重要,這樣查詢就不會掃描不必要的行。

InnoDB設置的鎖的具體類型如下:

1、SELECT ... FROM 是一個一致讀,讀取資料庫的快照並且不設置鎖,除非將事務隔離級別設置為SERIALIZABLE。

2、唯一索引的 SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 語句為掃描的行獲取鎖,並釋放不符合包含在結果集中條件的行(例如,如果它們不滿足WHERE子句中給定的條件)的鎖。然而,在某些情況下,行可能不會立即被解鎖,因為在查詢執行期間,結果行與其原始源之間的關係丟失了。例如,在UNION中,從表中掃描(和鎖定)的行可能會在評估它們是否符合結果集之前插入臨時表。在這種情況下,臨時表中的行與原始表中的行之間的關係將丟失,而後者的行直到查詢執行結束才被解鎖。

3、對於鎖定讀(帶有 FOR UPDATE 或 FOR SHARE 的 SELECT)、UPDATE 和 DELETE 語句,所採用的鎖取決於語句是使用具有唯一搜索條件的唯一索引,還是使用範圍類型的搜索條件。

  • 對於具有唯一搜索條件的唯一索引,InnoDB 只鎖定找到的索引記錄,而不鎖定它之前的間隙。
  • 對於其他搜索條件和非唯一索引,InnoDB鎖定所掃描的索引範圍,使用間隙鎖或next-key鎖阻止其他會話對該範圍覆蓋的間隙進行插入。

4、對於搜索遇到的索引記錄,SELECT ... FOR UPDATE 阻止其他會話執行 SELECT ... FOR SHARE 或從某些事務隔離級別讀取。一致讀忽略在讀視圖中存在的記錄上設置的任何鎖。

5、UPDATE ... WHERE ... 在搜索遇到的每條記錄上設置一個排它的next-key鎖。但是,對於使用唯一索引來搜索唯一行的語句,只需要一個索引記錄鎖。

6、DELETE FROM ... WHERE ... 在搜索遇到的每條記錄上設置一個排它的next-key鎖。但是,對於使用唯一索引來搜索唯一行的語句,只需要一個索引記錄鎖。

7、當 UPDATE 修改聚集索引記錄時,將對受影響的二級索引記錄進行隱式鎖定。在插入新的二級索引記錄之前執行重覆檢查掃描,以及在插入新的二級索引記錄時,UPDATE操作還會在受影響的二級索引記錄上使用共用鎖。

8、INSERT 在插入的行上設置排它鎖。這個鎖是索引記錄鎖,不是next-key鎖(即沒有間隙鎖),並且不會阻止其他會話插入到插入行之前的間隙中。

9、在插入行之前,設置了一種稱為插入意向間隙鎖的間隙鎖。該鎖表示要以這樣一種方式插入,即插入到同一索引間隙的多個事務如果不在間隙內的同一位置插入,則不需要相互等待。假設有值為4和7的索引記錄。嘗試插入值5和6的獨立事務在獲得插入行上的排它鎖之前,每個事務都用插入意向鎖鎖定4和7之間的間隙,但不會阻塞彼此,因為行是不衝突的。

10、INSERT ... ON DUPLICATE KEY UPDATE 與簡單的 INSERT 不同之處在於,當發生重覆鍵錯誤時,將在要更新的行上放置排他鎖而不是共用鎖。對重覆的主鍵值採用排它索引記錄鎖。對重覆的唯一鍵值採用排它的next-key鎖。

11、INSERT INTO T SELECT ... FROM S WHERE ... 在插入到 T 的每一行上設置一個排它索引記錄鎖(沒有間隙鎖)。如果事務隔離級別為 READ COMMITTED,InnoDB 將 S 上的搜索作為一致 讀(無鎖)。 否則,InnoDB 在 S 的行上設置共用的 next-key 鎖。

12、InnoDB在初始化表上先前指定的AUTO_INCREMENT列時,會在與AUTO_INCREMENT列相關的索引的末尾設置一個排它鎖。

13、如果在表上定義了一個FOREIGN KEY約束,那麼任何需要檢查約束條件的插入、更新或刪除都會在它查看的記錄上設置共用記錄級別的鎖,以檢查約束。InnoDB會在約束失敗的情況下也設置這些鎖。

14、LOCK TABLES設置表鎖,但是設置這些鎖的是InnoDB層之上的MySQL層。如果innodb_table_locks = 1(預設值)和autocommit = 0, InnoDB就知道表鎖,而且InnoDB上面的MySQL層知道行級鎖。

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

 

給表現加一個意向排它鎖,然後給行id=4的行加排它的記錄鎖,以及正無窮

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 一:背景 1. 講故事 前段時間遇到了好幾起關於窗體程式的 進程載入鎖 引發的 程式卡死 和 線程暴漲 問題,這種 dump 分析難度較大,主要涉及到 Windows操作系統 和 C++ 的基礎知識,所以有必要簡單整理和大家分享一下,上 windbg 說話。 二:WinDbg 分析 1. 主線程此時 ...
  • 經過長時間的規劃和編碼,終於出來了.NET 版本的微服務框架,而且框架的使用簡單,無入侵集成,可幫助任意項目輕鬆向大併發架構演進。 ...
  • 前言 接著上周寫的截圖控制項繼續更新添加 文字。 1.WPF實現截屏「仿微信」 2.WPF 實現截屏控制項之移動(二)「仿微信」 3.WPF 截圖控制項之伸縮(三) 「仿微信」 4.WPF 截圖控制項之繪製方框與橢圓(四) 「仿微信」 5.WPF 截圖控制項之繪製箭頭(五)「仿微信」 6.WPF 截圖控制項之繪 ...
  • 為什麼要用ssh密鑰登錄 購買的伺服器設置密碼很容易被暴力破解,用密鑰登錄安全很多。root用戶新建的用戶也要用密鑰登錄更安全,如果一直su - 用戶名登錄 不方便 用xftp等服務上傳文件到用戶使用的服務下,歸屬人是root,還要chown改許可權才能使用。 為其他用戶創建ssh密鑰的步驟 # 密鑰 ...
  • Naarak Studio DirEqua mac版是Mac的高級目錄比較實用程式。它可以檢測文件夾之間最小的變化,並以清晰直觀的方式顯示結果。使用顏色和圖標突出顯示差異類型(大小,日期或項目內容),以突出顯示差異。DirEqual Mac版將比較的目錄併排顯示為可擴展樹,併為每個項目指示大小和日期 ...
  • sudo:superuser do,實現普通用戶執行root命令的授權工具。 一般用戶管理系統的方式是利用su切換為超級用戶。但是使用su的缺點之一在於必須要先告知超級用戶的密碼。 sudo使一般用戶不需要知道超級用戶的密碼即可獲得許可權 #過程: (1)超級用戶授權:首先 超級用戶 將普通用戶的名字 ...
  • 今天是接觸C++的第二天,學習了基礎內容之後用了兩個多小時的時間完成了一個通訊錄管理程式,功能相對簡單,代碼也不複雜,歡迎各位大佬指出不足之處 點擊查看代碼 #include<iostream> #include<string> #include<regex> using namespace std ...
  • lamp 1. lamp簡介 lamp,其實就是由Linux+Apache+Mysql/MariaDB+Php/Perl/Python的一組動態網站或者伺服器的開源軟體 LAMP指的是Linux(操作系統)、Apache(HTTP伺服器)、MySQL(也指MariaDB,資料庫軟體)和PHP(有時也 ...
一周排行
    -Advertisement-
    Play Games
  • 使用原因: 在我們服務端調用第三方介面時,如:支付寶,微信支付,我們服務端需要模擬http請求並加上一些自己的邏輯響應給前端最終達到我們想要的效果 1.使用WebClient 引用命名空間 using System.Net; using System.Collections.Specialized; ...
  • WPF 實現帶蒙版的 MessageBox 消息提示框 WPF 實現帶蒙版的 MessageBox 消息提示框 作者:WPFDevelopersOrg 原文鏈接: https://github.com/WPFDevelopersOrg/WPFDevelopers.Minimal 框架使用大於等於.N ...
  • 一、JSON(JavaScript Object Notation)的簡介: ① JSON和XML類似,主要用於存儲和傳輸文本信息,但是和XML相比,JSON更小、更快、更易解析、更易編寫與閱讀。 ② C、Python、C++、Java、PHP、Go等編程語言都支持JSON。 二、JSON語法規則: ...
  • 1.避免Scoped模式註冊的服務變成Singleton模式 當提供一個生命周期模式為Singleton的服務實例時,如果發現該服務中還依賴生命周期模式為Scoped的服務實例(Scoped服務實例將被一個Singleton服務實例所引用),那麼這個被依賴的Scoped服務實例最終會成為一個Sing ...
  • 索引時資料庫提高數據查詢處理性能的一個非常關鍵的技術,索引的使用可以對性能產生上百倍甚至上千倍的影響。接下來,會介紹索引的基本原理、概念,並深入學習資料庫中所使用的索引結構和存儲方式,以及如何管理、維護索引等。 1.索引的基本概念 索引時用來快速查詢表記錄的一種存儲結構,一般使用索引有一下兩個方面: ...
  • django2 路由控制器 Route路由,是一種映射關係。路由是把客戶端請求的url路徑和用戶請求的應用程式,這裡意指django裡面的視圖進行綁定映射的一種關係。 請求路徑和視圖函數不是一一對應的關係 在django中所有的路由最終都被保存到一個叫urlpatterns的文件里,並且該文件必須在 ...
  • 1、我們的目標是獲取微博某博主的全部圖片、視頻 2、拿到網址後 我們先觀察 打開F12 隨著下滑我們發現載入出來了一個叫mymblog的東西,展開響應發現需要的東西就在裡面 3、重點來了!!! 通過觀察發現第二頁比第一頁多了參數since_id 而第二頁的since_id參數剛好在上一頁中能獲取到, ...
  • 一、實現原理 在Servlet3協議規範中,包含在JAR文件/META-INFO/resources/路徑下的資源可以直接訪問。 二、舉例說明 如下圖所示,是我新建的一個Spring Boot Starter項目:zimug-minitor-threadpool,用於實現可配置、可觀測的線程池。其中 ...
  • 精華筆記: static final常量:應用率高 必須聲明同時初始化 由類名打點來訪問,不能被改變 建議:常量所有字母都大寫,多個單詞用_分隔 編譯器在編譯時會將常量直接替換為具體的數,效率高 何時用:數據永遠不變,並且經常使用 抽象方法: 由abstract修飾 只有方法的定義,沒有具體的實現( ...
  • Python有一個for...else語法,它的寫法如下 for i in range(0,100): if i == 3: break else: print("Not found") 該語句表示:若for迴圈遍歷完畢,則執行else部分的語句。也就是說上述代碼不會有任何輸出,而下述代碼會輸出“N ...