MySQL中的锁机制和MVCC

最新推荐文章于 2024-05-29 14:41:02 发布
最新推荐文章于 2024-05-29 14:41:02 发布
阅读量773 收藏 2
点赞数
分类专栏： mysql 文章标签： mysql
原文链接：https://blog.csdn.net/weixin_43184769/article/details/91350743
版权

MySQL中的锁和MVCC

 

在学习MySQL中的锁机制相关时搜集了几篇写得非常不错的博客，这里就不再花时间详细介绍，本篇仅做总结和重点部分摘录（摘自：传送门）。推荐先理解下面篇博客（写得很赞）： https://www.cnblogs.com/crazylqy/p/7611069.html

概述

InnoDB的MVCC

  • 概述： MVCC（Multi-Version Concurrency Control）多版本并发控制，MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，它在不同的数据库引擎中有不同的实现。MySQL中MVCC只能在Repeatable Read（读可重复读）、Read Committed（读可提交）这两个隔离级别下工作。
  • 用途： MVCC实现的是普通读取不加锁，并且读写不冲突，根据28定律，通常大部分为读操作，避免了读操作的加锁可以大大提高性能。
  • 原理：
  1. MVCC是通过保存了数据库某个时间的快照来实现的。也就是说当几个事务开启的时间不同，可能会出现同一时刻、不同事务读取同一张表的同一行记录是不一样的。这个机制也是可重复读的实现。

先看一个例子：

在一个与MySQL的连接中启动事务，读取tno为1的教师姓名，结果为tom（还未commit）
在这里插入图片描述
再启动第二个连接，将tno为1的教师名改成了jery

begin;
update teacher set tname="jery" where tno=1;
commit;
  • 1
  • 2
  • 3

此时，事务已经提交，我们再次从第一个连接的事务中查询tno为1的教师姓名
在这里插入图片描述
结果依然为tom，并没有读取到最新修改的数据jery，原因就在于每个事务读取的都是专有的快照。

  1. 在InnoDB引擎的数据库中，每一行记录后都有几个隐藏列来记录信息：

    先了解一下两个概念：

系统版本号： 每当启动一个事务时，系统版本号会递增。
事务版本号 事务开始时的系统版本号作为该事务的版本号，事务的版本号用于在select操作中与记录的DATA_TRX_ID字段做对比。

隐藏列：

DATA_TRX_ID： 记录了某行记录的系统版本号，每当事务commit对该行的修改操作时就会将。
DATA_ROLL_PTR： 记录了此行记录的回滚记录指针，找之前的历史版本就是通过它。
DELETE BIT： 标记此记录是否正在有事务删除它，最后真正的删除操作是在事务commit后。

  1. 增删改查中的MVCC操作：

select：①执行select操作时，InnoDB会查找到对应的数据行，并对比DATA_TRX_ID（版本号），要求数据行的版本必须小于等于事务的版本，如果当前数据行版本大于此事务版本，那么InnoDB会进入undo log中查找。确保当前事务读取的是事务之前存在的，或者是由当前事务创建或修改的行。 ② InnoDB会查找到对应的数据行后，查看DELETE BIT是否被定义，只允许未定义，或者删除的版本要大于此事务版本号。保证在执行此事务之前还未被删除。 当且仅当这两个条件都成立才允许返回select结果！

insert： InnoDB创建新记录，并以当前系统的版本号为新增记录的DATA_TRX_ID，如果需要回滚则丢弃undo log。

delete： InnoDB寻找到需要删除的记录，将此记录的DELETE BIT设置为系统当前版本号，若事务回滚则去除DELETE BIT定义的版本号，若事务提交则删除行。

update： InnoDB寻找到需要更新的行记录，复制了一条新的记录，新记录的版本ID为当前系统版本号，新记录的回滚指针指向原记录，将原记录的删除ID也设置为当前系统版本号。提交后则删除原记录，若回滚则删除复制的记录，并清除原记录的删除ID。

现在分析一下上一个例子：
假设当前tno=1的教师记录的DATA_TRX_ID = 2，那么第一个事务开启时系统版本号假设为3，在第一个事务中执行的查询操作只会读取DATA_TRX_ID <= 3的记录。此时第二个事务开启了，假设事务版本号为4，它执行了对该行数据的更新操作并提交了，新的记录中DATA_TRX_ID >= 4（期间可能还有其他事务的发送，使系统版本号增加）。

MVCC锁相关

  • 在MVCC中，读操作可以分成：快照读 (snapshot read)与当前读 (current read)。

快照读： 读取的是记录的可见版本，不加锁。
当前读： 读取的是记录的最新版本，并且会对读取的记录加上锁（有共享和排他锁），确保其他事务不会并发地修改这条记录。

快照读：简单的select操作属于快照读，不会加锁。 select * from table where id=1;

