MySql之深入分析MySql 的锁

最新推荐文章于 2024-03-23 19:18:40 发布

东山富哥

最新推荐文章于 2024-03-23 19:18:40 发布

阅读量1.8k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/u014636209/article/details/104114296

版权

mysql 专栏收录该内容

26 篇文章 4 订阅

订阅专栏

文章目录

1.InnoDB 存储引擎的锁
2.隔离级别的实现
3.隔离级别的选择
4.死锁

数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
insert into emp 
values (1,"gaoxinfu11");
insert into emp 
values(2,"gaoxinfu22");
insert into emp 
values(3,"gaoxinfu33");
insert into emp 
values(4,"gaoxinfu44");

1.InnoDB 存储引擎的锁

1.0.锁存在的意义

1.实际上锁定的是数据库的资源(table表和数据记录)，解决的是资源竞争的问题

1.1.锁的类型 -八中类型

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
在这里插入图片描述

1.2.锁的基本模式

1.2.1.表锁

1.表锁，顾名思义，是锁住一张表；

1.2.2.行锁

1.行锁就是锁住表里面的一行数据。

1.2.3.关于行锁和表锁的粒度概述

1.锁的粒度主要是指加锁的数据范围；
2.表锁肯定是大于行锁的。
3.加锁效率，表锁也是大于行锁的；表锁只需要直接锁住这张表就行了，而行锁，还需要在表里面去检索这一行数据，所以表锁的加锁 效率更高。
4.表锁的冲突概率肯定是大于行锁的，
  因为当我们锁住一张表的时候，其他任何一个事务都不能操作这张表。但是我们锁住了表里面的一行数据的时候，
  其他的事务还可以来操作表里面的其他没有被锁定的行，所以表锁的冲突概率更大。
  表锁的冲突概率更大，所以并发性能更低

1.3.锁的类型

1.3.1.Shared Locks (共享锁) ----->行级别的锁

在这里插入图片描述

1.3.1.1.概念

1.共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改;

1.3.1.2.如何设置共享锁

select * from emp where id=1 LOCK IN SHARE MODE;

在share-mode-tx01这个事务中进行加锁

BEGIN;
select * from emp where id=1 LOCK IN SHARE MODE;

在这里插入图片描述

1.3.1.2.如何释放共享锁

commit或者rollback

1.3.1.3.案例1-查询加锁后查询

在share-mode-tx02事务中进行查询

BEGIN;
select * from emp where id=1 ;

在这里插入图片描述

结论

1.共享锁很明显，加锁之后，我们还是能够进行查询操作的；

1.3.1.3.案例2-查询加锁后更新

同样的share-mode-tx01事务中我们进行加锁之后，操作下面的事务
在share-mode-tx03事务中我们对同一条记录进行更新

BEGIN;
update emp set name='gaoxinfu-sm' where id=1;

在这里插入图片描述

1.同一条记录被加共享锁之后，因为被锁住，无法进行更新/删除等操作；

1.3.2.Exclusive Locks（排它锁）又称写锁或者X锁 ----->行级别的锁

在这里插入图片描述

1.3.2.1.概念

1.排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），
  只有该获取了排他锁的事务是可以对数据行进行读取和修改。

1.3.2.2.如何设置排它锁

自动加锁 delete/update/insert

delete/update/insert 默认加上X锁；

手动加锁 FOR UPDATE

手动：select * from emp where id=1 FOR UPDATE;

1.3.2.3.如何释放排它锁 commit/rollback;

commit/rollback;

1.3.2.3.案例演示

在事务exclusive-mode-01中进行更新id=1的记录

begin;
update emp set name='gaoxinfu-x-mode' where id=1;

在这里插入图片描述

在事务exclusive-mode-02中进行再次更新id=1的记录

begin;
update emp set name='gaoxinfu-x-mode-02' where id=1;

在这里插入图片描述

1.很明显由于在事务exclusive-mode-01之中没有提交(没有结束)也就是排它锁加锁之中，所以在事务exclusive-mode-02之中再次
  去更新的时候，不会成功；

1.3.3.意向锁

在这里插入图片描述

1.3.3.1.概念

