MySQL-锁：共享锁（读）、排他锁（写）、表锁、行锁、意向锁、间隙锁，锁升级

2024-03-10 06:52
文章标签 mysql 共享 升级 间隙 database 行锁 意向锁 表锁

本文主要是介绍MySQL-锁：共享锁（读）、排他锁（写）、表锁、行锁、意向锁、间隙锁，锁升级，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL-锁：共享锁（读）、排他锁（写）、表锁、行锁、意向锁、间隙锁

      • 共享锁（读锁）、排他锁
      • 表锁
      • 行锁
      • 意向锁
      • 间隙锁
      • 锁升级

MySQL数据库中的锁是控制并发访问的重要机制，它们确保数据的一致性和完整性。下面是MySQL数据库中常见的锁类型以及它们的特点和应用场景：

  1. 共享锁（Shared Lock）：
    • 特点：共享锁允许多个事务同时读取同一资源，但阻止其他事务获取排他锁，从而防止并发写入。
    • 应用场景：适用于读取操作，例如SELECT语句。
  2. 排他锁（Exclusive Lock）：
    • 特点：排他锁阻塞其他事务的读锁和写锁，只允许一个事务对资源进行写操作，防止其他事务读取或写入资源。
    • 应用场景：适用于写入或修改操作，例如INSERT、UPDATE、DELETE语句。
  3. 表锁（Table Lock）：
    • 特点：表锁会锁定整张表，阻塞其他事务对表的写操作，但不阻塞对表的读操作。
    • 应用场景：适用于对整张表进行操作的场景，例如MyISAM存储引擎的表级锁。
  4. 行锁（Row Lock）：
    • 特点：行锁允许事务锁定表中的特定行，而不是整张表，提高并发性。但容易引发死锁。
    • 应用场景：适用于需要精确控制行级别访问的场景，例如InnoDB存储引擎的行级锁。
  5. 意向锁（Intention Locks）：
    • 意向锁是一种表明事务将在某个级别上加锁的锁类型。意向锁有两种类型：意向共享锁和意向排他锁。它们用于表示一个事务打算在一个更高的层级上（如行级别或表级别）加锁。
    • 意向锁是为了帮助数据库管理系统了解一个事务打算在哪个级别上进行锁定，以便避免死锁。
  6. 间隙锁（Gap Locks）：
    • 间隙锁是一种特殊类型的锁，用于防止其他事务将新的键插入到已有的索引范围中。间隙锁在某个范围的键之间创建了一个间隙，防止其他事务插入新的键。
    • 间隙锁通常与范围查询操作一起使用，例如SELECT ... WHERE ... BETWEEN ... AND ...语句。

现在，让我们来看一个实际案例，说明MySQL中锁的应用：

假设有一个电子商务网站，多个用户同时访问产品库存信息，并尝试下单购买同一件商品，这时候可能会涉及到对产品库存的读写操作。

  • 对于读取产品库存信息的操作，可以使用共享锁（读锁），允许多个用户同时读取产品库存信息，不会互相阻塞，保证并发性。
  • 当某个用户下单购买商品时，需要更新产品库存信息，此时需要使用排他锁（写锁），阻止其他用户同时修改库存信息，确保数据的一致性。

如果没有使用合适的锁机制，可能会导致以下问题：

  • 如果多个用户同时读取产品库存信息但不加锁，则可能读取到错误的库存数量。
  • 如果多个用户同时尝试购买同一件商品而没有加锁，则可能导致超卖或者库存错误的问题。

共享锁（读锁）、排他锁

CREATE TABLE articles (article_id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255),content TEXT
);

事务1：查看博客文章内容（共享锁）

共享锁允许多个事务同时读取同一资源，但阻止其他事务获取排他锁，从而防止并发写入。

START TRANSACTION;
SELECT * FROM articles WHERE article_id = 123 LOCK IN SHARE MODE;
-- 读取文章内容
COMMIT;

事务2：可以编辑文章内容（排他锁）

排他锁阻塞其他事务的读锁和写锁，只允许一个事务对资源进行写操作，防止其他事务读取或写入资源

START TRANSACTION;
SELECT * FROM articles WHERE article_id = 123 FOR UPDATE;
-- 编辑文章内容
UPDATE articles SET content = 'New content' WHERE article_id = 123;
COMMIT;

在这里插入图片描述

表锁

假设有一个在线预订系统，有一个订单表，需要定期对订单表进行清理和维护操作

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATETIME
);

-- 事务1：清理过期订单（表锁）
START TRANSACTION;
LOCK TABLES orders WRITE;
-- 清理过期订单
DELETE FROM orders WHERE order_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
UNLOCK TABLES;
COMMIT;

