深入理解 MySQL ——锁、事务与并发控制

最新推荐文章于 2024-01-13 19:09:30 发布

IT运维社区

最新推荐文章于 2024-01-13 19:09:30 发布

阅读量149

点赞数

分类专栏：运维文章标签：运维 mysql

本文链接：https://blog.csdn.net/weixin_52170224/article/details/109458187

版权

运维专栏收录该内容

3 篇文章 0 订阅

订阅专栏

@[toc]深入理解 MySQL ——锁、事务与并发控制

本文对 MySQL 数据库中有关锁、事务及并发控制的知识及其原理做了系统化的介绍和总结，希望帮助读者能更加深刻地理解 MySQL 中的锁和事务，从而在业务系统开发过程中可以更好地优化与数据库的交互。

1、MySQL 服务器逻辑架构

在这里插入图片描述

每个连接都会在 MySQL 服务端产生一个线程（内部通过线程池管理线程），比如一个 select 语句进入，MySQL 首先会在查询缓存中查找是否缓存了这个 select 的结果集，如果没有则继续执行解析、优化、执行的过程；否则会之间从缓存中获取结果集。

2.1、Shared and Exclusive Locks （共享锁与排他锁）

它们都是标准的行级锁。

共享锁（S） 共享锁也称为读锁，读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰；

排他锁（X） 排他锁也称为写锁，一个写锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。

注意：所谓共享锁、排他锁其实均是锁机制本身的策略，通过这两种策略对锁做了区分。

2.2、Intention Locks（意向锁）

InnoDB 支持多粒度锁(锁粒度可分为行锁和表锁)，允许行锁和表锁共存。例如，一个语句，例如 LOCK TABLES…WRITE 接受指定表上的独占锁。为了实现多粒度级别的锁定，InnoDB 使用了意图锁。

意向锁：表级别的锁。先提前声明一个意向，并获取表级别的意向锁（共享意向锁 IS 或排他意向锁 IX），如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁(S或X)了。（除了 LOCK TABLES … WRITE,会锁住表中所有行，其他场景意向锁实际不锁住任何行)

举例来说：

SELECT … LOCK IN SHARE MODE，要获取IS锁；An intention shared lock (IS)

SELECT … FOR UPDATE ，要获取IX锁；An intention exclusive lock (IX) i

意向锁协议 在事务能够获取表中的行上的共享锁之前，它必须首先获取表上的IS锁或更强的锁。在事务能够获取表中的行上的独占锁之前，它必须首先获取表上的IX锁。

前文说了，意向锁实现的背景是多粒度锁的并存场景。如下兼容性的汇总：
在这里插入图片描述

意向锁仅表意向，是一种较弱的锁，意向锁之间兼容并行(IS、IX 之间关系兼容并行)。 X与IS\IX互斥；S与IX互斥。可以体会到，意向锁是比X\S更弱的锁，存在一种预判的意义！先获取更弱的IX\IS锁，如果获取失败就不必要再花费跟大开销获取更强的X\S锁 … …

2.3、Record Locks (索引行锁)

record lock 是一个在索引行记录的锁。

比如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE，如果c1 上的索引被使用到。防止任何其他事务变动 c1 = 10 的行。

record lock 总是会在索引行上加锁。即使一个表并没有设置任何索引，这种时候 innoDB 会创建一个隐式的聚集索引（primary Key）,然后在这个聚集索引上加锁。

**当查询字段没有索引时，**比如 update table set columnA="A" where columnB=“B".如果 columnB 字段不存在索引（或者不是组合索引前缀），这条语句会锁住所有记录也就是锁表。如果语句的执行能够执行一个 columnB 字段的索引，那么仅会锁住满足 where 的行(RecordLock)。

锁出现查看示例：

(使用 show engine innodb status 命令查看)：

