MySQL 事务功能

首页 > 代码库 > MySQL 事务功能

2024-09-09 08:51:23 217人阅读

MySQL 事务功能

事务：

1.介绍

★事务：
一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；
★ACID测试：
☉A：AUTOMICITY，原子性；
整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
☉C：CONSISTENCY，一致性；
数据库总是应该从一个一致性状态转为另一个一致性状态；
☉I：ISOLATION，隔离性；
一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别；
☉D：DURABILITY，持久性；
事务一旦提交，其所做出的修改会永久保存；

2.事务日志：

mysql会最大程度的使用缓存机制来提高数据库的访问效率，但是万一数据库发生断电，因为缓存的数据没有写入磁盘，导致缓存在内存中的数据丢失而导致数据不一致怎么办？
★Innodb主要是通过事务日志实现ACID特性；
★事务日志包括：
☉重做日志redo
Redo记录的是已经全部完成的事务，就是执行了commit（提交）的事务，记录文件是ib_logfile0 ib_logfile1；
☉回滚日志undo
Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）；
★执行过程：
一般情况下，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;  # node会话执行回滚后查看，可以看到又为原来的状态
+----------+----------------+
| 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会话只要建立事务，第一次查看数据的结果将贯穿整个事物过程中，期间底层数据的变化是看不到的，只有在结束事物后才可以看到数据的变化，这样直接导致的结果就是幻读，即各自会话看到的数据并非真实的数据，底层数据可能已经发生了改变；

本文出自 “逐梦小涛” 博客，请务必保留此出处http://1992tao.blog.51cto.com/11606804/1907956

MySQL 事务功能

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MySQL 事务功能

MySQL 事务功能

事务：

看完仍有疑问？有类似问题直接问程序猿