【MySQL进阶】——锁
-
- 1. 全局锁
- 2. 表级锁
-
- 2.1 表锁
- 2.2 元数据锁（MDL）
- 2.3 意向锁
- 3. 行级锁
-
- 3.1 行锁
- 3.2 间隙锁与临键锁
- 4. 总结
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
- MySQL中的锁，按照锁的粒度分，分为三类：
- 全局锁：锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁：每次操作锁住对应的行数据。
1. 全局锁
- 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 DML 的写语句，DDL 语句，已经更新操作的事务提交语句都将被阻塞。
- 其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
// 数据备份前先加上全局锁
flush tables with read lock;
// 数据备份
mysqldump -uroot-p1234 itcast>itcast.sql
// 释放锁
unlock tables;
2. 表级锁
- 表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM、InnoDB、BDB 等存储引擎中。
2.1 表锁
加锁：lock tables 表名… read/write。
释放锁：unlock tables / 客户端断开连接。
2.2 元数据锁（MDL）
- MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。
- 在MySQL5.5中引入了 MDL ，当对一张表进行增删改查的时候，加 MDL读锁（共享)；当对表结构进行变更操作的时候，加MDL写锁（排他)。（共享锁兼容）
| 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 | 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 |
| alter table … | EXCLUSIVE | 与其他的MDL都互斥 |
2.3 意向锁
- 为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
- 解决 InnoDB 引擎中表锁和行锁的冲突问题。
意向共享锁（IS）：由语句select…lock in share mode添加。 与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
意向排他锁（IX）：由insert、update、delete、select…for update添加。 与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。
3. 行级锁
- 行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。
- InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：
3.1 行锁
- InnoDB 实现了以下两种类型的行锁：
| S（共享锁） | 兼容 | 冲突 |
| X（排他锁） | 冲突 | 冲突 |
- 添加情况：
| INSERT … | 排他锁 | 自动加锁 |
| UPDATE … | 排他锁 | 自动加锁 |
| DELETE … | 排他锁 | 自动加锁 |
| SELECT （正常） | 不加任何锁 | |
| SELECT … LOCK IN SHARE MODE | 共享锁 | 需要手动在 SELECT 之后加 LOCK IN SHARE MODE |
| SELECT… FOR UPDATE | 排他锁 | 需要手动在 SELECT 之后加 FOR UPDATE |
- 默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。
- 1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
- 2.InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。
3.2 间隙锁与临键锁
- 默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。
注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
4. 总结
- 在并发访问时，解决数据访问的一致性、有效性问题。
- 全局锁、表级锁、行级锁。
- 对整个数据库实例加锁，加锁后整个实例就处于只读状态。
- 性能较差，数据逻辑备份时使用。
- 操作锁住整张表，锁定粒度大，发生锁冲突的概率高。
- 表锁、元数据锁、意向锁。
- 操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低。
- 行锁、间隙锁、临键锁。
