深入探索MySQL：锁机制、事务隔离级别与分层架构全景解析-每日运维

锁机制

MySQL 中的读锁（共享锁）和写锁（排他锁）通常通过锁定表或行来实现。这些锁可以通过 SQL 语句显式地进行管理。
以下是如何在 MySQL 中使用读锁和写锁的一些示例：

读锁（共享锁）

读锁允许事务读取一行数据，而其他事务也可以读取同一行，但不能修改它，直到锁被释放。

表级读锁示例:

-- 锁定表格为读模式
LOCK TABLES your_table READ;

-- 执行一些读取操作
SELECT * FROM your_table WHERE ...;

-- 解锁表格
UNLOCK TABLES;

行级读锁示例:

-- 开启一个事务
START TRANSACTION;

-- 对特定的行加上读锁
SELECT * FROM your_table WHERE ... LOCK IN SHARE MODE;

-- SELECT...FOR SHARE是MySQL 8.0的新语句， 取代了以前版本的SELECT...LOCK IN SHARE MODE


-- 执行一些读取操作
...

-- 提交事务，释放锁
COMMIT;

SELECT...FOR SHARE是MySQL 8.0的新语句，取代了以前版本的SELECT...LOCK IN SHARE MODE

写锁（排他锁）

写锁阻止其他事务读取或修改数据，直到锁被释放。

表级写锁示例:

-- 锁定表格为写模式
LOCK TABLES your_table WRITE;

-- 执行一些修改操作
UPDATE your_table SET column_name = 'value' WHERE ...;

-- 解锁表格
UNLOCK TABLES;

行级写锁示例:

-- 开启一个事务
START TRANSACTION;

-- 对特定的行加上写锁
SELECT * FROM your_table WHERE ... FOR UPDATE;

-- 执行一些修改操作
UPDATE your_table SET column_name = 'new_value' WHERE ...;

-- 提交事务，释放锁
COMMIT;

需要注意的是，行级锁通常是在 InnoDB 这样的事务型存储引擎中使用，而 MyISAM 这样的非事务型存储引擎通常只支持表级锁。

在使用锁时，应该遵循最小锁定原则，即只在必要时加锁，并尽快释放锁，以避免死锁和降低并发性能。
此外，对于行级锁，MySQL 通常会在需要时自动加锁和解锁，但在某些情况下，您可能需要显式地使用 LOCK IN SHARE MODE 或 FOR UPDATE 来控制锁的行为。

间隙锁

间隙锁（Gap Lock）是 InnoDB 存储引擎特有的一种锁，它锁定一个范围，但不包括记录本身。这种锁主要用于事务隔离级别为 REPEATABLE READ 和 SERIALIZABLE 时，防止幻读（Phantom Read）的发生。间隙锁通常在以下情况下自动由 InnoDB 存储引擎设置：

当使用范围条件检索数据但不检索记录本身时。
当使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 对范围进行查询，并且范围内没有匹配的行时。
当插入一条记录，但由于该记录的值在索引中有一个“间隙”（Gap）时。

以下是一个使用间隙锁的示例，假设我们有一个名为 your_table 的表，其中包含一个名为 id 的整数类型的主键列：

-- 设置事务隔离级别为 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 开启一个事务
START TRANSACTION;

-- 尝试通过范围条件获取排他锁
SELECT * FROM your_table WHERE id BETWEEN 10 AND 20 FOR UPDATE;

-- 提交事务
COMMIT;

事务隔离级别

SQL 标准定义了四个事务隔离级别，每个隔离级别都旨在解决并发事务中的一些问题，同时也会引入不同程度的性能影响。这四个隔离级别分别是：

READ UNCOMMITTED (读未提交):

描述：这是最低的隔离级别，事务可以读取其他未提交事务的数据（脏读）。这意味着一个事务可能会看到另一个事务未提交的更改。
问题：脏读、不可重复读、幻读。
应用场景：由于它允许脏读，这个隔离级别很少使用，只有当读取的准确性不是很关键时才考虑。

READ COMMITTED (读已提交):

