MySQL如何解決幻讀？

目錄

一、什么是幻讀？

1.1 幻讀的定義

1.2 幻讀的示例

1.3 幻讀產生的原因？

1.4?讀已提交（Read Committed）

1.4.1 確定事務等級

1.4.2 非鎖定讀取

準備

示例

結論

1.4.3 鎖定讀取

準備

示例

分析

結論

1.5?可重復讀（Repeatable Read）

1.5.1 確定事務等級

1.5.2 非鎖定讀取

準備

示例

結論

1.5.3 鎖定讀取

準備

示例

分析

結論

二、怎么解決幻讀？

2.1 提高事務隔離等級

2.2 間隙鎖（Gap Locking）

三、間隙鎖

3.1 定義

3.2 工作原理

無間隙鎖

有間隙鎖

3.3?間隙鎖的類型

范圍查詢

相等查詢

參考資料

一、什么是幻讀？

1.1 幻讀的定義

**幻讀（Phantom Read）**是數據庫事務中的一種并發問題，指的是在一個事務執行過程中，另一個事務插入、刪除或修改了符合查詢條件的記錄，導致前一個事務讀取到不同的數據集，從而產生“幻覺”般的讀操作。

1.2 幻讀的示例

具體來說，事務A執行過程中，事務B插入或者修改了記錄，導致事務A在相同查詢條件下讀取到的結果發生了改變。

假設有用戶表users，包含列username、password、state。

1.事務A，查詢所有狀態啟用的用戶

select count(id) from users where state=1;

2.事務B，插入新用戶，state=1

insert into users(username, password, state) values('test11', 123456, 1);

3.事務A，再次執行相同的sql

select count(id)?from users where state=1;

事務A兩次查詢，得到了不同的結果。

1.3 幻讀產生的原因？

1.對表進行了插入或刪除

2.事務隔離等級

**幻讀（Phantom Read）**通常在較低的事務隔離等級中產生（如 讀已提交（READ COMMITTED）、可重復讀（REPEATABLE READ））。

讀已提交（Read Committed）：可以讀取到已提交的記錄，但無法保證查詢過程中數據不被改變，因此會發生幻讀；

可****重復讀（Repeatable Read）：可以保證事務中多次查詢的結果一致，但仍然可能出現幻讀，因為在非鎖定讀取時，允許新記錄的插入（即事務 A 查詢的范圍可能會變化）。

兩種隔離等級下，產生幻讀的情況看下文具體示例。

1.4****讀已提交（Read Committed）

1.4.1 確定事務等級

查看當前事務隔離等級

 SELECT @@transaction_isolation;

將事務隔離等級設為【讀已提交】

#設置事務隔離等級

SET SESSION TRANSACTION ISOLATION LEVEL {level};

#{level}可選項:：

#讀未提交: READ UNCOMMITTED·

#讀已提交: READ COMMITTED

#可重復讀: REPEATABLE READ

#可串行化: SERIALIZABLE

1.4.2 非鎖定讀取
準備

準備一組數據，進行以下測試：事務A執行查詢，事務B插入一條記錄，事務A再次執行查詢。

示例

1.事務A執行查詢

select * from tmp where id>=2;

2.事務B插入數據，并提交

insert into `tmp`(`id`,`value`) values (4, 'dd');

3.事務A再次執行查詢

結論

事務A兩次查詢結果不一致，可以讀取到事務B已提交的記錄，出現【幻讀】

1.4.3 鎖定讀取
準備

準備一組數據，進行以下測試：事務A執行查詢，事務B插入一條記錄，事務A再次執行查詢。

示例

1.事務A進行鎖定讀取

select * from tmp where id >= 4 for update;

2.事務B，在間隙插入數據，并提交

insert into `tmp`(`id`,`value`) values (5, 'dd');

3.事務A再次執行查詢，兩次讀取結果不一致，發現間隙并沒有鎖住

并且事務B插入的行已經落庫。

分析

然后我們看MySQL文檔解釋

意思就是，在【讀已提交】隔離等級下，針對鎖定讀取（共享鎖or排它鎖）、UPDATE語句和DELETE語句，innodb只鎖定索引到的記錄，鎖定記錄的前后都能插入新記錄。也就說“間隙”并沒有被鎖住。

