mysql事务

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

理论

1. • 事务就是一组原子性的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，所以采用事务日志的方式相对来说快很多。事务日志持久后，内存中修改的数据在后台可以慢慢的刷回磁盘，修改数据需要写两次磁盘。如果修改的数据已经记录到事务日志并持久化，即使数据本身还没有写回磁盘，操作系统崩溃了，存储引擎在重启时能够自动回复这部分数据。

实践

1. • 提交读和可重复读的区别
     • 提交读的隔离级别下，我们进行下面的试验，验证“一个事务从开始知道提交之前，所作的任何修改对其他事务都是不可见的”，先测试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

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

1. • 隔离级别不变，测试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影响。

1. • 隔离级别为可重复读，测试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的时候同样符合预期结果。

1. • 关于事务日志
     • 关于事务日志的说明中，我们可以看得出来，只要修改的数据已经写入到日志并且持久化了，数据本身还没有写入磁盘时，即使断电了，系统在重启的时候依然会将数据恢复。那么我们再来看看官网给出的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