1.首先意向锁是一个表锁，是对整个表的一个加锁操作；
2.意向锁包含两种：意向共享锁（Intention Shared Lock，简称IS锁）和意向排它锁(Intention Exclusive Lock)
3.意向锁是什么呢？我们好像从来没有听过，也从来没有使用过，其实他们是由数据 库自己维护的。
  也就是说，
  当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。
  当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。
  反过来讲：
  如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。 
  如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

1.3.3.1.如何设置意向锁

LOCK TABLES table_name WRITE/READ; #

1.3.3.2.意向锁的意义

第一个，我们有了表级别的锁，在 InnoDB 里面就可以支持更多粒度的锁。
第二个，我们想一下，如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么？是不是必须先要去
       判断有没其他的事务锁定了其中了某些行？如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才
       能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低？

但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，
   如果有，就直接返回失败。
   如果没有，就可以加锁成功。
所以InnoDB里面的表锁，我们可以把它理解成一个标志。就像火车上厕所有没有人使用的灯，是用来提高加锁的效率的。

1.3.3.3.意向锁使用场景

1.3.3.4.案例演示- 行锁之后进行表锁试验

在事务intention-mode-01这个事务中我们先手动加锁(排它锁)

BEGIN;
select * from emp where id=1 for update;

在这里插入图片描述

在intention-mode-02事务中对表进行加意向锁

BEGIN;
LOCK TABLES emp WRITE;

在这里插入图片描述

1.如上一旦如果表里记录有如果有被锁定的，那么我们去加表锁的话，是无法成功的；

1.3.3.5.案例演示- 表锁之后进行行锁试验

我们先在intention-mode-02这个事务之中先进行表的加锁

BEGIN;
LOCK TABLES emp WRITE;

在这里插入图片描述

然后在intention-mode-01这个事务中进行对记录id=1加锁

BEGIN;
select * from emp where id=1 for update;

在这里插入图片描述

1.如上表一旦被锁定，是无法进行行锁的

1.3.3.6.解锁

UNLOCK TABLES;

1.4.行锁的原理

在这里插入图片描述

1.下面我们对下面三种情况的表分别去研究一下行锁的问题

1.4.1.案例一：没有索引的表（假设锁住记录）

1.4.1.1.创建一个没有索引的表 emp01,插入三条记录

create table emp1 
(
id BIGINT,
name VARCHAR(100)
) ENGINE=INNODB DEFAULT charset=utf8;
insert into emp1 VALUES
(1,'gaoxinfu1');
insert into emp1 VALUES
(2,'gaoxinfu2');
insert into emp1 VALUES
(3,'gaoxinfu3');

1.4.1.2.在事务no-primary-key-01这个事务中进行锁定id=1的记录

BEGIN;
select * from emp1 where id=1 for update;

在这里插入图片描述

1.4.1.3.在事务no-primary-key-02这个事务中进行验证是否可以操作emp1表中的数据

1.再次对id=1进行加锁获取，发现无法加锁

select * from emp1 where id=1 for update;

在这里插入图片描述
2.查询非id=1的进行加锁，发现无法加锁成

select * from emp1 where id=2 for update;

在这里插入图片描述

3.插入新的记录，发现也是无法插入成功<----无法获取锁

在这里插入图片描述

1.4.2.案例二：有主键索引的表（假设锁住记录）

1.4.2.1.创建一个有主键索引的表 emp2,插入三条记录

create table emp2 
(
id BIGINT not null,
name VARCHAR(100) not null,
PRIMARY key(id)
) ENGINE=INNODB DEFAULT charset=utf8;
insert into emp2 VALUES
(1,'gaoxinfu1');
insert into emp2 VALUES
(2,'gaoxinfu2');
insert into emp2 VALUES
(3,'gaoxinfu3');

1.4.1.2.在事务primary-key-01这个事务中进行锁定id=1的记录

BEGIN;
select * from emp2 where id=1 for update;

在这里插入图片描述

1.4.1.2.在事务primary-key-02这个事务中进行锁定id=1的记录

先执行primary-key-01这个事务

select * from emp2 where id=1 for update;

在这里插入图片描述

1.很明显，同事加载肯定成功不了的；

1.4.1.3.在事务primary-key-03这个事务中进行锁定id=3的记录

