数据库篇:mysql锁详解

前言

sql事务的执行,如果需要锁定数据进行更新操作,则必定离不开锁

  • 共享锁和排他锁
  • 表锁
  • 行锁 Record Lock
  • 间隙锁 Gap Lock
  • 行锁+间隙锁 Next-Key Lock
  • 加锁场景(加锁sql)

关注公众号,一起交流,微信搜一搜: 潜行前行

1 共享锁和排他锁

  • 排他锁(X锁),当前事务给记录上锁后(insert update delete),可以进行读写,其他事务不可以加任何锁
  • 共享锁(S锁),是指当前事务给一条记录上锁后,其他事务也可以给当前记录加共享锁。共享锁只用于锁定读,如需要更新数据,是不允许的

2 表锁

针对数据库表的锁,又称为表

  • 开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
  • MySQL表级锁有两种模式:表共享锁(Table Read Lock)和表独占写锁(Table Write Lock)
  • myisam,memory,merge 存储引擎只支持表锁

表级别的 AUTO_INC 锁

在为表某列添加 AUTO_INCREDMENT 属性,之后插入数据时可以不指定该字段,系统会自动为它赋值,此时获取自增值是需要 AUTO_INC 锁锁定的

3 行锁 Record Lock

  • innodb 既支持表锁也支持行锁,行锁是针对一行记录的锁
  • 行锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
  • innodb 执行 select update delete insert 语句是不会为表加 S 锁和 X 锁,因为表锁力度大,容易阻塞
  • 单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁

何为意向锁

如果存在行锁的情况,想给表加锁,怎么办?遍历查看表有没有行锁,太浪费时间了。此时意向锁登场啦

  • 意向共享锁(IS锁):当事务给某行记录增加 S 锁时,同时给表加个 IS 锁
  • 意向独占锁(IX锁):当事务给某行记录增加 X 锁时,同时给表加个 IX 锁
    image.png
  • 有了意向锁,则不需要遍历数据也可以直接判断是否可以给表加锁

4 间隙锁 Gap Lock

mysql 在 repeatable read 隔离级别解决幻读的,有两种实现方式。一是靠 MVCC 方案解决;二是加锁方案解决。然而在事务加锁时,记录是不存在的,是无法加行锁的。则需要靠间隙锁(gap lock)实现
image.png

  • 例如给 id=10 记录加 gap 锁,锁住(5,10)区间。另一事务想插入 id=8 的记录,会先定位到 id=10 的记录,然后发现存在一个 gap 锁,则阻塞直到 第一个事务将 gap 锁释放掉,才可以在(5,10)区间插入记录
  • gap lock 仅仅是为了防止插入幻影记录,并不会限制其他事务对记录继续加行锁 或者 gap 锁

5 行锁+间隙锁 Next-Key Lock

  • next-key锁的本质是 record lock 加一个gap lock的结合体。它既能保护该记录,又能阻止别的事务将新的记录插入被保护记录的前面间隙中

6 加锁场景(repeatable read 隔离级别加锁 sql,忽略二级索引的加锁操作)

  • 快照读:读的是数据库记录的快照版本,sql 执行前和当前事务ID生成的最新数据
  • 当前读:读取已提交事务生成的数据,大于当前事务ID的已提交事务,其生成的数据也可以读取

select from

  • 不加任何锁,快照读

select for update

  • 聚簇索引上加 X 锁,当前读

select lock in share mode

  • 聚簇索引上加行级 S 锁,采用当前读

insert、update 和 delete 语句的加锁情况

  • insert 在可重复读级别加的是 next-key lock
  • delete、update 是在聚簇索引记录加上 X 锁

欢迎指正文中错误

参考文章

  • mysql是怎样运行的(书籍)

 1 total views,  1 views today

页面下部广告