SQL 事务隔离级别

最新推荐文章于 2020-11-21 08:24:21 发布
最新推荐文章于 2020-11-21 08:24:21 发布
阅读量267 收藏
点赞数
分类专栏： sql mysql
本文为博主原创文章，未经博主允许不得转载，转载请附原文链接说明出处。
本文链接：https://blog.csdn.net/xupeng874395012/article/details/78562907
版权
mysql 同时被 2 个专栏收录
14 篇文章 0 订阅
订阅专栏
sql
8 篇文章 0 订阅
订阅专栏

mysql b innerdb 测试
查询全局和会话事务隔离级别：

SELECT @@global.tx_isolation; 
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

set @@tx_isolation= REPEATABLE-READ
set session tx_isolation='READ-COMMITTED';
set session tx_isolation=’READ-UNCOMMITTED’;

clientA

begin ;
select * from info where money>1500;


update  info set money=5000 where id =1 

commit ;

clientB

update  info set money=1000 where id =1

update  info set money=4000 where id =1

select * from info

默认级别TransactionRepeatableRead 的事务中测试
首先先查询下数据

select * from info 
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  1000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
begin;
mysql> select * from info where money>1500;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
2 rows in set (0.00 sec)

在CB中执行

update  info set money=4000 where id =1
mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

切换回CA

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  1000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

通过这个现象说明，在可重复读的隔离级别下，一个事务开启过程查询的数据没受到其他事务的修改影响。

继续操作CA

mysql> update  info set money=5000 where id =1 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  5000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

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

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  5000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

事务提交之后的值会覆盖掉CB的修改；

在继续看下一段的执行sql
CA

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

mysql> select * from info where money>1500;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> update  info set money=5000 where id =1  and money=1000;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  1000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

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

CB

mysql> update  info set money=4000 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

跟第一次的区别是事务的更新中添加了money=${查询的值}
CB的更新先与CA，这个时候update没有修改这条ID=1的数据。

第三种情况
CA

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

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  1000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> update  info set money=5000 where id =1  and money=1000;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  5000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

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

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

