【博学谷学习记录】超强总结，用心分享 | MySQL锁简单介绍

标签：insert 间隙 lock 博学 MySQL update values 超强 select

全局锁
表级锁
行级锁
- 间隙锁例子
- 临键锁和记录锁例子

全局锁

概念：

全局锁就是对整个数据库实例加锁，即数据库中的所有表都将被加上锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

作用：

全局锁在用作全量备份时，保证表与表之间的数据一致性。

语法：

flush tables with read lock;

使用全局读锁锁定所有数据库的所有表。这时会阻塞其它所有 DML 以及 DDL 操作，这样可以避免备份过程中的数据不一致。接下来可以执行备份，最后用 unlock tables 来解锁

注意

但 flush tables 属于比较重的操作，可以使用 --single-transaction 参数来完成不加锁的一致性备份（仅针对 InnoDB 引擎的表）
mysqldump --single-transaction -uroot -p test > 1.sql

表级锁

表级锁 - 表锁

语法：

加锁 lock tables 表名 read/write 
解锁 unlock tables

共享锁：lock tables 表名 read; 排他锁：lock tables 表名 write;

缺点：

粒度较粗，在 InnoDB 引擎很少使用。粒度粗，并发能力低。

表级锁 - 元数据锁

元数据锁没有专门的sql加锁语句

元数据锁即 metadata-lock（MDL），主要是为了避免 DML 与 DDL 冲突，DML 的元数据锁之间不互斥。

加元数据锁的几种情况
- lock tables read/write，类型为 SHARED_READ_ONLY 和 SHARED_NO_READ_WRITE
- alter table，类型为 EXCLUSIVE，与其它 MDL 都互斥
- select，select … lock in share mode，类型为 SHARED_READ
- insert，update，delete，select for update，类型为 SHARED_WRITE
查看元数据锁（适用于 MySQL 8.0 以上版本）
- select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

表级锁 - IS（意向共享锁）与 IX（意向排他锁）

作用：

主要是避免 DML 与表锁冲突，DML 主要目的是加行锁，为了让表锁不用检查每行数据是否加锁，加意向锁（表级）来减少表锁的判断，意向锁之间不会互斥

加意向表锁的几种情况
- select … lock in share mode 会加 IS 锁
- insert，update，delete， select … for update 会加 IX 锁
```
例如:给表加上IX(意向排他锁) :  select * form table for update； 
```
查看意向表锁（适用于 MySQL 8.0 以上版本）
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

种类
- 行锁 – 在 RC 下，锁住的是行，防止其他事务对此行 update 或 delete
- 间隙锁 – 在 RR 下，锁住的是间隙，防止其他事务在这个间隙 insert 产生幻读
- 临键锁 – 在 RR 下，锁住的是前面间隙+行，特定条件下可优化为行锁
RC：提交读 RR：可重复读
查看行级锁
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks where object_name='表名';

注意

它们锁定的其实都是索引上的行与间隙，根据索引的有序性来确定间隙

测试数据:

create table t (id int primary key, name varchar(10),age int, key (name)); 
insert into t values(1, 'zhangsan',18); 
insert into t values(2, 'lisi',20); 
insert into t values(3, 'wangwu',21); 
insert into t values(4, 'zhangsan', 17); 
insert into t values(8,'zhang',18);
insert into t values(12,'zhang',20);

说明

1,2,3,4 之间其实并不可能有间隙

4 与 8 之间有间隙

8 与 12 之间有间隙

12 与正无穷大之间有间隙

其实我们的例子中还有负无穷大与 1 之间的间隙，想避免负数可以通过建表时选择数据类型为 unsigned int

间隙锁例子

事务1：

begin;
select * from t where id = 9 for update; /* 锁住的是 8 与 12 之间的间隙 */

id=9 的记录不存在，所以在(8,12)区间添加了间隙锁，防止其他事务在这个间隙 insert 产生幻读

事务2：

update t set age=100 where id = 8; /* 不会阻塞 */
update t set age=100 where id = 12; /* 不会阻塞 */
insert into t values(10,'aaa',18); /* 会阻塞 */

临键锁和记录锁例子

事务1：

begin; /*开启一个事务*/
select * from t where id >= 8 for update;

临键锁锁定的是左开右闭的区间，与上条查询条件相关的区间有 (4,8]，(8,12]，(12,+∞)
临键锁在某些条件下可以被优化为记录锁，例如 (4,8] 被优化为只针对 8 的记录锁，前面的区间不会锁住

临键锁 : 行+前面的间隙

锁定的其实都是索引上的行与间隙，根据索引的有序性来确定间隙