再看【間隙鎖】這邊的解釋，間隙鎖在讀已提交隔離等級下，在查詢和索引掃描被禁用了。

結論

雖然使用了鎖定讀取，但讀已提交（Read Committed）下，幻讀依然存在。

1.5可重復讀（Repeatable Read）

1.5.1 確定事務等級

查看當前事務隔離等級

?SELECT @@transaction_isolation;

1.5.2 非鎖定讀取
準備

準備一組數據，進行以下測試：事務A執行查詢，事務B插入一條記錄，事務A再次執行查詢。

示例

1.事務A執行查詢

select * from tmp where id>=4;

2.事務B插入一條記錄, 并commit

insert into `tmp`( `id`, `value` ) values (5, 'dd');

3.事務A再次執行查詢

select * from tmp where id>=4;

事務B執行commit后，由于事務隔離等級緣故，事務A再次查詢，結果不影響，具備可重復讀特性；事務A結束后，再次查詢多了一條數據（5, ‘dd’）。

結論

在可重復讀（Repeatable Read）隔離等級下，事務內部具備可重復讀特性； 但由于“間隙”并未鎖住，非鎖定讀取會出現“幻讀”。

1.5.3 鎖定讀取
準備

準備一組數據，進行以下測試：事務A執行查詢，事務B插入一條記錄，事務A再次執行查詢。

示例

事務A執行查詢

select * from tmp where id>=4 for update;

事務B插入一條記錄

insert into `tmp`( `id`, `value` ) values (5, 'dd');

這時，發現插入操作被阻塞了。

分析

我們看一下執行的查詢語句

select * from tmp where id>=4 for update;

  1. Id=4所在的行
  2. Id=4與 id=6之間的間隙
  3. Id=6所在的行
  4. Id=6之后的間隙

這就意味著，在間隙插入記錄會被阻止，直到事務A結束。

共享鎖與示例的排它鎖相同，都是通過阻止其他會話變更，來避免對當前會話的影響。

UPDATE語句、DELETE語句與鎖定讀取（Locking Reads）同理，在執行時，會根據使用的查詢條件進行加鎖：

  1. 相等（=、in）：InnoDB只鎖定找到的索引記錄
  2. 范圍（BETWEEN、>、<）：InnoDB鎖定掃描的索引范圍

要注意的是，對于使用唯一索引的語句，完全沒必要進行加鎖。

注：

我們知道，默認情況下，MySQL是在autocommit開啟的狀態下運行。開啟事務（START TRANSACTION）后，自動提交模式(autocommit)會隱式禁用，所有的純SELECT語句會被轉化成SELECT…LOCK IN SHARE MODE, 但是如果想要鎖住間隙或是鎖住索引記錄的話，需要顯示觸發鎖定行為。

結論

在可重復讀（Repeatable Read）隔離等級下，針對鎖定讀取（共享鎖or排它鎖）、UPDATE語句和DELETE語句，不會出現“幻讀”。

二、怎么解決幻讀？

2.1 提高事務隔離等級

使用可串行化（Serializable），提事務隔離等級來避免。在可串行化隔離等級下,數據庫會對查詢和寫進行加鎖，確保事務的完全順序執行，但是并發效率低下。

2.2 間隙鎖（G****ap Locking）

間隙鎖是加在索引記錄之間間隙的鎖，又或者是在索引區間第一條記錄之前、或最后一條記錄之后。

示例：

select * from users where age between 10 and 30FOR UPDATE;

age（10-30）范圍內所有現有值之間的間隙都被鎖定，防止其他事務將值等于15插入列age中，無論該列中是否已有該值。

三、間隙鎖

3.1 定義

**間隙鎖（Gap Lock）**是一種數據庫鎖定機制，常見于支持行級鎖的數據庫（如 MySQL 的 InnoDB 存儲引擎）中，主要用于解決并發事務中的“幻讀”問題。

3.2 工作原理

假定有數據表

±—±------+

| id | title |

±—±------+

| 1 | aa |

| 2 | bb|

| 3 | cc|

±—±------+

無間隙鎖

