锁

upanddown
169 阅读4分钟

全局锁

全局锁是对整个数据库实例加锁，加锁后实例处于只读状态，DML 写语句、DDL 语句、更新操作的事务提交语句会被阻塞。

典型使用场景

用于全库逻辑备份，锁定所有表以获取一致性视图，保障数据完整性。

  • 开启全局锁: flush tables with read lock;
  • 开启全库备份(不要在mysql中执行，在命令行中执行): mysqldump -u root -p 库名>文件路径.sql
  • 关闭全局锁: unlock tables;

数据库加全局锁存在的问题

  • 在主库备份时，备份期间无法执行更新操作，业务基本停滞。
  • 在从库备份时，从库无法执行主库同步的二进制日志（binlog），导致主从延迟。

InnoDB引擎的解决方法

备份时可添加--single-transaction参数实现不加锁的一致性数据备份。

示例命令：

mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql 。

表级锁

每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

表级锁主要分为以下几类:

  • 表锁
  • 元数据锁
  • 意向锁

表锁

对于表锁，可以分为两类:

  • 表共享读锁
  • 表独占写锁

语法

  • 加锁: lock tables 表名1, 表名2, ... read/write;
  • 释放锁: unlock tables 或者客户端断开连接

读锁不会阻塞其他连接的读，但是会阻塞本次连接以及其他连接的写；

写锁不会阻塞当前连接的读写，但是会阻塞其他连接的读写

元数据锁

MDL（Metadata Lock）加锁由系统自动控制，访问表时自动添加。作用是维护表元数据一致性，避免 DML 与 DDL 冲突，保证读写正确性。MySQL 5.5 引入，增删改查加 MDL 读锁（共享），表结构变更加 MDL 写锁（排他）。

不同 SQL 对应的锁类型及说明

对应 SQL锁类型说明
lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITE-
select、select...lock in share modeSHARED_READ与 SHARED_READ、SHARED_WRITE 可共存，与 EXCLUSIVE 互斥
insert、update、delete、select...for updateSHARED_WRITE与 SHARED_READ、SHARED_WRITE 可共存，与 EXCLUSIVE 互斥
alter table...EXCLUSIVE与其他的 MDL 都互斥

查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

意向锁

在InnoDB中引入意向锁，目的是避免DML执行时行锁与表锁冲突，减少表锁检查每行数据是否加锁的操作。

  • 意向共享锁（IS）：由select ... lock in share mode语句添加。
  • 意向排他锁（IX）：由insert、update、delete、select ... for update语句添加。

意向共享锁（IS） 与表锁共享锁（read）兼容，与表锁排他锁（write）互斥

意向排他锁（IX） 与表锁共享锁（read）及排他锁（write）都互斥，意向锁之间不互斥。

可通过以下SQL查看意向锁及行锁加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

行级锁概述

行级锁每次操作锁定对应行数据，锁定粒度最小，锁冲突概率低，并发度高，应用于InnoDB存储引擎。InnoDB行锁通过对索引项加锁实现。

行级锁分类

  • 行锁（Record Lock）：锁定单个行记录，阻止其他事务对该行update和delete ，在RC、RR隔离级别下支持。
  • 间隙锁（Gap Lock）：锁定索引记录间隙（不含记录本身），防止其他事务在间隙insert引发幻读，在RR隔离级别下支持。
  • 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁定数据及数据前间隙，在RR隔离级别下支持。

行锁

InnoDB行锁类型

  • 共享锁（S）：允许事务读一行数据，阻止其他事务获取相同数据集的排他锁。
  • 排他锁（X）：允许获取锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。

锁兼容性

当前锁类型请求S（共享锁）请求X（排他锁）
S（共享锁）兼容冲突
X（排他锁）冲突冲突

sql语句加锁类型

SQL行锁类型说明
INSERT ...排他锁自动加锁
UPDATE ...排他锁自动加锁
DELETE ...排他锁自动加锁
SELECT（正常）不加任何锁-
SELECT ... LOCK IN SHARE MODE共享锁需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT ... FOR UPDATE排他锁需要手动在SELECT之后加FOR UPDATE

InnoDB事务隔离级别与锁机制

InnoDB默认在REPEATABLE READ事务隔离级别运行，使用next-key锁搜索和扫描索引防止幻读。

  • 索引等值匹配优化：针对唯一索引等值匹配已存在记录时，自动优化为行锁。
  • 索引依赖与锁升级：InnoDB行锁基于索引，若不通过索引检索数据，会对表中所有记录加锁，行锁升级为表锁。
  • 唯一索引等值查询（记录不存在）: 对不存在记录加锁时，优化为间隙锁。
  • 普通索引等值查询：向右遍历时最后一个值不满足查询需求，next - key lock 退化为间隙锁。
  • 唯一索引范围查询：访问到不满足条件的第一个值为止。

间隙锁特性

间隙锁用于防止其他事务在间隙插入，间隙锁可共存

查看意向锁及行锁加锁情况SQL

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

avatar
文章
阅读
粉丝