mysql试验分析事务隔离级别

2024-05-15 18:48

本文主要是介绍mysql试验分析事务隔离级别,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

参考:http://www.zsythink.net/archives/1233
记住自己的事物的隔离级别是什么就可能引发什么问题,和别人的隔离级别没关系

一、概念引入:

1.1 为什么会有事务,什么是事务?

解释:

以银行转账为例:A要转账给B有两步:

  1. 从A账户减去转账数目,
  2. 向B账户增加转账数目
    那么为了防止程序执行中只执行了一步,所以要在数据库层次保证这两步要么一起执行,要么都不执行,即把它们看成一个整体也就是一个事务。

1.2 什么是事务的隔离级别,为什么会有隔离级别?

解释:

其实这是为了解决并发问题产生的。如果每次都只有一个人操作数据库,就不会有什么隔离级别了,但这是不可能的。
那么在并发的情况下如何保证互不影响的,这就是隔离级别了。
mysql中的四个隔离级别从低到高以此为:读未提交<读已提交<可重读<串行化
1.读未提交:并发度很高,但是数据隔离的非常不好,会出现脏读数据(个人认为不应该有这种隔离级别,oracle中没这种,但是mysql和sqlserver中都有)。
2.读已提交:并发度还行,数据隔离的也还行,但就是我开启事务后别人对数据的操作还会影响到我(个人认为应当如此,sqlserver的默认级别也是这个)。
3.可重读:并发度还行,数据隔离的还行,但就是会出现幻觉(个人感觉这个幻觉要不得,但是这个是mysql的默认隔离级别)。
4.串行化:并发度很差,数据隔离的非常好(实际中不会用,并发度太低了,事务中一条查询语句都可以将这个表锁死)。

1.3 事务特性是什么

解释:

四大特性:
1.原子性 即要把一个事务中的sql语句看成是一个整体,要么一起执行要么都不执行
2.一致性 类似原子性,也是为了说明事务是一个整体
3.隔离性 就是上面说的事务隔离级别
4.持久性 其实这和一般的sql语句是一样的
可以看到最终要的特性就是原子性

二、mysql事务操作命令

准备的数据库:test
数据库引擎:innodb
表:t

create table t(id int primary key,name varchar(50)
);
insert into t values(1,45);

2.1 显示全局事务是否默认提交

mysql> show global variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.2 显示当前会话事务是否默认提交

mysql> show session variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.3 显示当前的事务隔离级别

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

2.4 关闭当前会话的默认事务提交

mysql> set @@session.autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> show session variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

2.5 查看准备的表数据

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

2.6 开启一个事务

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

2.7 插入数据

