Mysql事务

于 2021-01-02 21:59:48 发布
阅读量3.1k 收藏 128
点赞数 27
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43549291/article/details/112120707
版权

在这里插入图片描述

本篇内容

  1. 什么是事务，它有什么用？
  2. 事务的几个特性
  3. 事务常见操作指令详解
  4. 事务的隔离级别详解
  5. 脏读、不可重复读、可重复读、幻读详解
  6. 演示各种隔离级别产生的现象
  7. 关于隔离级别的选择

什么是事务？

数据库中的事务是指对数据库执行一批操作，这些操作最终要么全部执行成功，要么全部失败；

举个例子

比如A用户给B用户转账100操作，过程如下：

1.从A账户扣100
2.给B账户加100

如果在事务的支持下，上面最终只有2种结果：

  1. 操作成功：A账户减少100；B账户增加100
  2. 操作失败：A、B两个账户都没有发生变化

如果没有事务的支持，可能出现错：A账户减少了100，此时系统挂了，导致B账户没有加上100，而A账户凭空少了100。

事务的几个特性(ACID)

原子性(Atomicity)

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

一致性(Consistency)

事务开始之前、执行中、执行完毕，这些时间点，多个人去观察事务操作的数据的时候，看到的数据都是一致的；

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。每个事务的执行过程是相对独立的；

持久性(Durability)

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘；

Mysql中事务操作（隐式事务 显式事务）

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交、回滚事务。

是否开启隐式事务是由变量autocommit控制的。

隐式事务

insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量autocommit是否开启了自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
显式事务

事务需要手动开启、提交或回滚，由开发者自己控制。

2种方式手动控制事务：

方式1：

语法：

//关闭隐式事务
set autocommit=0;
//执行事务操作
commit|rollback;

示例1：提交事务操作，如下：

mysql> create table test1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> set autocommit=0; #关闭隐式事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;  #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

示例2：回滚事务操作，如下：

mysql> set autocommit=0; #关闭隐式事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback; #回滚事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

我们把autocommit还原回去：

mysql> set autocommit=1; #开启隐式事务
Query OK, 0 rows affected (0.00 sec)

方式2：

语法：

start transaction; #开启显式事务
commit|rollback;  #提交|回滚

示例1：提交事务操作，如下：

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values (3);
Query OK, 1 row affected (0.00 sec)

mysql> commit; #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

上面成功插入了2条数据。

示例2：回滚事务操作，如下：

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> start transaction; #开启显式事务
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> rollback;  #回滚事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

上面事务中我们删除了test1的数据，显示删除了3行，最后回滚了事务。

savepoint关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，可以使用savepoin来实现，效果如下：

清除test1表数据：

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

演示savepoint效果：

mysql> start transaction; #开启显式事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint part1; #设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to part1;#回滚到保存点part1
Query OK, 0 rows affected (0.00 sec)

mysql> commit;#提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

从上面可以看出，执行了2次插入操作，最后只插入了1条数据。

savepoint需要结合rollback to sp1一起使用，可以将保存点sp1到rollback to之间的操作回滚掉。

只读事务

事务执行的操作是一些查询操作，性能上可能会有优化；

用法如下：

start transaction read only;

示例：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> delete from test1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

只读事务中执行增删改的时候会报错；

事务中的一些问题

脏读

一个事务在执行的过程中读取到了其他事务还没有提交的数据。

读已提交

一个事务操作过程中可以读取到其他事务已经提交的数据。

事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据

可重复读

一个事务操作中对于一个读取操作不管多少次，读取到的结果都是一样的。

幻读

以mysql为例：

幻读在可重复读的模式下才会出现，其他隔离级别中不会出现

幻读现象例子：

可重复读模式下，比如有个用户表，手机号码为主键，有两个事物进行如下操作

事务A操作如下：
1、打开事务
2、查询号码为X的记录，不存在
3、插入号码为X的数据，插入报错（为什么会报错，先向下看）
4、查询号码为X的记录，发现还是不存在（由于是可重复读，所以读取记录X还是不存在的）

事物B操作：在事务A第2步操作时插入了一条X的记录，所以会导致A中第3步插入报错（违反了唯一约束）

上面操作对A来说就像发生了幻觉一样，明明查询X（A中第二步、第四步）不存在，但却无法插入成功