当前读：添加了关键字的特殊查询操作，或者update、delete、insert都属于当前读，需要加锁。这里的锁分为共享锁和排他锁（忘记概念了？传送门）。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
以上语句中除了第一条是共享锁（S锁），其他都是排他锁（X锁）

  • 为什么增删改也是当前读？ 因为要进行增删改之前都得先找到符合条件的行，找的过程不就是读嘛~为了保证数据的线程安全性，需要对当前行进行加锁，有时也会出现锁表。

  • lock in share mode和for update有何区别？

  1. 前者为记录添加的是S锁，后者添加的是X锁。共享锁和快照锁都不会影响快照读。
  2. 根据S锁和X锁的规则，当记录中有S锁时，其他事务允许快照读，或再添加一个S锁，但是不允许添加X锁，必须等所有S锁都被释放以后才能上X锁。
  3. 当记录中有X锁时，只允许快照读，不允许再添加X锁和S锁，直到该X锁释放（事务commit）。

SQL语句的加锁分析

首先介绍InnoDB中的锁。

Record lock： 给单挑索引的记录上锁，它锁的是索引而不是记录本身。如果没有指定主键索引，那么InnoDB会创建一个隐藏的主键索引，它本身是一个索引组织表。

Gap lock： 间隙锁，它是存在于某一条记录和前一条或者后一条之间间隙的锁，它只要是用于解决RR隔离级别下的幻读问题。举个例子：在b和a，b和c之间加入了间隙锁，那么b的前后相邻的位置都不能插入记录。
在这里插入图片描述

delete from t1 where id = 10;
  • 1

  1. 在id是主键+隔离级别RC。（RR相同）
    在这里插入图片描述
    主键是唯一的，只需要在id=10的这条记录的主键上加X锁即可

  2. id是唯一索引+隔离级别RC。（RR相同）
    在这里插入图片描述
    关于索引的总结可以看我的另一篇博客，有助于理解：传送门

  • 这里根据唯一索引找到索引表中的记录，再根据记录中的主键去寻找真正的数据行，加了两个锁分别在id=10的主键上和name=d的唯一索引上。
  • 为什么要两个列都加上锁？ 如果只给唯一索引上了锁，那么并发事务来了个where条件为name=d的update操作，那么此update并不知道该记录已经被delete操作锁定，违背了同一记录上的更新和删除操作串行执行的约束。

  1. id是非唯一索引+隔离级别为RC
    在这里插入图片描述
    同理，非唯一索引可能搜索到的结果有好几个记录，那么对所有满足的记录都加上锁。主键和非唯一索引都会上锁。

  2. id不是索引+隔离级别RC

在这里插入图片描述
由于条件中的id不是索引，那么InnoDB将会根据主键进行全表的遍历扫描，所有的记录的主键都会被加上X锁，即便在MySQL中有相关的优化，它会判断每条记录是否满足条件，如果不满足则会释放锁，直到最后加锁的是符合条件的记录。但是仍然无法避免对不满足条件的主键的加锁、释放锁的步骤。

  1. id是非唯一索引+隔离级别为RR

    先回顾一下隔离级别，RC中允许存在幻读和不可重复读，RR中解决了幻读和不可重复读，其中可重复读的实现是通过快照，幻读的解决则是通过MVCC。这个情况就是对幻读预防的原理。

在这里插入图片描述
我们将例子中的SQL语句换为查询会更好理解：

begin;
select * from T1 where id=10 for update;
commit;
  • 1
  • 2
  • 3

如图所示，在X锁的基础上加入了gap锁，它将非唯一索引之间、之前、之后的间隙都锁定上了，这意味着在这一次事务commit之前，其他事务不能再插入id=10的记录，更不可能去删除。那么在这一次的事务中重复执行该当前读语句，只能读取到快照的版本或者该事务自身修改的记录，也就杜绝了幻读！

  1. id不是索引+隔离级别RR

在这里插入图片描述
这个的情况和RC的类似，只是更可怕了，除了全表的X锁还有全表的gap锁，虽然也有类似的优化机制，会主动释放与条件不符合的索引的锁，但是性能依然不可观。这也是我们写SQL语句时需要避免的情况。

RR隔离级别是如何解决幻读的？

通过gap锁，将可能重复的记录之间的间隙锁上，其他事务无法并发的往间隙中进行插入。通过X锁锁定索引，其他事务无法并发进行删除。通过读取快照，每次只能读取到在此事务之前的历史版本或此事务修改的数据，实现可重复读。

死锁

简单的表结构。
在这里插入图片描述
情况一：

现有两个事务启动，T1和T2，对teacher表进行操作。顺序如图所示：
在这里插入图片描述
执行到③时T1等待T2，执行到④时T2又会等待T1，互相等待就造成了死锁。

情况二：

