重磅干货：多维度深入总结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)，会自动给涉及到的表加写锁。这个过程其实不需要用户干预，当然如果我们想看到读锁和写锁的效果，我们也可以显示出加锁的语法：

#加读锁：
lock table table_name read;

#加写锁：
lock table table_name write;

1. 数据准备

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

1.1 准备数据表

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

CREATE TABLE tb_book(
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(30),
  publish_time DATE,
  `status` CHAR(1)
)ENGINE=MYISAM CHARSET=utf8

INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'天龙八部','2001-8-1','1');
INSERT INTO `day05`.`tb_book`(`id`,`name`,`publish_time`,`status`) VALUES ( NULL,'射雕英雄传','2003-10-10','0');

CREATE TABLE tb_user(
   id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(30)
)ENGINE=MYISAM CHARSET=utf8

INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'令狐冲');
INSERT INTO `day05`.`tb_user`(`id`,`name`) VALUES ( NULL,'田伯光');

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

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

2.1 客户端1

获取tb_book表的读锁：

lock table tb_book read;

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

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

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

2.2 客户端2

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

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

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

2.3 查询未锁定的表

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

mysql> select * from tb_user;

ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

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

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

mysql> select * from tb_user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 令狐冲    |
|  2 | 田伯光    |
+----+-----------+
2 rows in set (0.00 sec)

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

2.4 进行写操作

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

mysql> insert into tb_book(name,publish_time,status) values('碧血剑','2000-12-1',1);

ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated
mysql> insert into tb_user(name) values('岳不群');
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

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

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

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

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

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

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

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

Query OK, 1 row affected (13.67 sec)

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

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

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

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

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

这里会发现查询成功。

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

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

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

然后在客户端1修改tb_book：

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

我们发现修改成功。

接着在客户端1修改tb_user：

mysql> update tb_user set name='岳不群' where id = 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：

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

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

mysql> select * from tb_user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 令狐冲    |
|  2 | 田伯光    |
+----+-----------+
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操作：

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

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

四. 基于InnoDB支持的行锁

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

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

CREATE TABLE test_innodb_lock(
   id INT,
   `name` VARCHAR(20),
   sex VARCHAR(5)
)ENGINE=INNODB CHARSET=utf8

2.准备2个客户端

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

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

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

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

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

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

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

3.更新操作

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

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

3.1 在窗口1执行更新操作

mysql> update test_innodb_lock set name = 'sunny' where id = 1;
Query OK, 1 row affected (0.00 sec)
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的数据

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

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

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

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

五. 总结

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