幻读可以这么理解：事务中后面的操作（插入号码X）需要上面的读取操作（查询号码X的记录）提供支持，但读取操作却不能支持下面的操作时产生的错误，就像发生了幻觉一样。

事务的隔离级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，比如A、B两个事物同时进行的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的隔离级别来保证，不同的隔离级别中所产生的效果是不一样的。

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。

隔离级别分为4种：

  1. 读未提交：READ-UNCOMMITTED
  2. 读已提交：READ-COMMITTED
  3. 可重复读：REPEATABLE-READ
  4. 串行：SERIALIZABLE

上面4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别
mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)
隔离级别的设置

分2步骤，修改文件、重启mysql，如下：

修改mysql中的my.init文件，我们将隔离级别设置为：READ-UNCOMMITTED，如下：

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=READ-UNCOMMITTED

以管理员身份打开cmd窗口，重启mysql，如下：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。
各种隔离级别中会出现的问题
隔离级别脏读不可重复读幻读
READ-UNCOMMITTED有有无
READ-COMMITTED无有无
REPEATABLE-READ无无有
SERIALIZABLE无无无

下面我们来演示一下，各种隔离级别中可见性的问题，开启两个窗口，叫做A、B窗口，两个窗口中登录mysql。

READ-UNCOMMITTED：读未提交

将隔离级别置为READ-UNCOMMITTED：

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

先清空test1表数据：

delete from test1;
select * from test1;

按时间顺序在2个窗口中执行下面操作：

时间窗口A窗口B
T1start transaction;
T2select * from test1;
T3start transaction;
T4insert into test1 values (1);
T5select * from test1;
T6select * from test1;
T7commit;
T8commit;

A窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> select * from test1; #这里我们是能够看到b事务未提交的数据的
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

结论：读未提交情况下，可以读取到其他事务还未提交的数据，多次读取结果不一样，出现了脏读、不可重复读

READ-COMMITTED：读已提交

将隔离级别置为READ-COMMITTED

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

先清空test1表数据：

delete from test1;
select * from test1;

按时间顺序在2个窗口中执行下面操作：

时间窗口A窗口B
T1start transaction;
T2select * from test1;
T3start transaction;
T4insert into test1 values (1);
T5select * from test1;
T6select * from test1;
T7commit;
T8select * from test1;
T9commit;

A窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> select * from test1;  #b事务没有提交的数据是看不到；
Empty set (0.00 sec)

mysql> select * from test1; #这里读取到的是b事务已经提交的数据
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

结论：读已提交情况下，无法读取到其他事务还未提交的数据，可以读取到其他事务已经提交的数据，多次读取结果不一样，未出现脏读，出现了读已提交、不可重复读。

REPEATABLE-READ：可重复读

将隔离级别置为REPEATABLE-READ

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

先清空test1表数据：

delete from test1;
select * from test1;

按时间顺序在2个窗口中执行下面操作：

时间窗口A窗口B
T1start transaction;
T2select * from test1;
T3start transaction;
T4insert into test1 values (1);
T5select * from test1;
T6select * from test1;
T7commit;
T8select * from test1;
T9commit;
T10select * from test1;

A窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

B窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

结论：可重复读情况下，未出现脏读，未读取到其他事务已提交的数据，多次读取结果一致，即可重复读。

幻读演示

幻读只会在REPEATABLE-READ（可重复读）级别下出现，需要先把隔离级别改为可重复读。

将隔离级别置为REPEATABLE-READ

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

准备数据：

