MySql - 锁 - 掘金

1.什么是MySQL锁

锁是计算机用以协调多个进程间兵法访问统一共享资源的一种机制.锁保证数据并发访问的一致性、有效性,MySQL中的锁是在服务器层或者存储引擎层实现的

ps.具体解释详见百度

2.先通过一个Update语句的执行流程来看看锁, 在哪里

MySQL锁的大致分类

按颗粒度分类
1. 全局锁: 锁定整个mysql实例(database), 由MySQL的SQL layer层实现
2. 表级锁: 锁定某一个表(不同行的数据更新需要串行执行,慢!)
3. 行级锁: 锁定特定的数据行,也可能锁定行之间的间隙,只有InnoDB引擎支持(同一表内不同数据行的修改,可并行执行,提高并发速度)
根据功能分类

1.Exclusive Locks - X锁(写锁) - 排他锁:
- 兼容性: 加了X锁的记录不允许其他事务再加S锁和X锁
- 加锁方式: select ... for update
2.Shared Locks - S锁(读锁) - 共享锁:
- 兼容性: 加了S锁的记录允许其他事务再加S锁, 不允许其他事务再加X锁
- 加锁方式: select ... lock in share mode
3.还包括MetaData Locks - 元数据锁, Intention Locks - 意向锁, AUTO-INC LOCKS 自增锁

锁的实现和使用

1.全局锁

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

加全局锁的命令: flush tables with read lock;

释放全局锁的命令: unlock tables;

ps. 断开加锁session的连接,自动释放全局锁

⚠️全局锁十分危险,主库加锁会导致整个数据库不能写入,备份期间影响业务运行,从库加锁会导致主从数据同步的操作延迟

对于InnoDB这种支持事务的引擎,使用MySQL dump备份时可以使用 --single-transaction参数, 利用mvcc提供一致性视图,而不使用全局锁,对于MyISAM这种不支持事务的表,只能通过全局锁获得一致性视图,对应的参数为 --lock-all-tables

2.表级锁

共享锁(S锁): 一个Session加锁之后,表处于只读的状态,所有的session都只能读取不能修改

加锁: lock table_name read;

解锁:unlock tables;

排他锁(X锁): 加锁之后当前session会独占资源,加锁的session可读可写,其他session不可读写

加锁: `lock table_name write;

解锁:unlock tables;
MySQL 实现表级锁定的争用状态变量:
```
show status like 'table%';
-- table_locks_immediate: 产生表级锁定的次数
-- table_locks_waited: 出现表级锁定争用而发生等待的次数
```
查看表锁的情况:

show open tables;

元数据锁: 当开启事务并且执行对某一个表的查询操作之后,此时会对表加元数据锁,不允许对表结构进行修改操作 (保证读写的正确性) .MDL不需要显式实用,在访问一个表的时候会自动加上
在MySQL 5.5版本中引入MDL, 当对一张表做CRUD操作时,加MDL读锁,当要对表做结构变更操作的时候,加MDL写锁
- 读锁之间不互斥,因此可以有多个线程同时对一张表做CRUD
- 读写锁之间,写锁之间是互斥的,用来保证变更表结构的安全性. p.s: 当两个线程要同时给一个表加字段,其中一个要等待另一个执行完才开始执行
```
session1: begin; -- 开启事务
          select * from mylock; -- 加MDL读锁
