MySQL 的事务和事务的隔离级别

robch

已于 2024-10-28 10:17:28 修改

阅读量379

点赞数

分类专栏： Django # 并发数据库文章标签： mysql 数据库 java

于 2020-01-17 20:46:16 首次发布

本文链接：https://blog.youkuaiyun.com/cpxsxn/article/details/104024466

版权

Django 同时被 3 个专栏收录

48 篇文章

订阅专栏

数据库

38 篇文章

订阅专栏

并发

20 篇文章

订阅专栏

MySQL 数据库，当且仅当引擎是InnoDB，才支持事务。
对于一个MySQL数据库（InnoDB），事务的开启与提交模式无非下面这两种情况：

若参数autocommit=0，事务则在用户本次对数据进行操作时自动开启，(有没有START TRANSACTION都没关系)在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。
若参数autocommit=1（系统默认值），事务的开启与提交又分为两种状态：
a. 手动开启手动提交：
当用户执行start transaction命令时（事务初始化），一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start. transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。

b.自动开启自动提交：
如果用户在当前情况下（参数autocommit=1）未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

在MySQL中, SET autocommit=0;指事务非自动提交，自此句命令执行以后，每个SQL语句或者语句块所在的事务都需要显式调用commit才能提交事务。

不管 autocommit 是1还是0 :
	(如果有)START TRANSACTION + (则需要有) commit 数据才会生效，ROLLBACK会回滚。
	
当 autocommit 为 0 时:
	有没有START TRANSACTION都没关系。
	只有当commit数据才会生效，ROLLBACK会回滚。

当autocommit 为 1 时 ，
	如果没有START TRANSACTION, 调用ROLLBACK是没有用的, 即便设置了SAVEPOINT。 
	也就是说, 必须设置START TRANSACTION才能回滚。


MySQL 中 autocommit，start transaction 和 begin/commit的异同?
这三个关键字都与mysql的事务相关，直接操纵事务的语句，不同点如下：
1.作用范围不一样。
   AUTOCOMMIT是数据库innodb引擎级别的属性，对于 start transaction 和 begin 而言是全局的，
   一旦使用SET AUTOCOMMIT=0 禁止自动提交，则在这个数据库内部的所有事务都不会自动提交，除非你手动的为每一个事务执行了commit或者rollback语句；
   而 start transaction和begin/commit只能控制某一个事务。
2.优先级不同。
   存在一种set autocommit = 1/0 但是对于某一个sql语句使用了 begin/commit的原子性操作，
   那么mysql会优先使用begin/commit命令控制被这组命令修饰的事务；这一点比较重要，
   因为之前很多的博主都说要先设置set autocommit = 0关闭掉事务的自动提交属性才能使用 begin/commit或者begin/rollback的原子性操作，这是错误的.

至于start transaction 和 begin的区别：
   两者的作用一摸一样，只是在 begin 可能成为关键字的时候，使用 start transaction 可以避免这种情况，
   start transaction或者begin开启一个事务，然后使用commit提交事务或者ROLLBACK回滚事务

事务的隔离级别分为：未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)。
我的本地 MySQL 默认的设置是：可重复读

mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set, 1 warning (0.05 sec)
mysql>

在 Console 1: a 和 b 二选一即可
a. 执行 set autocmmit = 0; 即关闭mysql数据库的自动提交属性；从上可知其实这步不是必须的
b. 执行 begin; 即开启事务(这个 begin 不是必须的，好像设置了 autocmmit 为 0 就会开启事务)
c. 执行 select * from student where id = 3
在 Console 2:
d. 执行 update student set name = concat(name,‘3’) where id = 3;
在 Console 1:
e. 执行 select * from student where id = 3 可以看到和步骤 1 看到的结果一样，并没有显示步骤2的更新
也就是说对可重复读的事务隔离级别，在 console1手动开启的事务中看不到 console2已经提交的事务

示例：

Console 1:
	mysql> use robertdb;
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A
	Database changed
	mysql> 
	mysql> begin;
	Query OK, 0 rows affected (0.00 sec)
	mysql> 
	mysql> select * from student where id = 3 ;
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	| id | name | gender | address | create_date                | update_date                | country_id |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	|  3 | rob  |      4 | BeiJing | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	1 row in set (0.00 sec)
	mysql> 
	mysql> select * from student where id = 3;
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	| id | name | gender | address | create_date                | update_date                | country_id |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	|  3 | rob  |      4 | BeiJing | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	+----+------+--------+---------+----------------------------+----------------------------+------------+
	1 row in set (0.00 sec)
	mysql>

Console2:
	mysql> use robertdb;
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A
	Database changed
	mysql> 
	mysql> select * from student;
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	| id  | name         | gender | address      | create_date                | update_date                | country_id |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	|   1 | c'ppp\'ppp"x |      2 | HeNan        | 2018-11-12 22:15:48.000000 | 2019-10-30 15:50:50.452264 |          1 |
	|   3 | rob          |      4 | BeiJing      | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	|   4 | gww          |      1 | BeiJing      | 2018-11-12 22:16:06.000000 | 2020-01-10 14:58:19.914886 |          1 |
	|   5 | sxn          |      2 | HeNan        | 2018-11-12 22:16:15.000000 | 2018-11-12 22:16:15.000000 |          1 |
	|   7 | syy          |      2 | MoNaShi      | 2018-11-14 10:21:24.179000 | 2018-11-14 10:21:24.179000 |          3 |
	|   8 | cjs          |      2 | ShanXi       | 2018-11-14 13:52:41.193000 | 2018-11-14 13:52:41.193000 |          1 |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	6 rows in set (0.00 sec)
	mysql>
	mysql> update student set name = concat(name,'3') where id  = 3;
	Query OK, 1 row affected (0.08 sec)
	Rows matched: 1  Changed: 1  Warnings: 0
	mysql> 
	mysql> select * from student;
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	| id  | name         | gender | address      | create_date                | update_date                | country_id |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	|   1 | c'ppp\'ppp"x |      2 | HeNan        | 2018-11-12 22:15:48.000000 | 2019-10-30 15:50:50.452264 |          1 |
	|   3 | rob3         |      4 | BeiJing      | 2018-11-12 22:16:01.000000 | 2018-11-12 22:16:01.000000 |          1 |
	|   4 | gww          |      1 | BeiJing      | 2018-11-12 22:16:06.000000 | 2020-01-10 14:58:19.914886 |          1 |
	|   5 | sxn          |      2 | HeNan        | 2018-11-12 22:16:15.000000 | 2018-11-12 22:16:15.000000 |          1 |
	|   7 | syy          |      2 | MoNaShi      | 2018-11-14 10:21:24.179000 | 2018-11-14 10:21:24.179000 |          3 |
	|   8 | cjs          |      2 | ShanXi       | 2018-11-14 13:52:41.193000 | 2018-11-14 13:52:41.193000 |          1 |
	+-----+--------------+--------+--------------+----------------------------+----------------------------+------------+
	6 rows in set (0.01 sec)
	mysql>