mysql> create table t_user(id int primary key,name varchar(16) unique key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user values (1,'mysql'),(2,'mysql');
ERROR 1062 (23000): Duplicate entry 'mysql' for key 'name'

mysql> select * from t_user;
Empty set (0.00 sec)

上面我们创建t_user表，name添加了唯一约束，表示name不能重复，否则报错。

按时间顺序在2个窗口中执行下面操作：

时间窗口A窗口B
T1start transaction;
T2start transaction;
T3– 插入 insert into t_user values (1,‘mysql’);
T4select * from t_user;
T5– 查看是否存在 select * from t_user where name=‘mysql’;
T6commit;
T7– 插入 insert into t_user values (2,‘mysql’);
T8– 查看是否存在 select * from t_user where name=‘mysql’;
T9commit;

A窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_user where name='mysql';
Empty set (0.00 sec)

mysql> insert into t_user values (2,'mysql');
ERROR 1062 (23000): Duplicate entry 'mysql' for key 'name'
mysql> select * from t_user where name='mysql';
Empty set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

B窗口如下：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_user values (1,'mysql');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | mysql   |
+----+---------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
SERIALIZABLE：串行

SERIALIZABLE会让并发的事务串行执行。

看效果：

将隔离级别置为SERIALIZABLE

# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolation=SERIALIZABLE

重启mysql：

C:\Windows\system32>net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。

C:\Windows\system32>net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。

查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set, 1 warning (0.00 sec)

先清空test1表数据：

delete from test1;
select * from test1;

按时间顺序在2个窗口中执行下面操作：

时间窗口A窗口B
T1start transaction;
T2select * from test1;
T3start transaction;
T4insert into test1 values (1);
T5select * from test1;
T6commit;
T7commit;

按时间顺序运行上面的命令，会发现T4-B这样会被阻塞，直到T6-A执行完毕。

可以看出来，事务只能串行执行了。串行情况下不存在脏读、不可重复读、幻读的问题了。

关于隔离级别的选择

  1. 隔离级别越高，并发性也低，比如最高级别SERIALIZABLE会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
  2. 具体选择哪种需要结合具体的业务来选择。
  3. 读已提交（READ-COMMITTED）通常用的比较多。

总结

  1. 理解事务的4个特性：原子性、一致性、隔离性、持久性
  2. 掌握事务操作常见命令的介绍
  3. set autocommit可以设置是否开启自动提交事务
  4. start transaction：开启事务
  5. start transaction read only：开启只读事物
  6. commit：提交事务
  7. rollback：回滚事务
  8. savepoint：设置保存点
  9. rollback to 保存点：可以回滚到某个保存点
  10. 掌握4种隔离级别及了解其特点
  11. 了解脏读、不可重复读、幻读

img

「已注销」
关注
  • 27
    点赞
  • 踩
  • 128
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
MySQL事务
QLH的博客
08-14 266
事务1、事务定义2、转账操作理解事务3、事务四大特征(ACID)4、关于事务的一些术语5、和事务相关的两条重要的SQL语句(TCL)6、事务开启的标志？事务结束的标志？7、事物与数据库底层数据8、在MySQL中，事务提交与回滚9、无隔离性的问题10、事务的隔离级别读未提交读已提交可重复度可串行化11、设置事务隔离级别12、事务隔离级别的作用范围分为两种： 1、事务定义 Transaction 事务...
MySql事务无法回滚的原因有哪些
09-10
MySQL事务无法回滚的情况可能由多种因素引起，尤其是在配置如Hibernate、Spring或JDBC等框架时，如果一切看似正常，但事务处理仍然失效，我们应当深入检查数据库本身的特性和设置。首要的问题通常与所选用的表类型...
MySQL——事务(Transaction)详解
热门推荐
浅然的专栏
03-23 23万+
该博客详解MySQL中的事务 一、事务定义 Transaction 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元) 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同...
MySQL 事务
Demo_Null
07-06 3438
一篇文章让你看懂什么是 MySQL 事务
Mysql事务1
08-08
MySQL 事务是数据库管理系统中用于确保一组数据库操作的完整性和一致性的重要机制。在多用户、多线程的环境中，事务的使用确保了数据的一致性，避免了在并发操作中可能出现的问题。 1.1. 事务简介 事务是数据库操作...
mySQL事务处理
06-13
关于mysql的事务处理 public static void StartTransaction(Connection con, String[] sqls) throws Exception { if (sqls == null) { return; } Statement sm = null; try { // 事务开始 System....
Mysql事务隔离级别原理实例解析
09-08
MySQL事务隔离级别是数据库管理系统中确保事务之间正确交互的重要机制，它主要涉及到四个级别：读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）。...
mysql事务
最新发布
06-01
MySQL 事务是指一组数据库操作，这些操作要么全部执行，要么全部不执行，其目的是保证在并发环境下，数据的一致性和完整性。MySQL 事务具有 ACID 性质，即原子性、一致性、隔离性和持久性。 MySQL 中使用事务需要...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值