MySQL事务和隔离级别

最新推荐文章于 2024-04-12 02:59:43 发布
最新推荐文章于 2024-04-12 02:59:43 发布
阅读量128 收藏
点赞数
分类专栏： Linux运维笔记 文章标签： MySQL事务和隔离级别 常见的四种隔离级别
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36294875/article/details/80247031
版权

  • MySQL基础架构

  • 连接管理器:接收请求接收请求-->创建线程-->认证用户-->建立安全连接;

  • 优化器的缺陷:

    • 有时候执行的优化并不一定是最优化的,如果有最优化的方式,直接进行指定,不建议进行优化,省去优化器优化的开销;
    • 优化器优化的过程并不会考虑存储引擎的不同;

  • 不是所有的结果都是需要进行缓存的,非确定性结果就不应该进行缓存,具有实时性要求的,不应该进行缓存;

  • 并发控制的一种原理,每个用户操作访问的都不是源数据,而是数据的快照,最后完成快照恢复,这种机制成为MVCC,也就是多版本并发访问控制机制,

  • 不能够缓存的例如当前时间,这种结果是非确定性结果;
    这里写图片描述

  • 对于多版本并发控制依赖的手段:

    • 锁机制:一个事务可能在启动事务时,长时间得不到锁,出现饥饿现象;还有可能出现死锁状况,如果出现死锁,事务调度,就应该控制事务进行回退;
    • 时间戳;
    • 多版本和快照隔离

  • 简单的锁控制机制:

    • 锁是最简单的并发机制控制;
    • 读锁是共享锁;
    • 写锁是独占锁;

  • 表锁:锁定一张表;

  • 页锁:锁定数据页面,数据页面可能包含多个行;

  • 行锁:锁定某一个行;

  • MySQL服务器仅仅支持表级别的锁,行锁需要由存储引擎完成,存储引擎都有自己的锁策略,很少需要手动的在服务器上面施加锁;

  • LOCK_TABLE
    这里写图片描述

  • 手动施加读锁

mysql> LOCK TABLES tutors read;
Query OK, 0 rows affected (0.00 sec)
  • 在另一个会话中开始进行查询,数据查询是允许的;
mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | F      |   49 |
|  10 | DingDian     | M      |   25 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   20 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
+-----+--------------+--------+------+
14 rows in set (0.00 sec)
  • 插入数据是不能够完成的,陷入等待释放读锁
    这里写图片描述
  • 释放读锁,数据插入立即完成
    这里写图片描述

这里写图片描述

  • 事务的特性

  • 需要满足ACID特性,也就是满足

    • 原子性:Automicity事务涉及的多个执行语句,要么同时完成,要么同时不完成,必须当作一个整体来看待;
    • 一致性:Consistency:一致性,事务执行结束之后,数据库的执行状态是没有改变的;
    • 隔离性:Isolation:隔离性,用于保证各个事务之间彼此不被影响,通过各个之间的事务调度执行,各个事务之间降低彼此之间的影响;
    • 持久性:Durability持久性,一旦事务成功完成,都必须保证这个事务是稳定的,系统出现必须保证任何故障下都不会引起事务表示出不一致性;
      • 1.事务提交之前的数据应该写入永久性存储,效率低下;
      • 2.结合事务日志,事务日志是保存在永久性存储设备上面的;
      • 3.事务日志产生的是顺序IO,在存储的时候是连续的,数据文件是随机IO;

  • MySQL的事务引擎是交给存储引擎来控制的,InnoDB是支持事务的;

  • 事务日志:

  • 重做日志Redo:表示按照日志里面记录的信息,将需要的重新完成一遍;

  • 撤销日志undo log: 表示撤销日志纪录中已经完成的操作;

  • 为了结合事务日志的处理机制,MySQL的数据处理都是在内存中完成,并且将操作的记录写在事务日志中,然后将事务日志按照里面的记录在磁盘上面对数据进行操作;

  • 对于数据的增删查改,首先在内存中完成,然后写入事务日志中,通过事务日志的重做日志在执行一遍,然后写入实际的数据文件中去,写在日志中,要快的多,日志中记录的仅仅是操作,而不是数据文件本身;

    • 对于完成提交的事务,如果在写入磁盘的过程中崩溃,那么需要重做日志进行重做;
    • 对于没有完成的事务,再写入次怕的过程中崩溃,那么执行撤销日志,撤销执行;
    • 日志文件应该尽量小,但是应该存在不止一个,通常使用是日志组来完成的;

  • 事务日志文件通常不是一个,而是多个,称为日志组,一个用于进行写入,另一个马上在磁盘上面的重做操作,通常是交替进行;

  • 事务日志和数据文件应该在不同的设备上面;

  • 事务的状态

  • ACTIVE:活动的;

  • 部分提交:最后一条语句正在执行;

  • 失败的:事务正常提交,但是提交没有完成;

  • 终止的:没有提交,就提前结束的;

  • 提交的:成功提交,并且成功执行的,事务一旦提交,就没有办法撤销,如果需要撤销,就只能够通过补偿事务来完成;

  • 事务允许进行并发执行,并且通过调度来保证事务的特性:

  • 可恢复调度:任何两个事务的交叉执行,都不会导致另外一个事务状态的改变,会滚的时候可能存在影响;

  • 无级联调度: 为了避免事务在会滚的时候产生影响;

  • 事务的隔离级别

  • 这是在启动两个事务的情况下,根据不同的隔离级别,出现不同的结果;

  • 隔离级别越低,事务存在干扰的可能性越大

  • read uncommitted:读未提交, 别人操作,可以立即看见,隔离级别最低;

  • read commited:读提交,表示别人提交之后,可以立即看到,会出现幻读;

  • repeatabled read:表示可重读,表示无论是否进行提交,从第一次看到直到事务提交之前都是什么样;MySQL默认是这样的;

  • serialiable:表示按照串行进行执行;

  • 隔离级别越高,并发能力越低,降低隔离级别可以提高MySQL性能;

  • 查看默认的隔离级别