在这里插入图片描述

在这里插入图片描述

行锁

假设有一个在线论坛系统，用户可以对帖子进行评论，需要确保在用户对同一篇帖子进行评论时不会产生冲突。

CREATE TABLE posts (post_id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255),content TEXT
);CREATE TABLE comments (comment_id INT AUTO_INCREMENT PRIMARY KEY,post_id INT,user_id INT,comment_text TEXT,
);

-- 事务1：添加评论（行锁）
START TRANSACTION;
SELECT * FROM posts WHERE post_id = 123 FOR UPDATE;
-- 添加评论
INSERT INTO comments (post_id, user_id, comment_text) VALUES (123, 456, 'Great post!');
COMMIT;

事务执行SELECT * FROM posts WHERE post_id = 123 FOR UPDATE;语句获取了帖子ID为123的行级排他锁。这样做的目的是确保在添加评论之前，对于帖子ID为123的行数据，其他事务无法同时修改，以避免数据不一致或丢失的情况发生

意向锁

假设有一个在线购物系统，其中有两张表：orders（订单表）和order_details（订单详情表）。订单表记录了每个订单的基本信息，订单详情表则记录了每个订单中的具体商品信息。

CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,order_date DATETIME
);CREATE TABLE order_details (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT,product_id INT,quantity INT,
);

现在假设有一个事务需要向订单表中插入一条新的订单记录，并且需要向订单详情表中插入相关的商品信息。

START TRANSACTION;-- 获取意向排他锁，表明将在订单表上进行排他锁定
SELECT * FROM orders WHERE customer_id = 100 FOR UPDATE;-- 插入新的订单记录
INSERT INTO orders (customer_id, order_date) VALUES (100, NOW());
SET @last_order_id = LAST_INSERT_ID();-- 插入订单详情
INSERT INTO order_details (order_id, product_id, quantity) VALUES (@last_order_id, 1, 2);
INSERT INTO order_details (order_id, product_id, quantity) VALUES (@last_order_id, 2, 3);COMMIT;

初始：

在这里插入图片描述

执行事务后：

在这里插入图片描述

​ 在这个例子中，事务首先获取了意向排他锁，以表明将在订单表上进行排他锁定。然后向订单表中插入了一条新的订单记录，并且通过获取LAST_INSERT_ID()函数获取了刚插入的订单ID，接着向订单详情表中插入了相关的商品信息。

间隙锁

假设有一个产品表，其中的产品ID是一个唯一索引。现在有两个事务，一个事务需要检查某个特定的产品是否存在，而另一个事务需要向产品表中插入一个新的产品。

-- 事务1：检查产品是否存在
START TRANSACTION;
SELECT * FROM products WHERE product_id = 5 LOCK IN SHARE MODE;
-- 如果存在特定产品，则执行相应操作
COMMIT;-- 事务2：插入新的产品
START TRANSACTION;
-- 获取间隙锁，防止其他事务在该范围内插入新的产品
SELECT * FROM products WHERE product_id > 4 AND product_id < 6 FOR UPDATE;
-- 插入新的产品
INSERT INTO products (product_id, name, price) VALUES (5, 'New Product', 10.99);
COMMIT;

事务1：检查产品是否存在

在这里插入图片描述

START TRANSACTION;
SELECT * FROM products WHERE product_id = 5 LOCK IN SHARE MODE;
-- 如果存在特定产品，则执行相应操作
INSERT INTO products (product_id, name, price) VALUES (5, 'New Product', 23.11);
COMMIT;

无法插入

在这里插入图片描述

在这个事务中，我们使用LOCK IN SHARE MODE语句获取了产品ID为5的共享锁，以确保其他事务可以同时读取相同的产品信息，但是不能进行写操作。

事务2：插入新的产品

在这里插入图片描述

START TRANSACTION;
-- 获取间隙锁，防止其他事务在该范围内插入新的产品
SELECT * FROM products WHERE product_id > 4 AND product_id < 6 FOR UPDATE;
-- 插入新的产品
INSERT INTO products (product_id, name, price) VALUES (5, 'New Product', 10.99);
COMMIT;

无法插入

在这里插入图片描述

在这个事务中，我们使用FOR UPDATE语句获取了产品ID大于4且小于6的间隙锁，以防止其他事务在这个范围内插入新的产品。然后，我们向产品表中插入了一个新的产品。

锁升级

在MySQL中，锁升级是指事务在执行过程中，将当前持有的锁从低级别升级到更高级别的过程。这个过程通常是为了保证数据的完整性和一致性，同时尽量减少锁的持有时间，以提高并发性能。

