12-锁.md

锁

---

介绍

• 锁是计算机协调多个进程或线程并发访问某一个资源的机制
• 在数据库中, 除传统的计算资源(CPU, RAM, I/O)的争用外, 数据也是一种供许多用户共享的资源
• 如何保证数据并发访问的一致性, 有效性是所有数据库必须解决的一个问题
• 锁重提也是影响数据库并发访问性能的一个重要因素

分类

• 按粒度分
  • 全局锁
    • 对整个数据库实例加锁, 之后整个实例处于只读状态, 后续的 DDL, DML 语句, 已经更新操作的事务提交语句都将被阻塞
    • 全库的备份
      • flush tables with read lock;
      • mysqldump -uroot -p123456 数据库 > 数据库.sql
      • unlock tables;
    • 特点
      • 数据库加全局锁, 是一个比较重的操作, 存在以下问题
        • 主库上备份, 期间无法执行更新, 业务基本停摆
        • 从库上备份, 期间从库不能执行主库同步过来的二进制日志,(binlog), 会导致主从延迟
      • 在 InnoDB 引擎中, 备份时加上参数--single-transaction 参数来完成不加锁的一致性数据备份
  • 表级锁
    • 每次操作锁住整张表, 锁定粒度大, 发生锁冲突的概率高, 并发度低, 应用在 MyISAM, InnoDB, BDB 存储引擎中
    • 分类
      • 表锁
        • 类型
          • 表共享读锁(表锁共享锁, read lock)
          • 表独占写锁(表锁排他锁, write lock)
        • lock tables 表名 read/write
        • unlock tables;
      • 元数据锁(meta data lock, MDL)
        • MDL 加锁的过程是系统自动控制的, 无需显式使用, 在访问一张表的时候会自动加上
        • 主要作用是维护表元数据的一致性, 在表上有活动事务的时候, 不可以对元数据进行写入操作
        • 为了避免 DML 与 DDL 冲突, 保证读写的正确性
        • 在 MySQL5.5 中引入 MDL, 当对一张表进行增删改查时, 加 DML 读锁(共享)
        • 当对表结进行变更操作时, 加 DML 写锁(排他)
      • 意向锁
        • 为了避免 DML 在执行时, 加的行锁与表锁的冲突,在 InnoDB 中引入了意向锁, 使得表锁不用检查每行数据是否加锁, 使用意向锁来减少表锁的检查
        • 类型
          • 意向共享锁(IS)
            • select ... lock in share mode
            • 与表锁共享锁兼容, 与表锁排他锁互斥
          • 意向排他锁(IX)
            • insert, update, delete, select ... for update
            • 与表锁共享锁, 表锁排他锁互斥, 意向锁之间不会互斥
  • 行级锁
    • 每次操作所著对应的行数据, 锁定粒度小, 发生冲突的概率最低, 并发度最高, 应用在 InnoDB 引擎中
    • InnoDB 的数据是基于索引组织的, 行锁是通过对索引上的索引项加锁来实现的, 而不是对记录加的锁
    • 分类
      • 行锁(Record Lock):
        • 锁定单个行记录的锁, 防止其他事务对此行进行 update/delete, 在 RC, RR 隔离级别下都支持
      • 间隙锁(Gap Lock):
        • 锁定索引记录间隙(不含该记录), 确保索引记录间隙不变, 防止其他事务在这个间隙 insert, 产生幻读, 在 RR 隔离级别下都支持
      • 临键锁(Next-Key Lock):
        • 行锁和间隙锁组合, 同时锁住数据, 并锁住数据前面的间隙 Gap, 在 RR 隔离级别下支持
    • 行锁
      • 类型
        • 共享锁(S):
          • 允许一个事务去读取一行, 阻止其他事务获得相同数据集的排他锁
        • 排他锁(X)
          • 允许获取排他锁的事务更新数据, 阻止其他事务获得相同数据集的共享锁和排他锁
        • 默认情况下, InnoDB 在 Repeatable Read 事务隔离级别运行, InnoDB 使用 next-key 锁进行搜索和扫描, 以防止幻读
        • 针对唯一索引进行检索时, 对已存在的记录进行等值匹配时, 将会自动优化为行锁
        • InnoDB 的行锁是针对于索引加的锁, 不通过索引条件(或索引失效)检索数据, 那么会升级为表锁(会全表扫描, 对每一条记录加锁)
    • 间隙锁/临键锁
      • 默认情况下, InnoDB 在 Repeatable Read 事务隔离级别运行, InnoDB 使用 next-key 锁进行搜索和扫描, 以防止幻读
      • 索引上的等值查询(唯一索引), 给不存在的记录加锁时, 优化为间隙锁
      • 索引上的等值查询(普通索引), 向右遍历时最后一个值不满足需求时, next-key 锁退化为间隙锁
      • 索引上的等值查询(唯一索引), 会访问到不满足条件的第一个值为止
      • 间隙锁唯一目的时防止其他事务插入间隙, 间隙锁可以共存, 一个事务蚕蛹的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

---

• 表级锁

SQL	锁类型	描述
lock table ... 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 都互斥

• 行级锁

SQL	类型	描述
insert, update, delete ...	排他锁	自动
select ...	不加锁
select ... in share mode	共享锁	手动
select ... for update	排他锁	手动

-- 查看锁
use `java_basic`;

select `OBJECT_TYPE`, `OBJECT_SCHEMA`, `OBJECT_NAME`, `LOCK_TYPE`, `LOCK_DURATION`
from `performance_schema`.`metadata_locks`
where `OBJECT_SCHEMA` = 'java_basic';

select `OBJECT_SCHEMA`, `OBJECT_NAME`, `INDEX_NAME`, `LOCK_TYPE`, `LOCK_MODE`, `LOCK_DATA`
from `performance_schema`.`data_locks`;

delete
from `student`
where `id` >= 4
  and `id` <= 8;
update `student`
set `age` = 100
where `id` = 9;
create index `idx_student_age` on `student` (`age`);
show index from `student`;

-- 表级锁
-- SHARED_READ_ONLY
lock tables `student` read;
unlock tables;

-- SHARED_NO_READ_WRITE
lock tables `student` write;
unlock tables;

-- 元数据锁
-- SHARE_READ
start transaction;
select *
from `student`
where `id` = 1;
commit;

-- SHARED_WRITE
start transaction;
update `student`
set `age` = `age` + 1
where `id` = 1;
commit;

-- 意向锁
-- TABLE IS
start transaction;
select *
from `student`
where `id` = 1 lock in share mode;
commit;

-- TABLE IX
start transaction;
update `student`
set `age` = `age` + 1
where `id` = 1;
commit;

-- 行级锁
-- RECORD S,REC_NOT_GAP
start transaction;
select *
from `student`
where `id` = 1 lock in share mode;
commit;

-- RECORD X,REC_NOT_GAP
start transaction;
update `student`
set `age` = `age` + 1
where `id` = 1;
commit;

-- 全表扫描, 对所有的记录加锁
start transaction;
update `student`
set `name` = 'stu0001'
where `name` = 'student0001';
commit;

-- 间隙锁 RECORD X,GAP 9
start transaction;
update `student`
set `name` = 'lock'
where `id` = 6;
commit;

-- 临键锁 RECORD S,GAP 20
start transaction;
select *
from `student`
where `age` = 19 lock in share mode;
commit;

-- RECORD S supremum pseudo-record RECORD S 100, 9 RECORD S,REC_NOT_GAP 9
start transaction;
select *
from `student`
where `age` >= 50 lock in share mode;
commit;