MySQL 事务功能
==============================================================================
事务:
1.介绍★事务:一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元;
★ACID测试:
☉A:AUTOMICITY,原子性;整个事务中的所有操作要么全部成功执行,要么全部失败后回滚;
☉C:CONSISTENCY,一致性;数据库总是应该从一个一致性状态转为另一个一致性状态;
☉I:ISOLATION,隔离性;一个事务所做出的操作在提交之前,是否能为其它事务可见;出于保证并发操作之目的,隔离有多种级别;
☉D:DURABILITY,持久性;事务一旦提交,其所做出的修改会永久保存;
2.事务日志:mysql会最大程度的使用缓存机制来提高数据库的访问效率,但是万一数据库发生断电,因为缓存的数据没有写入磁盘,导致缓存在内存中的数据丢失而导致数据不一致怎么办?
★Innodb主要是通过事务日志实现ACID特性;
★事务日志包括:
☉重做日志redoRedo记录的是已经全部完成的事务,就是执行了commit(提交)的事务,记录文件是ib_logfile0 ib_logfile1;
☉回滚日志undoUndo记录的是已部分完成并且写入硬盘的未完成的事务,默认情况下回滚日志是记录在表空间中的(共享表空间或者独享表空间);
★执行过程:一般情况下,mysql在崩溃之后,重启服务,innodb通过回滚日志undo将所有已完成并写入磁盘的未完成事务进行rollback,然后redo中的事务全部重新执行一遍(后台启动相关线程自动执行)即可恢复数据,但是随着redo的量增加,每次从redo的第一条开始恢复就会浪费长的时间,所以引入了checkpoint(检查点)机制;
★事务日志相关配置参数:innodb_log_files_in_group//一组中有几个文件,默认为2个
innodb_log_group_home_dir//日志目录的存放位置,默认在数据目录中
innodb_log_file_size//日志文件大小
innodb_mirrored_log_groups//镜像日志组
演示:
1.查看事物日志相关的参数MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%innodb%log%';
+-------------------------------------------+---------+
| Variable_name | Value |
+-------------------------------------------+---------+
| innodb_flush_log_at_trx_commit | 1 | 要不要在提交时从内存中同步数据到事物日志所在磁盘上,1表示ON开启
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 | 日志文件大小,默认为5M
| innodb_log_files_in_group | 2 | 一组中有几个文件,默认为2个
| innodb_log_group_home_dir | ./ | 日志目录的存放位置,默认在数据目录中
| innodb_mirrored_log_groups | 1 | 镜像日志组(自己)
| innodb_recovery_update_relay_log | OFF |
| innodb_use_global_flush_log_at_trx_commit | ON |
+-------------------------------------------+---------+
10 rows in set (0.00 sec)
3.自动提交:单语句事务★自动提交:单语句事务默认情况下mysql开启的是单语句事物功能(即每一次执行完成后都会自动提交至事物日志中,然后再从日志中同步到数据文件中,非常影响性能),我们要想手动控制事物,就要把自动提交事物的功能关闭。
★查看自动提交命令:mysql> SELECT @@autocommit;
mysql> SHOW GLOBAL|SESSION VARIABLES LIKE 'autocommit'
★关闭当前会话单语句功能:mysql> SET @@session.autocommit=0;
演示:
1.为了演示效果,支持事物功能,我这里修改了之前hellodb.sql的存储引擎为InnoDB,并重新导入mysql;现在,我们要想手动控制事物,就需要关闭单语句事物功能,如下:MariaDB [hellodb]> show table status like 'students'\G
*************************** 1. row ***************************
Name: students
Engine: InnoDB # 存储引擎为InnoDB
Version: 10
Row_format: Compact
Rows: 25
Avg_row_length: 655
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 26
Create_time: 2016-11-28 11:42:19
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)
MariaDB [hellodb]> SHOW VARIABLES LIKE 'autocommit'; # 查看当前会话的单语句事物功能为ON
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET @@session.autocommit=0;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> SHOW VARIABLES LIKE 'autocommit'; # 再次查看变为OFF
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------
4.手动控制事务:★启动:START TRANSACTION
★提交:COMMIT
★回滚:ROLLBACK
★事务支持 savepoints:SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
演示:
1.在关闭单语句事务的基础上,手动启动事物功能,并演示回滚操作MariaDB [hellodb]> START TRANSACTION; # 启动事物功能
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DESC courses;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| CourseID | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| Course | varchar(100) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.01 sec)
MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet'); # 插入一行数据
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | Zabbix |
| 9 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> DELETE FROM courses WHERE CourseID=3; # 删除一行数据
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 8 | Zabbix |
| 9 | Puppet |
+----------+----------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> ROLLBACK; # 如果这时我们后悔了,可以执行回滚操作
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses; # 查看可以发现又会到原来开始的状态了
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
2.在操作上添加savepoints(存储点),可以指定回滚到存储点的位置,如下:MariaDB [hellodb]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> SAVEPOINT first; # 添加第一个存储点
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELETE FROM courses WHERE CourseID=3;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> SAVEPOINT second; # 添加第二个存储点
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> UPDATE courses SET Course='nginx' WHERE CourseID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> SELECT * FROM courses; # 查看修改后的表数据
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 4 | nginx |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 10 | Zabbix |
| 11 | Puppet |
+----------+----------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> ROLLBACK TO second; # 回滚到第二个存储点
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 10 | Zabbix |
| 11 | Puppet |
+----------+----------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> ROLLBACK TO first;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses; # 回滚到第一个存储点
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 10 | Zabbix |
| 11 | Puppet |
+----------+----------------+
9 rows in set (0.01 sec)
MariaDB [hellodb]> ROLLBACK; # 回滚到原始状态
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
3.启动事物,执行操作后提交(COMMIT),发现再回滚就无效了,因为这时已经同步到数据中了;MariaDB [hellodb]> START TRANSACTION; # 启动事物
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> INSERT INTO courses (Course) VALUES ('Zabbix'),('Puppet');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> COMMIT; # 提交
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> SELECT * FROM courses; # 提交后查看数据
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses; # 执行回滚命令后发现无效
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
5.事务隔离级别:★事务隔离级别:READ-UNCOMMITTED:读未提交 --> 脏读;
READ-COMMITTED:读提交--> 不可重复读;
REPEATABLE-READ:可重复读 --> 幻读;(默认级别)
SERIALIZABLE:串行化;
★查看及设定事物隔离级别:mysql> SELECT @@session.tx_isolation;
mysql> SET @@session.tx_isolation='...'
演示:
1.READ-UNCOMMITTED:读未提交 --> 脏读;即当node1和node2两个会话同时访问修改同一张表时,node1用户的事物还没有提交,node2用户就可以看到,导致的结果就是脏读(看到了别人没能提交的数据),如下:
1)在node1会话中,修改隔离级别为读未提交,然后启动事物,修改一行数据,并回滚MariaDB [hellodb]> SELECT @@tx_isolation; # 查看隔离级别
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET @@session.tx_isolation='READ-UNCOMMITTED'; # 修改隔离级别为读未提交
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> START TRANSACTION; # 启动事物
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> UPDATE courses SET Course='nginx' WHERE CourseID=4; # 修改数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | nginx |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from courses; # 回滚后查看数据
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
2)在node2会话中可以发现,它没有做任何操作,前后两次查看时数据发生了变化,所以读未提交可以看到别人没能提交的数据MariaDB [hellodb]> set @@session.tx_isolation='READ-UNCOMMITTED'; # 同样设置运行级别为读未提交
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> start transaction; # 同样启动事物
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from courses; # node1会话修改数据后查看,可以看到修改的数据nginx
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | nginx |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.01 sec)
MariaDB [hellodb]> select * from courses; # node1会话执行回滚后查看,可以看到又为原来的状态
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
2.READ-COMMITTED:读提交--> 不可重复读;即当node1和node2两个会话同时访问修改同一张表时,node1用户的事物只有提交了,node2用户才可以看到,但node2用户并没有修改数据。所以,node2会话如果重复读的话,每次的结果可能是不一样的,如下:
1)在node1会话中,修改隔离级别为读提交,然后启动事物,修改一行数据,并提交;MariaDB [hellodb]> set @@session.tx_isolation='READ-COMMITTED'; #修改隔离级别为读提交
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> start transaction; # 启动事物
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> UPDATE courses SET Course='Nagios' WHERE CourseID=12; # 修改数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Nagios |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> COMMIT; # 提交数据
Query OK, 0 rows affected (0.01 sec)
2)在node2会话中,可以发现在node1会话未提交数据之前,node2查看到的数据一直没有发生改变,直到node1会话提交数据之后,node2才发现数据发生了改变,但node2没有做任何操作MariaDB [hellodb]> set @@session.tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses; # 在node1会话没有提交数据前,数据为改变
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Zabbix |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> SELECT * FROM courses; # node1会话提交数据后,可以看到数据发生改变
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
| 12 | Nagios |
| 13 | Puppet |
+----------+----------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> COMMIT;
Query OK, 0 rows affected (0.00 sec)
3.REPEATABLE-READ:可重复读 --> 幻读,也是mysql和mariadb的默认运行级别,即两个会话为各自独立的事务。node1和node2会话只要建立事务,第一次查看数据的结果将贯穿整个事物过程中,期间底层数据的变化是看不到的,只有在结束事物后才可以看到数据的变化,这样直接导致的结果就是幻读,即各自会话看到的数据并非真实的数据,底层数据可能已经发生了改变;