欢迎访问shiker.tech

请允许在我们的网站上展示广告

您似乎使用了广告拦截器,请关闭广告拦截器。我们的网站依靠广告获取资金。

Mysql锁介绍
(last modified Jun 23, 2023, 8:56 PM )
by
侧边栏壁纸
  • 累计撰写 176 篇文章
  • 累计创建 61 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

Mysql锁介绍

橙序员
2023-06-23 / 0 评论 / 0 点赞 / 216 阅读 / 2,572 字 / 正在检测百度是否收录... 正在检测必应是否收录...
文章摘要(AI生成)

按照锁的粒度进行区分,Mysql主要包含三种类型的锁:全局锁:锁整个数据库。表级锁:锁某一张表行级锁:锁的是某条记录,或者记录之间的间隙。按照锁的功能进行区分,锁主要分为共享锁和排他锁:共享锁Shared Locks:允许其他事务加共享锁,不允许其他事务加排他锁排他锁Exclusive Locks:

按照锁的粒度进行区分,Mysql主要包含三种类型的锁:

  • 全局锁:锁整个数据库。
  • 表级锁:锁某一张表
  • 行级锁:锁的是某条记录,或者记录之间的间隙。

按照锁的功能进行区分,锁主要分为共享锁和排他锁:

  • 共享锁Shared Locks:允许其他事务加共享锁,不允许其他事务加排他锁
  • 排他锁Exclusive Locks:不允许其他事务加共享锁或者排他锁

全局锁

用于全库的数据备份。对所有表加锁可以保证数据的完整性。显式加全局锁的命令为 flush tables with read lock;,释放锁的命令为unlock tables

对于innodb这种支持事务的引擎,使用mysqldump备份时可以使用--single-transaction参数,利用 mvcc提供一致性视图,而不使用全局锁,不会影响业务的正常运行。而对于有MyISAM这种不支持事务 的表,就只能通过全局锁获得一致性视图,对应的mysqldump参数为--lock-all-tables

表级锁

读锁、写锁:读锁可通过lock table 表名称 read(write),表名称2 read(write),其他;手动添加,通过 unlock tables;删除。

元数据锁:在访问一个表的时候会被自动加上。元数据锁的作用是,保证读写的正确性。当对一个表做增删改查操作的时候,加元数据锁读锁;当要对表做结构变更操作的时候,加元数据锁写锁。

自增锁:生涉及AUTO_INCREMENT列的事务性插入操作时数据库会加自增锁。

意向锁:本质是一个锁标识,是mysql内部使用的,不需要用户干预。意向锁和行锁可以共存,意向锁的主要作用是提升全表更新数据时的性能。

当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定(行锁)。即意向锁方便了我们对表中是否存在行锁中的读锁和写锁。

当我们需要加一个排他锁时,需要根据意向锁去判断表中有没有数据行被锁定(行锁):

  1. 如果意向锁是行锁,则需要遍历每一行数据去确认;
  2. 如果意向锁是表锁,则只需要判断一次即可知道有没数据行被锁定,提升性能。

意向锁同样有两种:

  1. 意向共享锁(IS):事务再请求共享读锁前,要先判断意向共享锁
  2. 意向排他锁(IX):事务在请求排他锁之前,要先获得意向排他锁

上了表级S锁后,不允许其他事务再加X锁,所以表级S锁和X、IX锁不兼容;

上了表级X锁后,会修改数据,所以表级X锁和 IS、IX、S、X(即使是行排他锁,因为表级锁定的行肯定包括行级速订的行,所以表级X和IX、行级X)不兼容。

行级锁

记录锁:对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT 语句,InnoDB不会加任何锁。在查询时可以通过LOCK IN SHARE MODE加共享锁,通过FOR UPDATE添加排他锁。

间隙锁:区间锁, 仅仅锁住一个索引区间(开区间,不包括双端端点)。在索引记录之间的间隙中加锁,或者是在某一条索引记录之前或者之后加锁,并不包括该索引记录本身。间隙锁可用于防止幻读,保证索引间的不会被插入数据

