MySQL隔离级别和锁机制的深入讲解

目录
  • 简述:
  • 1. 事务的四大特性
  • 2.多事务并发带来的问题
  • 3.事务的隔离级别
  • 4.演示不同隔离级别出现的问题
    • 读未提交
    • 读已提交
    • 可重复读
    • 串行化
  • 5.锁机制
    • 间隙锁
    • 临建锁
    • 排他锁
  • 总结

简述:

我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读、不可重复读、幻读这些问题.

这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制、MVCC多版本并发控制隔离机制、以及事务隔离机制,用一整套机制来解决多事务并发所出现的问题.

1. 事务的四大特性

特性 特点
Atomicity(原子性) 事务是不可分割的,其对数据的修改,要么全都执行,要么全都不执行
Consistency(一致性) 在事务提交的前后的状态和数据都必须是一致的
Isolation(隔离性) 在多事务并发时,保证事务不受并发操作影响的"独立"环境执行,这就意味着事务处理过程中的中间状态对外部是不可见的,反之亦然
Druability(持久性) 指事务一旦提交,数据就持久化保存到磁盘中不会丢失

2.多事务并发带来的问题

问题 现象 描述
脏读 A事务正在对一条记录做修改,在A事务完成并提交前,这条记录的数据就处于不一致的状态(有可能回滚也有可能提交),与此同时,B事务也来读取同一条记录,如果不加控制,B事务读取了这些"脏"数据,并据此作进一步处理,就会产生未提交的数据以来关系 一个事务中读取到另一个事务尚未提交的数据,不符合一致性要求
不可重复读 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变或某些记录已经被删除了 一个事务中多次读取的数据不一致,原因是收到其他事务已提交update的干扰,不符合隔离性
幻读 一个事务按相同的查询条件重新读取以前查询过的数据,却发现其他事务插入满足其查询条件的新数据 一个事务中多次读取的数据不一致,原因是受其他事务已提交insert/delete的干扰,不符合隔离性

3.事务的隔离级别

脏读、不可重复读和幻读,其实都是MySQL读一致性问题,必须由数据库提供一定的事务隔离机制来解决.

隔离级别 脏读 不可重复读 幻读
Read uncommitted(读未提交)
Read committed(读已提交) ×
Repetatble read(可重复读)(MySQL默认) × ×
Serializable(串行化) × × ×

查看当前数据库的事务隔离级别:show variables like ‘tx_isolation';

设置事务隔离级别:set tx_isolation='隔离级别'

4.演示不同隔离级别出现的问题

mysql版本:5.7.34

涉及表:

两个MySQL客户端

