【MySQL锁机制】2-MySQL中锁的分类

最新推荐文章于 2022-10-29 10:31:09 发布

风暴计划

最新推荐文章于 2022-10-29 10:31:09 发布

阅读量290

点赞数

分类专栏： MySQL+Redis基础知识文章标签： mysql MySQL中的共享锁和排它锁意向共享锁和意向排它锁 MySQL中的临键锁 MySQL中的行锁与表锁

本文链接：https://blog.csdn.net/cmm0401/article/details/115857079

版权

MySQL+Redis基础知识专栏收录该内容

36 篇文章 6 订阅

订阅专栏

1、MySQL中锁的简述

2、MySQL中常见的几种锁机制

2.1 读写锁【是一种行锁row lock】:可分为共享锁和排它锁

2.1.1 共享锁(S锁)与排它锁(X锁)的概念

2.1.2 另一种方式的读写锁、共享锁与排它锁的SQL演示

2.2 意向锁【表级锁table lock】:分为意向共享锁和意向排它锁

2.3 记录锁【record lock】

2.4 间隙锁【gap lock】

2.5 临键锁【next-key lock】

2.5.1 临键锁的概念

2.5.2 临键锁的SQL举例

99、参考

1、MySQL中锁的简述

数据库中的锁，是在多线程高并发的情况下用来保证数据一致性的一种机制。

MySQL 根据底层存储引擎的不同，锁的支持粒度和实现机制也是不同的。MySQL中常用的存储引擎有MyISAM和InnoDB。

其中，MyISAM存储引擎只支持表锁，InnoDB存储引擎既支持表锁也支持行锁。

目前，MySQL 默认的存储引擎是 InnoDB，所以这里主要介绍 InnoDB 的锁。InnoDB 存储引擎有两大优点：一是支持事务；二是支持行锁。

在高并发的情况下，MySQL 事务的并发处理会带来三个问题：脏读、不可重复读、幻读。由于高并发事务带来这三个问题，所以就产生了4种 MySQL中事务的隔离级别。

2、MySQL中常见的几种锁机制

MySQL中，锁的实现思想主要是：1、乐观锁。2、悲观锁。

MySQL中，锁粒度主要可以分为：1、行锁（row lock）。2、表锁（table lock）。3、页锁（page lock，暂不讨论）。

2.1 读写锁【是一种行锁row lock】:可分为共享锁和排它锁

2.1.1 共享锁(S锁)与排它锁(X锁)的概念

在 MySQL 的 InnoDB 存储引擎中，读写锁是一种行锁（row lock），它是通过共享锁和排他锁两种方式实现了标准的行锁。

读写锁（ReadWriteLock），可以分为即我们常说的共享锁和排他锁。

（1）共享锁，shared lock，简称 S 锁：允许事务在获得共享锁之后去读取数据。

详细解释：在一个事务获取某条记录的共享锁之后，允许其他事务继续获取该条记录的共享锁。此时，有多个事务都持有该条记录的共享锁，这多个事务都可以读取该条记录，但是这多个事务都不能对该条记录做修改操作。同时，不允许其他事务获取该条记录的排它锁。这也就是说：共享锁与共享锁之间是兼容的，共享锁与排它锁之间是不兼容的。

（2）排它锁，exclusive lock，简称 X 锁：允许事务在获得排它锁之后去更新或者删除数据。

详细解释：如果一个事务获取了某条记录的排它锁，那么，则不允许其他事务获取该条记录的排它锁或者共享锁，必须等到这个事务释放排它锁之后，其他等待中的事务才有机会获取到。这也就是说：排它锁与共享锁、或者排它锁与排它锁之间都是不兼容的。

2.1.2 另一种方式的读写锁、共享锁与排它锁的SQL演示

（1）lock table tableName read;

使用【读锁】锁表，会阻塞其他事务的写，但不会阻塞其他事务的读。此外：本事务只能读。

（2）lock table tableName write;

使用【写锁】锁表，会阻塞其他事务的读和写。此外：本事务可以读也可以写。

（3）select * from tableName where id = 3 lock in share mode;

使用【共享锁】锁住该条记录，仅对 id=3 这一行记录加了读锁，会阻塞其他事务的写，但不会阻塞其他事务的读。此外：本事务只能读。

（4）select * from tableName where id = 3 for update;

使用【排它锁】锁住该条记录，仅对 id=3 这一行记录加了写锁，会阻塞其他事务的读和写。此外：本事务可以读也可以写。

# T1
start transaction;
select * from tableName where category_no = 2 lock in share mode;  //显示加上共享锁
select * from tableName where category_no = 2 for update;          //显示加上排他锁
commit;

# T2
start transaction;
select * from tableName where category_no = 2 lock in share mode;  //显示加上共享锁
update tableName set mchopin_name = '动漫' where mchopin_no = 2;    //隐式加上排他锁
commit;

2.2 意向锁【表级锁table lock】:分为意向共享锁和意向排它锁

在 MySQL 的 InnoDB 存储引擎中，意向锁，intention locks，可以分为意向共享锁（IS）和意向排它锁（IX），依次表示接下来的一个事务将会获得共享锁还是排它锁。

意向锁，不需要用户显示获取，它是用户在获取共享锁或者排它锁的时候MySQL本身自动获取的。这也就是说，如果要获取共享锁或者排它锁，则一定是先获取到了意向共享锁或者意向排它锁。