先执行primary-key-01这个事务

select * from emp2 where id=3 for update;

在这里插入图片描述

1.从上面看我们查询id非1的是可以查询的

1.4.1.3.在事务primary-key-04这个事务中插入新的记录

先执行primary-key-01这个事务

insert into emp2 
values(
5,'gaoxinfu55'
)

在这里插入图片描述

1.很明显，也是可以插入记录的

1.4.3.案例三：唯一索引(这里不是主键)的表（假设锁住记录）

1.4.3.1.创建一张表emp3：主键id，唯一索引name字段

create table emp3
(
id BIGINT not null PRIMARY key,
name VARCHAR(100) not null UNIQUE
) ENGINE=INNODB DEFAULT charset=utf8;
insert into emp3 VALUES
(1,'gaoxinfu1');
insert into emp3 VALUES
(2,'gaoxinfu2');
insert into emp3 VALUES
(3,'gaoxinfu3');

1.4.3.2.在unique-key-01这个事务之中

BEGIN;
select * from emp3 WHERE name='gaoxinfu1' for update

在这里插入图片描述

1.4.3.3.在unique-key-02这个事务之中

在这里插入图片描述

1.对同一条记录，加锁之后，不管通过那个字段再去获取锁，都无法获取到；
2.这里实际上通过name这个索引，先找到主键索引，然后找到数据记录；

1.4.4.总结

那么我们还有两个问题没有解决：

1.4.4.1、为什么表里面没有索引的时候，锁住一行数据会导致锁表？或者说，如果锁住的是索引，一张表没有索引怎么办？

 所以，一张表有没有可能没有索引？

 1）如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。 
 2）如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索 引作为主键索引。
 3）如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作 为隐藏的聚集索引，它会随着行记录的写入而主键递增。 
       所以，为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

1.4.4.2、为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

   大家还记得在 InnoDB 里面，当我们使用辅助索引的时候，它是怎么检索数据的吗？ 辅助索引的叶子节点存储的是什么内容？ 
   在辅助索引里面，索引存储的是二级索引和主键的值。比如name=4，存储的是name 的索引和主键id的值4。 
   而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，
   会通过主键值找到主键索引，然后也锁定。

在这里插入图片描述

1.5.锁的算法

1.5.1.记录锁 (Record Lock)

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-record-locks
在这里插入图片描述

1.5.1.1.概念

1.对于唯一性的索引（包括唯一索引和主键索引）使用等值查询,精准匹配到一条记录加锁的时候，这个时候使用的就是记录锁；
2.记录锁，只会锁住这一行数据；

1.5.1.2.样例演示

1.这个案例我们在上面已经演示过了，比如我们对primary-key或者unique-key去加锁，
  这个时候就是锁住的是一条记录；
  参考的案例：1.4.2案例二，1.4.3.案例三
2.这里要注意下，我们说的是主键索引和唯一索引

1.5.2.间隙锁 (Gap Lock)

在这里插入图片描述

数据准备，创建表emp4，插入8条数据

CREATE table emp4
(
id BIGINT not null PRIMARY key,
name VARCHAR(100) not null UNIQUE
) ENGINE =INNODB DEFAULT charset=utf8;

insert into emp4
values(
1,'gaoxinfu1'
);
insert into emp4
values(
2,'gaoxinfu2'
);
insert into emp4
values(
3,'gaoxinfu3'
);
insert into emp4
values(
6,'gaoxinfu6'
);
COMMIT;

1.5.2.1.概念

1.间隙锁：锁住是一个范围；
2.另外间隙锁，锁住的范围内，是无法插入记录的；
3.间隙锁范围举例（数据源是上面我们刚刚准备的）：
     where id>3 and id<6 ------>（3，6）这个范围被锁住，加入你要插入id=4这个 是插入不成功的
     或者 where id=5  ------->由于id=5的这个记录不存在，所以会把id=5这个锁住，这个时候你插入id=5的记录，是插入不成功的；

在这里插入图片描述

1.5.2.2.案例一：where id>3 and id<6演示

在事务gap-locks-tx01这个事务中，锁定范围(3,6)

begin;
select * from emp4 where id>3 and id<6 for update;