在这里插入图片描述
两个事务都只有一条SQL语句，但是仍然有可能造成死锁，原因在于事务对索引的加锁是逐个加锁。下面详细分析出现死锁时的情况：

  1. session1的加锁顺序为（1，hdc，100），（6，hdc，10）。session的加锁顺序以此类推。
  2. 那么在特定的情况下出现了如下的顺序：S1（1，hdc，100）—> S2（6，hdc，10）----> S1（6，hdc，10）—> S2（1，hdc，100）。也就出现了死锁。

简单总结： 从上面的两个例子中可以发现，死锁的发生关键在于并发下事务加锁的顺序。

模拟死锁

现在使用Springboot+Mybatis简单搭建环境操作数据库来模拟死锁。

teacher表结构：
在这里插入图片描述
TeacherMappper.xml

	<select id="getTeacherByLock" resultType="com.springboot.bean.Teacher">
        select * from teacher where tno=#{tno} for update;
    </select>

    <update id="updateTeacherByLock">
        update teacher set tname=#{tname} where tno=#{tno};
    </update>
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

TeacherMapper.java接口中的方法：

    Teacher getTeacherByLock(int tno);

    Teacher updateTeacherByLock(Teacher teacher);
  • 1
  • 2
  • 3

TeacherService.java中模拟死锁的业务逻辑，这里为了方便调试起见写了两个方法：

@Service
public class TeacherService {
    @Autowired
    TeacherMapper mapper;

    @Transactional
    public void deadLock(){
        //锁tno=1的
        Teacher teacher = mapper.getTeacherByLock(1);
        //更新tno=2的
        Teacher teacher2 = new Teacher();
        teacher2.setTno(2);
        teacher2.setTname("aaa");
        mapper.updateTeacher(teacher2);
        System.out.println();
    }

    @Transactional
    public void deadLock2(){

        //锁tno=2的
        Teacher teacher = mapper.getTeacherByLock(2);

        //更新tno=1的
        Teacher teacher2 = new Teacher();
        teacher2.setTno(1);
        teacher2.setTname("bbb");
        mapper.updateTeacher(teacher2);
        System.out.println();
    }

}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

测试类：

