网站流量报告,如何进入优容网站,上海已经开始二次感染,2022最新国际国内新闻环境#xff1a;mysql5.7.25#xff0c;cmd命令中进行演示。
开发过程中#xff0c;会经常用到数据库事务#xff0c;所以本章非常重要。
本篇内容 什么是事务#xff0c;它有什么用#xff1f; 事务的几个特性 事务常见操作指令详解 事务的隔离级别详解 脏读、不可…
环境mysql5.7.25cmd命令中进行演示。
开发过程中会经常用到数据库事务所以本章非常重要。
本篇内容 什么是事务它有什么用 事务的几个特性 事务常见操作指令详解 事务的隔离级别详解 脏读、不可重复读、可重复读、幻读详解 演示各种隔离级别产生的现象 关于隔离级别的选择
什么是事务
数据库中的事务是指对数据库执行一批操作这些操作最终要么全部执行成功要么全部失败不会存在部分成功的情况。
举个例子
比如A用户给B用户转账100操作过程如下
1.从A账户扣100
2.给B账户加100如果在事务的支持下上面最终只有2种结果 操作成功A账户减少100B账户增加100 操作失败A、B两个账户都没有发生变化
如果没有事务的支持可能出现错A账户减少了100此时系统挂了导致B账户没有加上100而A账户凭空少了100。
事务的几个特性(ACID)
原子性(Atomicity)
事务的整个过程如原子操作一样最终要么全部成功或者全部失败这个原子性是从最终结果来看的从最终结果来看这个过程是不可分割的。
一致性(Consistency)
事务开始之前、执行中、执行完毕这些时间点多个人去观察事务操作的数据的时候看到的数据都是一致的比如在事务操作过程中A连接看到的是100那么B此时也去看的时候也是100不会说AB看到的数据不一样他们在某个时间点看到的数据是一致的。
隔离性(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)autocommit为ON表示开启了自动提交。 显式事务 事务需要手动开启、提交或回滚由开发者自己控制。 2种方式手动控制事务
方式1
语法
//设置不自动提交事务
set autocommit0;
//执行事务操作
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 autocommit0;
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 autocommit0;
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 autocommit1;
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;//将savepint 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之间的操作回滚掉。 只读事务
表示在事务中执行的是一些只读操作如查询但是不会做insert、update、delete操作数据库内部对只读事务可能会有一些性能上的优化。
用法如下
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)只读事务中执行delete会报错。 事务中的一些问题
这些问题主要是基于数据在多个事务中的可见性来说的。
脏读
一个事务在执行的过程中读取到了其他事务还没有提交的数据。 这个还是比较好理解的。
读已提交
从字面上我们就可以理解即一个事务操作过程中可以读取到其他事务已经提交的数据。
事务中的每次读取操作读取到的都是数据库中其他事务已提交的最新的数据相当于当前读
可重复读
一个事务操作中对于一个读取操作不管多少次读取到的结果都是一样的。
幻读
脏读、不可重复读、可重复读、幻读其中最难理解的是幻读
以mysql为例
幻读在可重复读的模式下才会出现其他隔离级别中不会出现
幻读现象例子
可重复读模式下比如有个用户表手机号码为主键有两个事物进行如下操作
事务A操作如下 1、打开事务 2、查询号码为X的记录不存在 3、插入号码为X的数据插入报错为什么会报错先向下看 4、查询号码为X的记录发现还是不存在由于是可重复读所以读取记录X还是不存在的
事物B操作在事务A第2步操作时插入了一条X的记录所以会导致A中第3步插入报错违反了唯一约束
上面操作对A来说就像发生了幻觉一样明明查询XA中第二步、第四步不存在但却无法插入成功
幻读可以这么理解事务中后面的操作插入号码X需要上面的读取操作查询号码X的记录提供支持但读取操作却不能支持下面的操作时产生的错误就像发生了幻觉一样。
如果还是理解不了的继续向下看后面后详细的演示。
事务的隔离级别
当多个事务同时进行的时候如何确保当前事务中数据的正确性比如A、B两个事物同时进行的时候A是否可以看到B已提交的数据或者B未提交的数据这个需要依靠事务的隔离级别来保证不同的隔离级别中所产生的效果是不一样的。
事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。
隔离级别分为4种 读未提交READ-UNCOMMITTED 读已提交READ-COMMITTED 可重复读REPEATABLE-READ 串行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-isolationREAD-UNCOMMITTED以管理员身份打开cmd窗口重启mysql如下
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net start mysql
mysql 服务正在启动 .
mysql 服务已经启动成功。各种隔离级别中会出现的问题
隔离级别脏读不可重复读幻读READ-UNCOMMITTED有有无READ-COMMITTED无有无REPEATABLE-READ无无有SERIALIZABLE无无无 表格中和网上有些不一样主要是幻读这块幻读只会在可重复读级别中才会出现其他级别下不存在。 下面我们来演示一下各种隔离级别中可见性的问题开启两个窗口叫做A、B窗口两个窗口中登录mysql。
READ-UNCOMMITTED读未提交
将隔离级别置为READ-UNCOMMITTED
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolationREAD-UNCOMMITTED重启mysql
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net 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窗口BT1start 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;
------
| 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)看一下:
T2-A无数据T6-A有数据T6时刻B还未提交此时A已经看到了B插入的数据说明出现了脏读。
T2-A无数据T6-A有数据查询到的结果不一样说明不可重复读。
结论读未提交情况下可以读取到其他事务还未提交的数据多次读取结果不一样出现了脏读、不可重复读
READ-COMMITTED读已提交
将隔离级别置为READ-COMMITTED
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolationREAD-COMMITTED重启mysql
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net 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窗口BT1start 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;
Empty set (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)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)看一下:
T5-B有数据T6-A窗口无数据A看不到B的数据说明没有脏读。
T6-A窗口无数据T8-A看到了B插入的数据此时B已经提交了A看到了B已提交的数据说明可以读取到已提交的数据。
T2-A、T6-A无数据T8-A有数据多次读取结果不一样说明不可重复读。
结论读已提交情况下无法读取到其他事务还未提交的数据可以读取到其他事务已经提交的数据多次读取结果不一样未出现脏读出现了读已提交、不可重复读。
REPEATABLE-READ可重复读
将隔离级别置为REPEATABLE-READ
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolationREPEATABLE-READ重启mysql
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net 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窗口BT1start 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)看一下:
T2-A、T6-A窗口无数据T5-B有数据A看不到B的数据说明没有脏读。
T8-A无数据此时B已经提交了A看不到B已提交的数据A中3次读的结果一样都是没有数据的说明可重复读。
结论可重复读情况下未出现脏读未读取到其他事务已提交的数据多次读取结果一致即可重复读。
幻读演示
幻读只会在REPEATABLE-READ可重复读级别下出现需要先把隔离级别改为可重复读。
将隔离级别置为REPEATABLE-READ
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolationREPEATABLE-READ重启mysql
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net 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,路人甲Java),(2,路人甲Java);
ERROR 1062 (23000): Duplicate entry 路人甲Java for key namemysql select * from t_user;
Empty set (0.00 sec)上面我们创建t_user表name添加了唯一约束表示name不能重复否则报错。 按时间顺序在2个窗口中执行下面操作
时间窗口A窗口BT1start transaction;T2start transaction;T3-- 插入路人甲Java insert into t_user values (1,路人甲Java);T4select * from t_user;T5-- 查看路人甲Java是否存在 select * from t_user where name路人甲Java;T6commit;T7-- 插入路人甲Java insert into t_user values (2,路人甲Java);T8-- 查看路人甲Java是否存在 select * from t_user where name路人甲Java;T9commit;
A窗口如下
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)mysql select * from t_user where name路人甲Java;
Empty set (0.00 sec)mysql insert into t_user values (2,路人甲Java);
ERROR 1062 (23000): Duplicate entry 路人甲Java for key name
mysql select * from t_user where name路人甲Java;
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,路人甲Java);
Query OK, 1 row affected (0.00 sec)mysql select * from t_user;
-------------------
| id | name |
-------------------
| 1 | 路人甲Java |
-------------------
1 row in set (0.00 sec)mysql commit;
Query OK, 0 rows affected (0.00 sec)看一下:
A想插入数据路人甲Java插入之前先查询了一下T5时刻该用户是否存在发现不存在然后在T7时刻执行插入报错了报数据已经存在了因为T6时刻B已经插入了路人甲Java。
然后A有点郁闷刚才查的时候不存在的然后A不相信自己的眼睛又去查一次T8时刻发现路人甲Java还是不存在的。
此时A心里想数据明明不存在啊为什么无法插入呢这不是懵逼了么A觉得如同发生了幻觉一样。
SERIALIZABLE串行
SERIALIZABLE会让并发的事务串行执行。
看效果
将隔离级别置为SERIALIZABLE
# 隔离级别设置,READ-UNCOMMITTED读未提交,READ-COMMITTED读已提交,REPEATABLE-READ可重复读,SERIALIZABLE串行
transaction-isolationSERIALIZABLE重启mysql
C:\Windows\system32net stop mysql
mysql 服务正在停止..
mysql 服务已成功停止。C:\Windows\system32net 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窗口BT1start transaction;T2select * from test1;T3start transaction;T4insert into test1 values (1);T5select * from test1;T6commit;T7commit;
按时间顺序运行上面的命令会发现T4-B这样会被阻塞直到T6-A执行完毕。
可以看出来事务只能串行执行了。串行情况下不存在脏读、不可重复读、幻读的问题了。
关于隔离级别的选择 需要对各种隔离级别产生的现象非常了解然后选择的时候才能游刃有余 隔离级别越高并发性也低比如最高级别SERIALIZABLE会让事物串行执行并发操作变成串行了会导致系统性能直接降低。 具体选择哪种需要结合具体的业务来选择。 读已提交READ-COMMITTED通常用的比较多。
总结 理解事务的4个特性原子性、一致性、隔离性、持久性 掌握事务操作常见命令的介绍 set autocommit可以设置是否开启自动提交事务 start transaction开启事务 start transaction read only开启只读事物 commit提交事务 rollback回滚事务 savepoint设置保存点 rollback to 保存点可以回滚到某个保存点 掌握4种隔离级别及了解其特点 了解脏读、不可重复读、幻读