客户端A <===================> 客户端B(下面每张图片两个客户端皆以第一张图命名为准

读未提交

1.1 设置事务隔离级别set tx_isolation=‘read-uncommitted';

1.2 客户端A和客户端B各开启一个事务,

1.3 客户端A只做查询,客户端B对id = 1的记录做修改;

1.4 再两个事务都未提交的情况下,事务A读到了事务B修改后的数据

1.5 一旦客户端B的事务因为某种原因rollback,那么客户端A查询到的数据其实就是脏数据,不符合一致性的要求

读已提交

2.1 设置隔离级别读已提交:set tx_isolation=‘read-committed';

2.2 客户端A和客户端B各开启一个事务,

2.3 客户端A只做查询,客户端B对id = 1的记录做修改;

2.4 客户端B未提交事务时,客户端A不能查询客户端B未提交的数据,解决了脏读的问题

2.5 当客户端B提交事务后,客户端A再次对表进行查询,结果与上一步不一致,即产生了不可重复读的问题,不符合隔离性

可重复读

3.1 设置隔离级别可重复读:set tx_isolation=‘repeatable-read';

3.2 客户端A和客户端B各开启一个事务,

3.3 客户端B修改表中数据然后提交;

3.4 客户端A查询表中数据,并未出现与上一步不一致的问题,解决了不可重复读的问题

3.5 在客户端A中执行update account set balance = balance - 100 where id = 1;blance并未有变成800-100=700;而是使用客户端B提交后的数据来算的,所以是600;数据的一致性并没有被破坏;可重复读的隔离级别下使用的是MVCC机制,select操作不会更新版本号,是快照读(历史版本),保证同一事务下的可重复读;insert/update/delete会更新版本号,是当前读(当前版本)保证数据的一致性

3.6 客户端B重新开启一个事务插入一条数据后提交

3.7 在客户端A中重新查询表数据,并没有出现客户端B刚才新增的数据,没有出现幻读

3.8 验证幻读:在客户端A中,对id = 4 的数据做修改;可以更新成功;再次进行查询就能查询出客户端B新增的数据,出现幻读问题,不符合隔离性

串行化

4.1 设置隔离级别串行化:set tx_isolation=‘serializable';

4.2 客户端A和客户端B各开启一个事务,

4.3 客户端A先查询表中id = 1的数据

4.4 在客户端A事务未提交时,客户端B对表中id = 1 的数据做更新;由于客户端A的事务并没有提交,客户端B的更新动作将会阻塞至到客户端A提交事务或者超时,超时SQL报错:Lock wait timeout exceeded; try restarting transaction

4.5 在客户端B中更新id = 2 的数据却可以成功,说明在串行化的隔离级别下,innodb的查询也会被加上行锁;

4.6 如果客户端A执行的是一个范围查询,那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行未被插入也会加锁,这种是间隙锁)都会被加锁,此时如果客户端B对该范围内的数据做任何操作都会被阻塞;所以就避免了幻读;

4.7 串行化这种隔离级别并发性极低,所以再真实的开发很少会遇到,这也是MySQL为什么使用可重复读作为默认的隔离级别的重要原因

5.锁机制

MySQL默认的隔离级别是可重复读,可是还是会出现幻读问题;间隙锁再某种情况下可以解决幻读问题;

间隙锁

概述:间隙锁,锁的就是两个值之间的空隙.

假设表中数据如下:

那么间隙就有(4,10)、(10,15)和(15,正无穷)三个间隙;

1.1 设置隔离级别可重复读:set tx_isolation=‘repeatable-read';

1.2 客户端A和客户端B各开启一个事务,

1.3 在客户端A执行update account set balance = 1000 where id > 5 and id < 13 ;

1.4 在客户端A未提交的时候,客户端B是没有办法对这个范围包含的所有行记录(包括间隙行记录)以及行记录所在间隙里执行insert/update操作,即4<id<=15这个区间内都无法修改数据,id = 15 同样不能修改;

1.5 间隙锁只有在可重复读的隔离级别下才会生效

临建锁

概述:临建锁是行锁和间隙锁的结合,想上面那个4<id<=15就属于临建锁;

无索引行锁会升级成为表锁

3.1 客户端A和客户端B各开启一个事务,

3.2 在客户端A执行update account set balance = 1000 where name = ‘李四';

3.3 在客户端A未提交的时候,客户端B执行update account set balance = 800 where id = 15 ;同样会被阻塞至客户端A提交或者超时;

3.4 MySQL中的锁主要是加载索引字段上,如果使用再非索引字段上,行锁会升级成表锁;

排他锁

4.1 客户端A和客户端B各开启一个事务,

4.2 在客户端A执行select * from account where id = 1 for update ;

4.3 在客户端A未提交的时候,客户端B执行update account set balance = 800 where id = 1 ;会被阻塞至客户端A提交或者超时;

结论:Innodb引擎实现了行锁,虽然行锁机制实现方面所带来的性能损耗可能比表级锁定会更高,但是再整体并发处理能力肯定要强于表级锁;当系统并发量高的时候,行级锁和表级锁相比就会有比较明显的优势;但是行级锁使用起来也比表级锁复杂,当我们使用不当的时候,可能会使行锁的性能不仅不比表级锁的性能高,甚至可能会更差.

为什么行锁锁定的粒度小,开销反而会比表级锁的开销大?

因为表级锁只需要找到当前表就可以进行加锁,行锁的话需要对表中记录进行扫描,直至扫描到需要加锁的行才可以进行加锁,所以行锁的开销是比表级锁的开销要来得大的.

