Linux系统——Mysql数据库锁的拓展

一、锁的概述

二、锁的分类

1.按锁粒度分类

2.按性能分类

3.按对数据库操作类型

三、全局锁

1.定义

2.操作

3.特点

四、表级锁

1.表级锁分类

2.表锁分类

2.1表共享读锁（read lock）

2.2表独占写锁（write lock）

3.元数据锁

3.1元数据锁定义

3.2操作

五、意向锁

1.意向锁定义

2.意向锁分类

六、行级锁

1.定义

2.行级锁分类

2.1行锁

2.2间隙锁

2.3临键锁

3.总结

一、锁的概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

二、锁的分类

1.按锁粒度分类

粒度：即细化的程度。被封锁的对象的粒度。例如数据项、记录、文件或整个数据库，锁粒度越小事务的并行度越高。

全局锁：锁定数据库中所有的表。
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据。

表级锁：表级锁是对整张表进行锁定，它是mysql最基本的锁策略，所有存储引擎都支持。表锁开销最小，粒度最大，发生锁冲突的概率最高，所以并发度最低，可以很好的避免死锁问题。

行级锁：也称为记录锁。行级锁是最细粒度的锁，它只锁定数据表中的某一行数据。InnoDB引擎才支持行级锁。行级锁开销大，粒度最小，发生锁冲突概率低，所以并发度最高，容易产生死锁。

页级锁：页级锁是最粗粒度的锁，它锁定整个数据页面，即数据库中存储数据的最小单位。在使用页级锁时，如果有多个用户想要访问同一个页面，则只有一个用户能够获得锁定，其他用户要么等待，要么被阻塞。页级锁也会产生死锁。BDB引擎支持页级锁。

2.按性能分类

乐观锁（用版本对比来实现）
悲观锁（读锁和写锁）

3.按对数据库操作类型

读锁（共享锁）针对某一份数据，多个读操作可以同时进行而不会相互影响，写则等待读的完成
写锁（排它锁）当前写操作没有完成前，它会阻断其他写锁和读锁

三、全局锁

1.定义

全局锁就是对数据库的整个实例加锁，加锁之后整个实例就处于只读状态，后续的DML写语句，DDL语句，以及更新操作的事务提交语句都会被阻塞，全局锁的典型使用场景就是进行全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

为什么进行全库备份时加全局锁

假设数据库中存在着三张表 , tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表

在进行数据备份时：

先备份了 tb _ stock 表
然后接下来, 在业务系统中,执行了下单操作,扣减库存, 生成订单,(更新 tb_stock 表, 插入 tb _ order 表)
然后再执行备份 tb_order 表的逻辑
业务中执行插入订单日志的操作
最后 , 备份了 tb_orderlog 表

在上述的执行过程中, 备份出来的数据是存在问题的 , 因为备份出来的数据 , tb_stock 表和 tb_order 表存在着数据不一致的问题, 有最新的订单信息 , 但是总的库存数没有发生变化

在进行数据库的逻辑备份之前, 先对数据库加上全局锁,一旦加上全局锁之后,其他的DDL,DML全部都处于阻塞状态，但是可以执行DQL语句,也就是只读状态,而数据备份就是查询操作, 那么在数据备份的过程中 , 数据库中的数据是不会发生变化的,这样就保证了数据的完整性和一致性。

2.操作

flush tables with read lock;
#加全局锁

mysqldump -uroot –p1234 itcast > itcast.sql
#数据备份

unlock tables;
#释放锁

3.特点

数据库中如果加全局锁 , 是一个粒度比较重的操作, 容易存在以下问题:

如果在主库上进行备份,那么在备份期间都不能执行更新操作,业务基本就处于停摆
如果在从库上进行备份,那么在备份期间,从库不能执行主库同步过来的二进制文件 ,就会导致主从延迟.

在 InnoDB 引擎中, 可以通过备份时增加一个参数来完成不加锁的数据一致性备份

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

四、表级锁

表级锁 , 顾名思义,每次操作能够锁住整张表, 锁定粒度大,发生锁冲突概率较高,并发度最低 , 通常应用在 InnoDB , MyISAM, BDB 等引擎当中, 此处我们只对 InnoDB 中的表级锁进行详解。

1.表级锁分类

表锁
元数据锁
意向锁

2.表锁分类

表共享读锁(read lock)
表独占写锁(write lock)

2.1表共享读锁（read lock）

特点 : 对指定表加了读锁之后,当客户端一进行读操作时,不会影响客户端二的读,但是都会阻塞两哥客户端的写操作。

2.2表独占写锁（write lock）

针对指定表增加了独占写锁之后, 客户端一可以针对表进行读和写, 而客户端二的读和写就会被阻塞

