备案控制台
开发者社区 数据库 文章 正文

mysql事务

2015-01-09 760
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 版权声明：欢迎转载，请注明沉默王二原创。 https://blog.csdn.net/qing_gee/article/details/42551179 项目中使用的是mysql数据库，有幸看到《高性能mysql》一书，并且参考其对事务的介绍，结合实际开发对事务进行了测试，主要关注点在事务的隔离级别、innodb_flush_log_at_trx_commit、autocommit参数对mysql性能的影响。
版权声明：欢迎转载，请注明沉默王二原创。 https://blog.csdn.net/qing_gee/article/details/42551179

项目中使用的是mysql数据库，有幸看到《高性能mysql》一书，并且参考其对事务的介绍，结合实际开发对事务进行了测试，主要关注点在事务的隔离级别、innodb_flush_log_at_trx_commit、autocommit参数对mysql性能的影响。

理论
  • 事务就是一组原子性的SQL查询，或者说一个独立的工作单元。那么如果数据库引擎能够成功的执行该组内的所有语句，那么就执行该组语句。如果其中有任何一条语句执行失败，则所有语句都不执行。也就是说，事务内的语句，要么全部执行，要不都不执行。
  • 然后是事务的ACID特征。
    • 原子性：一个事务必须被视作一个不可分割的最小单元。整个事务中的所有操作要么全部执行成功，否则全部回滚，不可能只执行其中一部分，确保事务内的所有语句是一个原子。
    • 一致性：数据库总是从一个一致性的状态到另外一个一致性的状态。如果事务中一条语句执行失败，任何已经执行成功的语句也不会保存到数据库中。
    • 隔离性：通常情况下（这个因事务隔离级别有不同的情况），一个事务在最终提交之前，对其他事务时不可见的，注意是通常情况。
    • 持久性：一旦事务提交，则其所作的修改就会永久保存到数据库中。这个概念我本人还没有很好的理解（比如对与innodb来说，其中的一个参数innodb_flush_log_at_trx_commit，对于事务的提交后数据写入磁盘的时间点有很大的不同。）
  • 一个实现了ACID的数据库，比如对于mysql数据库，通常会需要更强的CPU处理能力、更多的磁盘空间、更大的内存大小。但具体需要什么样的配置，我也没有找出合理的方案，对于我们的期货交易平台，到底需要什么样的配置更合理。
  • 隔离级别（每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些不可见。较低级别的隔离通常可以执行更高的并发，系统的开销个更低）：
    • READ UNCOMMITTED（未提交读）：在这个级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据（被称为脏读），这种级别会有很多问题，性能也不会比其他级别好，不推荐使用。
    • READ COMMITTED（提交读）：一个事务开始时，只能看见已经提交的事务所作的修改，换言之，就是只要该事务没有提交，其他事务是无法看见本事务已经做的修改。
    • REPEATABLE READ（可重复读）：该级别保证了同一个事务中多次读取同样记录的结果是一致的（这块我稍后会做出测试，但是有疑问），对于mysql的innodb引擎来说，该引擎使用了MVCC模式进行控制，解决了幻读的问题，但是理论上，其他的引擎如果没有解决幻读的问题时，会出现幻行（对于幻读和幻行，稍后再进行研究）。mysql默认的隔离级别为可重复读。
    • SERIALIZABLE（可串行化）：它强制事务串行执行，避免了幻读的问题。简单来说，该级别时，事务在读取到每一行数据时都会加锁，所以可能导致大量的争锁和超时问题，实际应用下，很少用到。
  • 自动提交（AUTOCOMMIT）：mysql默认采用自动提交模式。也就是说，如果不显式的开始一个事务，则每个查询都会被当作一个事务提交。也这样说，如果显示的开始一个事务，就是说使用了start transaction开启事务的时候，事务内的所有查询不再分离成一个个小的事务进行提交操作。
  • 事务日志：事务日志可以帮助提高事务的效率，存储引擎在修改表的数据时只需要修改你内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作时磁盘上一小块区域内的顺序IO，所以采用事务日志的方式相对来说快很多。事务日志持久后，内存中修改的数据在后台可以慢慢的刷回磁盘，修改数据需要写两次磁盘。如果修改的数据已经记录到事务日志并持久化，即使数据本身还没有写回磁盘，操作系统崩溃了，存储引擎在重启时能够自动回复这部分数据。
实践
  • 提交读和可重复读的区别
    • 提交读的隔离级别下，我们进行下面的试验，验证“一个事务从开始知道提交之前，所作的任何修改对其他事务都是不可见的”，先测试insert的情况
事务1 事务2
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
+----+-------+
2 rows in set
 1
2 mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
+----+-------+
2 rows in set


mysql> insert into test values(3,3);
Query OK, 1 row affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
+----+-------+
2 rows in set
 3
