• 重磅干货:多维度深入总结MySQL锁机制


    一. 前言

    有过面试经历的小伙伴都知道,现在面试Java开发工作,数据库几乎是你绕不过去的一道坎。就以文哥班上的学员为例,几乎每个学员的每场面试都会被问到数据库的问题。文哥很用心地给大家总结了一下,数据库基本都是从以下几个维度来考察求职者的:索引、锁、SQL优化。

    那么今天,文哥就给大家聊聊MySQL锁的问题,希望给正在求职或即将毕业的小伙伴带来一些帮助。

    二. MySQL锁的概述

    1. 什么是锁

    文哥是这么定义锁的:

    在数据库中,数据是一种可能会被许多用户共享的资源。在多个用户共享数据的时候,如何保证数据并发访问时的一致性、有效性,这是数据库必须要解决的问题,此时锁对数据库而言就是一个非常好的保障机制。

    2. MySQL的锁分类

    根据锁的操作粒度,我们可以把锁分为:

    • 表锁:操作时,会锁定整张表的数据;

    • 行锁:操作时,只会锁定当前操作的行数据。

    从对数据的读写角度,可以把锁分为:

    • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行,而互不影响;

    • 写锁(排它锁):当前操作没有完成之前,它会阻断其他的读锁和写锁。

    相对于其他数据库而言,MySQL不同的存储引擎可以支持不同的锁机制。

    存储引擎表级锁行级锁

    MYISAM

    支持

    不支持

    INNODB

    支持

    支持

    MEMORY

    支持

    不支持

    DBD

    支持

    不支持

    3. 锁的特性

    那么这些锁又有哪些特性呢?文哥给大家做了如下总结:

    表锁:偏向于MYISAM存储引擎。开销小,加锁快,不会出现死锁的问题。由于锁的粒度大,出生锁冲突的概率就越高,并发度很低。
    行锁:InnoDB默认支持行锁。开销大,加锁慢,可能会出现死锁的问题。由于锁的粒度小,出现锁冲突的概率就越低,并发度高。

    通过以上描述可知,不同的存储引擎,对锁的支持是不一样的。接下来文哥就以最常见的存储引擎myisam和innodb为例,给大家聊聊锁的实现机制。

    三. 基于MyISAM引擎的表锁

    MYISAM只支持表锁,那么如何给表加表锁呢?MYISAM存储引擎的表,在执行select语句的时候,MySQL会自动给涉及到的表加读锁。在执行更新操作时(insert update delete),会自动给涉及到的表加写锁。这个过程其实不需要用户干预,当然如果我们想看到读锁和写锁的效果,我们也可以显示出加锁的语法:

    1. #加读锁:
    2. lock table table_name read;
    3. #加写锁:
    4. lock table table_name write;

    1. 数据准备

    为了方便后面的案例测试,我们先准备一些必要的数据表。

    1.1 准备数据表

    这里我们先准备一个数据表:

    1. CREATE TABLE tb_book(
    2.   id INT PRIMARY KEY AUTO_INCREMENT,
    3.   `name` VARCHAR(30),
    4.   publish_time DATE,
    5.   `status` CHAR(1)
    6. )ENGINE=MYISAM CHARSET=utf8
    7. INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'天龙八部','2001-8-1','1');
    8. INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'射雕英雄传','2003-10-10','0');
    9. CREATE TABLE tb_user(
    10.    id INT PRIMARY KEY AUTO_INCREMENT,
    11.     `name` VARCHAR(30)
    12. )ENGINE=MYISAM CHARSET=utf8
    13. INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'令狐冲');
    14. INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'田伯光');

    2.基于MyISAM存储引擎的读锁案例

    这里我们分别打开两个操作数据库的客户端。

    2.1 客户端1

    获取tb_book表的读锁:

    lock table tb_book read;

    在客户端1执行tb_book表的查询操作:

    1. mysql> select * from tb_book;
    2. +----+-----------------+--------------+--------+
    3. | id | name            | publish_time | status |
    4. +----+-----------------+--------------+--------+
    5. |  1 | 天龙八部        | 2001-08-01   | 1      |
    6. |  2 | 射雕英雄传      | 2003-10-10   | 0      |
    7. +----+-----------------+--------------+--------+
    8. 2 rows in set (0.00 sec)

    我们发现,这时可以正常查询数据。

    2.2 客户端2

    然后我们再次执行tb_book表的查询操作。

    1. mysql> select * from tb_book;
    2. +----+-----------------+--------------+--------+
    3. | id | name            | publish_time | status |
    4. +----+-----------------+--------------+--------+
    5. |  1 | 天龙八部        | 2001-08-01   | 1      |
    6. |  2 | 射雕英雄传      | 2003-10-10   | 0      |
    7. +----+-----------------+--------------+--------+
    8. 2 rows in set (0.00 sec)

    由于读锁是共享锁,所以共享锁作用于整张表,两个客户端都可以访问。那么如果我们是在操作读锁没有起作用的表,会怎么样呢?

    2.3 查询未锁定的表

    我们再次回到客户端1,查询未被锁定的表,结果如下:

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

    这是因为,我们读锁操作的是tb_book表,但这个读锁并没有释放,所以不能操作锁之外的其他表。举个例子,我们在12306上面买票,如果我们没有付款,是不能购买其他票的。

    如果我们再回到客户端2,然后去查询tb_user表,你会发现此时是可以查询到的。

    1. mysql> select * from tb_user;
    2. +----+-----------+
    3. | id | name      |
    4. +----+-----------+
    5. |  1 | 令狐冲    |
    6. |  2 | 田伯光    |
    7. +----+-----------+
    8. 2 rows in set (0.00 sec)

    因为客户端2没有拿到客户端1的锁,所以可以正常查询没有被锁定表的数据。

    2.4 进行写操作

    接下来我们再看看在MyISAM中如何进行写操作,首先我们在客户端1执行写操作:

    1. mysql> insert into tb_book(name,publish_time,statusvalues('碧血剑','2000-12-1',1);
    2. ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated
    3. mysql> insert into tb_user(name) values('岳不群');
    4. ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

    我们发现插入时会直接报错,这是因为当前客户端获得是读锁,不能执行更新操作。然后在客户端2中执行写操作:

    mysql> insert into tb_book(name,publish_time,statusvalues('鹿鼎记','2000-12-1',1);

    此时光标一直处于闪烁状态,当客户端1释放锁之后,客户端2里面的数据才会插入成功。

    我们在客户端1执行释放锁的命令:

    mysql> unlock tables; -- 释放锁的命令

    此时再看看客户端2插入的数据如下:

    1. mysql> insert into tb_book(name,publish_time,statusvalues('鹿鼎记','2000-12-1',1);
    2. Query OK, 1 row affected (13.67 sec)

    3. 基于MyISAM存储引擎的写锁案例

    我们先在客户端1获取tb_book的写锁

    mysql> lock table tb_book write; -- 给tb_book上写锁

    然后在客户端1查询tb_book表。

    1. mysql> select * from tb_book;
    2. +----+-----------------+--------------+--------+
    3. | id | name            | publish_time | status |
    4. +----+-----------------+--------------+--------+
    5. |  1 | 天龙八部        | 2001-08-01   | 1      |
    6. |  2 | 射雕英雄传      | 2003-10-10   | 0      |
    7. |  3 | 碧血剑          | 2000-12-01   | 1      |
    8. |  4 | 鹿鼎记          | 2000-12-01   | 1      |
    9. +----+-----------------+--------------+--------+
    10. 4 rows in set (0.00 sec)

    这里会发现查询成功。

    接着我们继续在客户端1查询tb_user

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

    此时查询失败,因为这个表不再写锁的范围内。

    然后在客户端1修改tb_book

    1. mysql> update tb_book set name='连城诀' where id=4;
    2. Query OK, 1 row affected (0.00 sec)
    3. Rows matched: 1  Changed: 1  Warnings: 0

    我们发现修改成功。

    接着在客户端1修改tb_user

    1. mysql> update tb_user set name='岳不群' where id = 2;
    2. ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

    修改失败,因为写锁不作用于这张表。

    我们再回到客户端2在客户端2执行查询tb_book的操作。

    mysql> select * from tb_book;

    此时光标一直处于等待状态,只有客户端1释放写锁,客户端2才会查询到数据。

    然后我们在客户端1释放写锁:

    mysql> unlock tables; -- 释放锁的命令

    回到客户端2:

    1. mysql> select * from tb_book;
    2. +----+-----------------+--------------+--------+
    3. | id | name            | publish_time | status |
    4. +----+-----------------+--------------+--------+
    5. |  1 | 天龙八部        | 2001-08-01   | 1      |
    6. |  2 | 射雕英雄传      | 2003-10-10   | 0      |
    7. |  3 | 碧血剑          | 2000-12-01   | 1      |
    8. |  4 | 连城诀          | 2000-12-01   | 1      |
    9. +----+-----------------+--------------+--------+
    10. 4 rows in set (11.72 sec)

    在客户端2,我们可以直接查询tb_user因为客户端1的写锁只作用域tb_book。

    1. mysql> select * from tb_user;
    2. +----+-----------+
    3. | id | name      |
    4. +----+-----------+
    5. |  1 | 令狐冲    |
    6. |  2 | 田伯光    |
    7. +----+-----------+
    8. 2 rows in set (0.00 sec)

    在客户端1加写锁,在客户端2执行更新tb_book操作

    mysql> update tb_book set name='倚天屠龙记' where id = 2;

    此时光标会一直闪烁,只有客户端1释放写锁,客户端2才会更新数据成功。

    在客户端1加写锁,在客户端2执行更新tb_user操作

    1. mysql> update tb_user set name='岳不群' where id = 2;
    2. Query OK, 1 row affected (0.00 sec)
    3. Rows matched: 1  Changed: 1  Warnings: 0

    在客户端2,我们可以直接更新tb_user。因为客户端1的写锁只作用域tb_book。

    四. 基于InnoDB支持的行锁

    和MYISAM存储引擎一样,INNODB存储引擎实现了读锁和写锁这两种锁。对应更新的操作(insert update delete),INNODB会自动给涉及的数据加上写锁。对于select语句,innodb不会加任何锁。

    1.首先我们准备一张数据表

    1. CREATE TABLE test_innodb_lock(
    2.    id INT,
    3.    `name` VARCHAR(20),
    4.    sex VARCHAR(5)
    5. )ENGINE=INNODB CHARSET=utf8

    2.准备2个客户端

    2.1 在客户端1和客户端2,分别关闭事务的自动提交功能。

    mysql> set autocommit = 0;在窗口1执行数据查询的操作

    2.2 在窗口1执行数据查询的操作

    1. mysql> select * from test_innodb_lock;
    2. +------+--------+------+
    3. | id   | name   | sex  |
    4. +------+--------+------+
    5. |    1 | eric   | 1    |
    6. |    2 | lily   | 1    |
    7. |    3 | james  | 0    |
    8. |    4 | durant | 1    |
    9. +------+--------+------+
    10. 4 rows in set (0.00 sec)

    2.3 在窗口2执行数据查询的操作

    1. mysql> select * from test_innodb_lock;
    2. +------+--------+------+
    3. | id   | name   | sex  |
    4. +------+--------+------+
    5. |    1 | eric   | 1    |
    6. |    2 | lily   | 1    |
    7. |    3 | james  | 0    |
    8. |    4 | durant | 1    |
    9. +------+--------+------+
    10. 4 rows in set (0.00 sec)

    为什么两个窗口都可以查询到数据,是因为对于普通的select语句,innodb存储引擎不会加任何锁。

    3.更新操作

    接下来我们再来看看更新的操作。

    现在我们窗口1和窗口2更新的是同一行数据。

    3.1 在窗口1执行更新操作

    1. mysql> update test_innodb_lock set name = 'sunny' where id = 1;
    2. Query OK, 1 row affected (0.00 sec)
    3. Rows matched: 1  Changed: 1  Warnings: 0

    3.2 在窗口2对id为1的数据进行更新

    mysql> update test_innodb_lock set name = 'sunny' where id = 1;

    此时光标一直闪烁,只有窗口1提交事务了(commit),窗口2的数据才会真正修改完成。说明行锁生效了。

    接下来,我们演示窗口1和窗口2修改的不是同一条记录。

    3.3 窗口1修改id=3的数据

    1. mysql> update test_innodb_lock set name = 'sunny1' where id = 3;
    2. Query OK, 1 row affected (0.00 sec)
    3. Rows matched: 1  Changed: 1  Warnings: 0

    3.4 窗口2修改id=4的数据

    1. mysql> update test_innodb_lock set name = 'sunny1' where id = 4;
    2. Query OK, 1 row affected (0.00 sec)
    3. Rows matched: 1  Changed: 1  Warnings: 0

    由于操作的不是同一行,所以会修改成功。

    五. 总结

    今天文哥基于不同的存储引擎,给小伙伴们演示了不同存储引擎锁的特性,希望大家能够在下面按照文哥的思路逐行演练。

  • 相关阅读:
    【java】mybatis拦截器实现单表对于geometry类型字段的新增查询更新
    K8S部署dashboard
    深度解读GPT基本原理
    Mybatis整合spring
    Vue_Bug npm install报错 code:128
    【AI视野·今日NLP 自然语言处理论文速览 第六十五期】Mon, 30 Oct 2023
    使用acme.sh配置https证书
    Nginx常用模块
    kafka rebalance你真的了解吗
    《009.SpringBoot之汽车租赁系统》
  • 原文地址:https://blog.csdn.net/finally_vince/article/details/127731591