mysql锁

最新推荐文章于 2024-11-07 11:56:55 发布

GYX--UP

最新推荐文章于 2024-11-07 11:56:55 发布

阅读量802

点赞数

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

本文链接：https://blog.csdn.net/weixin_45797834/article/details/124663222

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

锁

按照锁的粒度划分：行锁、表锁、页锁
按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）
还有两种思想上的锁：悲观锁、乐观锁。
InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock
Record Lock：在索引记录上加锁
Gap Lock：间隙锁
Next-key Lock：Record Lock+Gap Lock

锁粒度类型

行锁

粒度最细。

行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

共享锁（s锁/读锁）

select … lock in share mode;

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁（x锁/写锁）

select … for update

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

表锁

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

意向锁

例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

意向共享锁（IS）

事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

用法

LOCK TABLE table_name [ AS alias_name ] READ

意向排他锁（IX）

事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

用法

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁用法

unlock tables;

页锁

粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。

锁使用方法类型

共享锁

排他锁

innodb中行锁类型

Record Lock（记录锁）

记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

Gap Lock（间隙锁）

间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。

这里的共同间隙包括两种场景：其一是两个间隙锁的间隙区间完全一样；其二是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

1.举例间隙锁

mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
|  1 | 伊利   |    68 |   1 |
|  2 | 蒙牛   |    88 |   1 |
|  6 | tom    |  2788 |   3 |
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+
其中id为主键 num为普通索引
窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+
1 row in set

窗口B：
mysql> insert into product_copy values(5,'kris',1888,2);
这里会等待  直到窗口A commit才会显示下面结果
Query OK, 1 row affected

但是下面是不需要等待的
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
mysql> insert into product_copy values(5,'kris',1888,5);
Query OK, 1 row affected

通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表，我更新num=1，5的数据是可以的.可以看出锁住的范围是（1,3]U[3,4)。

2.举例主键索引/唯一索引+当前读会加上Gap锁吗？

窗口A：
mysql> select * from product_copy where id=6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+

窗口B：并不会发生等待
mysql> insert into product_copy values(5,'kris',1888,3);
Query OK, 1 row affected

例子说明的其实就是行锁的原因，我只将id=6的行数据锁住了，用Gap锁的原理来解释的话：因为主键索引和唯一索引的值只有一个，所以满足检索条件的只有一行，故并不会出现幻读，所以并不会加上Gap锁。

3. 举例通过范围查询是否会加上Gap锁

窗口A：
mysql> select * from product_copy where num>3 for update;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+

窗口B：会等待
mysql> insert into product_copy values(11,'kris',1888,5);
Query OK, 1 row affected
不会等待
mysql> insert into product_copy values(3,'kris',1888,2);
Query OK, 1 row affected

其实原因都是一样，只要满足检索条件的都会加上Gap锁

4.举例检索条件并不存在的当前读会加上Gap吗？

1.等值查询

窗口A：
mysql> select * from product_copy where num=5 for update;
Empty set

窗口B：6 和 4都会等待
mysql> insert into product_copy values(11,'kris',1888,6);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

原因一样会锁住（4,5]U[5,n）的区间

2.范围查询

窗口A：
mysql> select * from product_copy where num>6 for update;
Empty set
窗口B：8 和 4 都会锁住
mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,8);
Query OK, 1 row affected

上面的2例子看出当你查询并不存在的数据的时候，mysql会将有可能出现区间全部锁住。

间隙锁解决的问题

能够解决幻读的问题。

间隙锁只在可重复读和可串行化这两个隔离级别下产生（在RU和RC两种隔离级别下，即使你使用select … in share mode或select … for update，也无法防止幻读（读后写的场景）。因为这两种隔离级别下只会有行锁，而不会有间隙锁。）

为什么RU和RC隔离级别下不会产生间隙锁？

因为首先这两种隔离级别比较低，他们本身就会产生幻读这个问题，因此不需要间隙锁的产生。间隙锁是可以解决幻读的问题的。

Next-key Lock（临键锁）

是InnoDB加锁的基本单位，它是一个前开后闭的区间，即行锁+间隙锁

（1）record lock + gap lock, 左开右闭区间。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:

在这里插入图片描述

innodb加锁规则

两个“原则”、两个“优化”和一个“bug”：

原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

参考

https://mp.weixin.qq.com/s/fmSHG0SejfD0IdnpIYHT9w
https://blog.csdn.net/Saintyyu/article/details/91269087?ops_request_misc=&request_id=&biz_id=102&utm_term=mysql%E9%94%81&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-1-91269087.nonecase&spm=1018.2226.3001.4187