描述：这个隔离级别保证了一个事务不会读取到其他事务未提交的数据。这减少了脏读的可能性，但是仍然可能遇到不可重复读的情况。
问题：不可重复读、幻读。
应用场景：这是许多数据库系统的默认隔离级别（例如 Oracle），它在并发性和数据准确性之间提供了一个平衡。

REPEATABLE READ (可重复读):

描述：在这个隔离级别下，事务可以多次从同一个字段中读取相同的值，即使另一个事务试图修改它。这防止了不可重复读，但仍可能出现幻读。
问题：幻读。
应用场景：MySQL 的 InnoDB 存储引擎默认使用这个隔离级别。它适合需要更高一致性要求的场景，但是可能导致更多的锁定和降低并发性能。

SERIALIZABLE (可串行化):

描述：这是最高的隔离级别，它通过锁定涉及到的每一行来防止脏读、不可重复读和幻读。在这个隔离级别下，事务会完全串行执行，以确保事务之间不会相互影响。
问题：可能导致大量的锁定以及死锁。
应用场景：当需要完全的数据一致性，且可以接受较低并发性能时，可以使用这个隔离级别。

下面是这些隔离级别在 SQL 中的设置示例：

-- 设置隔离级别为 READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 设置隔离级别为 READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置隔离级别为 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置隔离级别为 SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

在实际应用中，选择哪个隔离级别取决于应用程序对一致性和并发的需求。较低的隔离级别（如 READ UNCOMMITTED 和 READ COMMITTED）可以提供更高的并发性能，但牺牲了一定的数据一致性。而较高的隔离级别（如 REPEATABLE READ 和 SERIALIZABLE）可以提供更强的一致性保证，但可能会降低并发性能并增加锁的竞争。

分层架构

MySQL的分层架构是其软件设计的核心，它定义了数据处理和存储的多个层次，确保了数据库的高效运行和灵活扩展。以下是MySQL分层架构的概述：
1. 连接层：
- 这是架构的最外层，负责管理客户端和服务器之间的连接。
- 它处理用户认证、SSL加密、连接池等。

2. 服务层：
- 包括SQL接口、解析器、优化器以及缓存等组件。
- SQL接口接收并解析客户端发送的SQL命令。
- 解析器将SQL命令解析成抽象语法树。
- 优化器负责查询的优化，生成执行计划。
- 查询缓存用于存储执行结果，提高查询效率。

3. 引擎层：
- 这一层包含了多个存储引擎，如InnoDB、MyISAM等。
- 存储引擎负责数据的存储、检索、更新和删除操作。
- 它们实现了MySQL的事务控制、锁定机制、索引维护等功能。

4. 存储层：
- 这是架构的最底层，直接与文件系统交互。
- 负责数据文件和索引文件的物理存储。
- 包括数据文件、索引文件、二进制日志等。
-

MySQL 分层架构图

graph TD
    client[("Client(客户端)")] --> |SQL Queries| net[Network Interface网络接口]
    net --> |SQL| sql_parser[SQL Interface SQL接口]
    sql_parser --> |Parsed SQL| optimizer[Optimizer优化器]
    optimizer --> |Execution Plan| executor[Execution Engine执行引擎]
    executor --> |Handlers| storage_engines[Storage Engines存储引擎]
    storage_engines --> |Data Request| innodb[InnoDB]
    storage_engines --> |Data Request| myisam[MyISAM]
    storage_engines --> |Data Request| memory[Memory]
    storage_engines --> |Data Request| csv[CSV]
    storage_engines --> |Data Request| archive[ARCHIVE]
    storage_engines --> |Data Request| other[Other Engines其他引擎]
    innodb --> |Data| filesys[File System文件系统]
    myisam --> |Data| filesys
    memory --> |Data| ram[RAM内存]
    csv --> |Data| filesys
    archive --> |Data| filesys
    other --> |Data| filesys

MySQL 从客户端接收查询到数据存储的基本流程。
以下是每个组件的简要说明：

