likes
comments
collection
share

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

作者站长头像
站长
· 阅读数 30

锁机制

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

  1. READ UNCOMMITTED (读未提交):

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

  2. READ COMMITTED (读已提交):

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

  3. REPEATABLE READ (可重复读):

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

  4. 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 分层架构图

SQL Queries
SQL
Parsed SQL
Execution Plan
Handlers
Data Request
Data Request
Data Request
Data Request
Data Request
Data Request
Data
Data
Data
Data
Data
Data
Client(客户端)
Network Interface网络接口
SQL Interface SQL接口
Optimizer优化器
Execution Engine执行引擎
Storage Engines存储引擎
InnoDB
MyISAM
Memory
CSV
ARCHIVE
Other Engines其他引擎
File System文件系统
RAM内存

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

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

MySQL 的主要组件和层次结构

Parsing
Preprocessing
Optimization
SQL Queries
Execution
Transaction Control
Data Read/Write
Disk Write
Transaction Control
Logging
Cache Hit/Miss
SQL Queries
Cached Data
Data Read/Write
Data Read/Write
Data Read/Write
Command Line Interface命令行接口
API Interface应用程序接口
Network Layer网络层
SQL ParserSQL 解析器
Preprocessor预处理器
Optimizer查询优化器
Query Cache查询缓存
Execution Engine执行引擎
Transaction Layer事务层
Buffer Pool缓冲池
Log Buffer日志缓冲
Write-Ahead Log预写式日志
Disk Storage磁盘存储
Storage Engines存储引擎
InnoDBInnoDB引擎
MyISAMMyISAM引擎
Other Engines其他引擎

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

  • 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: 磁盘存储，存储引擎将数据持久化到磁盘上。
转载自:https://juejin.cn/post/7383887420325937206
评论
请登录