MySQL基礎篇(06)：事務管理，鎖機制案例詳解

来源:https://www.cnblogs.com/cicada-smile/archive/2020/03/18/12520893.html
-Advertisement-
Play Games

本文源碼： "GitHub·點這裡" || "GitEE·點這裡" 一、鎖概念簡介 1、基礎描述 鎖機制核心功能是用來協調多個會話中多線程併發訪問相同資源時，資源的占用問題。鎖機制是一個非常大的模塊，貫徹MySQL的幾大核心難點模塊：索引，鎖機制，事務。這裡是基於MySQL5.6演示的幾種典型場景， ...


本文源碼：GitHub·點這裡 || GitEE·點這裡

一、鎖概念簡介

1、基礎描述

鎖機制核心功能是用來協調多個會話中多線程併發訪問相同資源時，資源的占用問題。鎖機制是一個非常大的模塊，貫徹MySQL的幾大核心難點模塊：索引，鎖機制，事務。這裡是基於MySQL5.6演示的幾種典型場景，對面MySQL這幾塊問題時，有分析流程和思路是比較關鍵的。在MySQL中常見這些鎖概念：共用讀鎖、排它寫鎖 ; 表鎖、行鎖、間隙鎖。

2、存儲引擎和鎖

  • MyISAM引擎：基於讀寫兩種模式，支持表級鎖 ;
  • InnoDB引擎：支持行級別讀寫鎖，跨行的間隙鎖,InnoDB也支持表鎖 ;

3、鎖操作API

  • LOCK TABLE name [READ,WRITE] ;加表鎖
  • UNLOCK TABLES ; 釋放標所

二、MyISAM鎖機制

1、基礎描述

MySQL的表級鎖有兩種模式：共用讀鎖（Read-Lock）和排它寫鎖（Write-Lock）。針對MyISAM表的讀操作，不會阻塞其他線程對同一表的讀請求，但阻塞對同一表的寫請求；針對MyISAM表的寫操作，會阻塞其他線程對同一表的讀和寫操作；MyISAM引擎讀寫操作之間，以及寫與寫操作之間是串列化。當一次會話線程獲取表的寫鎖後，只有當前持有鎖的會話線程可以對錶進行操作。其它線程的讀、寫操作都會等待，直到鎖被釋放為止。

2、驗證案例

基於上面的表鎖機制特點，使用下麵兩個案例驗證。

  • 基礎表結構