CB

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> update  info set money=4000 where id =1;
Query OK, 1 row affected (7.57 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  4000 |
|  2 | 马五   |  3000 |
|  3 | 李四   |  2000 |
+----+--------+-------+
3 rows in set (0.00 sec)

这里值得说明的是
CA的update先与CB执行，这个时候CB执行update时会发生等待。CAcommit之后CB立刻执行成功。
这个是因为中间发生了锁的争夺；

再开启一个客户端执行如下代码即可发现了。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table       | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
| 734524:163:3:2 | 734524      | X         | RECORD    | `project`.`info` | PRIMARY    |        163 |         3 |        2 | 1         |
| 734521:163:3:2 | 734521      | X         | RECORD    | `project`.`info` | PRIMARY    |        163 |         3 |        2 | 1         |
+----------------+-------------+-----------+-----------+------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

注
如果事务过程中获取到了排它锁的时候，其他事务就会发生锁等待的现象。

CA

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

mysql> select * from info where name like '张%';
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
+----+-----------+-------+
1 row in set (0.00 sec)

mysql> select * from info where name like '张%';
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
+----+-----------+-------+
1 row in set (0.00 sec)

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

mysql> select * from info where name like '张%';
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
|  2 | 张小三    |  3000 |
+----+-----------+-------+
2 rows in set (0.00 sec)

CB

update  info set name = '张小三' where id =2;

CB的更新发生在CA两次查询直接，但是对CA并没有产生任何的影响

CA

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

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
+----+-----------+-------+
4 rows in set (0.00 sec)

mysql> insert into info(id,name,money) values(5,'test',10000);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
|  5 | test      | 10000 |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql>

在CA事务中CB插入一条记录然后CA插入CB那条记录的指定ID出现异常

当前事务MVCC版本发生变化的时候出现幻读

mysql> select * from info;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  5000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
| 11 | name      | 10000 |
| 12 | name      | 10000 |
| 13 | name      | 10000 |
| 14 | 333       |   555 |
+----+-----------+-------+
7 rows in set (0.00 sec)

mysql> select * from info for update;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  5000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
| 11 | name      | 10000 |
| 12 | name      | 10000 |
| 13 | name      | 10000 |
| 14 | 333       |   555 |
| 15 | name      | 10000 |
+----+-----------+-------+
8 rows in set (0.00 sec)

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

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

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  5000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
| 11 | name      | 10000 |
| 12 | name      | 10000 |
| 13 | name      | 10000 |
| 14 | 333       |   555 |
| 15 | name      | 10000 |
+----+-----------+-------+
8 rows in set (0.00 sec)

mysql> select * from info LOCK IN SHARE MODE;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  5000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
| 11 | name      | 10000 |
| 12 | name      | 10000 |
| 13 | name      | 10000 |
| 14 | 333       |   555 |
| 15 | name      | 10000 |
| 16 | name      | 10000 |
+----+-----------+-------+
9 rows in set (0.00 sec)

看下别的隔离级别

READ-COMMITTED

mysql> set session tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  4000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
|  5 | test      | 10000 |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> select * from info ;
+----+-----------+---------+
| id | name      | money   |
+----+-----------+---------+
|  1 | 张小二    | 1000000 |
|  2 | 张小三    |    3000 |
|  3 | 李四      |    2000 |
|  4 | test      |   10000 |
|  5 | test      |   10000 |
+----+-----------+---------+
5 rows in set (0.00 sec)

mysql>

其他的数据提交以后就直接可以读取到了
但是事务没有提交的数据是查询不到的。这个隔离级别每次获取的数据都是最新的值。

set session tx_isolation=’READ-UNCOMMITTED’;
CA

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

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |  5000 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
|  5 | test      | 10000 |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> select * from info ;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |     1 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
|  5 | test      | 10000 |
+----+-----------+-------+
5 rows in set (0.00 sec)

CB

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

mysql> update info set money=1 where id=1;
Query OK, 1 row affected (12.61 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | 张小二    |     1 |
|  2 | 张小三    |  3000 |
|  3 | 李四      |  2000 |
|  4 | test      | 10000 |
|  5 | test      | 10000 |
+----+-----------+-------+
5 rows in set (0.00 sec)

CB事务还没有提交但是数据已经可以在CA事务中查询到了

xupeng874395012
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Server 事务隔离级别
Hello World
03-14 1107
1、SQL Server 的 5个标准事务隔离级别分别是：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ 和           SERIALIZABLE，SNAPSHOT . SQL Server 使用锁来实现隔离级别。 下面使用 GIF 动画对 SQL Server 前4 个隔离级别进行描述： 2、
SQL Server中锁与事务隔离级别
weixin_34295316的博客
07-15 142
SQL Server中的锁分为两类： 共享锁 排它锁 锁的兼容性：事务间锁的相互影响称为锁的兼容性。 锁模式 是否可以持有排它锁 是否可以持有共享锁 已持有排它锁 否 否 已持有共享锁 否 是 SQL Server中可以锁定的资源包括：RID或键（行）、页、对象（如表）、数据库等等。 在试图修改数据（增删改）时，事务会请求数据资源...
SQL 事务隔离级别
power_to_go
09-16 302
Mysql 默认开启自动提交和事务隔离级别 repeatable-read sql 语句的执行 sql 语句的执行是放在 Transaction 中的。sql 语句默认开启事务。也可以用 start transaction；手动开启事务。 执行 sql 语句。 commit；或者 rollback； 事务隔离级别 read uncommitted read committed read r...
Sql Server中的事务与事务隔离级别
data eudemon
10-07 1397
事务是数据库进行并发控制非常重要的机制。 1、什么是事务？ 　　事务是作为单个逻辑工作单元执行的一系列操作，它由一条或者一组语句组成，它们么全部成功，要么全部失败。 　　举个例子，比如在12306订火车票，要么你订票成功，余票显示就减少一张；要么你订票失败，余票显示还是那么多。不允许出现你订票成功了，余票却没有减少的情况。那么这种购票和余票减少的两个不同的操作必须放在一起，成为一个完成的逻辑...
事务隔离级别
hdhai9451的专栏
09-04 1911
SQL Server通过在锁资源上使用不同类型的锁来隔离事务。为了开发安全的事务，定义事务内容以及应在何种情况下回滚至关重要，定义如何以及在多长时间内在事务中保持锁定也同等重要。这由隔离级别决定。应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们为每一个单独事务定义与其他事务的隔离程度。事务隔离级别的定义如下： 是否在读数据的时候使用锁 读锁持续多长时间 在读数据的时候使用何
SQL Server事务的隔离级别
12-14
这篇数据库教程SQLServer 事务的隔离级别，教程操作方法: 　数据库是要被广大客户所共享访问的，那么在数据库操作过程中很可能出现以下几种不确定情况。 　更新丢失（Lost update） 　两个事务都同时更新一行数据...
MySQL数据库事务隔离级别详解
12-16
MySQL数据库的事务隔离级别是数据库管理系统中用于控制事务处理中并发操作的重要机制。在多用户同时访问数据库的情况下，事务隔离级别能够确保数据的一致性和完整性，避免并发操作带来的各种问题，如脏读、不可重复...
MySQL事务隔离级别详解
10-17
查询当前或全局的事务隔离级别的方法也是通过SQL命令实现： - 查询当前会话的事务隔离级别：`SELECT @@tx_isolation;` 或者 `SHOW VARIABLES LIKE 'tx_isolation';` - 查询全局事务隔离级别：`SELECT @@global.tx_...
Mysql事务隔离级别原理实例解析
12-14
MySQL中的事务隔离级别是数据库管理系统确保事务之间正确交互的关键特性，尤其在并发环境中。事务的隔离级别主要包括四种：读未提交（READ UNCOMMITTED）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）...
简述MySql四种事务隔离级别
12-14
在SQL标准中定义了四种隔离级别, 每一个事务中所做的修改,哪些在事务内和事务间是可见的,哪些是不可见的。较低级别的隔离通常可以执行更高的并发,系统的开销也更低。 下面简单地介绍一下四种隔离级别。 1.READ ...
SQLServer事务的隔离级别
小白将
09-12 2852
传统隔离级别隔离级别确定了并发用户读取或者写入的行为。读取者可以是任何选择数据的语句，默认情况下使用共享锁。写入者是任何对表进行修改的语句，并且需要一个排它锁。 SQL Server支持4个基于悲观并发控制（锁定）的传统隔离级别：READ UNCOMMITTED，READ COMMITTED， REPEATABLE READ与SERIALIZABLE。对于这4个隔离级别，隔离级别越高，读取者请求的
SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因...
weixin_34319640的博客
09-25 189
原本打算写有关 SSIS Package 中的事务控制过程的，但是发现很多基本的概念还是需要有 SQL Server 事务和事务的隔离级别做基础铺垫。所以花了点时间，把 SQL Server 数据库中的事务概念，ACID 原则，事务中常见的问题，问题造成的原因和事务隔离级别等这些方面的知识好好的整理了一下。 其实有关 SQL Server 中的事务，说实话因为内容太多, 话题太广，稍微力度控制不...
sql中的四种事务隔离级别
222222的博客
07-08 2508
事务隔离级别 问题 同一个应用程序中的多个事务或不同应用程序中的多个事务在同一个数据集上并发执行时, 可能会出现许多意外的问题，这些问题可分为如下三种类型： 脏读(Dirty Read): 已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据，之后，B回滚事务，A读取的数据就是脏数据。 场景：公司发工资了，领导把5000元打到Tom的账号上，但是该事务并未提交，而Tom正好去查看账户，发现工资已经到账，账户多了5000元，非常高兴，可是不幸的是，领导发现发给Tom的工资金额不对，是2000元，
500并发相当于多少人_一文掌握Oracle、MySQL、DB2并发控制机制的异同
weixin_39615257的博客
11-21 272
作者介绍李为，现任金融行业核心业务系统DBA，主要涉及DB2、Oracle、MySQL等数据库开发工作。数据库的并发控制机制，顾名思义，是用来控制数据库的并发操作的机制。控制的目的是为了保证数据完整和数据一致性。何为数据一致性？在数据库的并发操作中，多个事务同时读取同一份数据，要保证多个事务读取的同一份数据是准确的。可能数据的不一致更好理解。例如事务T1、T2同时更新余额为1000余额表...
30分钟全面解析-SQL事务+隔离级别+阻塞+死锁
weixin_34347651的博客
07-05 199
以前总是追求新东西，发现基础才是最重要的，今年主要的目标是精通SQL查询和SQL性能优化。 本系列主要是针对T-SQL的总结。 【T-SQL基础】01.单表查询-几道sql查询题 【T-SQL基础】02.联接查询 【T-SQL基础】03.子查询 【T-SQL基础】04.表表达式-上篇 【T-SQL基础】04.表表达式-下篇 【T-SQL基础】05.集合运算 【T-SQL基础...
mysql索引命中规则
热门推荐
xupeng874395012的博客
11-29 1万+
最左匹配原则 1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。 2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。分析讲解 1、mysql的索引分为聚簇索引和非聚簇索引，mysql的表是聚集索引组织表。 聚集规则是：有主键则定义主键索引为聚集索引；没有主键则选第一个不允许为NULL的唯一索引；还没有就使用in
mysql insert 空数据默认值问题
xupeng874395012的博客
03-30 1万+
CREATE TABLE task ( id bigint unsigned NOT NULL AUTO_INCREMENT, taskType INT(10) unsigned DEFAULT '0' NOT NULL, retryCount INT DEFAULT '3' NOT NULL, status INT(1
mysql show status 常用命令
xupeng874395012的博客
07-06 4173
查询返回的行数 show status like '%innodb_rows_read%'插入成功的行数 show status like '%innodb_rows_inserted%'更新成功的行数 show status like '%innodb_rows_updated%'删除成功的行数 show status like '%innodb_rows_deleted%'查看锁 show st
mysql可能造成索引失效的场景
xupeng874395012的博客
05-23 3022
1、当使用or的情况下，如果不是每一列的条件都有索引，索引失效 2、当使用多列索引的时候，没有匹配到第一部分，索引失效 3、当使用like的时候，以%开头，索引失效 4、当数据类型是字符串类型的时候，如果条件数据没有被引号引起来，索引失效 5、如果使用全表扫描比索引更快，索引失效
SQL事务隔离级别详解：Oracle并发与锁机制
本文主要探讨了SQL-92标准定义的事务隔离级别以及在Oracle数据库中的并发和锁机制。首先，事务（Transaction）是数据库管理中一个基本概念，它是一组操作的集合，遵循ACID原则，确保数据的一致性和完整性。ACID特性...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值