mysql> SHOW GLOBAL VARIABLES LIKE '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
  • 事务的隔离级别
mysql> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
  • 事务的操作
  • 启动一个事务
mysql> START TRANSACTION;
  • 执行SQL语句
  • 然后进行提交事务,
mysql> commit;
  • 执行commit之后,事务就不能够撤销的;
  • 在执行SQL语句的时候,如果放弃事务的执行,可以使用ROLLBACK来放弃事务的执行;
  • 启动一个事务
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age  |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi  | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze  | F      |   49 |
|  10 | DingDian     | M      |   25 |
|  11 | HuFei        | M      |   31 |
|  12 | ZhangWuji    | M      |   20 |
|  13 | Xuzhu        | M      |   26 |
|  14 | LingHuchong  | M      |   22 |
|  15 | jerry        | M      |   50 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)
  • 接下来执行一些操作
mysql> DELETE FROM tutors WHERE Tname LIKE 'je%';
Query OK, 0 rows affected (0.00 sec)
  • 查看数据,已经被删除,但是没有同步到磁盘上面;
  • 如果不想进行删除,不要进行commit而是进行ROLLBACK进行事务的回滚操作
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
  • auto_commit:表示事务的自动提交
mysql>  SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
  • 如果没有明确的启动事务,就能够实现自动提交的,不能够执行ROLLBACK的,建议明确使用事务,并且关闭自动提交,否则IO导致性能降低;
  • 关闭这个变量之后,就可以进行回滚操作了,但是对于确定的操作,需要执行commit操作
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE Tname='jerry';
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
  • 事务的SAVEPOINT:
  • 在进行事务里面的大量语句操作时,设置保存点,在进行语句撤销时,一次撤销到某个保存点,而不是撤销完成所有的语句;
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE TID=10;
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT ab;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE TID=9;
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT ac;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE TID=8;
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT ad;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE TID=7;
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT ae;
Query OK, 0 rows affected (0.00 sec)
  • 执行完上述操作之后
    这里写图片描述
  • 然后进行回滚到某个SAVEPOINT;
    这里写图片描述
  • 事务的隔离级别对于事务的影响
  • 启动两个MySQL会话,并且设定默认的隔离级别为下面的几种:
  • 验证READ_UNCOMMITTED:
  • 会话1:
mysql> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
  • 会话2:
mysql> SET tx_isolation='READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)
  • 交叉启动事务
  • 会话1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)
  • 会话2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 修改某个用户的年龄
  • 在会话1里面修改HuFei的年龄
mysql> UPDATE tutors SET Age=50 WHERE TID=12;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  • 然后再会话2里面进行查看,是可以查看到修改的内容的
    这里写图片描述
  • 如果会话1执行了,撤销操作
mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)
  • 那么会话2就会就会修为原来的数据
    这里写图片描述
  • 接下来修改级别为READ-COMMITTED;
  • 会话1
mysql>  SET tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 会话2:
mysql> SET tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 接下来修改数据
  • 会话1
