【MySQL】鎖之MyISAM

来源:https://www.cnblogs.com/ydongy/archive/2020/06/11/13092059.html
-Advertisement-
Play Games

紙上得來終覺淺，絕知此事要躬行。 鎖的分類 從對數據操作的粒度分 ： 表鎖：操作時，會鎖定整個表。 行鎖：操作時，會鎖定當前操作行。 從對數據操作的類型分： 讀鎖（共用鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。 MyS ...


紙上得來終覺淺，絕知此事要躬行。

鎖的分類

從對數據操作的粒度分 ：

  1. 表鎖：操作時，會鎖定整個表。
  2. 行鎖：操作時，會鎖定當前操作行。

從對數據操作的類型分：

  1. 讀鎖（共用鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
  2. 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。

MySQL鎖

MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。下表中羅列出了各存儲引擎對鎖的支持情況：

存儲引擎 表級鎖 行級鎖 頁面鎖
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持

特性：

鎖類型 特點
表級鎖 偏向MyISAM 存儲引擎，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,併發度最低。
行級鎖 偏向InnoDB 存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。
頁面鎖 開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

MyISAM表鎖

如何加表鎖

MyISAM 在執行查詢語句SELECT前，會自動給涉及的所有表加讀鎖，在執行更新操作UPDATE、DELETE、INSERT前，會自動給涉及的表加寫鎖，這個過程並不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

顯示加表鎖語法：

加讀鎖 ： lock table table_name read;

加寫鎖 ： lock table table_name write；

雖然MyIASM預設加了表鎖，但是我們仍然可以手動加鎖，具體通過案例來瞭解一下加鎖之後，我們操作資料庫會有什麼影響。

讀寫鎖案例

在進行測試之前我們需要準備測試環境和相關資料庫以及表，並且把MySQL的存儲引擎指定為MyISAM，如下先創建一個資料庫並切換到資料庫：

create database demo2 default charset=utf8;

接著創建表，本次我們測試的是MyISAM存儲引擎，所以要創建表時要指定存儲引擎（說明存儲引擎是作用於表，而不是作用於資料庫，也就是說一個資料庫有多個表，而多個表可以使用不同的存儲引擎）

CREATE TABLE `tb_book` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  `publish_time` DATE DEFAULT NULL,
  `status` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

# 插入數據

INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'高性能MySQL','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'MySQL技術內幕','2088-08-08','0');