4 mysql> commit;
Query OK, 0 rows affected
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set


mysql> commit;
Query OK, 0 rows affected
 5


通过上面的顺序执行，可以看出来“一个事务开始时，只能看到已经提交的事务所作的修改”，并且“一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的”，然后在一个事务内“两次执行同样的查询，可能会得到不一样的结果”。

  • 隔离级别不变，测试update情况
事务1 事务2
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set		 1
2 mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set


mysql> update test set value = 11 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |    11 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set		 3
4 mysql> commit;
Query OK, 0 rows affected
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |    11 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set


mysql> commit;
Query OK, 0 rows affected		 5
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id = 1;
+-------+
| value |
+-------+
|    11 |
+-------+
1 row in set		 6
7 mysql> start transaction;
Query OK, 0 rows affected


mysql> update test set value = 1 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select value from test where id = 1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set
mysql> select value from test where id = 1;
+-------+
| value |
+-------+
|    11 |
+-------+
1 row in set		 8
9 mysql> commit;
Query OK, 0 rows affected
mysql> select value from test where id = 1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set


mysql> commit;
Query OK, 0 rows affected		 10
从以上步骤可以看出来，使用READ-COMMITTED的时候，一个事务内部，其查询结果会受到其他事务的update和insert影响。


  • 隔离级别为可重复读，测试insert情况
首先我们先看一下命令行模式下，怎么修改事务隔离级别（以下是把可重复读改成提交读，注意如果按照我的测试顺序，请注意修改） 
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql>
貌似隔离级别没有变？关掉当前命令行，重新打开一个命令行 
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql>

事务隔离级别修改完毕后，我们开始测试
事务1 事务2
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set		 1
2 mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set


mysql> insert into test values(4,4);
Query OK, 1 row affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
|  4 |     4 |
+----+-------+
4 rows in set


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set		 3
4 mysql> commit;
Query OK, 0 rows affected
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |     1 |
|  2 |     2 |
|  3 |     3 |
+----+-------+
3 rows in set


mysql> commit;
Query OK, 0 rows affected		 5


从以上结果可以看得出来“该级别保证了同一个事务中，多次读取同样的结果是一致的”。这里并没有出现幻行，因为我的数据库引擎为innodb，书中说这种引擎可以解决幻读的问题。
    • 隔离级别为可重复读，测试update情况
事务1 事务2
mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id=1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set

mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id =1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set


mysql> update test set value=11 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select value from test where id =1;
+-------+
| value |
+-------+
|    11 |
+-------+
1 row in set
mysql> select value from test where id=1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set

mysql> commit;
Query OK, 0 rows affected


mysql> select value from test where id=1;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set


mysql> commit;
Query OK, 0 rows affected


update的时候同样符合预期结果。

  • 关于事务日志
    • 关于事务日志的说明中，我们可以看得出来，只要修改的数据已经写入到日志并且持久化了，数据本身还没有写入磁盘时，即使断电了，系统在重启的时候依然会将数据恢复。那么我们再来看看官网给出的innodb_flush_log_at_trx_commit参数的介绍
      Controls the balance between strict ACID compliance for commit operations, and higher performance
that is possible when commit-related I/O operations are rearranged and done in batches. You can
achieve better performance by changing the default value, but then you can lose up to a second of
transactions in a crash.
• The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB
log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.
• With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once
per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed
at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second,
due to process scheduling issues. Because the flush to disk operation only occurs approximately once
per second, you can lose up to a second of transactions with any mysqld process crash.
• With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction
commit and the log file is flushed to disk approximately once per second. Once-per-second flushing
is not 100% guaranteed to happen every second, due to process scheduling issues. Because the
flush to disk operation only occurs approximately once per second,
      大致的意思是将该属性主要是为数据库的ACID原则进行服务的，并且默认为1，但是实际情况下（我们项目是结合spring和mybatis，可能是某一方面设置不当），设置为2会提高很多的事务性能，从文档中可以看得出来，“1的时候，innodb的缓存会在事务提交或者每秒钟时都会进行磁盘的刷新操作，2的时候，innodb缓存会在提交事务时写入到事务日志但不会刷新磁盘，然后在每秒钟时进行磁盘刷新操作”，2要比1提高很多性能，但是对于隐患来说，我没有太好的理解，按照文档中给出的结果好像是“在操作系统崩溃的时候，2的情况下，会丢失1秒的数据”，但是仔细想想发生的时间节点，1.事务没有commit时，断电了，此时肯定数据是没有更新成功的，因为都还没有来得及写入事务日志，2.事务提交后，在写入事务日志的时候，发生断电，此时无论是参数的值是1还是2，都应该恢复不了数据了，3.每秒钟刷新磁盘时，发生断电，按照《高性能mysql》的字面意思，此时既然事务日志已经持久化了，那么重启后，数据是会自动恢复的。那么疑问来了，2和1的隐患到底在什么情况下会发生。
  • 关于autocommit
    • mysql默认情况下，该参数是打开的，那么这个参数对于应用了spring和mybatis的项目来说，到底是关闭该参数还是打开该参数，会有怎么样的性能影响，我没有弄清楚，如果有热心的读者可以给出一点答复。
  • 另外我们可以通过以下命令进行该参数的设置，当然1为ON，0为OFF。
    mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set

