MySQL锁

知识库大约 5 分钟

按照锁的粒度来说，MySQL 主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个 database。由 MySQL 的 SQL layer 层实现的
表级锁：锁的是某个 table。由 MySQL 的 SQL layer 层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如 InnoDB。
按照锁的功能来说分为：共享锁和排他锁。

死锁：有两个或两个以上的资源的情况下，有两个事物互相持有了对方期待的资源，导致互相等待对方释放资源时，产生死锁。

全局锁

加全局锁，会导致数据库变成只读状态，一般不建议使用。

-- 加全局锁的命令
flush tables with read lock;
-- 释放全局锁，或者会话关闭，锁自动解除。
unlock tables;

MySQL 表级锁

表读、写锁。

表锁一般也不经常使用，粒度比较粗。

-- 查看表级锁定的争用状态变量
show status like 'table%';
-- table_locks_immediate：产生表级锁定的次数;
-- table_locks_waited：出现表级锁定争用而发生等待的次数;

-- 手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
-- 查看表锁情况
show open tables;
-- 删除表锁
unlock tables;

元数据锁（meta data lock，MDL)。

当事务未结束时，在事务中执行了 select 操作，不允许对表结构进行修改的。

-- （session1）开启事务
begin;
-- （session1）加MDL读锁
select * from users;
-- （session2）新开一个窗口，执行修改语句，会被阻塞，知道前面的事务释放
alter table users add age int;
-- （session1）提交或者回滚（rollback）事务
commit;

自增锁(AUTO-INC Locks)。

AUTO-INC 锁是一种特殊的表级锁，发生涉及 AUTO_INCREMENT 列的事务性插入操作时产生。

InnoDB 的行锁

记录锁（Record Locks）

锁定索引中一条记录。

-- 加共享锁：
select * from t where id=1 lock in share mode;
-- 加排它锁：
select * from t where id=1 for update;

意向锁（Intention Locks）

就是一个标志位，为了快速判断表上是否有行锁。

意向锁是表级锁。是InnoDB实现的，是行锁的一个副产品。
- 意向锁共享锁 IS：表中某个记录上有共享锁是添加。
- 意向排他锁 IX：表中某个记录有排他锁是添加。

间隙锁（Gap Locks）

区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）

间隙锁可用于防止幻读，保证索引间的不会被插入数据。

-- session1:
begin;
select * from t1_simple where id > 4 for update;
---------------------------------------------------------
-- session2:
insert into t1_simple values (7,100); --阻塞
insert into t1_simple values (3,100); --成功

临键锁（Next-Key Locks）

是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
- 记录锁：条件中根据主键等值查询，并且值存在时，只有记录锁。
- 间隙锁：条件中根据主键等值查询，并且值不存在时，只有间隙锁，锁等值所在的那个区间。
- 临键锁：条件中根据索引做范围查询，使用临键锁。
插入意向锁(Insert Intention Locks)

做 insert 操作时添加的对记录 id 的锁。

锁相关参数

show status like 'innodb_row_lock%';
-- Innodb_row_lock_current_waits：当前正在等待锁定的数量；
-- Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
-- Innodb_row_lock_time_avg：每次等待所花平均时间；
-- Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
-- Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

-- 查看事务、锁的sql
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;

行锁分析实战

select * from t1 where id = 10，这个 SQL 加什么锁？
答：因为 MySQL 是使用多版本并发控制的，读不加锁。
delete from t1 where id = 10，这个 SQL 加什么锁？
- 组合一：id 列是主键，RC 隔离级别
  
  答：id 是主键时，此 SQL 只需要在 id=10 这条记录上加 X 锁即可。
- 组合二：id 列是二级唯一索引（unique），RC 隔离级别
  
  答：加两个 X 锁，一个对应于 id unique 索引上的 id = 10 的记录，另一把锁对应于聚簇索引上的【name=’d’,id=10】的记录。
- 组合三：id 列是二级非唯一索引，RC 隔离级别
  
  答：对应的所有满足 SQL 查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
- 组合四：id 列上没有索引，RC 隔离级别
  
  答：若 id 列上没有索引，SQL 会走聚簇索引的全扫描进行过滤，由于过滤是由 MySQL Server 层面进行的。因此每条记录，无论是否满足条件，都会被加上 X 锁。但是，为了效率考量，MySQL 做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了 2PL 的约束。
- 组合五：id 列是主键，RR 隔离级别（组合一一致）
  
  答：id 是主键时，此 SQL 只需要在 id=10 这条记录上加 X 锁即可。
- 组合六：id 列是二级唯一索引，RR 隔离级别
  
  答：两个 X 锁，id 唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个
- 组合七：id 列是二级非唯一索引，RR 隔离级别
  
  答：首先，通过 id 索引定位到第一条满足查询条件的记录，加记录上的 X 锁，加 GAP 上的 GAP 锁，然后加主键聚簇索引上的记录 X 锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录 X 锁，但是仍旧需要加 GAP 锁，最后返回结束。
- 组合八：id 列上没有索引，RR 隔离级别
  
  答：如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有 GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发 semi- consistent read，来缓解加锁开销与并发影响，但是 semi-consistent read 本身也会带来其他问题，不建议使用

MySQL锁

# 全局锁

# MySQL 表级锁

# InnoDB 的行锁

# 锁相关参数

# 行锁分析实战

全局锁

MySQL 表级锁

InnoDB 的行锁

锁相关参数

行锁分析实战