MySQL锁

MySQL锁

介绍

是计算机协调多个进程或线程并发访问某一资源的机制。

锁的分类

1. 全局锁
   锁定数据库中所有表。
2. 表级锁
   每次操作锁住整张表。
3. 行级锁
   每次操作锁住对应的行数据。

全局锁

• 是对整个数据库加锁，加锁后处于只读状态
• 后续DML、DDL语句、已经更新操作的事务提交语句都会被阻塞
• 典型使用场景是做全库逻辑备份（保证数据一致性）

加全局锁SQL语句

FLUSH TABLES WITH READ LOCK;

解除全局锁SQL语句

UNLOCK TABLES;

全局锁特点

• 如果在主库上备份，备份期间都不能执行更新，业务基本上就得停摆
• 如果在从库上备份，备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟

表级锁

• 每次操作锁住整张表，应用在MyISAM、InnoDB、BDB中

两种表锁

1. 表共享读锁(read lock)：所有客户端可读不可写
2. 表独占写锁(write lock)：当前客户端可读可写，其他客户端不可读不可写

加锁SQL语句

LOCK TABLES 表名列表 READ/WRITE

释放锁SQL语句

UNLOCK TABLES  或关闭客户端连接

元数据锁(MDL)(元数据就是表结构)

• MDL是系统自动控制，主要作用是维护数据一致性。
• 可以避免DML与DDL的冲突，保证读写的正确性
• MySQL5.5中进行增删改查时，加MDL读锁(共享)，修改表结构时，加MDL写锁(排他)

SQL语句对应的元数据锁类型

SQL	锁类型
LOCK TABLE xxx READ/WRITE	SHARED_READ_ONLY/SHARED_ON_READ_WRITE
SELECT	SHARED_READ
INSERT/UPDATE/DELETE	SHARED_WRITE
ALTER	EXCLUSIVE

• EXCLUSIVE与其他MDL都互斥，SHARED_READ与SHARED_WRITE兼容（共享）
• 互斥：不能同时执行
• 共享：可以同时执行

查看元数据锁SQL语句

SELECT object_type,object_schema,object_name,lock_type,lock_duration 
FROM performance_schema.metadata_locks;

意向锁

• InnoDB中引入意向锁，避免了DML执行时行锁与表锁的冲突
• 意向锁使表锁不用检查每行数据是否加锁，可减少表锁的检查

意向共享锁(IS)

• 由语句 SELECT…LOCK IN SHARE MODE 添加
• 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥

意向排他锁(IX)

• 由 INSERT、UPDATE、DELETE、SELECT…FOR UPDATE 添加
• 与表锁共享锁(read)和排他锁(write)都互斥

查看意向锁及行锁加锁语句

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

行级锁

• 每次操作锁住对应行的数据。
• 锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB引擎中。
• 行锁是通过数据索引上的索引项加锁来实现的，而不是对记录加锁。

行锁

• 锁定单个行记录的锁，防止其他事务进行UPDATE和DELETE
• 在RC、RR隔离下支持
• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
• InnoDB的行锁是针对与索引加的锁，若不通过索引条件检索数据，就会对表中所有记录加锁（升级为表锁）

共享锁(S)

• 允许一个事务读一行，阻止其他事务获得相同数据集的排他锁。
• 共享锁之间兼容，与排他锁冲突。

排他锁(X)

• 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
• 与共享锁和排他锁都冲突。

各SQL对应的行锁类型

SQL	行锁类型	说明
INSERT…	排他锁	自动加锁
UPDATE…	排他锁	自动加锁
DELETE…	排他锁	自动加锁
SELECT…	不加锁
SELECT…LOCK IN SHARE MODE	共享锁	需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT…FOR UPDATE	排他锁	需要手动在SELECT之后加FOR UPDATE

间隙锁/临键锁

• 间隙锁锁定索引记录间隙，不包含该记录，防止其他事务在这个间隙进行INSERT
• 临键锁是行锁和间隙锁组合，同时锁住数据和数据前面的间隙。
• 在RR隔离下支持间隙锁和临键锁
• InnoDB使用临键锁进行搜索和索引扫描，以防止幻读
• 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁
• 索引上的等值查询(普通索引，可能有多个相同值)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁
• 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止
• 间隙锁的唯一目的是防止其他事务插入间隙。
• 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