临键锁:记录锁+间隙锁,左开右闭区间,例如(5,8]。)默认情况下,innodb使用临键锁来锁定记录。

临键锁退化的条件:

场景 退化后的锁
使用唯一索引精准查询,且记录存在 记录锁
使用唯一索引精准查询,且记录不存在 间隙锁
使用唯一索引做范围查询 记录锁和间隙锁

插入意向锁:插入意向锁是一种间隙锁,不是意向锁,在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。

假设有一个记录索引包含键值4和7,不同的事务分别插入5和6,每个事务都会产生一个加在4-7之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突

行锁加锁规则

主键索引

  1. 等值查询

    (1)命中记录,加记录锁。

    (2)未命中记录,加间隙锁。

  2. 范围查询

    (1)没有命中任何一条记录时,加间隙锁。

    (2)命中1条或者多条,包含where条件的临键区间,加临键锁

辅助索引

  1. 等值查询

    (1)命中记录,命中记录的辅助索引项+主键索引项加记录锁,辅助索引项两侧加间隙锁。

    (2)未命中记录,加间隙锁

  2. 范围查询

    (1)没有命中任何一条记录时,加间隙锁。

    (2)命中1条或者多条,包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。

不同事务的加锁规则

修改操作

设有如下语句

delete from t1 where id = 10;

当事务为读已提交时:

  • 若id为主键,在只在id=10的记录上加排他锁
  • 若id为唯一索引,则既要在对应的唯一索引上id=10的记录加排他锁,又要在聚簇索引上对应的记录加排他锁
  • 若id非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加排他锁。
  • 若id无索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上排他锁。

当事务为可重复读时:

  • 若id为主键、唯一索引,加锁情况同读已提交
  • 若id非唯一索引,则首先通过id所属索引定位到第一条满足查询条件的记录,为记录加上排他锁,然后左右两侧加上间隙锁,然后为主键聚簇索引上的记录加排他锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录,此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。
  • 若id无索引,会进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入操作。当然,也可以通过触发设置半一致性读,来缓解加锁开销与并发影响,但是半一致性读本身也会带来其他问题,不建议使用。

原文:

当前读、更新或删除通常会在 SQL 语句处理过程中会对扫描的每个索引记录设置记录锁。 语句中是否存在排除该行的 WHERE 条件并不重要。 InnoDB不记得确切的WHERE条件,但是知道扫描了哪些索引范围。

这些锁通常是临键锁,它们也会阻止插入到紧邻记录之前的“间隙”中。 但是,可以显式禁用间隙锁定,这会导致不使用临键锁。 事务隔离级别也会影响设置哪些锁。

如果搜索时使用二级索引,并且设置的索引记录锁是排它锁,那么InnoDB也会检索相应的聚集索引记录并对其设置锁。

如果SQL没有命中任何索引,那么MySQL 必须扫描整个表来处理该语句,则表的每一行都会被锁定,从而阻止其他用户对该表的所有插入。 创建良好的索引非常重要,这样您的查询就不会扫描不必要的行。

查询操作

当语句为如下语句时:

select * from t1 where id = 10

当时事务为可重复读或者读已提交时,为快照读,不会加锁;当事务为串行化时,则会加共享读锁。

死锁原理

当两个进行中的事务,互相去获取对方已经占用的锁时,就会发生死锁。

如何避免死锁

  1. 注意程序的逻辑 根本的原因是程序逻辑的顺序,最常见的是交差更新

  2. 保持事务的轻量 越是轻量的事务,占有越少的锁资源,这样发生死锁的几率就越小

  3. 提高运行的速度 避免使用子查询,尽量使用主键等等

  4. 尽量快提交事务,减少持有锁的时间。越早提交事务,锁就越早释放

资料参考:

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

0

评论区