事務A查詢id > 2 的記錄并鎖定這條記錄，然后事務B插入一條id=4的新紀錄，事務A再次查詢時會得到不同的結果集。

有間隙鎖

事務A執行查詢 id > 2時，這條查詢會鎖定 id > 2 的記錄、所有行之間的間隙。

3.3****間隙鎖的類型

范圍查詢

當事務執行范圍查詢（如BETWEEN、>、<等）時，數據庫會在查詢范圍內加上間隙鎖。

例如：

select * from `tmp` where `id` > 5 for update;

可重復讀（Repeatable Read）隔離等級下，可能會鎖定 id >5 的所有間隙.

相等查詢

當事務執行相等查詢（如 = ）時，數據庫會鎖定對應的索引記錄。如果索引記錄不存在，則會鎖定對應的間隙，防止其他事務插入重復的記錄。

示例：

±—±------+

| id | value |

±—±------+

| 2 | aa |

| 4 | bb |

| 6 | cc |

±—±------+

事務A, 執行查詢, 鎖住id=5的間隙

select * from tmp where id=5 for update;

事務B, 嘗試在id=5插入數據

insert into `tmp`( `id`, `value` ) values ( 5, 'bb' );

發現事務B，在事務A未結束的情況下，一直被阻塞直至超時

參考資料

MySQL數據庫事務隔離等級：The InnoDB Engine: Transaction Isolation Levels

InnoDB一致性非鎖定讀取：The InnoDB Engine | Consistent Nonlocking Reads

InnoDB幻影行（幻讀）：The InnoDB Engine | Phantom Rows

InnoDB間隙鎖：The InnoDB Engine | Gap locking

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/895992.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/895992.shtml
英文地址，請注明出處：http://en.pswp.cn/news/895992.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

Openssl之SM2加解密命令

Openssl之SM2加解密命令

### 1. 生成 SM2 私鑰openssl genpkey -algorithm EC \-pkeyopt ec_paramgen_curve:sm2 \-out sm2_private_key.pem### 2. 從私鑰導出 SM2 公鑰openssl pkey -in sm2_private_key.pem \-pubout \-out sm2_public_key.pem### 3. 使用 SM2 公鑰加密openssl pkeyutl -encrypt \-pu…
閱讀更多...
【含文檔+PPT+源碼】基于Python的圖書推薦系統的設計與實現

【含文檔+PPT+源碼】基于Python的圖書推薦系統的設計與實現

課程簡介&#xff1a; 本課程演示的是一款基于python的圖書推薦系統的設計與實現&#xff0c;主要針對計算機相關專業的正在做畢設的學生與需要項目實戰練習的 Python學習者。 1.包含&#xff1a;項目源碼、項目文檔、數據庫腳本、軟件工具等所有資料 2.帶你從零開始部署運行…
閱讀更多...
Nginx 安裝及配置教程（Windows）【安裝】

Nginx 安裝及配置教程（Windows）【安裝】

文章目錄 一、 Nginx 下載 1. 官網下載2. 其它渠道 二、 Nginx 安裝三、 配置四、 驗證五、 其它問題 1. 常用命令2. 跨域問題 軟件 / 環境安裝及配置目錄 一、 Nginx 下載 1. 官網下載 安裝地址&#xff1a;https://nginx.org/en/download.html 打開瀏覽器輸入網址 htt…
閱讀更多...
Spring Boot（8）深入理解 @Autowired 注解：使用場景與實戰示例

Spring Boot（8）深入理解 @Autowired 注解：使用場景與實戰示例

搞個引言 在 Spring 框架的開發中&#xff0c;依賴注入&#xff08;Dependency Injection&#xff0c;簡稱 DI&#xff09;是它的一個核心特性&#xff0c;它能夠讓代碼更加模塊化、可測試&#xff0c;并且易于維護。而 Autowired 注解作為 Spring 實現依賴注入的關鍵工具&…
閱讀更多...
DeepSeek教unity------Dotween

DeepSeek教unity------Dotween

1、命名法 Tweener&#xff08;補間器&#xff09;&#xff1a;一種控制某個值并對其進行動畫處理的補間。 Sequence&#xff08;序列&#xff09;&#xff1a;一種特殊的補間&#xff0c;它不直接控制某個值&#xff0c;而是控制其他補間并將它們作為一個組進行動畫處理。 Tw…
閱讀更多...
【JAVA實戰】JAVA實現Excel模板下載并填充模板下拉選項數據