在这里插入图片描述
在gap-locks-tx02这个事务中插入id=4的记录，发现无法插入成功

insert into emp4
values(
4,'gaoxinfu4'
);

在这里插入图片描述

1.5.2.3.案例一：id=5演示 <------------当查询的记录不存在的时候，使用间隙锁

在事务gap-locks-tx03锁定主键为id=5的记录

BEGIN;
select * from emp4 where id=5 for update ;

在这里插入图片描述
在事务gao-lock-tx04中插入主键id=5的记录

1.5.2.4.案例三：Gap锁的理解

在这里插入图片描述
在gap-locks-tx05事务中进行锁定，大家注意下，id>20 ，由于20在(6,+∞)范围内，实际上锁定的范围是(6,+∞)

1.这里的重点是看看你对比的这个id的值在的范围是哪里，如果在(6,+∞) 里面，锁定的就是这个范围(6,+∞)
  比如20在（6,+∞)这个范围里面，那么锁定的就是这个(6,+∞)
  比如5在(3,6)这个范围里面，那么锁定的就是(3,6)这个范围里面

在这里插入图片描述
在gap-locks-tx06这个事务中插入记录，很明显(6,+∞)这个范围由于被锁定了，所以插入id=7是无法插入的

1.5.3.临键锁 (Next-Key Locks)

在这里插入图片描述

数据准备

CREATE table emp5
(
id BIGINT not null PRIMARY key,
name VARCHAR(100) not null UNIQUE
) ENGINE =INNODB DEFAULT charset=utf8;

insert into emp5
values(
1,'gaoxinfu1'
);
insert into emp5
values(
2,'gaoxinfu2'
);
insert into emp5
values(
3,'gaoxinfu3'
);
insert into emp5
values(
6,'gaoxinfu6'
);
insert into emp5
values(
10,'gaoxinfu10'
);
COMMIT;

1.5.3.1.概念

1.我们上面记录锁是命中一条记录，间隙锁是命中一个固定范围的
  假如我们锁定一个范围，是跨了gap，而且里面我们命中值
  相当于包含上面的两种情况；

在这里插入图片描述

1.我们对我们准备的数据进行了分段，
  (-∞,1]
  (1,2]
  (2,3]
  (3,6] ---这里注意下，4，5不存在的id
  (6,10]---- 7，8，9是不存在的id
  (10,+∞)

1.5.3.1.案例说明 id >2 and id<5

BEGIN;
select * from emp5 where id >2 and id<5 for update; # 锁住了(2,3]和 (3,6] 并且命中了id=3

在这里插入图片描述

插入id=4的记录由于(2,3]和 (3,6] 已经被锁住，是无法插入记录的
在这里插入图片描述

1.实际上，间隙锁解决的问题就是幻读问题，锁定范围，不让插入数据，保证当前锁定的事务中，查询结果前后一致
  关于幻读的概念，大家可以参考下面的博客

https://blog.csdn.net/u014636209/article/details/104104413
在这里插入图片描述

2.隔离级别的实现

参考
https://blog.csdn.net/u014636209/article/details/104104413
在这里插入图片描述

2.1.Read Uncommited （RU）

 RU 隔离级别：不加锁。

2.2.Read Commited

    RC 隔离级别下，普通的 select 都是快照读，使用 MVCC 实现。 加锁的 select 都使用记录锁，因为没有 Gap Lock。
    除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复 键检查(duplicate-key checking)时会使用间隙锁封锁区间。 所以RC会出现幻读的问题。

2.3.Repeatable Read (RR)

RR隔离级别下，普通的 select 使用快照读(snapshot read)，底层使用 MVCC 来实现。
加锁的 select(select ... in share mode / select ... for update)以及更新操作 update, delete 等语句使用当前读（current read），
底层使用记录锁、或者间隙锁、 临键锁。

2.4.Serializable

Serializable 所有的 select 语句都会被隐式的转化为 select ... in share mode，会和update、delete 互斥。

3.隔离级别的选择