真实开发情况下对锁优化的一些建议:

  • 合理使用索引字段加锁,缩小锁的范围
  • 尽可能让所有锁都加到索引字段上,避免无索引行锁升级成表锁
  • 尽可能减少查询范围,避免间隙过大的间隙锁
  • 尽可能低级别事务隔离
  • 尽可能控制事务大小,减少锁定资源量,涉及事务加锁的sql尽量放在事务最后执行,减少加锁的时间

总结

到此这篇关于MySQL隔离级别和锁机制的文章就介绍到这了,更多相关MySQL隔离级别和锁机制内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

  • mysql的事务,隔离级别和锁用法实例分析

    本文实例讲述了mysql的事务,隔离级别和锁用法.分享给大家供大家参考,具体如下: 事务就是一组一起成功或一起失败的sql语句.事务还应该具备,原子性,一致性,隔离性和持久性. 一.事务的基本要素 (ACID) 1.原子性:事务开始后,所有的操作,要么全部成功,要么全部失败,不可能处于中间状态,事务是一个不可分割的整体,就像原子一样. 2.一致性:事务开始前和结束后,数据库的完整性约束没有破坏,A向B转账,A扣了钱,但B却没到账. 3.隔离性:同时发生的事务(并发事务)不应该导致数据库处于不一致

  • 关于Mysql隔离级别、锁与MVCC介绍

    本文意在弄清楚这些概念间的关系及其作用.弄清Mysql在开启事务的情况下,每条sql执行时的加锁操作和MVCC版本控制.为使讨论简单,本文忽略了GAP锁(间隙锁.范围锁). 我们经常所高并发,高可用.就是从质和量来评估,任何事物都可以从这两个角度来分析.在Mysql数据库中,事务就是用来保证质的,MVCC就是用来保证量的. 事务 我们使用事务来保证每一条SQL语句的结果执行符合我们的预期.我们说事务必须具备ACID特性.ACID中的三者:原子性.一致性和持久性其实描述的都差不多,保证SQL执行结

  • 深入理解Mysql的四种隔离级别

    一.首先什么是事务? 事务是应用程序中一系列严密的操作,所有操作必须成功完成,否则在每个操作中所作的所有更改都会被撤消.也就是事务具有原子性,一个事务中的一系列的操作要么全部成功,要么一个都不做. 事务的结束有两种,当事务中的所以步骤全部成功执行时,事务提交.如果其中一个步骤失败,将发生回滚操作,撤消撤消之前到事务开始时的所以操作. 二.事务的 ACID 事务具有四个特征:原子性( Atomicity ).一致性( Consistency ).隔离性( Isolation )和持续性( Dura

  • MySQL数据库事务隔离级别详解

    数据库事务隔离级别 数据库事务的隔离级别有4个,由低到高依次为 Read uncommitted:允许脏读. Read committed: 防止脏读,最常用的隔离级别,并且是大多数数据库的默认隔离级别. Repeatable read:可以防止脏读和不可重复读. Serializable:可以防止脏读,不可重复读取和幻读,(事务串行化)会降低数据库的效率. 这四个级别可以逐个解决脏读 .不可重复读 .幻读 这几类问题. √: 可能出现 ×: 不会出现 事务级别 脏读 不可重复读 幻读 Read

  • Mysql事务隔离级别之读提交详解

    查看mysql 事务隔离级别 mysql> show variables like '%isolation%'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.00 sec) 可以看到

  • MySQL 四种事务隔离级别详解及对比

    MySQL 四种事务隔离级别详解及对比 按照SQL:1992 事务隔离级别,InnoDB默认是可重复读的(REPEATABLE READ).MySQL/InnoDB 提供SQL标准所描述的所有四个事务隔离级别.你可以在命令行用--transaction-isolation选项,或在选项文件里,为所有连接设置默认隔离级别. 例如,你可以在my.inf文件的[mysqld]节里类似如下设置该选项: transaction-isolation = {READ-UNCOMMITTED | READ-CO

  • MySQL四种事务隔离级别详解

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节.事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样.也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位. 2.一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 .比如A向B转账,不可能A扣了钱,

  • MySQL隔离级别和锁机制的深入讲解

    目录 简述: 1. 事务的四大特性 2.多事务并发带来的问题 3.事务的隔离级别 4.演示不同隔离级别出现的问题 读未提交 读已提交 可重复读 串行化 5.锁机制 间隙锁 临建锁 排他锁 总结 简述: 我们的MySQL一般会并发的执行多个事务,多个事务可能会并发的对同一条或者同一批数据进行crud操作;可能就会导致我们平常所说的脏读.不可重复读.幻读这些问题. 这些问题的本质都是MySQL多事务并发问题,为了解决多事务并发问题,MySQL设计了锁机制.MVCC多版本并发控制隔离机制.以及事务隔离

  • 深入理解Mysql事务隔离级别与锁机制问题

    概述 数据库一般都会并发执行多个事务,多个事务可能会并发的对相同的一批数据进行增删改查操作,可能导致脏读.脏写.不可重复度和幻读.这些问题的本质都是数据库的多事务并发问题,为了解决事务并发问题,数据库设计了事务隔离机制.锁机制.MVCC多版本并发控制隔离机制,用一整套机制来解决多事务并发问题. 事务及其ACID属性 原子性:操作的不可分割: 一致性:数据的一致性: 隔离性:事务之间互不干扰: 持久性:数据的修改时永久的: 并发事务处理带来的问题 脏写:丢失更新,最后的更新覆盖了由其他事务所做的更

  • 深入分析MSSQL数据库中事务隔离级别和锁机制

    锁机制 NOLOCK和READPAST的区别. 1.       开启一个事务执行插入数据的操作. BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2.       执行一条查询语句. SELECT * FROM Customer WITH (NOLOCK) 结果中显示"a"和"a".当1中事务回滚后,那么a将成为脏数据.(注:1中的事务未提交) .NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修

  • MSSQL与Oracle数据库事务隔离级别与锁机制对比

    一,事务的4个基本特征 Atomic(原子性): 事务中包含的操作被看做一个逻辑单元,这个逻辑单元中的操作要 么全部成功,要么全部失败. Consistency(一致性): 只有合法的数据可以被写入数据库,否则事务应该将其回滚到最初 状态. Isolation(隔离性): 事务允许多个用户对同一个数据进行并发访问,而不破坏数据的正 确性和完整性.同时,并行事务的修改必须与其他并行事务的修改 相互独立. Durability(持久性): 事务结束后,事务处理的结果必须能够得到固化. 以上属于废话

  • MySQL串行化隔离级别(间隙锁实现)

    目录 一.间隙锁的概念 二.测试间隙锁范围加锁 场景1:用不可重复的主键id测试间隙锁 场景2:用可重复的age(有索引)测试间隙锁 场景3:实际情况需要具体分析用的到底是行锁还是表锁 三.测试间隙锁等值加锁 1. 测试不能重复的主键索引 2. 测试能重复的辅助索引 串行化隔离级别怎么解决幻读问题?先说下幻读的含义,幻读就是在事务中按照同样的条件前后两次查询的结果数据量不同. 解决串行化的幻读问题用间隙锁(gap lock),间隙锁是给不存在的记录加锁,要正确理解间隙,知道间隙的范围.条件无非就

  • mysql隔离级别详解及示例

    目录 mysql的4种隔离级别 创建数据表: 设置隔离级别 只是单纯知道事物的隔离级别,但是从未操作过 今日操作一次. 脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的. 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据. 幻读(Phantom Read):在一个事务的两次查询中数

  • MySQL InnoDB中的锁机制深入讲解

    写在前面 数据库本质上是一种共享资源,因此在最大程度提供并发访问性能的同时,仍需要确保每个用户能以一致的方式读取和修改数据.锁机制(Locking)就是解决这类问题的最好武器. 首先新建表 test,其中 id 为主键,name 为辅助索引,address 为唯一索引. CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) NOT NULL, `address` int(11) NOT NULL, P

随机推荐