@Test
    public void contextLoads() {
        service.deadLock();
    }


    @Test
    public void contextLoads2() {
        service.deadLock2();
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

以断点调试的方式启动，断点打在service层的方法中
在这里插入图片描述

分别debug启动两个test。调试顺序为：

 

下面我们就来测试一下MyISAM中的表级锁机制，首先创建一个测试表employee ，这里要指定存储引擎为MyISAM，并插入两条测试数据：

CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE MyISAM

INSERT INTO employee(name, money) VALUES('黎杜', 1000);
INSERT INTO employee(name, money) VALUES('非科班的科班', 2000);

 

查看一下，表结果如下图所示：

MyISAM表级写锁

（1）与此同时再开启一个session窗口，然后在第一个窗口执行下面的sql，在session1中给表添加写锁：

LOCK TABLE employee WRITE

 

（2）可以在session2中进行查询或者插入、更新该表数据，可以发现都会处于等待状态，也就是session1锁住了整个表，导致session2只能等待：

（3）在session1中进行查询、插入、更新数据，都可以执行成功：

总结： 从上面的测试结果显示当一个线程获取到表级写锁后，只能由该线程对表进行读写操作，别的线程必须等待该线程释放锁以后才能操作。

MyISAM表级共享读锁

（1）接下来测试一下表级共享读锁，同样还是利用上面的测试数据，第一步还是在session1给表加读锁。

（2）然后在session1中尝试进行插入、更新数据，发现都会报错，只能查询数据。

（3）最后在session2中尝试进行插入、更新数据，程序都会进入等待状态，只能查询数据，直到session1解锁表session2才能插入、更新数据。

总结： 从上面的测试结果显示当一个线程获取到表级读锁后，该线程只能读取数据不能修改数据，其它线程也只能加读锁，不能加写锁。

MyISAM表级锁竞争情况

MyISAM存储引擎中，可以通过查询变量来查看并发场景锁的争夺情况，具体执行下面的sql语句：

show status like 'table%';

 


主要是查看table_locks_waited和table_locks_immediate的值的大小分析锁的竞争情况。

Table_locks_immediate：表示能够立即获得表级锁的锁请求次数；Table_locks_waited表示不能立即获取表级锁而需要等待的锁请求次数分析，值越大竞争就越严重。

并发插入

通过上面的操作演示，详细的说明了表级共享锁和表级写锁的特点。但是在平时的执行sql的时候，这些解锁和释放锁都是Mysql底层隐式的执行的。

上面的演示只是为了证明显式的执行事务的过程共享锁和表级写锁的加锁和解锁的特点，实际并不会这么做的。

在我们平时执行select语句的时候就会隐式的加读锁，执行增、删、改的操作时就会隐式的执行加写锁。

MyISAM存储引擎中，虽然读写操作是串行化的，但是它也支持并发插入，这个需要设置内部变量concurrent_insert的值。

它的值有三个值0、1、2。可以通过以下的sql查看concurrent_insert的默认值为AUTO(或者1)。

concurrent_insert的值为NEVER (or 0)表示不支持比并发插入；值为AUTO(或者1）表示在MyISAM表中没有被删除的行，运行另一个线程从表尾插入数据；值为ALWAYS (or 2)表示不管是否有删除的行，都允许在表尾插入数据。

锁调度

MyISAM存储引擎中，假如同时一个读请求，一个写请求过来的话，它会优先处理写请求，因为MyISAM存储引擎中认为写请求比都请求重要。

这样就会导致，假如大量的读写请求过来，就会导致读请求长时间的等待，或者"线程饿死"，因此MyISAM不适合运用于大量读写操作的场景，这样会导致长时间读取不到用户数据，用户体验感极差。

当然可以通过设置low-priority-updates参数，设置请求链接的优先级，使得Mysql优先处理读请求。

InnoDB

InnoDB和MyISAM不同的是，InnoDB支持行锁和事务，行级锁的概念前面以及说了，这里就不再赘述，事务的四大特性的概述以及实现的原理可以参考这一篇[]。

InnoDB中除了有表锁和行级锁的概念，还有Gap Lock（间隙锁）、Next-key Lock锁，间隙锁主要用于范围查询的时候，锁住查询的范围，并且间隙锁也是解决幻读的方案。

InnoDB中的行级锁是对索引加的锁，在不通过索引查询数据的时候，InnoDB就会使用表锁。

但是通过索引查询的时候是否使用索引，还要看Mysql的执行计划，Mysql的优化器会判断是一条sql执行的最佳策略。

若是Mysql觉得执行索引查询还不如全表扫描速度快，那么Mysql就会使用全表扫描来查询，这是即使sql语句中使用了索引，最后还是执行为全表扫描，加的是表锁。

若是对于Mysql的sql执行原理不熟悉的可以参考这一篇文章[]。最后是否执行了索引查询可以通过explain来查看，我相信这个大家都是耳熟能详的命令了。

InnoDB行锁和表锁

InnoDB的行锁也是分为行级共享读锁（S锁）和排它写锁（X锁），原理特点和MyISAM的表级锁两种模式是一样的。

若想显式的给表加行级读锁和写锁，可以执行下面的sql语句：

// 给查询sql显示添加读锁
select ... lock in share mode;
// 给查询sql显示添加写锁
select ... for update；

 

（1）下面我们直接进入锁机制的测试阶段，还是创建一个测试表，并插入两条数据：

// 先把原来的MyISAM表给删除了
DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(40),
    money INT
)ENGINE INNODB;
// 插入测试数据
INSERT INTO employee(name, money) VALUES('黎杜', 1000);
INSERT INTO employee(name, money) VALUES('非科班的科班', 2000);

 

（2）创建的表中可以看出对表中的字段只有id添加了主键索引，接着就是在session1窗口执行begin开启事务，并执行下面的sql语句：

// 使用非索引字段查询，并显式的添加写锁
select * from employee where name='黎杜' for update;

 

（3）然后在session2中执行update语句，上面查询的式id=1的数据行，下面update的是id=2的数据行，会发现程序也会进入等待状态：

update employee set name='ldc' where id =2;

 

可见若是使用非索引查询，直接就是使用的表级锁，锁住了整个表。

（4）若是session1使用的是id来查询，如下图所示：

（5）那么session2是可以成功update其它数据行的，但是这里我建议使用数据量大的表进行测试，因为前面我说过了是否执行索引还得看Mysql的执行计划，对于一些小表的操作，可能就直接使用全表扫描。

（6）还有一种情况就是：假如我们给name字段也加上了普通索引，那么通过普通索引来查询数据，并且查询到多行数据，拿它是锁这多行数据还是锁整个表呢？

下面我们来测试一下，首先给name字段添加普通索引，如下图所示：

（6）并插入一条新的数据name值与id=2的值相同，并显式的加锁，如下若是：

（7）当update其它数据行name值不是ldc的也会进入等待状态，并且通过explain来查看是否name='ldc’有执行索引，可以看到sql语句是有执行索引条件的。


结论：从上面的测试锁机制的演示可以得出以下几个结论：

 

InnoDB间隙锁

当我们使用范围条件查询而不是等值条件查询的时候，InnoDB就会给符合条件的范围索引加锁，在条件范围内并不存的记录就叫做"间隙（GAP）"

大家大概都知道在事务的四大隔离级别中，不可重复读会产生幻读的现象，只能通过提高隔离级别到串行化来解决幻读现象。

但是Mysql中的不可重复是已经解决了幻读问题，它通过引入间隙锁的实现来解决幻读，通过给符合条件的间隙加锁，防止再次查询的时候出现新数据产生幻读的问题。

例如我们执行下面的sql语句，就会对id大于100的记录加锁，在id>100的记录中肯定是有不存在的间隙：

Select * from  employee where id> 100 for update;

 

（1）接着来测试间隙锁，新增一个字段num，并将num添加为普通索引、修改之前的数据使得num之间的值存在间隙，操作如下sql所示：

alter table employee add num int not null default 0;
update employee set num = 1 where id = 1;
update employee set num = 1 where id = 2;
update employee set num = 3 where id = 3;
insert into employee values(4,'kris',4000,5);

 


（2）接着在session1的窗口开启事务，并执行下面操作：

（3）同时打开窗口session2，并执行新增语句：

insert into employee values(5,'ceshi',5000,2);  // 程序出现等待
insert into employee values(5,'ceshi',5000,4);  // 程序出现等待
insert into employee values(5,'ceshi',5000,6);  // 新增成功
insert into employee values(6,'ceshi',5000,0);  // 新增成功

 

从上面的测试结果显示在区间（1,3]U[3,5)之间加了锁，是不能够新增数据行，这就是新增num=2和num=4失败的原因，但是在这个区间以外的数据行是没有加锁的，可以新增数据行。