RU 和 Serializable 肯定不能用。为什么有些公司要用 RC，或者说网上有些文章推 荐有 RC？ 
RC 和 RR 主要有几个区别： 
 1、 RR 的间隙锁会导致锁定范围的扩大。 
 2、 条件列未使用到索引，RR 锁表，RC 锁行。 
 3、 RC 的“半一致性”（semi-consistent）读可以增加 update 操作的并发性。 
 在 RC 中，一个 update 语句，如果读到一行已经加锁的记录，此时 InnoDB 返回记 录最近提交的版本，
 由 MySQL 上层判断此版本是否满足 update 的 where 条件。若满 足(需要更新)，则 MySQL 会重新发起一次读操作，
 此时会读取行的最新版本(并加锁)
 
 实际上，如果能够正确地使用锁（避免不使用索引去枷锁），只锁定需要的数据， 用默认的 RR 级别就可以了。 
 在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥 的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，
 这个时候 会造成阻塞等待，如果循环等待，会有可能造成死锁。

4.死锁

4.1.锁的释放与阻塞

4.1.1.如何释放锁

4.1.1.1.commit/rollback

4.1.1.2.客户端连接断开

4.1.2.锁的超时时间设置

show VARIABLES like 'innodb_lock_wait_timeout'; # 锁定超时时间 默认是50秒

在这里插入图片描述

4.1.3.锁释放的意义

1.如果一个事务一直未释放锁，其他事务会被阻塞多久？会不会永远等待下去？
  在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。

4.2.死锁的的发生与检测

4.2.1.死锁的发生演示

数据准备：我们还是用前面的emp5这个表中的数据
在这里插入图片描述

4.2.1.1.在事务dead-locks-tx01中执行下面的SQL

BEGIN;
select * from emp5 where id=1 for update;

在这里插入图片描述

4.2.1.2.在事务dead-locks-tx02中执行下面的SQL

BEGIN;
select * from emp5 where id=2 for update;

在这里插入图片描述

4.2.1.3.在事务dead-locks-tx01中执行下面的SQL

select * from emp5 where id=2 for update;

在这里插入图片描述

1.可以看到出现了阻塞

4.2.1.4.在事务dead-locks-tx02中执行下面的SQL

select * from emp5 where id=1 for update;

在这里插入图片描述

4.2.1.5.总结

1.在4.2.1.1中我们锁定了id=1的记录
  在4.2.1.2中我们锁定了id=2的记录
  在4.2.1.3中我们可以看到由于id=2的就已经在dead-locks-tx02中已经被锁定，导致阻塞
  在4.2.1.4中我们可以看到由于id-2的就已经在dead-locks-tx01中已经被锁定，也阻塞，因此导致循环的阻塞，即死锁
2.通俗地讲死锁：你等我结束，我等你结束；

4.2.2.查看锁信息（日志）

4.2.2.1.show status查看

在这里插入图片描述

Innodb_row_lock_current_waits：当前正在等待锁定的数量； 
Innodb_row_lock_time ：从系统启动到现在锁定的总时间长度，单位 ms； 
Innodb_row_lock_time_avg ：每次等待所花平均时间； 
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间； 
Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

4.2.2.2. 表查看的数据

当前运行的所有事务

select * from information_schema.INNODB_TRX; -- 当前运行的所有事务 ，还有具体的语句

在这里插入图片描述

当前运行的所有事务

select * from information_schema.INNODB_LOCKS; -- 当前出现的锁

在这里插入图片描述

锁等待的对应关系

select * from information_schema.INNODB_LOCK_WAITS; -- 锁等待的对应关系

在这里插入图片描述

找出持有锁的事务之后呢？ 如果一个事务长时间持有锁不释放，可以kill事务对应的线程ID，也就是INNODB_TRX 表中的trx_mysql_thread_id，
例如执行 kill 4，kill 7，kill 8。 当然，死锁的问题不能每次都靠kill线程来解决，这是治标不治本的行为。
我们应该尽量在应用端，也就是在编码的过程中避免。有哪些可以避免死锁的方法呢？

4.3.死锁的避免

1、 在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）； 
2、 批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）； 
3、 申请足够级别的锁，如果要操作数据，就申请排它锁； 
4、 尽量使用索引访问数据，避免没有 where 条件的操作，避免锁表； 
5、 如果可以，大事务化成小事务； 
6、 使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响。