MySql之锁的详解（干货请备好咖啡）

1.锁的概念

在数据库中，除传统的计算资源（如 CPU 、 RAM 、 I/O 等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制 ，因此产生了数据库锁 。

加锁的目的是什么？

数据库的锁是为了解决事务的隔离性问题，为了让事务之间相互不影响。

如果你想一个人静一静，不被别人打扰，那么请在你的房门上加上一把锁。

2.锁的分类

类型	说明
全局锁	对整个数据库加锁
表级锁	每次操作锁住整张表，分为表锁、意向锁、元数据锁以及AUTO-INC锁
行级锁	每次操作锁住对应的行数据，分为记录锁、间隙锁以及临键锁

3.全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句MDL（数据的增删改）；
• 数据定义语句DDL（包括建表、修改表结构等）；
• 更新类事务的提交语句。

全局锁的命令：

Flush tables with read lock

执行后，整个数据库就处于只读状态了。如果需要释放锁请执行命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态。

那么如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

• 案例一：

-- 会话窗口1，操作如下：
-- 1.打开一个数据库连接，加全局锁
flush tables with read lock;
-- 4.释放锁
unlock tables;
-- 会话窗口2，操作如下：
-- 2.查询表
select * from mylock;
-- 3.执行删除语句，失败
delete from mylock where id = 1;
-- 5.再次执行删除语句，成功
delete from mylock where id = 1;

注意：上述案例请按照序号执行。

4.表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、

InnoDB、BDB等存储引擎中。MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

4.1.表锁

表锁由 MySQL Server 实现，一般在执行DDL语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。命令如下：

-- 表级别的共享锁，也就是读锁；
lock tables 表名 read;
-- 表级别的独占锁，也就是写锁；
lock tables 表名 write;

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对某表加了「共享表锁」，那么本线程接下来如果要对某表执行写操作的语句，是会被阻塞的，当然其他线程对某表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

• 案例一：表级共享读锁

-- 会话1窗口，操作如下：
-- 1.加表级共享读锁
lock tables mylock read;
-- 2.查询数据
select * from mylock;
-- 3.插入数据
-- 1099 - Table 'mylock' was locked with a READ lock and can't be updated
insert into mylock values(1,'小强');
-- 9.释放锁
unlock tables;
-- 会话2窗口，操作如下：
-- 4.在会话2窗口再次加表级共享读锁，成功
lock tables mylock read;
-- 5.查询数据
select * from mylock;
-- 6.新增数据
-- 1099 - Table 'mylock' was locked with a READ lock and can't be updated
insert into mylock values(1,'小明');
-- 7.释放锁
unlock tables;
-- 8.再次新增数据，由于会话1窗口有表级读锁，所以处于等待状态
insert into mylock values(1,'小明');

注意：上述案例请按照序号执行。

• 案例二：表级独占写锁

-- 会话1窗口，操作如下：
-- 1.加表级排他锁
lock tables mylock write;
-- 2.当前会话被锁定的表执行查询操作被阻塞
-- select * from mylock;
-- 3.执行修改操作，OK
update mylock set name = '小红' where id