MySQL锁

最新推荐文章于 2024-04-24 14:38:51 发布

bhying

最新推荐文章于 2024-04-24 14:38:51 发布

阅读量316

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/bhying/article/details/127229246

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

按照锁的粒度来说，MySQL 主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为：

共享锁Shared Locks （ S 锁） :

1、兼容性：加了S 锁的记录，允许其他事务再加 S 锁，不允许其他事务再加 X 锁

2、加锁方式：select…lock in share mode

排他锁Exclusive Locks （ X 锁） :

1、兼容性：加了 X 锁的记录，不允许其他事务再加 S 锁或者 X 锁

2、加锁方式：select…for update

行锁

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。

InnoDB行锁 是通过给索引上的 索引项加锁来实现的 ，因此 InnoDB 这种行锁实现特点意味着： 只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

行锁类型

InnoDB的行级锁，按照锁定范围来说，分为四种：

记录锁（Record Locks）:锁定索引中一条记录。
间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

InnoDB的行级锁，按照功能来说，分为两种：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT 语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

添加共享锁（S）

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

添加排他锁（x）

SELECT * FROM table_name WHERE ... FOR UPDATE

下面我们主要介绍四种范围行级锁：记录锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）和插入意向锁(Insert Intention Locks)。

记录锁（Record Locks）

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。

（2）record lock 锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么 innodb 会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引

-- 加记录共享锁 
select * from t1_simple where id = 1 lock in share mode; 
-- 加记录排它锁
select * from t1_simple where id = 1 for upd

间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。

（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

临键锁(Next-Key Locks)

（1）record lock + gap lock, 左开右闭区间 ，例如（ 5,8] 。

（2）默认情况下，innodb 使用 next-key locks 来锁定记录。 select … for update

（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

（4）Next-Key Lock 在不同的场景中会退化 :

插入意向锁(Insert Intention Locks)

插入意图锁是一种间隙锁，由行插入之前的insert操作设置。此锁定仅表示插入的意图，如果插入到同一索引间隙中的多个事务没有插入到间隙中的同一位置，则它们不需要等待对方。

假设：

存在值为4和7的索引记录。尝试分别插入值为5和6的单独事务，在获得插入行的排他锁之前，每个事务都使用插入意图锁锁定4和7之间的间隙，但不要相互阻塞，因为这些行是非冲突的。

行锁加锁规则

1）主键索引

1. 等值查询

（1）命中记录，加记录锁。

（2）未命中记录，加间隙锁。

2. 范围查询

（1）没有命中任何一条记录时，加间隙锁。

（2）命中1条或者多条，包含 where 条件的临键区间，加临键锁

2）辅助索引

1. 等值查询

（1）命中记录，命中记录的辅助索引项+ 主键索引项加记录锁，辅助索引项两侧加间隙锁。

（2）未命中记录，加间隙锁

2. 范围查询

（1）没有命中任何一条记录时，加间隙锁。

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

行锁案例分析

delete from t1 where id = 10;

上面的SQL，他们加什么锁？

要分析这个问题需要知道一些前提条件：

id 列是不是主键？

当前系统的隔离级别是什么？

id 列如果不是主键，那么 id 列上有索引吗？

组合一：id列是主键，RC隔离级别

id 是主键时，此 SQL 只需要在 id=10 这条记录上加 X 锁即可。

组合二： id 唯一索引 +RC

若id 列有 unique 索引。那么需要先通过id=10查到主键，所以 SQL 需要加两个 X 锁，一个对应于 id unique 索引上的 id = 10的记录，另一把锁对应于聚簇索引上(id=10对应的主键)的的记录。

组合三：id 非唯一索引 +RC

若id 列上有非唯一索引，那么对应的所有满足 SQL 查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

组合四：id 无索引 +RC

若id 列上没有索引， SQL 会走聚簇索引的全扫描进行过滤，由于过滤是由 MySQL Server 层面进行的。因此每条记录，无论是否满足条件，都会被加上X 锁。但是，为了效率考量， MySQL 做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/ 放锁动作不会省略。同时，优化也违背了2PL 的约束。

组合五：id主键 +RR

与组合一（ id列是主键，RC隔离级别 ）一致

组合六：id 唯一索引 +RR

与组合二（id唯一索引+RC）一致

组合七： id 非唯一索引 +RR

RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。