栈记
MySQL

锁

全局锁、表级锁、行级锁,以及死锁与乐观锁、悲观锁

本质就是多用户操作同一资源的时候,用来保证数据一致性

全局锁

锁整个数据库软件,其他客户端无法对任何库、任何表进行操作,但是可读,直到全局锁被释放;

在被锁期间,其他客户端的操作会被阻塞,直到锁释放后被执行;

-- 加锁
flush tables with read lock;
-- 释放锁
unlock tables;

一般使用场景是为了执行全局的维护任务,比如数据库备份:假设当前有两张表,库存、订单

  • 没锁:当备份库存表后,在备份订单表的过程中, 用户下单 -库存 +订单; 导致最后备份出来的数据就对应不上了
  • 有锁:在备份的期间,外部任何修改操作都进不来,直到备份完成手动释放,才会执行 -库存 +订单;
-- 选择要备份的数据库,输出到指定目录的下的文件
mysqldump --single-transaction -uroot -p1234 db1 > /root/db1.sql

问题:

  • 业务全部停止
  • 如果数据库是主从结构,读写分离;在写入的时候不会阻塞;锁的是从库,虽然主库可以写入,但从库不能从主库同步数据,导致主从延迟

解决:备份的时候增加参数 --single-transaction 不会出现不一致性问题,底层使用快照读实现

表级锁

  • 表共享读锁 read lock:所有客户端都只能读取,其他客户端写入操作会被阻塞,当前客户端只能读,写入会报错

  • 表独占写锁 write lock:当前客户端可读可写,其他客户端读写都会被阻塞;

-- 加锁
lock tables 表名1 [表名2] write/read;
lock tables 表名1 write [, 表名2 read];

-- 释放锁
unlock tables;
  • 元数据锁 meta data lock:系统控制,访问表时会自动加上,不需要手动操作。为了避免 DML 和 DDL 的冲突,当一张表存在未提交的事务,其他事务不能修改表结构
SQL 类型MDL 锁类型描述
select...SHARED_READ 共享读锁兼容
insert update deleteSHARED_WRITE 共享写锁兼容
alter tableEXCLUSIVE 排他锁与其他 DML 锁互斥
lock tables ... read writeSHARED_READ_ONLY / SHATED_NO_READ_WRITE
  • 意向共享锁 IS:兼容表共享读锁,排斥表独占写锁
select 语句 lock in shar mode;
  • 意向排他锁 IX:insert delete update 排斥所有表锁,意向锁和意向锁之间兼容

行级锁

每次操作锁定对应的行数据,它的粒度是最小的,发生锁冲突的概率是最低的,所以并发性能最高;

在 innodb 中行级锁不是针对记录加的锁,而是针对索引加的锁;

  • 行锁(record lock):锁定单条记录的锁,在 RC(读已提交)和 RR(可重复读) 隔离级别下都是支持的

    • 共享锁/读锁 (简写S)共享锁兼容其他共享锁,共享锁不兼容排他锁
    • 排他锁/写锁 (简写X)排他锁不兼容共享锁,也不兼容排他锁
SQL行锁类型
insert...排他锁(自动)
delete...排他锁(自动)
update...排他锁(自动)
select...无行锁
select... lock in share mode共享锁
select... for update排他锁
  • 间隙锁(cap lock):对两条记录之间的间隙加锁,锁住当前记录之前的间隙(不锁记录),加上间隙锁之后可以确保在当前的事务里面被锁住的间隙不变,防止其他事务在这个间隙插入数据,从而产生幻读现象,在 RR 隔离级别下支持,间隙锁之间兼容

  • 临键锁(next-key lock):行锁和间隙锁的组合,同时锁住数据,以及数据钱的间隙,在 RR 隔离级别下支持;

在默认的事务隔离级别下,innodb 会使用next-key(临键锁)进行扫描,防止出现幻读:

  1. 在唯一索引下,针对已存在的记录进行等值匹配的时候,会自动把这个临键所优化成行锁。
  2. innodb 的行锁是针对索引加的锁,如果不通过索引字段查询,innodb 就会对表中的所有记录加行锁,类似于行锁升级成了表锁
  3. 唯一索引上的等值查询,给不存在的记录加锁时,会自动把临键锁优化为间隙锁
  4. 常规索引上的等值查询,向右遍历到第一个不满足查询条件的值,临键锁会自动退化为间隙锁
  5. 对于范围查询(有索引),满足条件的值都会被加上临键锁,对应的主键上会加上行锁(本身是主键除外)

死锁

当两个或两个以上的事务相互等待对方释放资源时,就会发生死锁,假设:

事务A 先锁住 行1, 再锁住行2 (因为行2 已经被事务 B 锁住,所以阻塞); 前提这些锁是互斥的。

事务B 先锁住 行2, 再锁住行1 (同样阻塞)因为这个最后执行的造成了思索,会默认回滚最后这个操作

解决方案

  • 尽量避免长事务,考虑将长事务分割为短事务,从而减少锁的持有时间
  • 尽量避免一个事务锁定多个资源
  • 尽量都按照统一的顺序来请求锁
  • 如果允许出现幻读和不可重复度,可以使用 RC 隔离级别

乐观锁

没有具体的实现,只是一个概念;

在并发场景下,多个事务可能会访问同一条数据,乐观锁认为发生冲突的概率很低,所以在进行业务操作的时候,不会一开始就锁定数据,而是在提交数据更改时,检查当前数据是否被其他数据修改,如果没有就提交数据,否则就回滚数据;比如通过数据的更新时间

create table emp (
  id int primary key auto_increment,
  name varchar(16),
  salary int,
  update_time datetime default current_timestamp on update current_timestamp
);

-- 每次在事务中修改数据时,按照更新时间去修改,如果其他事务已经修改了之后,就证明这条数据已经被修改过了
begin;
update emp set salary = 1000 where id = 1 and update_time = '2025-12-13 10:10:10';
commit;

适用于冲突概率较低的场景,比如:读多写少的场景,分布式系统(网络延迟)

悲观锁

为了保证数据的完整性和一致性,从事务一开始读取的时候,就先把数据锁上,这只是一个概念,实现上面实际上就是 for update | lock in share mode

适用于冲突较高的场景,比如:写多读少的场景,强一致性的业务(银行相关)

On this page