Client (客户端): 发送 SQL 查询到服务器。
Network Interface (网络接口): 处理客户端连接和通信。
SQL Interface (SQL接口): 包括 SQL 解析器，将 SQL 文本转换为解析树。
Optimizer (优化器): 对解析树进行优化，生成执行计划。
Execution Engine (执行引擎): 根据优化器提供的执行计划来执行查询。
Storage Engines (存储引擎): 数据存储和提取的底层实现，如 InnoDB、MyISAM 等。
File System (文件系统), RAM (内存): 存储引擎使用的物理存储介质。

MySQL 的主要组件和层次结构

graph TD
    CLI[("Command Line Interface
命令行接口")]
    API[("API Interface
应用程序接口")]
    NET[("Network Layer
网络层")]
    PAR[("SQL Parser
SQL 解析器")]
    PRE[("Preprocessor
预处理器")]
    OPZ[("Optimizer
查询优化器")]
    CAC[("Query Cache
查询缓存")]
    EXE[("Execution Engine
执行引擎")]
    TRX[("Transaction Layer
事务层")]
    BUF[("Buffer Pool
缓冲池")]
    LOG[("Log Buffer
日志缓冲")]
    WAL[("Write-Ahead Log
预写式日志")]
    PAR -->|Parsing| PRE
    PRE -->|Preprocessing| OPZ
    OPZ -->|Optimization|EXE
    CLI --> NET
    API --> NET
    NET -->|SQL Queries| PAR
    EXE -->|Execution| TRX
    TRX -->|Transaction Control| BUF
    BUF -->|Data Read/Write| WAL
    WAL -->|Disk Write| DISK[("Disk Storage
磁盘存储")]
    TRX -->|Transaction Control| LOG
    LOG -->|Logging| DISK
    CAC -->|Cache Hit/Miss| PAR
    NET -->|SQL Queries| CAC
    BUF -->|Cached Data| SE[("Storage Engines
存储引擎")]
    SE --> INN[("InnoDB
InnoDB引擎")]
    SE --> MYI[("MyISAM
MyISAM引擎")]
    SE --> OTH[("Other Engines
其他引擎")]
    INN -->|Data Read/Write| DISK
    MYI -->|Data Read/Write| DISK
    OTH -->|Data Read/Write| DISK

在这个图中，我们尝试展示了从客户端接口到数据存储的详细流程：

Command Line Interface / API Interface: 客户端连接接口，包括命令行工具和应用程序编程接口。
Network Layer: 网络层，处理客户端与服务器之间的通信。
SQL Parser: SQL 解析器，将 SQL 文本转换为解析树。
Preprocessor: 预处理器，进行 SQL 语句的初步检查和处理。
Optimizer: 查询优化器，对解析树进行优化，生成执行计划。
Query Cache: 查询缓存，缓存执行结果，加速相同查询的执行。
Execution Engine: 执行引擎，根据优化器提供的执行计划来执行查询。
Transaction Layer: 事务层，处理事务的开始、提交和回滚。
Buffer Pool: 缓冲池，缓存数据页，减少磁盘 I/O。
Log Buffer: 日志缓冲，缓存事务日志，提高日志写入效率。
Write-Ahead Log: 预写式日志，确保数据的持久性和恢复能力。
Storage Engines: 存储引擎，如 InnoDB、MyISAM 等，实现数据的存储、检索和更新。
Disk Storage: 磁盘存储，存储引擎将数据持久化到磁盘上。

深入探索MySQL：锁机制、事务隔离级别与分层架构全景解析

锁机制

读锁（共享锁）

写锁（排他锁）

间隙锁

事务隔离级别

分层架构

MySQL 分层架构图

MySQL 的主要组件和层次结构

相关文章

发布评论取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

深入探索MySQL：锁机制、事务隔离级别与分层架构全景解析

锁机制

读锁（共享锁）

写锁（排他锁）

间隙锁

事务隔离级别

分层架构

MySQL 分层架构图

MySQL 的主要组件和层次结构

相关文章

发布评论 取消回复

泡泡

百度搜索：蓝易云 – Docker的安装与卸载(Centos、Ubuntu )

PostgreSQL系数据库使用COPY导数时如何实现增量及重复数据更新导入

【OceanBase DBA早下班系列】—— obdiag 收集的 SQL Monitor Report 如何解读

发布评论取消回复