根据索引的有序性，而普通索引是可以出现重复值，那么当我们第一个sesson查询的时候只出现一条数据num=3，为了解决第二次查询的时候出现幻读，也就是出现两条或者更多num=3这样查询条件的数据。

Mysql在满足where条件的情况下，给（1,3]U[3,5)区间加上了锁不允许插入num=3的数据行，这样就解决了幻读。

这里抛出几种情况接着来测试间隙锁。主键索引（唯一索引）是否会加上间隙所呢？范围查询是否会加上间隙锁？使用不存在的检索条件是否会加上间隙锁？

先来说说：主键索引（唯一索引）是否会加上间隙所呢？

因为主键索引具有唯一性，不允许出现重复，那么当进行等值查询的时候id=3，只能有且只有一条数据，是不可能再出现id=3的第二条数据。

因此它只要锁定这条数据（锁定索引），在下次查询当前读的时候不会被删除、或者更新id=3的数据行，也就保证了数据的一致性，所以主键索引由于他的唯一性的原因，是不需要加间隙锁的。

再来说说第二个问题：范围查询是否会加上间隙锁？

直接在session1中执行下面的sql语句，并在session2中在这个num>=3的查询条件内和外新增数据：

select * from employee where num>=3 for update;
insert into employee values(6,'ceshi',5000,2);  // 程序出现等待
insert into employee values(7,'ceshi',5000,4);  // 程序出现等待
insert into employee values(8,'ceshi',5000,1);  // 新增数据成功

 

我们来分析以下原理：单查询num>=3的时候，在现有的employee表中满足条件的数据行，如下所示：

idnum
33
45
56

那么在设计者的角度出发，我为了解决幻读的现象：在num>=3的条件下是必须加上间隙锁的。

而在小于num=3中，下一条数据行就是num=1了，为了防止在（1，3]的范围中加入了num=3的数据行，所以也给这个间隙加上了锁，这就是添加num=2数据行出现等待的原因。

最后来说一说：使用不存在的检索条件是否会加上间隙锁？

假如是查询num>=8的数据行呢？因为employee表并不存在中num=8的数据行，num最大num=6，所以为了解决幻读（6，8]与num>=8也会加上锁。

说到这里我相信很多人已经对间隙锁有了清晰和深入的认识，可以说是精通了，又可以和面试官互扯了。

假如你是第一次接触Mysql的锁机制，第一次肯定是懵的，建议多认真的看几遍，跟着案例敲一下自己深刻的去体会，慢慢的就懂了。

死锁

死锁在InnoDB中才会出现死锁，MyISAM是不会出现死锁，因为MyISAM支持的是表锁，一次性获取了所有得锁，其它的线程只能排队等候。

而InnoDB默认支持行锁，获取锁是分步的，并不是一次性获取所有得锁，因此在锁竞争的时候就会出现死锁的情况。

虽然InnoDB会出现死锁，但是并不影响InnoDB最受欢成为迎的存储引擎，MyISAM可以理解为串行化操作，读写有序，因此支持的并发性能低下。

死锁案例一

举一个例子，现在数据库表employee中六条数据，如下所示：

其中name=ldc的有两条数据，并且name字段为普通索引，分别是id=2和id=3的数据行，现在假设有两个事务分别执行下面的两条sql语句：

// session1执行
update employee set num = 2 where name ='ldc';
// session2执行
select * from employee where id = 2 or id =3;

 

其中session1执行的sql获取的数据行是两条数据，假设先获取到第一个id=2的数据行，然后cpu的时间分配给了另一个事务，另一个事务执行查询操作获取了第二行数据也就是id=3的数据行。

当事务2继续执行的时候获取到id=3的数据行，锁定了id=3的数据行，此时cpu又将时间分配给了第一个事务，第一个事务执行准备获取第二行数据的锁，发现已经被其他事务获取了，它就处于等待的状态。