CREATE TABLE `tb_user` (
  `id` INT(11) auto_increment,
  `name` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;

# 插入數據

INSERT INTO tb_user (id, name) VALUES(NULL,'張三');
INSERT INTO tb_user (id, name) VALUES(NULL,'李四');

讀鎖

數據準備完成接下來我們進行測試，使用兩個客戶端連接資料庫。

  • 查詢數據

客戶端一：

  1. 獲取tb_book表的讀鎖
lock table tb_book read;
  1. 執行查詢語句
select * from tb_book;

mysql> select * from tb_book;
+----+-------------------+--------------+--------+
| id | name              | publish_time | status |
+----+-------------------+--------------+--------+
|  1 | 高性能MySQL       | 2088-08-01   | 1      |
|  2 | MySQL技術內幕     | 2088-08-08   | 0      |
+----+-------------------+--------------+--------+
2 rows in set (0.00 sec)

可以正常執行，查詢出數據。

客戶端二：

  1. 執行查詢操作
select * from tb_book;

mysql> select * from tb_book;
+----+-------------------+--------------+--------+
| id | name              | publish_time | status |
+----+-------------------+--------------+--------+
|  1 | 高性能MySQL       | 2088-08-01   | 1      |
|  2 | MySQL技術內幕     | 2088-08-08   | 0      |
+----+-------------------+--------------+--------+
2 rows in set (0.00 sec)

也可以正常查詢數據

客戶端一：

  1. 查詢未鎖定的表
select * from tb_user;

mysql> select * from tb_user;
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

提示tb_user沒有加鎖，無法獲取數據

客戶端二：

  1. 查詢未鎖定的表
select * from tb_user;

mysql> select * from tb_user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 張三   |
|  2 | 李四   |
+----+--------+
2 rows in set (0.00 sec)

可以正常查詢出未鎖定的表

  • 插入數據

客戶端一：

  1. 執行插入數據
insert into tb_book values(null,'Mysql高級','2088-01-01','1');

mysql> insert into tb_book values(null,'Mysql高級','2088-01-01','1');
ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated

執行插入， 直接報錯，由於當前tb_book 獲得的是讀鎖，不能執行更新操作。

客戶端二：

  1. 執行插入數據
insert into tb_book values(null,'Mysql高級','2088-01-01','1');

執行語句阻塞，等待中。

當在客戶端一中釋放鎖指令unlock tables後 ， 客戶端二中的 inesrt 語句立即執行。

寫鎖

  • 查詢數據

客戶端一：

  1. 獲取tb_book表的寫鎖
lock table tb_book write;
  1. 執行查詢數據
select * from tb_book;

mysql> select * from tb_book;
+----+-------------------+--------------+--------+
| id | name              | publish_time | status |
+----+-------------------+--------------+--------+
|  1 | 高性能MySQL       | 2088-08-01   | 1      |
|  2 | MySQL技術內幕     | 2088-08-08   | 0      |
|  3 | Mysql高級         | 2088-01-01   | 1      |
+----+-------------------+--------------+--------+
3 rows in set (0.00 sec)
  1. 執行update操作
mysql> update tb_book set name = 'MySQL' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  1. 執行insert操作
mysql> insert into tb_book values(null,'Python','2088-01-01','1');
Query OK, 1 row affected (0.00 sec)
  1. 執行delete操作
mysql> delete from tb_book where id = 4;
Query OK, 1 row affected (0.00 sec)

當前客戶端處於write lock，其增刪改查都正常執行

客戶端二：

  1. 執行查詢操作
select * from tb_book;

此時查詢操作處於阻塞狀態。

客戶端三：

  1. 執行更新操作
update tb_book set name = 'Java' where id = 3;

也處於阻塞狀態。

當我們把客戶端一的表解鎖，則客戶端二的查詢操作和客戶端三的更新操作立刻執行。

鎖上加鎖

  • 不同客戶端加鎖

客戶端一：

  1. 獲取tb_book表的讀鎖
lock table tb_book read;

客戶端二：

  1. 獲取tb_book表的讀鎖
lock table tb_book read;

此時客戶端一，只能讀取加鎖的表，客戶端二同樣只能讀取加鎖的表，都無法更新表。

  1. 獲取tb_book表的寫鎖
lock table tb_book wtite;

由於客戶端一已經上了讀鎖，此時客戶端二上寫鎖會阻塞。

  • 同一客戶端多次加鎖

客戶端一：

  1. 執行tb_book表的讀鎖,然後繼續執行寫鎖
lock table tb_book read;
lock table tb_book write;

此時客戶端一可以對加鎖的表執行增刪改查，客戶端二對加鎖的表增刪改查阻塞。

總結

  • 對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求
  • 對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作

​簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。

此外，MyISAM 的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因為寫鎖後，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞。

查詢鎖的爭用情況

show open tables；

In_user：表當前被查詢使用的次數。如果該數為零，則表是打開的，但是當前沒有被使用。
Name_locked：表名稱是否被鎖定。名稱鎖定用於取消表或對錶進行重命名等操作。

show status like 'Table_locks%';


mysql> show status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 139   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

Table_locks_immediate：指的是能夠立即獲得表級鎖的次數，每立即獲取鎖，值加1。
Table_locks_waited：指的是不能立即獲取表級鎖而需要等待的次數，每等待一次，該值加1，此值高說明存在著較為嚴重的表級鎖爭用情況。


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

-Advertisement-
Play Games
更多相關文章
  • 適合開發者的最佳Linux發行版
    從Ubuntu到Pop!_OS，由Arch到Manjoro，哪個系統最適合咱們開發者?本文就來給大家說一說哪個Linux發版本最適合。 ...
  • 文件壓縮和打包
    文件壓縮與打包 壓縮文件的指令介紹： 壓縮文件通常分為: 常用尾碼名 壓縮命令 備註 .zip zip 不常用，用gzip取代 .gz gzip 常用.可以解開zip,gzip,可用zcat命令讀出 .bz2 bzip2 花費時間要久，但壓縮效率更高 .xz xz xz壓縮比很高，但花費時間大約為g ...
  • [windows][技巧]百度網盤提示您的電腦已安裝百度網盤，是否覆蓋，解決方法
    故障描述 解決方法 win+r，輸入regedit，進入註冊表 依次打開HKEY_CURRENT_USER->Software->Baidu->BaiDuYunGuanJia 右鍵BaiDuYunGuanJia，刪除 ...
  • Redis學習筆記（十九） 發佈訂閱（上）
    Redis 發佈訂閱(pub/sub)是一種消息通信模式：發送者(pub)發送消息，訂閱者(sub)接收消息，它的發佈與訂閱功能由PUBLISH、SUBSCRIBE、PSUBSCRIBE等命令組成。 通過執行SUBSCRIBE命令，客戶端可以訂閱一個或多個頻道，從而成為這些頻道的訂閱者：每當有其他客 ...
  • mysql查詢優化
    select * from a where id in (select id from b) 等價於: for select id from b for select 8 from a where a.id = b.id 當b表數據必須小於a表數據時,in優於exists select * from ...
  • 【MySQL】主從複製
    紙上得來終覺淺，絕知此事要躬行。 概述 複製是指將主資料庫的DDL 和 DML 操作通過二進位日誌傳到從庫伺服器中，然後在從庫上對這些日誌重新執行（也叫重做），從而使得從庫和主庫的數據保持同步。 MySQL支持一臺主庫同時向多台從庫進行複製， 從庫同時也可以作為其他從伺服器的主庫，實現鏈狀複製。 優 ...
  • Mysql大數據量分頁優化
    參考文章： MySQL百萬級數據量分頁查詢方法及其優化 MySQL分頁查詢優化 重點提一下： 在優化分頁語句過程中，一定註意查詢的語句添加排序欄位，一定自己建表實踐，有的可能不一定有效果哦 比如下麵這兩個語句，返回的結果就不是一樣的。具體什麼原因可以看看我另一篇博客 MySQL 預設排序是什麼 SE ...
  • 【MySQL】鎖之InnoDB
    紙上得來終覺淺，絕知此事要躬行。 InnoDB行鎖 開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。 InnoDB 實現了以下兩種類型的行鎖。 共用鎖（S）：又稱為讀鎖，簡稱S鎖，共用鎖就是多個事務對於同一數據可以共用一把鎖，都能訪問到數據，但是只能讀不能修改。 排他鎖 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類