session2: alter table mylock add f int; -- 修改阻塞
session1: commit; -- 提交事务
session2: Query OK; 0 rows affected(10.23 sec) -- 修改完成提示
```

自增锁(AUTO-INC Locks): 自增锁是一种特殊的表级锁,在设计AUTO_INCREMENT列的事务性插入操作时产生

MySQL 行级锁 - InnoDB

InnoDB行锁是通过给索引上的索引项加锁来实现的, 因此InnoDB这种行锁实现特点意味着: 只有通过索引条件检索的数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁,对于UPDATE、DELETE、和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X锁),对于普通SELECT语句,InnoDB不会加任何锁相较于表锁开销大,加锁慢,锁定粒度最小,发生锁冲突的概率最低,并发度最高

行级锁争用状态查看:

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_wait; 系统启动后到现在总等待次数

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

InnoDB行锁按锁定范围分类:

记录锁(Record Locks): 锁定索引中一条记录,e.g: 主键指令 where id = 1
间隙锁(Gap Locks): 要么锁住索引记录中间的值,要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值
临键锁(Next-Key Locks): 是索引记录上的记录锁和在索引记录之前的间隙锁的组合 (间隙锁 + 记录锁)
插入意向锁(Inert Intention Locks): 做inert操作时添加的记录id的锁

1.意向锁 Intention Locks

介绍:

意向锁是表级锁,是MySQL内部使用的,不需要用户干预,意向锁和行锁可以共存,意向锁的主要作用是为了全表更新数据时的性能提升,否则在全表更新数据时,需要先检索是否某些记录上有行锁

表明 - 某个事务正在某些行持有了锁、或该事务准备去持有锁

意向锁的存在是为了协调行锁和表锁的关系,支持多颗粒度(表锁和行锁)的锁共存
意向锁包括:
- 意向共享锁(IS锁): 事务有意向对表中的某些行为加共享锁,即事务在请求S锁之前,要先获得IS锁
- 意向排他锁(IX锁): ... 事务在请求X锁之前,要先获得IX锁

作用:

当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定

如果意向锁时行锁,则需要便利每一行数据去确认
如果意向锁是表锁,则只需要判断一次即可知道有没有数据行被锁定,提高性能

意向锁和共享锁、排他锁的兼容关系

意向锁相互兼容,因为IX、IS只是表明申请更低层次级别元素(page,记录)的X、S操作,上了行级X锁之后,行级X锁不会因为有别的事务上了IX而堵塞,一个MySQL是允许多个行级X锁同时存在的,只要他们不是针对同一数据行

是否兼容	当事务A上了:IS	IX	S	X
事务B是否上了:IS	是	是	是	否
IX	是	是	否	否
S	是	否	是	否
X	否	否	否	否

2.记录锁 Record Locks

仅仅锁住索引记录的一行,在单条索引记录上加锁, 记录锁,锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么InnoDB会在后台创建一个隐藏的聚集逐渐索引,记录锁会锁住这个索引

当一条sql没有走任何索引时,将会在每一条聚合索引后面加X锁,这个类似于表锁,但是原理和表锁实际完全不同

select * from table_name where id = 1 lock in share mode; -- 加记录共享锁
select * from table_name where id = 1 for update; -- 加记录排他锁

3.间隙锁 Gap Locks

区间锁, 仅仅锁住一个索引区间(开区间,不包括双端端点)
在索引记录之间的间隙中加锁,或者是某一条索引记录之前或者之后加锁,并不包含该索引记录本身
间隙锁用于防止幻读,保证索引间的索引不会被插入数据

-- session1:
begin;
select * from table_name where id > 3 for update;

-- session2:
insert into table_name values (4, 20); -- 阻塞
insert into table_name values (2, 20); -- 成功

e.g: 当主键索引的行锁为 1, 3, 6, 12时,间隙为(-∞, 1), (1, 3), (3, 6), (6, 12),其临键为(-∞, 1], (1, 3], (3, 6], (6, 12]

4.临键锁 Next-Key Locks

Record Locks + Gap Locks, 左开右闭区间
默认情况下,InnoDB使用Next-Key Locks来锁定记录 - select .. for update;
当查询的索引含有唯一属性的时候, Next-Key Locks会进行优化,将其降级为Record Locks,仅仅锁住索引本身,不再是范围

场景	退化成的锁类型
使用unique index精准匹配 (=),且记录存在	Record Locks
使用unique index精准匹配 (=),且记录不存在	Gap Locks
使用unique index范围匹配 (< 和 >)	Record Locks + Gap Locks

5.插入意向锁 Insert Intention Locks

插入意向锁是一种 Gap锁,不是意向锁,在insert操作时产生
在多事务同时写入不同数据至统一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待
假设有一个记录索引包含键值1和4,不同的事务分别插入2和3,每个事务都会在1-4之间插入意向锁,获取在插入行上的排他锁,但是不会被互相锁住,因为数据行并不冲突
插入意向锁不会阻止任何锁,对于插入的记录会持有一个记录锁

MySql - 锁