意向锁不会锁住任何东西，除非有进行全表请求的操作，否则，它不会锁住任何数据。意向锁存在的意义：只是用来表示有事务正在锁某一行数据，或者将要锁某一行数据。

原文：Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

下面这张表的正确理解姿势：横向是已经持有的锁，纵向是正在请求的锁：

2.3 记录锁【record lock】

在 MySQL 的 InnoDB 存储引擎中，记录锁，锁的是索引项，而非记录行本身。如果InnoDB表中没有索引，那么，会自动创建一个隐藏的聚集索引，锁住的就是这个聚集索引。通过SQL语句锁住某一行数据：

（1）如果SQL语句走了表中的索引，那么记录锁是锁在索引上的。

（2）如果SQL语句没有走表中的索引，那么 InnoDB 会退化成表锁来锁住整张表。

所以，在进行SQL查询的时候尽量采用索引进行查询，这样大幅度可以降低锁的冲突。

2.4 间隙锁【gap lock】

在 MySQL 的 InnoDB 存储引擎中，间隙锁，gap lock，简称为 Gap锁，是一种记录行与记录行之间存在的空隙、或者在第一行记录之前、或者在最后一行记录之后产生的锁。间隙锁，可能占据单行、多行、或者是空记录。

（1）间隙锁的 加锁点：不是加在记录上的，而是加在两条记录之间的间隙上。

（2）间隙锁的作用：在同一个事务中，前后两次的当前读返回的是完全相同的记录，不会出现幻读问题。

通常情况下是采用【范围查找】的时候，间隙锁会发挥作用：比如在学生成绩管理系统中，如果此时有学生成绩 60，72，80，95，一个老师要查下成绩大于 72 的所有同学的信息，采用的SQL语句是：select * from student where grade > 72 for update，这个时候 InnoDB 锁住的不仅是 80，95 这两条记录，而是所有在（72-80）、（80-95）、以及（95 以上）的所有记录与间隙。

为什么会这样呢？因为不锁住这些行，另一个事务在此时插入了一条分数大于 72 的记录，会导致在第一次事务中前后两次查询的结果不一样，会出现幻读问题。所以，为了在满足事务隔离级别的情况下需要锁住所有满足条件的行及其间隙。

另外需要注意的是：

（1）幻读和不可重复读的区别点在于：幻读是数据条数增加了；不可重复读是数据被修改了或者数据被删除了。

（2）从锁上来分析，幻读的关键是使用 Gap 锁，而不可重复读的关键是使用行锁。

2.5 临键锁【next-key lock】

2.5.1 临键锁的概念

在 MySQL 的 InnoDB 存储引擎中，next-key lock，翻译过来是临键锁，它是一种记录锁和间隙锁的组合锁。是 2.3 和 2.4 的组合形式，既锁住行也锁住间隙，并且采用【左开右闭】的原则。

目前，InnoDB 存储引擎对于查询都是采用临键锁的！

2.5.2 临键锁的SQL举例

CREATE TABLE `xxp` (
  `id`  int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `xxp`(uid) VALUES (1);
INSERT INTO `xxp`(uid) VALUES (2);
INSERT INTO `xxp`(uid) VALUES (3);
INSERT INTO `xxp`(uid) VALUES (6);
INSERT INTO `xxp`(uid) VALUES (10);

# T1
start transaction;                           //1
select * from xxp where uid = 6 for update;  //2
commit;                                      //5

# T2
start transaction;                           //3
INSERT INto xxp(uid) VALUES(11);
INSERT INto xxp(uid) VALUES(5);              //4
INSERT INto xxp(uid) VALUES(7);
INSERT INto xxp(uid) VALUES(8);
INSERT INto xxp(uid) VALUES(9);
select * from xxp where uid = 6 for update;
commit;

rollback;

演示说明：按照上面【1、2、3、4】的顺序执行，会发现第4步被阻塞住了，必须执行完第5步之后第4步才能插入成功。这里会很奇怪：明明锁住的是 uid=6 的这一行记录，为什么不能插入 5 呢？原因就是：这里采用了临键锁的算法，锁住的是 ( 3 , 6 ] 、( 6 , 10 ] 整个区间。

99、参考

（1）https://blog.csdn.net/cmm0401/article/details/107299458

（2）https://www.jianshu.com/p/615f3c7fbe6f

（3）https://www.jianshu.com/p/f7142e39f455

（4）https://www.jianshu.com/p/b4731a7d255a

（5）https://www.cnblogs.com/116970u/p/11006198.html

风暴计划

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【MySQL锁机制】2-MySQL中锁的分类

目录1、MySQL中锁的简述2、MySQL中常见的几种锁机制2.1 读写锁【是一种行锁row lock】:可分为共享锁和排它锁2.1.1 共享锁(S锁)与排它锁(X锁)的概念2.1.2 另一种方式的读写锁、共享锁与排它锁的SQL演示2.2 意向锁【表级锁table lock】:分为意向共享锁和意向排它锁2.3 记录锁【record lock】2.4 间隙锁【gap lock】2.5 临键锁【next-key lock】2.5.1 临键锁的概念2.5.2 临键锁的S
复制链接

扫一扫