mysql> insert into t values(2,53);
Query OK, 1 row affected (0.01 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  2 | 53   |
+----+------+
2 rows in set (0.00 sec)

2.8 回滚

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
+----+------+
1 row in set (0.00 sec)

2.9 插入数据

mysql> insert into t values(3,33);
Query OK, 1 row affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
+----+------+
2 rows in set (0.00 sec)

2.10 创建事务节点a

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

2.11 插入数据

mysql> insert into t values(4,44);
Query OK, 1 row affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

2.12 创建事务节点b

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

2.13 插入数据

mysql> insert t values(5,55);
Query OK, 1 row affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
|  4 | 44   |
|  5 | 55   |
+----+------+
4 rows in set (0.00 sec)

2.14 回滚到事务节点a

mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 45   |
|  3 | 33   |
+----+------+
2 rows in set (0.00 sec)

2.15 创建事务节点a

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

2.16 释放事务节点

mysql> release savepoint a;
Query OK, 0 rows affected (0.00 sec)

三、mysql事物隔离操作

数据库:test
表:t
表结构和数据:

create table t(id int primary key,name varchar(50)
);
insert into t values(1,11);
insert into t values(2,22);
insert into t values(3,33);

3.1 打开两个mysql客户端(A和B) 切换到test数据库的操作环境

3.2 查看默认的事务隔离级别(应为:REPEATABLE-READ[可重复读])

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

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

3.3 试验隔离级别:READ-UNCOMMITTED[读未提交] (出现脏读,脏读数据都有了就不要再提不可重复读和幻读了)

3.3.1 将A端设置为读未提交(READ-UNCOMMITTED)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

3.3.2 A端先查看下数据

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.3.3 B端开启事务并增删改数据但不提交

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(4,44);
Query OK, 1 row affected (0.01 sec)mysql> delete from t where id=1;
Query OK, 1 row affected (0.01 sec)mysql> update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.3.4 A端再次执行查询(出现脏读数据)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

这里可以看到,由于A端的事务隔离级别较低,所以访问到了别人还没有提交的数据,这种现象称之为脏读。在oracle中直接认为这是一个错误,不提供这种隔离级别,个人认为应当如此。
另外:如果此时在A端执行更新语句update t set name='22-2-2' where id=2;会发现A端将处于等待状态,直到报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,这是因为这一行数据已经被B端锁定了(B端还未提交事务)。

3.3.5 将B端事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.3.6 A端再执行查询(验证数据是否恢复到最初状态)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

可以看到数据又恢复到了最初的模样

3.4 试验隔离级别:READ-COMMITTED[读提交] 不可重复读现象

3.4.1 A端将事务隔离级别设置为 READ-COMMITTED

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

3.4.1 A端开启事务并查看当前的数据

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.4.2 B端增删改数据但不提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(4,'44');
Query OK, 1 row affected (0.01 sec)mysql> delete from t where id=1;
Query OK, 1 row affected (0.01 sec)mysql> update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.4.3 A端查看数据(验证不会出现脏读数据)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

由此可见A端并未查看到B端未提交的数据,即没有脏数据。
另外:如果此时执行update t set name='22-2-2' where id=2;会发现A端一直出现等待直到报错,同上,行数据被B端锁定了。

3.4.4 B端将事务提交

mysql> commit;
Query OK, 0 rows affected (0.04 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.4.5 A端查看数据(验证出现不可重复度、幻读)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

可以看到在A端的一个事务中的先后查到了不同的数据,包含增删改的数据,其中改的数据称之为不可重复读数据,增删的数据称之为幻读数据。

3.4.6 将表t删掉 重建并插入数据,为后面做准备

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> drop table t;
Query OK, 0 rows affected (0.19 sec)mysql> create table t(->     id int primary key,->     name varchar(50)-> );
Query OK, 0 rows affected (0.28 sec)mysql> insert into t values(1,11),(2,22),(3,33);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.5 试验隔离级别:REPEATABLE-READ[可重读] (会出现幻读现象)

3.5.1 将A端隔离级别设置为REPETABLE-READ开启事务并查看当下数据

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.5.2 B端开启事务并进行增删改并提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(4,'44');
Query OK, 1 row affected (0.01 sec)mysql> delete from t where id=1;
Query OK, 1 row affected (0.00 sec)mysql>  update t set name='22-2' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> commit;
Query OK, 0 rows affected (0.03 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 22-2 |
|  3 | 33   |
|  4 | 44   |
+----+------+
3 rows in set (0.00 sec)

3.5.3 A端查看数据

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

到这里为止,数据的隔离看着还是正常的,本事务内的数据果然是可重复读的。但是接着往下看。。。

3.5.4 A端修改数据后再进行查看,提交后再进行查看

mysql> update t set name='88';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 88   |
|  3 | 88   |
|  4 | 88   |
+----+------+
4 rows in set (0.00 sec)

幻觉发生了:这个update针对的所有的数据,为什么第一行还显示的这么怪异,其实这就是幻读。接着提交这个事务:

mysql> commit;
Query OK, 0 rows affected (0.04 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  2 | 88   |
|  3 | 88   |
|  4 | 88   |
+----+------+
3 rows in set (0.00 sec)

此时再看数据,发现是两个事务更改后的最后结果,但是上一步确实有幻觉,所以叫做幻读现象。

3.5.5 清理数据,恢复到最初状态

mysql> delete from t;
Query OK, 3 rows affected (0.05 sec)mysql> insert into t values(1,11),(2,22),(3,33);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.6 试验隔离级别:SERIALIZABLE[串行化] (沒有脏读、没有不可重复读、没有幻读,但是数据库并发性能极低)

3.6.1 将A端事务隔离级别设为SERIALIZABLE,开启事务并查看当前数据

mysql> set session transaction isolation level SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)

3.6.2 B端打开事务并进行数据操作

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 11   |
|  2 | 22   |
|  3 | 33   |
+----+------+
3 rows in set (0.00 sec)mysql> insert into t values(4,'44');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到,数据能查就查看的到,但是增删改的操作都不能允许,这是因为A端的事务中用到了表t(虽然只是查询了一下)。
这样的数据更安全,但是由于操作串行,数据库的并发也很低。
试验到此结束,A、B端分别回滚事务即可。

四、总结

图1:
在这里插入图片描述

五、附

另外mysql中不支持嵌套的事务,因为执行start transaction的时候是默认提交上次的事务的,但是通过事务的保存点和回滚到指定的保存点也可以提供相同的功能。参考:https://my.oschina.net/yurun/blog/2247901
sqlserver中是直接支持嵌套事务的,参考:https://blog.csdn.net/qq_40205468/article/details/87785634

这篇关于mysql试验分析事务隔离级别的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/992624

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

[MySQL表的增删改查-进阶]

🌈个人主页:努力学编程’ ⛅个人推荐: c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构,刷题刻不容缓:点击一起刷题 🌙心灵鸡汤:总有人要赢,为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:

SWAP作物生长模型安装教程、数据制备、敏感性分析、气候变化影响、R模型敏感性分析与贝叶斯优化、Fortran源代码分析、气候数据降尺度与变化影响分析

查看原文>>>全流程SWAP农业模型数据制备、敏感性分析及气候变化影响实践技术应用 SWAP模型是由荷兰瓦赫宁根大学开发的先进农作物模型,它综合考虑了土壤-水分-大气以及植被间的相互作用;是一种描述作物生长过程的一种机理性作物生长模型。它不但运用Richard方程,使其能够精确的模拟土壤中水分的运动,而且耦合了WOFOST作物模型使作物的生长描述更为科学。 本文让更多的科研人员和农业工作者

MOLE 2.5 分析分子通道和孔隙

软件介绍 生物大分子通道和孔隙在生物学中发挥着重要作用,例如在分子识别和酶底物特异性方面。 我们介绍了一种名为 MOLE 2.5 的高级软件工具,该工具旨在分析分子通道和孔隙。 与其他可用软件工具的基准测试表明,MOLE 2.5 相比更快、更强大、功能更丰富。作为一项新功能,MOLE 2.5 可以估算已识别通道的物理化学性质。 软件下载 https://pan.quark.cn/s/57

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就