mysql> UPDATE tutors SET Age=50 WHERE TID=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  • 会话2查看数据,TID为12的并没有进行修改
    这里写图片描述
  • 会话1在进行提交之后,这个值就会发生改变
mysql> COMMIT;
Query OK, 0 rows affected (0.25 sec)
  • 会话2查看的数据已经发生改变
    这里写图片描述
  • 提交事务
  • 会话1
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec
  • 会话2,进行提交
mysql> COMMIT;
Query OK, 0 rows affected (0.25 sec)
  • 修改事务隔离级别为REPEATABLE-READ,并且启动事务
  • 会话1
mysql> SET tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
  • 会话2,同时修改事务隔离级别,并且启动事务
mysql> SET tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 接下来进行数据的修改
  • 会话1
mysql> UPDATE tutors SET Age=100 WHERE TID=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这里写图片描述

  • 会话2,查看数据是否发生改变
    这里写图片描述
  • 会话1提交事务,会话2上面查询的数据仍然没有改变
mysql> COMMIT;
Query OK, 0 rows affected (0.79 sec)

  • 会话2上面的数据,没有发生改变;
    这里写图片描述

  • 会话2进行事务的提交,查看数据,已经进行更新
    这里写图片描述

  • 验证SERIALIZALE

  • 会话1修改,日志的隔离级别为SERIALIZABLE,并且启动事务

mysql> SET tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 会话2同样进行修改,并且启动日志
mysql> SET tx_isolation='SERIALIZABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
  • 在会话1上面进行修改数据
mysql> UPDATE tutors SET Age=10 WHERE TID=12;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
  • 会话2上面是查询不到变化的信息的,因为不允许两个事务同时操作同一个数据
mysql> SELECT * FROM tutors;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • 在会话1上面提交数据
mysql> COMMIT;
Query OK, 0 rows affected (0.03 sec)
  • 会话1的数据修改完成
    这里写图片描述
  • 会话2的查看就可以正常完成,并且可以得到信息
    这里写图片描述
  • 会话2提交数据之后,得到信息是不会发生改变的
    这里写图片描述