CREATE TABLE `dc_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用戶表';
CREATE TABLE `dc_user_info` (
  `user_id` int(11) NOT NULL COMMENT '用戶ID',
  `city` varchar(20) DEFAULT NULL COMMENT '城市',
  `country` varchar(20) DEFAULT NULL COMMENT '國家',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用戶信息表';
  • 共用讀鎖

會話視窗一

-- 1、加讀鎖
LOCK TABLE dc_user READ ;
-- 2、當前會話查詢，OK
SELECT * FROM dc_user ;
-- 4、當前會話寫入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 6、查詢其他表,Error
SELECT * FROM dc_user_info ;
-- 7、釋放鎖
UNLOCK TABLES ;

會話視窗二

-- 3、其他會話查詢，OK
SELECT * FROM dc_user ;
-- 5、其他會話寫入，Error
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 8、再次執行寫入讀取，OK
INSERT INTO dc_user (user_name,tell_phone) VALUES ('lock01','13267788998');
SELECT * FROM dc_user ;

這裡驗證表鎖的共用讀機制。

  • 排它寫鎖

這裡驗證表鎖的排它寫機制。

  • 查詢鎖爭用

通過下麵語句查看配置，

show status like 'table%';

Table_locks_waited的值越大，鎖爭用情況越嚴重，效率則越低下。

3、併發寫入問題

針對排它寫鎖的測試案例再說明：在一定條件下，MyISAM表也支持查詢和插入操作的併發執行。通過配置系統變數concurrent_insert的值[0,1,2]，可以實現併發寫入。

  • concurrent_insert=0，禁止併發寫入;
  • concurrent_insert=1，預設配置AUTO，在MyISAM表中沒有空洞，即表的中間沒有被刪除的行，例如[1,2,3],刪除2之後[1,,3]，則允許在讀表的同時，另一個線程從表尾寫入記錄。
  • concurrent_insert=2，無論MyISAM表中有沒有空洞，都允許在表尾併發插入記錄。
    在下麵的例子中，session_1獲得了一個表的READ LOCAL鎖，該線程可以對錶進行查詢操作，但不能對錶進行更新操作；其他的線程（session_2），雖然不能對錶進行刪除和更新操作，但卻可以對該表進行併發插入操作，這裡假設該表中間不存在空洞。

4、優先順序問題

MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串列的。但是當一個讀操作和寫操作同時請求，寫數據會優先獲得鎖，這一機制可以通過配置修改，指定配置參數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
通過執行命令SET

  • LOW_PRIORITY_UPDATES=1，使該會話的寫操作優先順序降低。
  • 指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。

5、表鎖應用

數據一致性校驗問題，比如銷售量+剩餘庫存=貨品總量，在校驗時就要在一次會話中同時鎖住訂單表和庫存表，免得在讀取訂單表的時候，庫存表被修改，導致數據誤差出現。

三、InnoDB鎖機制

1、事務基礎概念

  • 事務概念

事務是指作為單個邏輯工作單元執行的一系列操作（SQL語句）。這些操作要麼全部成功，要麼全部不成功。

  • 事務特性ACID

原子性(Atomicity)：事務中的多個操作要麼都成功要麼都失敗

一致性(consistency)：事務的執行的前後數據的完整性保持一致

隔離性(isolation)：事務執行的過程中,不應該受到其他事務的干擾

持久性(durability)：事務一旦結束,數據就持久到資料庫

  • 事務問題

臟讀：一個事務讀到另一個事務沒有提交的數據

不可重覆讀：一個事務前後多次讀取相同數據，數據內容不一致，update場景問題

虛讀(幻讀)：一個事務前後多次讀取，數據總量不一致，insert場景問題

  • 隔離級別

read uncommitted:事務可以讀取另一個未提交事務的數據。

read committed：事務要等另一個事務提交後才能讀取數據,解決臟讀。

repeatable read：在開始讀取數據時,事務開啟,不再允許修改操作,解決：臟讀、不可重覆讀。

serializable：最高事務隔離級別，事務串列化順序執行，解決臟讀、不可重覆讀、幻讀。但是效率低下，耗資料庫性能。

2、鎖機制描述

InnoDB與MyISAM的最大不同有兩點：一是支持事務TRANSACTION,二是採用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，另外，事務的引入也帶來新問題：併發，死鎖等。

  • 共用鎖：又稱讀鎖。允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。若事務T對數據對象A加上共用鎖，則事務T可以讀A但不能修改A，其他事務只能再對A加共用鎖，而不能加寫鎖，直到T釋放A上的共用鎖。這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。

  • 排他鎖：又稱寫鎖。允許獲取排他鎖的事務更新數據，阻止其他事務取得相同的資源的共用讀鎖和排他鎖。若事務T對數據對象A加上寫鎖，事務T可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到T釋放A上的寫鎖。

3、驗證案例

  • 基礎表結構
CREATE TABLE `dc_user_in01` (
  `id` int(11) DEFAULT NULL COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表';

CREATE TABLE `dc_user_in02` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用戶名',
  `tell_phone` varchar(20) DEFAULT NULL COMMENT '手機號',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='用戶表';

註意結構：表dc_user_in01主鍵沒有索引。表dc_user_in02主鍵有索引，但是都使用INNODB存儲引擎，下麵驗證案例會有不同。

  • 無索引結構表

會話視窗一

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=1，OK
SELECT * FROM dc_user_in01 WHERE id=1 ;
-- 3、添加寫鎖失敗
SELECT * FROM dc_user_in01 
WHERE id=1 FOR UPDATE ;
-- 4、恢復事務提交
SET AUTOCOMMIT = 1 ;

會話視窗二

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=2，OK
SELECT * FROM dc_user_in01 WHERE id=2 ;
-- 3、寫入失敗(等待)
INSERT INTO dc_user_in01 (id,user_name,tell_phone) 
VALUES (3,'lock01','13267788998');
-- 4、寫鎖失敗(等待)
SELECT * FROM dc_user_in01 
WHERE id=2 FOR UPDATE ;
-- 5、恢復事務提交
SET AUTOCOMMIT=1 ;
  • 索引結構表

會話視窗一

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=1，OK
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 3、添加寫鎖成功
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 執行到這裡，再執行視窗2
-- 4、恢復事務提交
SET AUTOCOMMIT = 1 ;

會話視窗二

-- 1、關閉自動提交
SET AUTOCOMMIT = 0 ;
-- 2、查詢id=2，OK
SELECT * FROM dc_user_in02 WHERE id=2 ;
-- 3、查詢id=1，OK，加讀鎖
SELECT * FROM dc_user_in02 WHERE id=1 ;
-- 4、寫入成功
INSERT INTO dc_user_in02 (user_name,tell_phone) VALUES ('lock01','13267788998');
-- 5、加寫鎖成功，id為2的
SELECT * FROM dc_user_in02 
WHERE id=2 FOR UPDATE ;
-- 6、加寫鎖失敗(等待)，占用id為1的
SELECT * FROM dc_user_in02 WHERE id=1 FOR UPDATE ;
-- 7、恢復事務提交
SET AUTOCOMMIT=1 ;
  • 索引失效問題

這裡要註意索引是否被使用問題，在很多查詢中，可能因為種種原因導致索引不執行。

explain SELECT * FROM dc_user_in02 WHERE id=1 ;
  • 查詢鎖爭用
show status like 'innodb_row_lock%';

Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，鎖爭用情況越嚴重，效率則越低下。

4、Next-Key鎖

  • 官方文檔說明

為了防止幻讀，InnoDB使用了一種名為Next-Key鎖定的演算法，它將記錄鎖和間隙鎖定結合在一起即：InnoDB在執行行級鎖的時候，會用這種方式-掃描索引記錄，會在符合索引條件的記錄上加共用鎖或者獨占鎖。

[Next-Key]=[Record-lock]+[Gap-lock]

如果說上面的幾種鎖機制給人的感覺是昏天暗地，那個這個Next-Key演算法就會叫人懷疑人生。

  • 驗證案例

這裡主要驗證Gap-lock間隙鎖的存在機制。

CREATE TABLE `dc_gap` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `id_index` int(11) NOT NULL COMMENT 'index',
  PRIMARY KEY (`id`),
  KEY `id_index` (`id_index`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='間隙表';
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('1', '2');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('3', '4');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('6', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('8', '7');
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('9', '9');

會話視窗一

-- 1、開始事務
START TRANSACTION ;
-- 3、鎖定id_index=7的兩條記錄
SELECT * FROM dc_gap 
WHERE id_index=7 FOR UPDATE ;
-- 9、提交
COMMIT ;

會話視窗二

-- 2、開始事務
START TRANSACTION ;
-- 4、寫入等待,id_index=6
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '6');
-- 5、寫入等待,id_index=4
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('4', '4');
-- 6、寫入成功,id_index=3
INSERT INTO `dc_gap` (`id`, `id_index`) 
VALUES ('4', '3');
-- 7、寫入等待,id_index=9
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '9');
-- 8、寫入成功,id_index=10
INSERT INTO `dc_gap` (`id`, `id_index`) VALUES ('7', '10');

7向上到4有間隙，7向下到9有間隙，所以間隙鎖定[4,9]，且包含首尾值。

5、Dead-Lock鎖

  • 基礎描述

兩個或者多個事務在同一個資源上相互占用，並請求鎖定對方占用的資源，從而導致死迴圈現象，也就是死鎖。

  • 驗證案例

會話視窗一

-- 1、開啟事務
START TRANSACTION ;
-- 3、占用id=6的資源
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;
-- 5、占用id=9的資源等待
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;

會話視窗二

-- 2、開啟事務
START TRANSACTION ;
-- 4、占用id=9的資源
SELECT * FROM dc_gap WHERE id=9 FOR UPDATE ;
-- 6、占用id=6的資源拋死鎖
SELECT * FROM dc_gap WHERE id=6 FOR UPDATE ;

補刀一句：資料庫實現各種死鎖檢測機制，或者死鎖超時等待結束，InnoDB存儲引擎在檢測到死鎖後，會立即返回錯誤，不然兩個事務會隔空對望，一眼萬年。

註意：死鎖在事務型業務中，是無法絕對避免的，鎖定資源少，粒度細，儘量避免該情況出現。

四、源代碼地址

GitHub·地址
https://github.com/cicadasmile/mysql-data-base
GitEE·地址
https://gitee.com/cicadasmile/mysql-data-base


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

-Advertisement-
Play Games
更多相關文章
  • Top命令你最少要瞭解到這個程度
    命令幾乎是每個程式員都會用到的Linux命令。這個命令用來查看Linux系統的綜合性能，比如CPU使用情況，記憶體使用情況。這個命令能幫助我快速定位程式的性能問題。 雖然這個命令很重要，但是之前對於這個命令的使用幾乎僅限於查看下哪個進程使用的CPU最高，哪個進程占用的記憶體最高。對於輸出的各個參數的含義 ...
  • VMware的虛擬網路編輯器，在配置的過程中沒有橋接模式！（虛擬機卸載）
    虛擬機沒有VMnet0 橋接模式，你可以試試以下幾種方式，儘量都試試，因為具體情況原因有很多種！！ 首先打開虛擬網路編輯器，發現沒有橋接。。。深吸一口氣，說我沒有毒！！！ 一、先右擊開始 網路連接 更改網路適配器，查看一下是否有下麵這倆個 如果沒有，試試最靠命運的方法，重啟主機！！！（win10這樣 ...
  • 032.核心組件-kube-proxy
    一 kube-proxy原理 1.1 kube-proxy概述 Kubernetes為了支持集群的水平擴展、高可用性，抽象出了Service的概念。Service是對一組Pod的抽象，它會根據訪問策略（如負載均衡策略）來訪問這組Pod。Kubernetes在創建Service時會為Service分配 ...
  • 031.核心組件-kubelet
    一 kubelet概述 1.1 kubelet作用 在Kubernetes集群中，在每個Node（又稱Minion）上都會啟動一個kubelet服務進程。該進程用於處理Master下發到本節點的任務，管理Pod及Pod中的容器。每個kubelet進程都會在API Server上註冊節點自身的信息，定 ...
  • 030.核心組件-Scheduler
    一 Scheduler原理 1.1 原理解析 Kubernetes Scheduler是負責Pod調度的重要功能模塊，Kubernetes Scheduler在整個系統中承擔了“承上啟下”的重要功能，“承上”是指它負責接收Controller Manager創建的新Pod，為其調度至目標Node；“ ...
  • 用Excel將時間戳轉換為日期格式
    從linux系統中獲取的時間戳信息通常為s，將其轉換的公式為： =TEXT((C4/1000+8*3600)/86400+70*365+19,"yyyy-mm-dd hh:mm:ss") 其中C4單元格所存的數據為ms因此需要除以1000 轉換後的結果如下： ...
  • 附013.Kubernetes永久存儲Rook部署
    一 Rook概述 1.1 Ceph簡介 Ceph是一種高度可擴展的分散式存儲解決方案，提供對象、文件和塊存儲。在每個存儲節點上，將找到Ceph存儲對象的文件系統和Ceph OSD（對象存儲守護程式）進程。在Ceph集群上，還存在Ceph MON（監控）守護程式，它們確保Ceph集群保持高可用性。 更 ...
  • 多個Windows10快速切換應用程式的快捷鍵!
    原文鏈接: "http://xiaoheidiannao.com/articles/Process Switch.html" 大家平時都是怎麼切換後臺運行的 "軟體" 的呢？如果是通過 "滑鼠" 點擊 "任務欄" 的話,那真的是太慢了,下麵介紹幾個快速切換後臺運行的 "軟體" 的 "快捷鍵" 。 " ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類