MySQL Innodb 锁介绍 简单记录下mysql的东东 为何加锁及事务隔离级别介绍 锁的分类 加锁分析 分析死锁 为什么要加锁 总所周知数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一 ..

Mysql Innodb 锁(摘录)

MySQL Innodb 锁介绍

简单记录下mysql的东东
  1. 为何加锁及事务隔离级别介绍

  2. 锁的分类

  3. 加锁分析

  4. 分析死锁

为什么要加锁

总所周知数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

数据库有 ACID 原则:

  1. 原子性(Atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性

  2. 一致性(Consistency): 数据库总是从一个一致性的状态转换到另一个一致性的状态。

  3. 持久性(Durability):一旦事务提交,则其所做的修改不会永久保存到数据库。

  4. 隔离性(Isolation):一个事务所做的修改在最终提交以前,对其他事务是不可见的。

其中 I 是隔离性,标准 SQL 规范中定义了四种隔离级别:
image.png
通过 show global variables like '%iso%';可以看出 MySQL 默认隔离级别是可重复读.
上图隔离级别越往下,隔离级别越高,问题越少,同时并发度也越低。隔离级别和并发度成反比的。

  1. 脏读:事务 A 读取了事务 B 未提交的数据
  2. 不可重复读:对于一条记录,事务 A 两次读取的数据变了
  3. 幻读:事务 A 按照相同的查询条件,读取到了新增的数据
    image.png

和标准 SQL 规范相比,MySQL 中可重复读解决了脏读,不可能重复度,实现了串行化隔离级别的功能,同时没有严重影响并发。是通过加锁、阻止插入新数据,来解决幻读。

通过客户端修改事务隔离级别演示脏读,不可重复度,幻读

准备数据:

CREATE TABLE `user` (  
 `id` int(11) NOT NULL,  
 `name` varchar(20) DEFAULT NULL,  
 `sex` varchar(5) DEFAULT NULL,  
 PRIMARY KEY (`id`),  
 KEY `name` (`name`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`user`(`id`, `name`, `sex`) VALUES (4, 'lala', 'm');
INSERT INTO `test`.`user`(`id`, `name`, `sex`) VALUES (5, 'lala', 'm');
INSERT INTO `test`.`user`(`id`, `name`, `sex`) VALUES (6, 'lala', 'm');

开始操作:
分别开启两个 MySQL 客户端设置当前会话隔离级别为读未提交与关闭自动提交

set session transaction isolation level read uncommitted;
set autocommit = 0;

通过show session variables like '%iso%';
show session variables like 'autocommit';查看是否设置成功

下面操作按中括号[]数字大小顺序进行操作(先执行[0],在执行[1],依次类推)

#客户端1
#1. 开启事务
start TRANSACTION 	[0]
#2. 查看数据
select * from  user		[1] 

update user set sex ='123' where id =4		[4]
INSERT into user(id,name,sex) values (7,'lala','m')		[8]
delete from user where id = 5			[6]
COMMIT

#客户端2
#1 开启事务
start TRANSACTION 	[2]
#2 查看数据
select * from user		[3]		[5]		[7]		[9]

COMMIT

执行到第五步会发现客户端 2 中事务 2 查询的数据发生了改变(脏读)
执行到第七步会发现客户端 2 中事务 2 查询的数据(id =5)不见了(不可重复读)
执行到第九步会发现客户端 2 中事务 2 查询的数据多了一条(id =7)(幻读)

注:幻读与不可重复读类似,不可重复读是读到了其他事务 update/delete 的结果,幻读是读到了其他事务 insert 的结果,分这么细真是觉得没啥必要

锁的分类

image.png

锁的种类繁多比如读锁、写锁、共享锁、互斥锁、行锁等等各种名词,根据自己的理解,简单对这些锁进行了分类。
image.png

加锁机制:

  1. 乐观锁:先修改,保存时判断是够被更新过,应用级别(CAS:Compare-and-Swap,比较并替换)

  2. 悲观锁:先获取锁,再操作修改,数据库级别

锁粒度:

表级锁:开销小,加锁快,粒度大,锁冲突概率大,并发度低,适用于读多写少的情况。

页级锁:BDB 存储引擎(数据库数据按页存储)

行级锁:Innodb 存储引擎,默认选项

兼容性:

下面这个表格是锁冲突矩阵,可以看到只有读锁和读锁之间兼容的,写锁和读锁、写锁都是冲突的。
image.png

冲突的时候会阻塞当前会话,直到拿到锁或者超时,开启两个事务这个很好模拟。
这里要提到的一点是,S 锁 和 X 锁是可以是表锁,也可以是行锁。

索引组织表
image.png
image.png

Innodb 中的索引数据结构是 B+ 树,数据是有序排列的,从根节点到叶子节点一层层找到对应的数据。普通索引,也叫做辅助索引,该(叶子 🍃)节点存放的是主键值。主键上的索引叫做聚集索引,表里的每一条记录都存放在主键的节点上。当通过辅助索引 select 查询数据的时候,会先在辅助索引中找到对应的主键值,然后用主键值在聚集索引中找到该条记录。举个例子,用 name=Alice 来查询的时候,会先找到对应的主键值是 18 ,然后用 18 在下面的聚集索引中找到 name=Alice 的记录内容是 77 和 Alice。

了解了索引数据结构的目的是为了说明,行锁是加在索引上的。

1.select * from user where id=10 for update
image.png
一条简单的 SQL。在 user 表中查找 id 为 10 的记录,并用 for update 加 X 锁。
这里 User 表中,有 3 个字段, 主键 id 和 另外一个字段 name。下面的表格是 B+ 树索引的简化表达。第一行 id 是索引的节点,第二行和第三行是这行记录,包含了姓名和性别。

如图所示,通过锁住聚集索引中的节点来锁住这条记录。

聚集索引上的锁,比较好理解,锁住 id=10 的索引,即锁住了这条记录。

2. select * from user where name=‘d’ for update
image.png

查询 user 表中 name 为 d 的记录,并用 for update 加 X 锁

这里的 name 上加了唯一索引,唯一索引本质上是辅助索引,加了唯一约束。所以会先在辅助索引上找到 name 为 d 的索引记录,在辅助索引中加锁,然后查找聚集索引,锁住对应索引记录。

为什么聚簇索引上的记录也要加锁?试想一下,如果有并发的另外一个 SQL,是直接通过主键索引 id=30 来更新,会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话,两个并发 SQL 之间是互相感知不到的。

3. select * from user where name=‘b’ for update
image.png
查询 user 表中 name 为 b 的记录,并用 for update 加 X 锁。这里 name 上加了普通的索引,不是唯一索引。普通索引的值是可以重复的。会先在辅助索引中找到 name 为 b 的两条记录,加 X 锁,然后得到主键值 7 和 30,到聚集索引中加 X 锁。

事情并没有那么简单,如果这时有另一个事务,插入了 name=b,id=40 的记录,却发现是可以插入的。
image.png
位置在途中红色线条标注的间隙内,这样就会出现幻读,两次查询得到的结果是不一致的,第一次查到两条数据,插入之后得到三条数据。

为了防止这种情况,出现了另一种锁,gap lcok 间隙锁。锁住的是索引的间隙。
image.png
即图中,红色线条标识的空隙。因为新插入 name=b 的记录,可能出现在这三个间隙内。

这张图里出现了三种锁

记录锁:单行记录上的锁

间隙锁:锁定记录之间的范围,但不包含记录本身。

Next Key Lock: 记录锁 + 间隙锁,锁定一个范围,包含记录本身。

4. 意向锁( Intention Locks )
InnoDB 为了支持多粒度(表锁与行锁)的锁并存,引入意向锁。意向锁是表级锁,

IS: 意向共享锁
IX: 意向排他锁

事务在请求某一行的 S 锁和 X 锁前,需要先获得对应表的 IS、IX 锁。

意向锁产生的主要目的是为了处理行锁和表锁之间的冲突,用于表明“某个事务正在某一行上持有了锁,或者准备去持有锁”。比如,表中的某一行上加了 X 锁,就不能对这张表加 X 锁。
如果不在表上加意向锁,对表加锁的时候,都要去检查表中的某一行上是否加有行锁,多麻烦。
image.png

5. 插入意向锁(Insert Intention Lock)
Gap Lock 中存在一种插入意向锁,在 insert 操作时产生。

有两个作用:

加锁分析

后面会有多个 SQL 语句,先说明一下表结构

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_no` varchar(255) DEFAULT NULL COMMENT '身份证号',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `mobile` varchar(255) DEFAULT NULL COMMENT '手机号',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_id_no` (`id_no`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 COMMENT='用户表';

这里有一个 user 表,5 个字段,其中 id 是主键,id_no 是身份证号,加了唯一索引,name 是用户姓名,可以重复的,加了普通索引,手机号、年龄、地址都没有索引。
1. 普通 select

select  * from user where id =1;

begin;
select  * from user where id =1;
commit:

普通的 select 语句是不加锁的。select 包裹在事务中,同样也是不加锁的。where 后面的条件不管多少,普通的 select 是不加锁的。

2. 显式加锁

select  * from user where id =1 lock in share mode;

select  * from user where id =1 for update;

显式指出要加什么样的锁。上面一个加的是共享锁,下面的是互斥锁。

这里需要强调的一点,需要明确在事务中是用这些锁,不在事务中是没有意义的。

3. 隐式加锁

update user set address '北京' where id=1;
delete from user where id=1;

update 和 delete 也会对查询出的记录加 X 锁,隐式加互斥锁。加锁类型和 for update 类似。

4. 按索引类型

select  * from user where id =1 for update;

select  * from user where id_no ='a22' for update;

select  * from user where name ='王二' for update;

select  * from user where address ='杭州' for update;

四条 SQL,区别在于 where 条件的过滤列,分别是主键、唯一索引、普通索引、无索引。
image.png

主键:之前提到过索引组织表,这里会在聚集索引上对查询出的记录,加 X 锁

唯一索引:会在辅助索引上,把在对应的 id_no=a22 的索引加 X 锁,因为是唯一的,所以不是 next-key 锁。然后在主键上,也会在这条记录上加 X 锁。

普通索引:因为不是唯一的,会在辅助索引上,把对应的 id_no=a22 的索引加 next-key 锁。然后在主键加 X 锁。

无索引:首先,是不推荐这种写法,没有索引的话,因为会全表扫描,数据量大的话查询会很慢。这里讨论的是,这种情况下,会加什么锁? 答案: 首先,聚簇索引上的所有记录,都被加上了 X 锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了 GAP 锁。在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发 SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。这是一个很恐怖的事情,请注意。

5. 记录不存在的情况
前面几个例子中,都是可以查到结果的。如果对应记录不存在会怎样?答案是锁住间隙,不允许插入。MySQL 要保证没有其他人可以插入,所以锁住间隙。

6. 普通 insert 语句
在插入之前,会先在插入记录所在的间隙加上一个插入意向锁。

insert 会对插入成功的行加上排它锁,这个排它锁是个记录锁,而非 next-key 锁(当然更不是 gap 锁了),不会阻止其他并发的事务往这条记录之前插入 。

分析死锁

先说一下死锁的定义,死锁是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。这个定义适用于数据库,有几个重点,两个或两个以上的事务,一个事务是不会出现死锁的。争夺的资源一般都是表或者记录。

出现死锁了会怎样,正常情况下,MySQL 会检查出死锁,并回滚某一个事务,让另一个事务正常运行。

MySQL 会回滚副作用小的事务,判定的标准是执行的时间以及影响的范围。

1.如何知道系统有没有发生过死锁,如何去查看发生过的锁
show status like 'innodb_row_lock%'
image.png
Innodb_row_lock_current_waits:当前正在等待锁的数量;

Innodb_row_lock_time :锁定的总时间长度,单位 ms;

Innodb_row_lock_time_avg :每次等待所花平均时间;

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;

Innodb_row_lock_waits :从系统启动到现在总共等待的次数。
平均时间和锁等待次数比较大的话,说明可能会存在锁争用情况

查看 innodb 存储引擎的运行状态
show engine innodb status
image.png

通过这个命令显示的内容比较多,其中有一项 lasted detected deadlock 显示最近发生的死锁。以及加了什么锁。

查看已开启事务的状态
select * from information_schema.innodb_trx;
trx_id:innodb 存储引擎内部事务唯一的事务 id。

trx_state:当前事务的状态。

trx_started:事务开始的时间。

trx_requested_lock_id:等待事务的锁 id,如 trx_state 的状态为 LOCK WAIT,那么该值代表当前事务之前占用锁资源的 id,如果 trx_state 不是 LOCK WAIT 的话,这个值为 null。

trx_wait_started:事务等待开始的时间。

trx_weight:事务的权重,反映了一个事务修改和锁住的行数。在 innodb 的存储引擎中,当发生死锁需要回滚时,innodb 存储引擎会选择该值最小的事务进行回滚。

trx_mysql_thread_id:正在运行的 MySQL 中的线程 id,show full processlist 显示的记录中的 thread_id。

trx_query:事务运行的 SQL 语句

完结 ~

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    468 引用 • 477 回帖 • 840 关注
回帖
请输入回帖内容...