bug--maker
关注
专栏目录
Mysql事务的隔离级别
越是憧憬，越要风雨兼程
01-17 290
并发下事务会产生的问题： 1、脏读：      所谓脏读，就是指事务A读到了事务B还没有提交的数据，比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读到的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交数据库里面的账户余额肯定还是原始余额，这就是脏读 2、不可...
mysql事务隔离级别
laozhang0910的博客
06-01 8080
隔离级别 MySQL是一个服务器／客户端架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。我们可以同时在不同的会话里输入各种语句，这些语句可以作为事务的一部分进行处理。不同的会话可以同时发送请求，也就是说服务器可能同时在处理多个事务，这样子就会导致不同的事务可能同时访问到相同的记录。我们前边说过事务有一个特性称之为...
MySql日志与事务的隔离级别
weixin_30820077的博客
06-22 146
MySql事务的隔离级别解析 一、日志 binlog（逻辑日志） 它是MySql服务层的日志 statement 格式的话是记sql语句 row格式会记录行的内容，记两条，更新前和更新后都有。 作用： 备份和恢复数据（可以指定恢复到某一时刻哦） redo log（物理日志） 这是innodb引擎提供的日志。为什么说它是物理日志呢，因为它对应着磁盘里的数据修改信息，我们的修改sql成...
MySQL事务的隔离级别和日志记录模式选择
qiuyepiaoling的专栏
04-26 762
<br /><br />导读：<br />MySQL的四种事务隔离级别：Read-uncommitted、Read-committed、Repeatable-read、Seriailizable，相信大家都清楚各自异同，不清楚的朋友可以查看另外一篇技术文章：MySQL_InnoDB之事务与锁详解。但是对于第二类、第三类隔离级别之间的性能区别和应用场景就会容易出现一些理解上的偏差，尤其是熟悉Oracle的技术朋友，为此专门撰写一篇技术文章，引导大家合理地选择这两种事务隔离级别。<br /> <br />测试环
MYSQL日志隔离级别演示
来一杯吧
05-10 336
首先连接mysql数据库， 创建测试表，插入3调数据 create table user( id bigint, name varchar(20), age int ); insert into user values (1,'1',1),(2,'2',2),(3,'3',3); 设置提交为手动提交 set autocommit=0; 查询mysql的隔离界别，发现是REPEATABLE-READ 可重复度 select @@transaction_isolation; 读未提
事务管理、隔离级别，日志管理
weixin_33967071的博客
02-21 172
事务Transactions：一组原子性的SQL语句，或一个独立工作单元事务日志：记录事务信息，实现undo,redo等故障恢复功能ACID特性：A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔...
自定义日志隔离级别与日志配置文件log4j
qq_38024724的博客
09-19 194
【代码】自定义日志隔离级别。
MySQL数据库事务隔离级别详解
12-16
MySQL数据库的事务隔离级别是...总的来说，理解并正确设置MySQL的事务隔离级别是数据库设计和管理中的关键环节，能够有效地平衡数据一致性和系统性能。根据具体的应用场景和业务需求，选择最适合的隔离级别至关重要。
Mysql事务隔离级别原理实例解析
09-08
MySQL事务隔离级别是数据库管理系统中确保事务之间正确交互的重要机制，它主要涉及到四个级别：读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）。...
聊聊MySQL事务的特性和隔离级别
12-14
MySQL提供了四种不同的事务隔离级别来实现这一点： - 未提交读（Read Uncommitted）：允许读取未提交的数据，可能导致脏读。 - 提交读（Read Committed）：只读取已提交的数据，但仍可能出现不可重复读。 - 可...
数据库undo与redo日志、事务的隔离级别
羽逸非凡的博客
01-28 1446
数据库事务的隔离级别 数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读这几类问题。 理解事务隔离级别，需要明确数据库中的事务机制： undo日志 数据库事务四大特性中有一个是原子性 ，具体来说就是原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。 实际上， 原子性底层就是通过undo log实现的
log4j的隔离级别
guyinghuan的专栏
04-14 1952
它们定义了日志显示的级别，一共分为五个级别：DEBUG、INFO、WARN、ERROR和FATAL。这五个级别是有顺序的，DEBUG
MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨
最新发布
2401_84007015的博客
04-12 920
对于面试，一定要有良好的心态，这位小伙伴面试美团的时候没有被前面阿里的面试影响到，发挥也很正常，也就能顺利拿下美团的offer。小编还整理了大厂java程序员面试涉及到的绝大部分面试题及答案，希望能帮助到大家，也祝愿大家都能够升职加薪！一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新**
【四种隔离级别详解】
Leeffee的博客
05-16 500
四种隔离级别详解读未提交读已提交可重复读串行化 读未提交 read uncommitted 未提交别人也能看得到 假如A事务和B事务对同一张表进行操作，B事务进行了DML语句，但没有提交(commit)或者回滚(rollback)，此时A事务便可以查看到B事务还没有提交的数据，就是保存在事务性活动的日志文件中的数据。这样可能会造成脏读。何为脏读：脏读就是B事务还没有提交的数据，A事务便可以查看到，在用户的眼里，A用户就会以为这条数据已经存在了，其实还没有提交，如果提交失败，便会回滚，这条数据并没有添加成功，
理解数据库4种隔离级别记录
u013062667的专栏
04-17 499
理解数据库4种隔离级别记录。
[短的文章] Spring Boot 日志创建使用、日志级别、@Slf4j、日志持久化——Spring Boot 系列
honglan297的博客
11-24 2444
想必日志大家伙在项目开发调试过程中常会使用，这篇文章我们来一起探讨一下 Spring Boot 项目中日志的定义与使用，主要针对以下几个方面重点叙述：日志级别、日志框架进行日志定义、日志持久化
使用apache log4j 的四个maven依赖
热门推荐
码基的博客
10-11 1万+
解决apache log4j cannot resolve的问题 <dependency> <groupId>commons-logging</groupId> <artifactId>commons-logging</artifactId> <versio...
mysql日志默认级别_MySQL 日志之 binlog 格式 → 关于 MySQL 默认隔离级别的探讨
weixin_30620431的博客
01-25 604
binlog 格式binlog 全称：binary log，即二进制日志，有时候也称归档日志，记录了对 MySQL 数据库执行了更改的所有操作，包括表结构变更(CREATE、ALTER、DROP TABLE…)、表数据修改(INSERT、UPDATE、DELETE...)，但不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改；若更改操作并未导致数据库变化，那么该操作也会...
MySQL事务隔离级别设置与影响
"MySQL数据库事务隔离级别详解，包括设置方法、查询方式及幻读现象解析" 在MySQL数据库中，事务隔离级别是确保数据一致性与并发控制的关键特性。事务是一系列数据库操作的逻辑单元，它们要么全部成功，要么全部失败...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值