语法：

加锁：lock tables 表名... read/write。
释放锁：unlock tables / 客户端断开连接。

总结：读锁不会阻塞其他客户端的读 , 但是会阻塞写 , 写锁既会阻塞其他客户端的读,又会阻塞其他客户端的写。

3.元数据锁

3.1元数据锁定义

元数据锁，简写MDL。MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

对应SQL	锁类型	说明
lock tables xxx read/write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select、select...lock in share mode	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert、update、delete、select ... for update	SHARED_WRITE	与SHARE_READ、SHARE_WRITE兼容，与EXCLUSIVE互斥
alter table ...	EXCLUSIVE	与其他的MDL都互斥

如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，
那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

共享锁：允许多个事务同时持有该锁，用于读取数据时，避免其他事务对数据进行修改。共享锁不会阻塞其他事务的共享锁请求，但会阻塞其他事务的独占锁和排他锁请求。

独占锁：只允许一个事务持有该锁，用于修改数据时，防止其他事务同时修改同一数据。独占锁会阻塞其他事务的共享锁和独占锁请求，但不会阻塞其他事务的排他锁请求。

排他锁：只允许一个事务持有该锁，用于修改数据时，防止其他事务同时修改同一数据。排他锁会阻塞所有其他事务的锁请求，包括共享锁、独占锁和排他锁。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

3.2操作

#查看元数据锁的加锁情况


mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema      | object_name    | lock_type    | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_WRITE | TRANSACTION   |
| TABLE       | MySQL_Advanced     | user_logs      | SHARED_WRITE | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   |
+-------------+--------------------+----------------+--------------+---------------+
5 rows in set (0.00 sec)

五、意向锁

1.意向锁定义

为了避免在DML进行执行时, 加的行锁与表锁相互冲突, 在 InnoDB 引擎中引入了意向锁, 使得表锁不需要去检查每行是否加锁 .

假如没有意向锁的情况下 , 客户端一对表加了行锁之后, 客户端二如何给表进行加锁呢?

首先, 客户端一 , 开启一个事务, 然后执行 DML 操作, 在执行DML语句时, 会对涉及到的行进行加行锁。

当客户端二想要对这张表进行加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此时就会从第一行数据检查到最后一行 , 效率较低。

有了意向锁之后, 在执行DML操作时,会对涉及的行加上行锁,同时也会给该表加上意向锁。

而其他客户端对该表进行加表锁时,就可以根据是否存在意向锁来判断是否可以成功加锁。

2.意向锁分类

意向共享锁 : 与表锁共享锁兼容, 与表锁排他锁互斥
意向排他锁: 与表锁排他锁和表锁共享锁都互斥

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

一旦事务提交了, 意向共享锁与意向排他锁都会释放

执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁 , 这样就可以快速判断表中是否有记录加锁.

六、行级锁

1.定义

每次操作锁住对应的行数据，锁定的粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB 引擎中

InnoDB 引擎与 MySlAM 引擎的三大区别：事务外键行锁

2.行级锁分类

行锁（Record Lock）
间隙锁
临键锁

2.1行锁

锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X）：允许排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

-- 会话 1
BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 对 id=1 的数据行进行修改
UPDATE orders SET amount = amount + 100 WHERE id = 1;
COMMIT;
 
-- 会话 2
BEGIN;
-- 因为会话 1 对 id=1 的数据行进行了行锁，所以会话 2 不能同时对该行进行修改，
-- 如果执行下面的语句会一直等待会话 1 的事务提交或回滚
UPDATE orders SET amount = amount - 50 WHERE id = 1;

2.2间隙锁

间隙锁：指对一个索引范围中的“空隙”进行锁定，防止其他事务在这个范围内插入新数据。间隙锁用于解决幻读问题。

例如在某个事务中执行了一个范围查询，然后在范围内的间隙处插入了新数据，这时再次执行相同的查询，会发现有一些行出现了两次，这就是幻读。通过间隙锁，可以防止其他事务插入新的数据，从而避免幻读。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

-- 会话 1
BEGIN;
-- 对 id 大于 1 小于 10 的范围进行间隙锁定
SELECT * FROM orders WHERE id > 1 AND id < 10 FOR UPDATE;
-- 间隙锁会阻止其他事务在 id 大于 1 小于 10 的范围内插入数据，
-- 但允许其他事务在该范围之外的位置插入数据
COMMIT;
 
-- 会话 2
BEGIN;
-- 因为会话 1 对 id 大于 1 小于 10 的范围进行了间隙锁定，所以会话 2 不能在该范围内插入数据，
-- 如果执行下面的语句会一直等待会话 1 的事务提交或回滚
INSERT INTO orders (id, amount) VALUES (5, 500);