【MYSQL】锁

锁

平时操作数据库时，如果多人同时修改同一份数据，很容易出现数据错乱，MySQL 里的各类锁就是用来避免这种问题的。不少人分不清全局锁、表锁、行锁的区别，写 SQL 时经常碰到查询卡住、更新被阻塞的情况。这份笔记从大范围到小范围，把所有常用锁的用法、适用场景都整理清楚，方便大家理解区分

全局锁

锁住整个 MySQL 实例所有库所有表，属于全局只读锁。

规则：

所有客户端：只能查，不能增删改、不能 DDL；

持有锁的会话自身：可以正常读写；

其他会话写操作会阻塞，直到解锁。

使用场景—备份数据

1.加锁

flash tables with read lock;

2.备份

mysqldump -u用户名-p密码 要拷贝的表 >拷贝到的地方

3.解锁

unlock tables;

表级锁

表锁

对于表锁分为两类

1. 读锁

• 多个会话可同时加读锁，互相不影响查询；

• 任何会话不能增删改该表，写操作阻塞。

语法：

lock table 表名 read;

unlock tables;

2. 写锁

• 只有持有锁的会话能查、改表数据；

• 其他会话不管读、写全部阻塞。

语法：

lock table 表名 write;

unlock tables;

元数据锁

MDL 元数据锁，每张表自带，作用对象是表结构信息（表名、字段、索引），不是行 / 表数据。只要事务访问一张表（SELECT/INSERT/UPDATE），事务全程持有 MDL，事务结束才释放。

两种元素锁：

1. 共享元数据锁（读MDL）

普通 DML（增删改查）申请共享元数据锁；

多个事务可以同时持有，互不冲突

2. 排他元数据锁（写MDL）

DDL 语句（ALTER、DROP、TRUNCATE、RENAME）申请排他元数据锁；

当有共享数据锁时，此时提交DDL语句，DDL语句将会阻塞，直到前面申请的共享数据锁释放后DDL语句才能执行

同时，如果后面有共享数据锁，后面的DML语句也会被阻塞，只有前面的DDL语句执行完后，后面的DML语句才能执行

对应情况如下：

意向锁

意向锁是用来解决行锁和表锁之间的冲突的

如果不上意向锁，当一个窗口进行增删改查的时候，另一个窗口并发进行表锁，此时另外一个窗口要从上到下遍历是否有行锁，降低了效率，而上了意向锁后，就不要挨个遍历行，一下可以看出是否有行锁。

意向锁分为两类

1. 共享意向锁（IS）

触发SQL：select … lock in share mode;

与表锁read兼容，与表锁write互斥

2. 排他意向锁（IX）

   触发SQL：insert / update / delete / select … for update;

   与表锁read和write都互斥

查看当前的所有锁：

SELECT object_schema,object_name,index_name,lock_type,lock_mode,lock_data FROM performance_schema.data_locks

行级锁

只锁某一行数据，不锁整张表，并发性能远高于表锁；仅 InnoDB 支持，MyISAM 没有行锁。

依赖索引生效：查询条件不走索引时，行锁会退化成表锁。

例子：

update stu set name=‘lily’ where name=‘mary’;

此时name没有索引，那么在另一个窗口想要更新一条非上面那一条的数据也是更新不了的，因为此时本身只锁lily的行锁升级为表锁，其他数据的更新也会被阻塞。

本质是给索引条目加锁，不是锁物理记录

行锁分为两类：

1. 共享锁（读锁）

语法：select … lock in share mode;

规则：

• 多个事务可同时加 S 锁（共享读）
• 持有 S 锁时，其他事务不能加写锁，只能读

场景：需要保证数据读取期间不被修改

2. 排他锁（写锁）

语法：

1. DML 自动加：insert / update / delete自动加 X 锁
2. 手动：select … for update;

规则：

• 一行数据只能有一个事务持有 X 锁
• 其他事务不能读、不能改该行，会阻塞等待锁释放

行级锁下面又有两种锁：

1. 间隙锁（Gap）

   定义

   锁住两条索引记录中间的空白区间，不锁已存在的数据行，只锁 “空位”。

   作用

   阻止其他事务在这个区间插入新数据，解决幻读。

   举例

   表主键索引数据：1、3、5、7

   执行update set name='a' where id=3;

   匹配到 id=3 这条记录，间隙锁锁住两个区间：(1,3)、(3,5)

   其他事务不能插入 2、4，避免新增数据导致幻读。

2. 临键锁（Next-Key Lock）

使用场景：

1. 范围查询（>、<、>=、<=、between）

索引数据：1、3、5、7

执行where id <=5

临键锁区间：(-∞,1]、(1,3]、(3,5]

区间内已有数据不能修改，区间空位不能插入

2. 普通索引等值查询（不会降级，保留临键锁）

update t set name=‘test’ where phone = ‘13800’;

此时的phone是普通索引，前后间隙都锁住

3. 等值查询，但匹配不到任何数据

update t set name=‘xx’ where id = 4;

表里无 id=4 这条记录，锁区间(3,5]，禁止插入4

不能修改，区间空位不能插入

2. 普通索引等值查询（不会降级，保留临键锁）

update t set name=‘test’ where phone = ‘13800’;

此时的phone是普通索引，前后间隙都锁住

3. 等值查询，但匹配不到任何数据

update t set name=‘xx’ where id = 4;

表里无 id=4 这条记录，锁区间(3,5]，禁止插入4

平时写 SQL 记住两个关键点，查询条件建好索引，别长时间占用表再修改表结构，就能减少数据库卡死、等待的情况，更好地保证数据正常读写。
如果喜欢我的内容，觉得我的内容有帮助到你的，可以动动小手点赞收藏，我们下期再见！