【JAVA實戰】JAVA實現Excel模板下載并填充模板下拉選項數據

背景 有這樣一個場景&#xff1a;前端下載Excel模板&#xff0c;進行數據導入&#xff0c;這個下載模板過程需要經過后端接口去數據庫查詢數據進行某些列的下拉數據填充&#xff0c;下拉填充的數據過程中會出現錯誤String literals in formulas can’t be bigger than 255 cha…
閱讀更多...
【深度學習】計算機視覺（CV）-目標檢測-DETR（DEtection TRansformer）—— 基于 Transformer 的端到端目標檢測

【深度學習】計算機視覺（CV）-目標檢測-DETR（DEtection TRansformer）—— 基于 Transformer 的端到端目標檢測

1.什么是 DETR&#xff1f; DETR&#xff08;DEtection TRansformer&#xff09; 是 Facebook AI&#xff08;FAIR&#xff09;于 2020 年提出的 端到端目標檢測算法&#xff0c;它基于 Transformer 架構&#xff0c;消除了 Faster R-CNN、YOLO 等方法中的 候選框&#xff08;…
閱讀更多...
IDEA + 通義靈碼AI程序員：快速構建DDD后端工程模板

IDEA + 通義靈碼AI程序員：快速構建DDD后端工程模板

作者&#xff1a;陳榮健 IDEA 通義靈碼AI程序員&#xff1a;快速構建DDD后端工程模板 在軟件開發過程中&#xff0c;一個清晰、可維護、可擴展的架構至關重要。領域驅動設計 (DDD) 是一種軟件開發方法&#xff0c;它強調將軟件模型與業務領域緊密結合&#xff0c;從而構建更…
閱讀更多...
責任鏈模式原理詳解和源碼實例以及Spring AOP攔截器鏈的執行源碼如何使用責任鏈模式？

責任鏈模式原理詳解和源碼實例以及Spring AOP攔截器鏈的執行源碼如何使用責任鏈模式？

前言 本文首先介紹了責任鏈的基本原理&#xff0c;并附帶一個例子說明責任鏈模式&#xff0c;確保能夠理解責任鏈的前提下&#xff0c;在進行Spring AOP執行責任鏈的源碼分析。責任鏈模式允許將多個處理對象連接成鏈&#xff0c;請求沿著鏈傳遞&#xff0c;直到被處理或結束。每…
閱讀更多...
React 與 Vue 對比指南 - 上

React 與 Vue 對比指南 - 上

React 與 Vue 對比指南 - 上 本文將展示如何在 React 和 Vue 中實現常見功能&#xff0c;從基礎渲染到高級狀態管理 Hello 分別使用 react 和 vue 寫一個 Hello World&#xff01; react export default () > {return <div>Hello World!</div>; }vue <…
閱讀更多...
大模型開發實戰篇7：語音識別-語音轉文字

大模型開發實戰篇7：語音識別-語音轉文字

語音識別大模型&#xff0c;是人工智能領域的一項重要技術&#xff0c;它能夠將人類的語音轉換為文本。近年來&#xff0c;隨著深度學習技術的不斷發展&#xff0c;語音識別大模型取得了顯著的進展&#xff0c;并在各個領域得到了廣泛應用。 主流語音識別大模型 目前&#xf…
閱讀更多...
向量的點乘的幾何意義

向量的點乘的幾何意義

源自AI 向量的點乘&#xff08;Dot Product&#xff09;在幾何和圖形學中有重要的意義。它不僅是數學運算&#xff0c;還可以用來描述向量之間的關系。以下是點乘的幾何意義及其應用&#xff1a; 1. 點乘的定義 對于兩個向量 a 和 b&#xff0c;它們的點乘定義為&#xff1a;…
閱讀更多...
國產芯片汽車氣壓表pcba方案

國產芯片汽車氣壓表pcba方案