```范围查询
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.4、Gap locks（间隙锁）

Gap Locks: 锁定索引记录之间的间隙([2])，或者锁定一个索引记录之前的间隙([1])，或者锁定一个索引记录之后的间隙([3])。

示例：如图[1]、[2]、[3]部分。一般作用于我们的范围筛选查询> 、< 、between…

![](https://img-blog.csdnimg.cn/img_convert/db2ba50d222a92cbd21d24529e46f53a.png例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事务将值3插入到列 userId 中。因为该范围内所有现有值之间的间隙都是锁定的。

在这里插入图片描述

对于使用唯一索引来搜索唯一行的语句 select a from ，不产生间隙锁定。(不包含组合唯一索引，也就是说 gapLock 不作用于单列唯一索引）

例如，如果id列有唯一的索引，下面的语句只对id值为100的行使用索引记录锁，其他会话是否在前一个间隙中插入行并不重要:

``` SELECT * FROM t1 WHERE id = 100;

```如果id**没有索引或具有非惟一索引，则语句将锁定前面的间隙**。

间隙可以跨越单个索引值、多个索引值(如上图2,3)，甚至是空的。

间隙锁是性能和并发性之间权衡的一种折衷，用于某些特定的事务隔离级别，如RC级别（RC级别：REPEATABLE READ，我司为了减少死锁，关闭了gap锁，使用RR级别）。

在重叠的间隙中（或者说重叠的行记录）中允许gap共存

比如同一个 gap 中，允许一个事务持有 gap X-Lock（gap 写锁\排他锁)，同时另一个事务在这个 gap 中持有(gap 写锁\排他锁)

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_new_table_a` (`a`),
  KEY `idx_new_table_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

INSERT INTO `new_table` VALUES (1,1,'1'),(2,3,'2'),(3,5,'3'),(4,8,'4'),(5,11,'5'),(6,2,'6'),(7,2,'7'),(8,2,'8'),(9,4,'9'),(10,4,'10');



######## 事务一 ########
START TRANSACTION;
SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE;
##暂不commit

######## 事务二 ########

SELECT * FROM new_table WHERE a = 4 FOR UPDATE;

##顺利执行！ 因为gap锁可以共存；


######## 事务三 ########

 SELECT * FROM new_table WHERE b = 3 FOR UPDATE;

##获取锁超时，失败。因为事务一的gap锁定了 b=3的数据。

**2.5、**next-key lock

next-key lock 是 record lock 与 gap lock 的组合。

比如存在一个查询匹配 b=3 的行(b上有个非唯一索引)，那么所谓 NextLock 就是：在b=3 的行加了 RecordLock 并且使用 GapLock 锁定了 b=3 之前（“之前”：索引排序）的所有行记录。

MySQL 查询时执行行级锁策略，会对扫描过程中匹配的行进行加锁（X 或 S），也就是加Record Lock，同时会对这个记录之前的所有行加 GapLock 锁。假设一个索引包含值10、11、13和20。该索引可能的NexKey Lock锁定以下区间：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

另外，值得一提的是： innodb 中默认隔离级别(RR)下，next key Lock 自动开启。 （很好理解，因为 gap 作用于RR，如果是 RC，gapLock 不会生效，那么 next key lock 自然也不会）

锁出现查看示例： (使用 show engine innodb status 命令查看)：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

2.6、Insert Intention Locks（插入意向锁）

一个 insert intention lock 是一种发生在 insert 插入语句时的 gap lock 间隙锁，锁定插入行之前的所有行。

这个锁以这样一种方式表明插入的意图，如果插入到同一索引间隙中的多个事务没有插入到该间隙中的相同位置，则它们不需要等待对方。

假设存在值为4和7的索引记录。尝试分别插入值为5和6的独立事务，在获得所插入行上的独占锁之前，每个事务使用 insert intention lock 锁定4和7之间的间隙，但不会阻塞彼此，因为这些行不冲突。

示例：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

##事务一
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

##事务二

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
##失败，已被锁定

mysql> SHOW ENGINE INNODB STATUS

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

2.7、 AUTO-INC Locks

AUTO-INC 锁是一种特殊的表级锁，产生于这样的场景：事务插入(inserting into )到具有 AUTO_INCREMENT 列的表中。

在最简单的情况下，如果一个事务正在向表中插入值，那么其他任何事务必须等待向该表中插入它们自己的值，以便由第一个事务插入的行接收连续的主键值。

2.8 Predicate Locks for Spatial Indexes 空间索引的谓词锁

略

3、事务

事务就是一组原子性的 sql，或者说一个独立的工作单元。事务就是说，要么 MySQL 引擎会全部执行这一组sql语句，要么全部都不执行（比如其中一条语句失败的话）。

自动提交（AutoCommit，MySQL 默认）

show variables like "autocommit";

set autocommit=0; //0表示AutoCommit关闭
set autocommit=1; //1表示AutoCommit开启

MySQL 默认采用 AutoCommit 模式，也就是每个 sql 都是一个事务，并不需要显示的执行事务。如果 autoCommit 关闭，那么每个 sql 都默认开启一个事务，只有显式的执行“commit”后这个事务才会被提交。

显****示事务 (START TRANSACTION…COMMIT)

比如，tim 要给 bill 转账100块钱：

1.检查 tim 的账户余额是否大于100块；

2.tim 的账户减少100块；

3.bill 的账户增加100块；

这三个操作就是一个事务，必须打包执行，要么全部成功，要么全部不执行，其中任何一个操作的失败都会导致所有三个操作“不执行”——回滚。

CREATE DATABASE IF NOT EXISTS employees;
USE employees;

CREATE TABLE `employees`.`account` (
  `id` BIGINT (11) NOT NULL AUTO_INCREMENT,
  `p_name` VARCHAR (4),
  `p_money` DECIMAL (10, 2) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ;
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('1', 'tim', '200'); 
INSERT INTO `employees`.`account` (`id`, `p_name`, `p_money`) VALUES ('2', 'bill', '200'); 

START TRANSACTION;
SELECT p_money FROM account WHERE p_name="tim";-- step1
UPDATE account SET p_money=p_money-100 WHERE p_name="tim";-- step2
UPDATE account SET p_money=p_money+100 WHERE p_name="bill";-- step3
COMMIT;

一个良好的事务系统，必须满足ACID特点：

3.1、事务的ACID：

A:atomiciy 原子性：一个事务必须保证其中的操作要么全部执行，要么全部回滚，不可能存在只执行了一部分这种情况出现。

C:consistency 一致性：数据必须保证从一种一致性的状态转换为另一种一致性状态。 比如上一个事务中执行了第二步时系统崩溃了，数据也不会出现 bill 的账户少了100块，但是 tim 的账户没变的情况。要么维持原装（全部回滚），要么 bill 少了100块同时 tim 多了100块，只有这两种一致性状态的。

I：isolation 隔离性：在一个事务未执行完毕时，通常会保证其他 Session 无法看到这个事务的执行结果。

D:durability 持久性：事务一旦 commit，则数据就会保存下来，即使提交完之后系统崩溃，数据也不会丢失。
更多运维交流请关注我
IT运维社区

IT运维社区

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
深入理解 MySQL ——锁、事务与并发控制

@[toc]深入理解 MySQL ——锁、事务与并发控制本文对 MySQL 数据库中有关锁、事务及并发控制的知识及其原理做了系统化的介绍和总结，希望帮助读者能更加深刻地理解 MySQL 中的锁和事务，从而在业务系统开发过程中可以更好地优化与数据库的交互。1、MySQL 服务器逻辑架构每个连接都会在 MySQL 服务端产生一个线程（内部通过线程池管理线程），比如一个 select 语句进入，MySQL 首先会在查询缓存中查找是否缓存了这个 select 的结果集，如果没有则继续执行解析、优化、执行的
复制链接

扫一扫