MySQL中的锁升级有时是隐式的，有时需要显式操作。以下是一些锁升级的例子，以及相应的SQL语句：

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),category_id INT,price DECIMAL(10, 2),quantity INT
);CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,customer_id INT,product_id INT,quantity INT,order_date DATETIME,
);

  1. 从行级锁升级到表级锁：

    例子：

    START TRANSACTION;
SELECT * FROM products WHERE category_id = 1 FOR UPDATE;
-- 此时事务持有了行级锁-- 执行一些操作，需要将行级锁升级到表级锁
UPDATE products SET price = price * 0.9 WHERE category_id = 1;COMMIT;

    在这个例子中，事务开始时使用SELECT ... FOR UPDATE语句获取了category_id为1的产品行的排他锁，随后执行更新操作需要将行级锁升级为表级锁。

  2. 从共享锁升级到排他锁：

    例子：

    START TRANSACTION;
SELECT * FROM products WHERE category_id = 1 LOCK IN SHARE MODE;
-- 此时事务持有了共享锁-- 执行一些操作，需要将共享锁升级到排他锁
UPDATE products SET quantity = quantity - 1 WHERE category_id = 1;COMMIT;

    在这个例子中，事务开始时使用LOCK IN SHARE MODE语句获取了category_id为1的产品行的共享锁，但在后续执行中需要对该行进行修改，因此需要将共享锁升级为排他锁。

  3. 从意向锁升级到表级锁：

    例子：

    START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 100 FOR UPDATE;
-- 此时事务持有了意向排他锁-- 执行一些操作，需要将意向锁升级为表级锁
DELETE FROM orders WHERE customer_id = 100;COMMIT;

    在这个例子中，事务开始时使用SELECT ... FOR UPDATE语句获取了customer_id为100的订单行的意向排他锁，后续执行了删除操作，需要将意向锁升级为表级锁。

这篇关于MySQL-锁：共享锁（读）、排他锁（写）、表锁、行锁、意向锁、间隙锁，锁升级的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/793406。

相关文章

MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...
MySQL中的锁机制详解之全局锁,表级锁,行级锁

MySQL中的锁机制详解之全局锁,表级锁,行级锁

《MySQL中的锁机制详解之全局锁,表级锁,行级锁》MySQL锁机制通过全局、表级、行级锁控制并发,保障数据一致性与隔离性,全局锁适用于全库备份,表级锁适合读多写少场景,行级锁(InnoDB)实现高并... 目录一、锁机制基础：从并发问题到锁分类1.1 并发访问的三大问题1.2 锁的核心作用1.3 锁粒度分
阅读更多...
MySQL数据库中ENUM的用法是什么详解

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面：本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1
阅读更多...
MySQL count()聚合函数详解

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式？总结深入剖析一下 mysql 中的 COUNT
阅读更多...
mysql中的服务器架构详解

mysql中的服务器架构详解

《mysql中的服务器架构详解》：本文主要介绍mysql中的服务器架构,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、mysql服务器架构解释3、总结1、背景简单理解一下mysqphpl的服务器架构。2、mysjsql服务器架构解释mysql的架
阅读更多...
MySQL之InnoDB存储引擎中的索引用法及说明

MySQL之InnoDB存储引擎中的索引用法及说明

《MySQL之InnoDB存储引擎中的索引用法及说明》：本文主要介绍MySQL之InnoDB存储引擎中的索引用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐... 目录1、背景2、准备3、正篇【1】存储用户记录的数据页【2】存储目录项记录的数据页【3】聚簇索引【4】二
阅读更多...
mysql中的数据目录用法及说明

mysql中的数据目录用法及说明

《mysql中的数据目录用法及说明》：本文主要介绍mysql中的数据目录用法及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、版本3、数据目录4、总结1、背景安装mysql之后，在安装目录下会有一个data目录，我们创建的数据库、创建的表、插入的
阅读更多...
MySQL中的InnoDB单表访问过程

MySQL中的InnoDB单表访问过程

《MySQL中的InnoDB单表访问过程》：本文主要介绍MySQL中的InnoDB单表访问过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、背景2、环境3、访问类型【1】const【2】ref【3】ref_or_null【4】range【5】index【6】
阅读更多...
MySQL 中 ROW_NUMBER() 函数最佳实践

MySQL 中 ROW_NUMBER() 函数最佳实践

《MySQL中ROW_NUMBER()函数最佳实践》MySQL中ROW_NUMBER()函数,作为窗口函数为每行分配唯一连续序号,区别于RANK()和DENSE_RANK(),特别适合分页、去重... 目录mysql 中 ROW_NUMBER() 函数详解一、基础语法二、核心特点三、典型应用场景1. 数据分
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2