当cpu把时间有分配给了第二个事务，第二个事务准备获取第一行数据的锁发现已经被第一个事务获取了锁，这样就行了死锁，两个事务彼此之间相互等待。

死锁案例二

第二种死锁情况就是当一个事务开始并且update一条id=1的数据行时，成功获取到写锁，此时另一个事务执行也update另一条id=2的数据行时，也成功获取到写锁（id为主键）。

此时cpu将时间分配给了事务一，事务一接着也是update id=2的数据行，因为事务二已经获取到id=2数据行的锁，所以事务已处于等待状态。

事务二有获取到了时间，像执行update id=1的数据行，但是此时id=1的锁被事务一获取到了，事务二也处于等待的状态，因此形成了死锁。

session1session2
begin;update t set name=‘测试’ where id=1;begin
 update t set name=‘测试’ where id=2;
update t set name=‘测试’ where id=2; 
等待…update t set name=‘测试’ where id=1;
等待…等待…

死锁的解决方案

首先要解决死锁问题，在程序的设计上，当发现程序有高并发的访问某一个表时，尽量对该表的执行操作串行化，或者锁升级，一次性获取所有的锁资源。

然后也可以设置参数innodb_lock_wait_timeout，超时时间，并且将参数innodb_deadlock_detect 打开，当发现死锁的时候，自动回滚其中的某一个事务。

总结

上面详细的介绍了MyISAM和InnoDB两种存储引擎的锁机制的实现，并进行了测试。

MyISAM的表锁分为两种模式：共享读锁和排它写锁。获取的读锁的线程对该数据行只能读，不能修改，其它线程也只能对该数据行加读锁。

获取到写锁的线程对该数据行既能读也能写，对其他线程对该数据行的读写具有排它性。

MyISAM中默认写优先于去操作，因此MyISAM一般不适合运用于大量读写操作的程序中。

InnoDB的行锁虽然会出现死锁的可能，但是InnoDB的支持的并发性能比MyISAM好，行锁的粒度最小，一定的方法和措施可以解决死锁的发生，极大的发挥InnoDB的性能。

InnoDB中引入了间隙锁的概念来决解出现幻读的问题，也引入事务的特性，通过事务的四种隔离级别，来降低锁冲突，提高并发性能。

  1. test1执行锁tno=1的索引
  2. test2执行锁tno=2的索引
  3. test1执行更新tno=2的tname，此时会进入等待test2释放锁，超时时间可以设置。
  4. test2执行更新tno=1的tname，此时会出现报错信息，同时test2事务回滚。
    在这里插入图片描述在test2尝试为tno=1的索引加锁时，InnoDB检测到了死锁并回滚了事务。

  5. MySQL锁机制

  6. Mysql的锁机制还是有点难理解的，所以这篇文章采用图文结合的方式讲解难点，帮助大家理解，讲解的主要内容如下图的脑图所示，基本涵盖了Mysql锁机制的所有知识点。原文链接：https://blog.csdn.net/qq_43255017/article/details/106818319?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param

  7. 本文脑图

    锁种类

    Mysql中锁的分类按照不同类型的划分可以分成不同的锁，按照锁的粒度划分可以分成：表锁、页锁、行锁；按照使用的方式划分可以分为：共享锁和排它锁；按照思想的划分：乐观锁和悲观锁。

    下面我们对着这几种划分的锁进行详细的解说和介绍，在了解设计者设计锁的概念的同时，也能深入的理解设计者的设计思想。

    表锁是粒度最大的锁，开销小，加锁快，不会出现死锁，但是由于粒度太大，因此造成锁的冲突几率大，并发性能低。

    Mysql中MyISAM储存引擎就支持表锁，MyISAM的表锁模式有两种：表共享读锁和表独占写锁。

    当一个线程获取到MyISAM表的读锁的时候，会阻塞其他用户对该表的写操作，但是不会阻塞其它用户对该用户的读操作。

    相反的，当一个线程获取到MyISAM表的写锁的时候，就会阻塞其它用户的读写操作对其它的线程具有排它性。

    页锁的粒度是介于行锁和表锁之间的一种锁，因为页锁是在BDB中支持的一种锁机制，也很少没人提及和使用，所以这里制作概述，不做详解。

    行锁是粒度最小的锁机制，行锁的加锁开销性能大，加锁慢，并且会出现死锁，但是行锁的锁冲突的几率低，并发性能高。

    行锁是InnoDB默认的支持的锁机制，MyISAM不支持行锁，这个也是InnoDB和MyISAM的区别之一。

    行锁在使用的方式上可以划分为：共享读锁（S锁）和排它写锁（X锁）。

    当一个事务对Mysql中的一条数据行加上了S锁，当前事务不能修改该行数据只能执行度操作，其他事务只能对该行数据加S锁不能加X锁。

    若是一个事务对一行数据加了X锁，该事物能够对该行数据执行读和写操作，其它事务不能对该行数据加任何的锁，既不能读也不能写。

    悲观锁和乐观锁是在很多框架都存在的一种思想，不要狭义地认为它们是某一种框架的锁机制。

    数据库管理系统中为了控制并发，保证在多个事务执行时的数据一致性以及事务的隔离性，使用悲观锁和乐观锁来解决并发场景下的问题。

    Mysql中悲观锁的实现是基于Mysql自身的锁机制实现，而乐观锁需要程序员自己去实现的锁机制，最常见的乐观锁实现就锁机制是使用版本号实现。

    乐观锁设计思想的在CAS的运用也是比较经典，之前我写过一篇关于CAS的文章，大家感兴趣的可以参考这一篇[]。

    从上面的介绍中说了每一种锁的概念，但是很难说哪一种锁就是最好的，锁没有最好的，只有哪种业务场景最适合哪种锁，具体业务具体分析。

    下面我们就具体基于Mysql的存储引擎详细的分析每一种锁在存储引擎中的运用和实现。

    MyISAM

    MyISAM中默认支持的表级锁有两种：共享读锁和独占写锁。表级锁在MyISAM和InnoDB的存储引擎中都支持，但是InnoDB默认支持的是行锁。

    Mysql中平时读写操作都是隐式的进行加锁和解锁操作，Mysql已经自动帮我们实现加锁和解锁操作了，若是想要测试锁机制，我们就要显示的自己控制锁机制。

    Mysql中可以通过以下sql来显示的在事务中显式的进行加锁和解锁操作：

    // 显式的添加表级读锁