汽車氣壓表的基本原理是利用氣壓傳感器將氣體氣壓轉換為電信號&#xff0c;再通過電子芯片電路進行處理傳輸&#xff0c;再將這些信息轉發給顯示屏顯示。常見的傳感器包括模擬氣壓傳感器和數字氣壓傳感器。其中&#xff0c;模擬氣壓傳感器是目前應用最廣泛的傳感器之一&#xf…
閱讀更多...
解鎖機器學習核心算法 | K -近鄰算法：機器學習的神奇鑰匙

解鎖機器學習核心算法 | K -近鄰算法：機器學習的神奇鑰匙

一、引言 今天我們繼續學習機器學習核心算法 —— K - 近鄰&#xff08;K-Nearest Neighbors&#xff0c;簡稱 KNN&#xff09;算法。它就像是一位經驗豐富的 “老江湖”&#xff0c;以其簡單而又強大的方式&#xff0c;在眾多機器學習任務中占據著不可或缺的地位。 K - 近鄰…
閱讀更多...
如何在Windows 10操作系統中安裝并配置PHP集成軟件XAMPP

如何在Windows 10操作系統中安裝并配置PHP集成軟件XAMPP

步驟1&#xff1a;下載XAMPP安裝包 訪問XAMPP官網&#xff1a; 打開瀏覽器&#xff0c;進入XAMPP官方網站&#xff1a;https://www.apachefriends.org/index.html 選擇XAMPP版本&#xff1a; 在XAMPP的下載頁面上&#xff0c;選擇適合Windows的最新穩定版本下載&#xff08;例…
閱讀更多...
【DeepSeek】本地部署，保姆級教程

【DeepSeek】本地部署，保姆級教程

deepseek網站鏈接傳送門&#xff1a;DeepSeek 在這里主要介紹DeepSeek的兩種部署方法&#xff0c;一種是調用API&#xff0c;一種是本地部署。 一、API調用 1.進入網址Cherry Studio - 全能的AI助手選擇立即下載 2.安裝時位置建議放在其他盤&#xff0c;不要放c盤 3.進入軟件后…
閱讀更多...
Python 入門教程（2）搭建環境 | 2.3、VSCode配置Python開發環境

Python 入門教程（2）搭建環境 | 2.3、VSCode配置Python開發環境

文章目錄 一、VSCode配置Python開發環境1、軟件安裝2、安裝Python插件3、配置Python環境4、包管理5、調試程序 前言 Visual Studio Code&#xff08;簡稱VSCode&#xff09;以其強大的功能和靈活的擴展性&#xff0c;成為了許多開發者的首選。本文將詳細介紹如何在VSCode中配置…
閱讀更多...
Oracle EBS 12.1和APEX 集成時 Apache的配置代理

Oracle EBS 12.1和APEX 集成時 Apache的配置代理

在有些場景下&#xff0c;apex的前端服務不是和oracle EBS 應用部署在同一個服務器上或者要求apex和訪問地址和EBS公用同一個域名同一個端口&#xff0c;那么怎么才能做到用EBS 的域名和端口來實現對apex的訪問呢 通過配置代理規則解決&#xff0c;以Oracle EBS 12.1.3 為例&am…
閱讀更多...
【第二節】C++設計模式(創建型模式)-抽象工廠模式

【第二節】C++設計模式(創建型模式)-抽象工廠模式

目錄 引言 一、抽象工廠模式概述 二、抽象工廠模式的應用 三、抽象工廠模式的適用場景 四、抽象工廠模式的優缺點 五、總結 引言 抽象工廠設計模式是一種創建型設計模式&#xff0c;旨在解決一系列相互依賴對象的創建問題。它與工廠方法模式密切相關&#xff0c;但在應用…
閱讀更多...
ubuntu20.04重啟后不顯示共享文件夾

ubuntu20.04重啟后不顯示共享文件夾

ubuntu20.04重啟后不顯示共享文件夾 主要參見這兩篇博客 Ubuntu重啟后不顯示共享文件夾_ubuntu 20.04 共享目錄無法使用-CSDN博客 ubuntu22.04 配置共享文件夾 找不到/mnt/hgfs_ubuntu安裝tools 后mnt文件夾在哪-CSDN博客 重啟Ubuntu20.04后&#xff0c;發現共享文件夾進不去…
閱讀更多...
最新文章

Copyright @ 2022~2023