mysql> set autocommit = 0;
Query OK, 0 rows affected

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set

文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
测试技术
监控
数据库
MySQL
关键词：
云数据库 RDS MySQL 版事务
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
沉默王二
目录
相关文章
技术蜜糖罐
|
1月前
|
SQL 关系型数据库 MySQL
轻松入门MySQL：保障数据完整性，MySQL事务在进销存管理系统中的应用（12）
轻松入门MySQL：保障数据完整性，MySQL事务在进销存管理系统中的应用（12）
技术蜜糖罐
36 0
LightOf
|
1月前
|
SQL 安全 关系型数据库
【MySQL实战笔记】03.事务隔离：为什么你改了我还看不见？-01
【4月更文挑战第6天】MySQL事务的隔离性确保数据操作的完整性和一致性，ACID原则包括原子性、一致性、隔离性和持久性。事务隔离级别有四种：读未提交、读提交、可重复读和串行化，分别解决并发问题如脏读、不可重复读和幻读。不同隔离级别在效率和安全性间权衡，例如读未提交允许未提交变更可见，而串行化通过锁保证安全但可能降低效率。在不同隔离级别下，事务看到的数据状态会有所变化，例如在可重复读级别，事务始终看到初始数据，而在串行化级别，事务会等待其他事务完成再继续，避免数据冲突。
LightOf
278 10
toKeep
|
5天前
|
SQL 存储 关系型数据库
MySQL索引及事务
MySQL索引及事务
toKeep
16 2
菜菜的后端私房菜
|
5天前
|
存储 算法 关系型数据库
MySQL事务与锁，看这一篇就够了！
MySQL事务与锁，看这一篇就够了！
菜菜的后端私房菜
12 0
qiuqiushuibx
|
5天前
|
Java 关系型数据库 MySQL
MySQL 索引事务
MySQL 索引事务
qiuqiushuibx
13 0
你的小笔记本YY
|
13天前
|
SQL 安全 关系型数据库
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
你的小笔记本YY
32 5
小谢在努力
|
15天前
|
存储 关系型数据库 MySQL
Mysql学习--深入探究索引和事务的重点要点与考点
Mysql学习--深入探究索引和事务的重点要点与考点
小谢在努力
10 0
游客hjgt3xacvje4w
|
15天前
|
存储 SQL 关系型数据库
Mysql_数据库事务
Mysql_数据库事务
游客hjgt3xacvje4w
11 1
桃李春风一杯酒
|
17天前
|
缓存 关系型数据库 MySQL
【专栏】提升MySQL性能和高可用性的策略，包括索引优化、查询优化和事务管理
【4月更文挑战第27天】本文探讨了提升MySQL性能和高可用性的策略，包括索引优化、查询优化和事务管理。通过合理使用B-Tree和哈希索引，避免过度索引，以及优化查询语句和利用查询缓存，可以改善性能。事务管理中，应减小事务大小并及时提交，以保持系统效率。主从或双主复制可增强高可用性。综合运用这些方法，并根据实际需求调整，是优化MySQL的关键。
桃李春风一杯酒
24 2
翔宇1
|
18天前
|
Java 关系型数据库 MySQL
{MySQL}索引事务和JDBC
{MySQL}索引事务和JDBC
翔宇1
21 0

热门文章

最新文章

  • 1
    PostgreSQL 聚合函数讲解 - 3 总体|样本 方差, 标准方差
  • 2
    一文读懂AnalyticDB MySQL过滤条件智能下推原理
  • 3
    mysql主从监控脚本：
  • 4
    mysql主从配置一些注意
  • 5
    mysql (双主，互主)
  • 6
    MySql创建用户及授权
  • 7
    mysql数据库引擎问题汇总
  • 8
    Mysql使用正则表达式-regexp
  • 9
    MySQL数据库约束条件和自增长序列
  • 10
    MySQL 5.6数据导入报 GTID 相关错误
  • 1
    连接和管理RDS
    30
  • 2
    购买和初始化阿里云RDS
    32
  • 3
    rds教程
    30
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    197
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    51
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    53
  • 7
    TIDB和MySQL的区别
    216
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    78
  • 9
    [MySQL]事务原理之redo log,undo log
    127
  • 10
    [MySQL]SQL优化之索引的使用规则
    42

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 极致弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • 如何通过RDS MySQL数据库内网访问
  • RDS MySQL Serverless资源与负载自适应

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）