LOCK TABLE 表名 READ
// 显示的添加表级写锁
LOCK TABLE 表名 WRITE
// 显式的解锁（当一个事务commit的时候也会自动解锁）
unlock tables;
  8. 1
  9. 2
  10. 3
  11. 4
  12. 5
  13. 6
  14. 1
  15. 2
  16. 3
  17. 4
  18. 5
  19. 6
  20. 7
  21. 8
  22. 1
  23. 1
  24. 1
  25. 2
  26. 3
  27. 4
  28. 1
  29. 2
  30. 3
  31. 4
  32. 5
  33. 6
  34. 7
  35. 8
  36. 9
  37. 10
  38. 1
  39. 2
  40. 1
  41. 执行非索引条件查询执行的是表锁。
  42. 执行索引查询是否是加行锁，还得看Mysql的执行计划，可以通过explain关键字来查看。
  43. 用普通键索引的查询，遇到索引值相同的，也会对其他的操作数据行的产生影响。
  44. 1
  45. 1
  46. 2
  47. 3
  48. 4
  49. 5
  50. 1
  51. 2
  52. 3
  53. 4
  54. 1
  55. 2
  56. 3
  57. 4
  58. 1
  59. 2
  60. 3
  61. 4
熊與猫v
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL——数据库锁原理
庄小焱
12-10 1375
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。人们认为行级锁总会增加开销。实际上，只有当实现本身会增加开销时，行级锁才会增加开销。InnoDB存储引擎不需要锁升级，因为一个锁和多个锁的开销是相同的。InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性的
Mysql锁机制及原理简析
Y先森0.0
05-15 3969
Mysql锁机制及原理简析 一.前言 1.什么是锁？ 锁是计算机协调多个进程或线程并发访问某一资源的机制。 锁保证数据并发访问的一致性、有效性； 锁冲突也是影响数据库并发访问性能的一个重要因素。 锁是Mysql在服务器层和存储引擎层的的并发控制 2.为什么要加锁？ 数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加...
『 MySQL篇 』：MySQL 锁机制介绍
m0_56361048的博客
06-15 8013
MySQL锁机制详解
【史上最全】MySQL各种锁详解：一文搞懂MySQL的各种锁
最新发布
木牛的博客
05-29 1469
隐式上锁（默认，自动加锁自动释放）显式上锁（手动）解锁（手动）1. 提交事务（commit）2. 回滚事务（rollback）3. kill 阻塞进程为什么上了写锁，别的事务还可以读操作？因为InnoD‍B有，可以使用快照读，而不会被阻塞。单个行记录上的锁（点查）Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。
MySQL锁机制详解
qq_43842093的博客
07-15 3631
会话192.168.11.101（1）”给表locktab加了锁，其他会话的操作：1、可以对其他表（表locktab以外的表）进行读、写操作。2、对表locktab：读操作可以，写操作需要等待释放锁。
MySQL中的锁机制详解
清风
08-07 5058
事务的（隔离级别）是由锁来保证的。并发访问数据的情况分为：1.读-读即并发事务相继读取相同的记录，因为没涉及到数据的更改，所以不会有并发安全问题，允许这种情况发生。2.写-写即并发事务对相同记录进行修改，会出现问题，因为任何一种隔离级别都不允许发生脏写，所以多个未提交的事务对同一个记录修改时需要加锁，保证它们是顺序执行的。锁内存中的结构，当事务想对某条数据进行更改时，首先会查看该记录有没有与之关联的锁结构，有的话则等待它的事务被提交，锁被释放；反之没有锁则生成锁结构与该记录继续关联。......
mysql锁机制与MVCC
许诗宇的博客
04-16 985
目录 乐观锁&悲观锁 悲观锁 悲观锁-数据表中的实现 悲观锁-扩展思考 乐观锁 乐观锁-数据表中的实现 乐观锁-乐观锁的锁粒度 乐观锁-扩展训练 乐观锁&悲观锁-小总结 共享锁&排他锁&意向锁&间隙锁 排他锁&共享锁 共享锁(shared locks,S锁) 排他锁(exclusive locks,X锁) 意向锁 意向锁......
关于Mysql隔离级别、锁与MVCC介绍
09-09
MySQL数据库在处理并发事务时，采用了多种机制来确保数据的一致性和隔离性，其中关键的概念包括隔离级别、锁以及多版本并发控制（MVCC）。理解这些机制有助于优化数据库性能和解决并发冲突。 首先，隔离级别是...
MySQL的锁机制解析
05-30
MySQL的锁机制是数据库管理系统中用于控制并发操作的重要机制，对于保证数据的一致性和完整性起着至关重要的作用。本文将深入解析MySQL中的锁机制，帮助读者理解其工作原理，为面试准备或日常数据库管理提供有力支持...
MySQL之锁和事务
12-14
MySQL数据库在处理并发和保证数据安全性方面，使用了锁机制和事务管理，以确保数据的一致性和完整性。这里我们将深入探讨锁的分类、事务的特性以及多版本并发控制（MVCC）。 一、锁分类 1. **表级锁**：这种锁对...
深入理解Mysql事务隔离级别与锁机制.zip
05-27
本知识点将深入探讨MySQL中的事务隔离级别和锁机制，这是确保数据一致性和并发控制的关键概念。 首先，让我们了解什么是事务。在数据库中，事务是一系列操作的集合，这些操作被视为一个逻辑工作单元，要么全部执行...
mysql 悲观锁与乐观锁的理解及应用分析
09-08
在MySQL中，这两种锁机制有各自的特点和应用场景。 一、悲观锁 悲观锁正如其名，它假设在数据处理过程中可能会出现并发冲突，因此在读取数据时就立即加锁，确保其他用户无法修改数据。在MySQL中，悲观锁通常通过`...
MySQL(四):锁的原理
qq_37805943的博客
06-08 1014
概念：针对于整个DB加的锁，目的是为了全库备份，整个DB只有读操作。MySQL提供了一种全局读锁的方法，保证线程只会读，不会做其他的DML，DDL操作。 MVCC机制明显优于全局锁技术，但MVCC需要引擎能提供事务支持，所以只有InnoDB具备MVCC机制，非事务引擎可以尝试使用全局锁来保证隔离性。表锁细分：读锁和写锁。语法：lock tables … read/write，可以使用unlock tables断开客户端释放锁，lock tables …read和select * from table …
一文详解MySQL的锁机制
后端元宇宙
11-18 1796
一、表级锁、行级锁、页级锁 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。 MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。 MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。 1、表级锁 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面.
MySQL锁相关——锁机制、表锁、行锁、死锁、锁优化等
weixin_43816711的博客
12-26 1498
MySQL不同的存储引擎支持不同的锁机制，所有存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现。默认情况下表锁和行锁都是自动获得的，不需要额外的命令，但某些情况下用户需要明确的进行行锁或进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。对于普通的select语句，InnoDB不会加任何锁。
Mysql中各类锁的机制图文详细解析（全）
码农研究僧的博客
12-28 2541
目录1. 定义1. 表锁（读写锁）2. 行锁3. 页级锁4. 间隙锁5. 总结6. 补充 1. 定义 锁是计算机协调多个进程或线程并发访问某一资源的机制。 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 锁的分类 从对数据操作的类型 读锁（共享锁）：针对同一份数据，多个读操作可以
【MySQL系列】- MYSQL锁机制
记录总结工作过往
09-18 1150
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁合理设计索引，尽量缩小锁的范围。
一文弄懂MySQL锁机制【记录锁、间隙锁、临键锁，共享锁、排他锁，意向锁】
小道仙的后宫
11-10 3056
没有使用索引做更新相关操作会锁表。通过唯一/主键索引等值加锁，只会锁具体的行，非唯一索引则不一定，SQL优化器会基于数据分布选择记录锁，或临键锁。只有在RR级别下才有间隙锁，目的是为了解决幻读，如果操作的数据是跨多个范围，就会加多个区间的间隙锁。MySQL默认的锁就是【临键锁】，所以在执行SQL的时候，记录锁和间隙锁是会同时存在的。范围是左开右闭的区间。
mysql锁机制和mvcc
08-24
- *1* [Mysql锁机制+MVCC](https://blog.csdn.net/qq_45901741/article/details/120245265)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值