锁
概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。数据库的数据中就是一种资源,用锁来保数据并发访问的一致性、有效性。锁冲突也是影响数据库并发访问性能的一个重要因素。
查看数据库中的锁
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks
全局锁
对整个数据库实例加锁,加锁后整个实例就处于只读状态。后续的DML写语句,DDL语句,以及更新操作的事务提交语句都将被阻塞。
典型的使用场景:全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
语法
加锁
flush tables with read lock;
> mysqldump -h 192.168.0.1 -uroot -p1234 dbname > db.sql #在终端备份数据库,演示用
解锁
unlock tables;
特点
数据库加全局锁存在以下问题
- 如果在主库上备份,那么在备份期间都不能执行更新,业务停摆
- 如果从库上备份,那么在备份期间不能执行主库同步过来的二进制日志(binlog),会导致主从延迟
在InnoDB引擎中,全库备份可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p123456 dbname > db2.sql
表级锁
每次操作锁住整张表。锁的粒度大,发生冲突的概率最高,并发度最低。应用在InnoDB、MyISAM中。
表级锁分为三类:
- 表所
- 元数据锁(meta data lock)
- 意向锁
表锁
表锁分为两类:
- 表共享读锁 read lock 阻塞所有线程的写
- 表共享写锁 write lock 阻塞其他线程的读和写
语法
加锁
lock tables tb1 read; -- 为tb1添加读锁
lock tables tb2 write -- 为tb2添加写锁
解锁
unlock tables;
或者客户端断开连接,也能解锁
元数据锁 meta data lock, MDL
MDL加锁是系统自动控制,无法显式使用,在访问一张表的时候会自动加上。MDL锁主要维护元数据表的一致性,在表上有活动事务的时候,不可以对元数据执行写入操作。为了避免DML与DDL的冲突,保证读写的正确性。
当对一张表执行DDL(curd)时,加MDL读锁;当对表结构进行变更操作的时候,加MDL写锁。
SQL语句 | 锁类型 | 说明 |
---|---|---|
select | SHARED_READ | 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 |
insert、update、delete、select…for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容,与EXCLUSIVE互斥 |
alter table… | EXCLUSIVE | 与其他MDL都互斥 |
结论
- 读锁之间不互斥,因此可以有多个线程同时对一张表增删改查
- 读锁与写锁之间、写锁与写锁之间是互斥的,保证 DML 和 DDL、DDL 和 DDL 顺序执行
默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能长时间读阻塞。
意向锁
意向锁用来解决行锁与表锁的冲突问题。
意向锁类型 | SQL语句 | 互斥情况 |
---|---|---|
意向共享锁(IS) | select … lock in share mode | 与表锁中共享锁(read)兼容,与表锁中排他锁(write)互斥 |
意向排他锁(IX) | insert、update、select… for update | 与表锁中的共享锁(read)和排他锁(write)互斥。意向锁之间不会互斥。 |
案例
意向共享锁IS 与表锁的兼容情况
-- 线程A
-- 为该行添加行锁
-- select ... in share mode 为tb_user添加意向共享锁
>begin;
>select * from tb_user where id=2 lock in share mode;
-- 线程B
-- 为tb_user表添加读锁。表锁中的读锁与意向共享锁IS兼容
>begin;
>lock tables tb_user read;
Query OK
> unlock tables;
Query OK
-- tb_user表添加写锁,将会阻塞。表锁中的写锁与意向共享锁IS互斥
>lock tables tb_user write;
-- 阻塞,直到线程A中的事务提交
意向排他锁IX 与表锁的兼容情况
-- 线程A
-- 为该行添加行锁
-- udate.. 为tb_user添加意向排他锁
>begin;
>update tb_user set name='liaoqi' where id=2;
-- 线程B
-- 为tb_user表添加读锁或写锁,将会阻塞
>begin;
>lock tables tb_user read;
-- 阻塞,直到线程A中的事务提交
行级锁
行级锁,每次操作都会锁住对应的数据,锁的粒度最小,发生锁冲突的概率最低,并发度最高。
InnoDB的数据是基于索引组织的。行锁是通过对索引上的索引项加锁来实现的,而不是对记录加锁。
行级锁的分类
行级锁类型 | 特点 | 支持隔离级别 |
---|---|---|
行锁 Record lock | 锁定单个记录的锁,防止其他事务对此行进行update和delete。 | RC 、RR |
间隙锁Gap lock | 锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙insert,产生幻读 | RR |
临建锁Next-key lock | 行锁和间隙锁组合,同时锁住数据,并锁柱前面的间隙Gap | RR |
行锁
行锁分为两类
-
共享锁 S: 允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁
(兼容其他事务的S,排斥其他事务的X)
-
排他锁 X:允许排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
(排斥其他事务的S和X)
SQL | 行锁类型 | 说明 |
---|---|---|
insert | 排他锁 X | 自动加锁 |
update | 排他锁 X | 自动加锁 |
delete | 排他锁 X | 自动加锁 |
select(正常) | 不加任何锁 | |
select … lock in share mode | 共享锁 | 需手动在select语句之后声明in share mode |
selet … for update | 排他锁 X | 需手动在select语句之后声明for update |
默认情况下,InnoDB在Repeatable Read事务级别运行,使用临界锁next-key搜索和索引扫描,以防止幻读
创建一张表stu,拥有字段 id (主键/唯一索引), name , age(普通索引),并插入以下数据
id | name | age |
---|---|---|
1 | java | 1 |
3 | php | 3 |
7 | ruby | 7 |
11 | python | 11 |
15 | go | 15 |
17 | vue | 17 |
-
针对唯一索引 unique 检索时,对已存在的记录进行等值匹配,将自动优化为行锁
begin; select * from stu where id = 1 lock in share mode; /*加锁情况: stu: IS id为 1 的行: Reocrd lock (S) 【其他事务也可为id为1的行添加Record lock (S)】 */
begin; update stu set name ='C++' where id = 3; /*加锁情况: stu: IX id为3的行: Recored lock (X) 【其他事务不能id为3的行添加Record lock (S/X)】 */
-
InnoDB的行锁是针对索引加的锁,不通过索引检索数据时,InnoDB将对表中的所有记录加锁。此时就会升级为表锁
begin; update stu set name ='C#' where name="java"; /*加锁情况: stu:IX 【name字段没有索引,升级为表锁】 */
间隙锁和临界锁
默认情况下,InnoDB在Repeatable Read事务级别运行,使用临界锁next-key搜索和索引扫描,以防止幻读
-
索引上的等值查询(唯一索引 unique),给不存在的记录加锁时,优化为间隙锁 gap lock
begin; update stu set age=10 where id = 8; /*加锁情况: stu: IX id (7,11)之间的间隙:Gap lock */
-
索引上的等值查询(普通索引 【非唯一】),向右遍历最后一个值不满足查询需求临界锁,next-key lock退化为gap lock
-- 已为age字段建立索引 begin; select * from stu where age = 3 lock in share mode; /*加锁情况 stu: IS age在(3, 7)之间的行的间隙:Gap lock */
-
索引上的范围查询(唯一索引),会访问到不满足条件的第一个值为止
begin; select * from stu where id>=11 lock in share mode; /*加锁情况 stu: IS id>=11的行: next-key lock 【即id为11、25的行Record lock + (11,25) (25,+∞)之间间隙的Gap lock】 */
总结
-
不使用索引查询,用表锁
-
唯一索引上的等值查询,如果记录存在,用Record lock,锁住该行
-
唯一索引上的等值查询,如果记录不存在,用Gap lock,锁住(last, next)之间的间隙
-
唯一索引上的范围查询,用Next-key lock 锁住范围之间的行和间隙
-
普通索引上的等值查询,用Gap lock,锁住(next,this)和(this,next)之间的间隙