Oracle、MySQL、PostGreSQL中的多版本读取一致性

multi-version read consistency in Oracle、MySQL、PostGreSQL

在多人同时访问与修改数据时， 最大的难题之一是：一方面要力争最大的并发访问，与此同时还要确保每个用户能以一致的方式读取和修改数据。

ANSI/ISO SQL 标准定义了4 种事务隔离级别，对于相同的事务，采用不同的隔离级别分别有不同的结果。

这些隔离级别是根据3 个“现象”定义的，如dirty read、nonrepeatable read、phantom read。Oracle 明确地支持READ COMMITTED（读已提交）和SERIALIZABLE（可串行化）隔离级别，在Oracle 中READ COMMITTED 则有得到读一致查询所需的所有属性，在其他数据库中的读READ COMMITTED 可能会有不同的答案， 最近有个客户在测试migrate oracle to postgreSQL测试发现一个批处理的结果并非一致，于是做一个小小的测试验证一下。

Oracle

## session 1
SQL> CREATE TABLE test (id INT PRIMARY KEY);
Table created.

SQL> INSERT INTO test VALUES (1);
1 row created.

SQL> INSERT INTO test VALUES (2);
1 row created.

SQL> alter table test add ctime date;
Table altered.

SQL> select * from test;
        ID CTIME
---------- -------------------
         1
         2

SQL> update test set ctime=sysdate;
2 rows updated.


SQL> set time on
12:22:03 SQL>
12:22:03 SQL>
12:22:03 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
end;
/
PL/SQL procedure successfully completed.

12:22:06 SQL> select * from test;
        ID CTIME
---------- -------------------
         2 2022-08-19 12:21:31
         1 2022-08-19 12:22:06

## session 2
SQL> set time on
12:21:47 SQL>
12:22:10 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
12:22:11   4  end;
12:22:12   5  /

--   hang
## session 1 
12:22:24 SQL> commit;
Commit complete.

## session 2
12:22:10 SQL>
begin
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,sysdate);
12:22:11   4  end;
12:22:12   5  /
PL/SQL procedure successfully completed.

12:23:00 SQL> select * from test;
             ID CTIME
--------------- -----------------
              1 20220819 12:22:36
              2 20220819 12:21:31

SELECT s.sid, s.serial#,decode(bitand(flag,power(2,28)),0,'READ COMMITTED',1,'SERIALIZABLE')  isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
       SID    SERIAL# ISOLATION_LEVE
---------- ---------- --------------
         1      34211 READ COMMITTED

Note:
默认系统事务隔离级别是READ COMMITTED,也就是读已提交, Oracle 可以在begin end 匿名块中做到以事务开始时间的一致性，session 1更新 delete insert 作为一个独立的事务，session 2在session 1后发起事务（begin），oracle虽然是在读已提交事务隔离级别，但是一样可以解决了不可重复读与幻读在读一致性上的实现， session 2可以在begin 事务种读到一致性数据也可以读到session 1 insert的新数据，并delete后重新insert了新数据。

POSTGRESQL

## SESSION1
sdbo=# CREATE TABLE test (id INT PRIMARY KEY);
CREATE TABLE
sdbo=# INSERT INTO test VALUES (1);
INSERT 0 1
sdbo=# INSERT INTO test VALUES (2);
INSERT 0 1

# session 1

sdbo=# alter table test add ctime time;
ALTER TABLE
sdbo=# update test set ctime=now();
UPDATE 2

sdbo=# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:47:37.415516
(2 行记录)

sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 1
sdbo=*# INSERT INTO test VALUES (1,now());
INSERT 0 1
sdbo=*# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:49:49.151784
(2 行记录)


# session 2

begin;
DELETE FROM test WHERE id=1;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,now());

sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
-- hang

# session 1
sdbo=*# commit;
COMMIT
sdbo=#

# session 2
sdbo=# begin;
BEGIN
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 0
sdbo=*# DELETE FROM test WHERE id=1;
DELETE 1
sdbo=*# INSERT INTO test VALUES (1,now());
INSERT 0 1
sdbo=*# select * from test;
 id |      ctime
----+-----------------
  2 | 14:47:37.415516
  1 | 14:50:13.062228
(2 行记录)


sdbo=# show transaction_isolation
sdbo-# ;
 transaction_isolation
-----------------------
 read committed
(1 行记录)

Note:
注意在postgresql中和oracle是不同的行为， 在同样读已提交事务隔离级别下， session 2的第一条delete在session 1 提交后，显示delete 0条记录，第二条delete在同一个begin 事务中显示delete 1条记录。同一个事务中相同SQL显示了不同的结果， 可见在一些应用中如果把多个SQL放在一个事务begin中，如果多用户并发，最终会导致和oracle不一样的结果。

Transaction Isolation Levels

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

观察快照

# session 1
[local]:5432 postgres@anbob=# update test set ctime=now();
UPDATE 2
[local]:5432 postgres@anbob=# select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |    0 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=# begin;
BEGIN
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |    0 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
(1 row)

[local]:5432 postgres@anbob=#* INSERT INTO test VALUES (1,now());
INSERT 0 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |    0 |  1 | 21:06:03.277602
(2 rows)

# session 2
[local]:5432 postgres@anbob=#
[local]:5432 postgres@anbob=# begin;
BEGIN
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |     ctime
------+------+----+----------------
  623 |    0 |  2 | 21:05:43.59544
  623 |  624 |  1 | 21:05:43.59544
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
-- hang

# session 1
[local]:5432 postgres@anbob=#* commit;
COMMIT

# session 2
[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 0
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)

[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |    0 |  1 | 21:06:03.277602
(2 rows)

[local]:5432 postgres@anbob=#* DELETE FROM test WHERE id=1;
DELETE 1
[local]:5432 postgres@anbob=#* select txid_current();
 txid_current
--------------
          625
(1 row)

[local]:5432 postgres@anbob=#* INSERT INTO test VALUES (1,now());
INSERT 0 1
[local]:5432 postgres@anbob=#* select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  625 |    0 |  1 | 21:06:36.750481
(2 rows)

# session 1
[local]:5432 postgres@anbob=# select xmin,xmax,t.* from test t;
 xmin | xmax | id |      ctime
------+------+----+-----------------
  623 |    0 |  2 | 21:05:43.59544
  624 |  625 |  1 | 21:06:03.277602
(2 rows)

[local]:5432 postgres@anbob=#

Note:
在postgresql中即使在begin同一个事务中，不同的SQL执行时，每个SQL会取一次当前数据的快照， 像上面session 2的事务块第1个delete执行前快照记录忆被session delete ,insert 虽已提交，但是postgresql中无法幻读，也就是无法像oracle一样根据事务开始时间保证一致性， 就读取不到insert的新数据。 第二个delete sql执行前的快照读取到了session 1 inserted的数据。

MySQL

mysql> select @@transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| SERIALIZABLE |
±------------------------+
设置隔离级别
方式1：通过set命令
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
其中level有4种值：
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}

#session 1
MYSQL_root@localhost [anbob]> select * from test;
±—±---------+
| id | ctime |
±—±---------+
| 1 | 15:31:15 |
| 2 | 15:31:15 |
±—±---------+
2 rows in set (0.00 sec)

MYSQL_root@localhost [anbob]>
MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
Query OK, 1 row affected (0.00 sec)

MYSQL_root@localhost [anbob]> INSERT INTO test VALUES (1,now());
Query OK, 1 row affected (0.00 sec)

#session 2
begin;
DELETE FROM test WHERE id=1;
INSERT INTO test VALUES (1,now());

MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
– hang

#session 1
MYSQL_root@localhost [anbob]> commit;
Query OK, 0 rows affected (0.01 sec)

MYSQL_root@localhost [anbob]> select * from test;
±—±---------+
| id | ctime |
±—±---------+
| 1 | 15:31:40 |
| 2 | 15:31:15 |
±—±---------+
2 rows in set (0.00 sec)

#session 2
Database changed
MYSQL_root@localhost [anbob]> begin;
Query OK, 0 rows affected (0.00 sec)

MYSQL_root@localhost [anbob]> DELETE FROM test WHERE id=1;
Query OK, 1 row affected (7.87 sec)

MYSQL_root@localhost [anbob]> INSERT INTO test VALUES (1,now());
Query OK, 1 row affected (0.00 sec)

MYSQL_root@localhost [anbob]> select * from test;
±—±---------+
| id | ctime |
±—±---------+
| 1 | 15:31:56 |
| 2 | 15:31:15 |
±—±---------+
2 rows in set (0.00 sec)

MYSQL_root@localhost [anbob]> commit;
Query OK, 0 rows affected (0.01 sec)

MYSQL_root@localhost [anbob]> select * from test;
±—±---------+
| id | ctime |
±—±---------+
| 1 | 15:31:56 |
| 2 | 15:31:15 |
±—±---------+
2 rows in set (0.00 sec)

#session 1
MYSQL_root@localhost [anbob]> select * from test;
±—±---------+
| id | ctime |
±—±---------+
| 1 | 15:31:56 |
| 2 | 15:31:15 |
±—±---------+
2 rows in set (0.00 sec)

MYSQL_root@localhost [anbob]> SELECT @@transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| REPEATABLE-READ |
±------------------------+
1 row in set (0.01 sec)

NOTE:
默认mysql是可重复读隔离级别，可见行为和oracle基本一致， 但是该模式下有间隙锁问题，建议修改为读已提交；

MYSQL_root@localhost [anbob]> set TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

修改隔离级别后 该问题现象一致；

Summary

在某些事务应用中begin delete xxx; insert ; end类似多条DML的事务中，oracle和mysql基本一致的形为，可以以begin 事务时间保持一致性， 而在postgresql中虽是begin 事务，但是不同的SQL是在执行时取数据的快照，产生了不致的数据， 可能这是undo 与 postgresql中的mvcc的实现方式不同的原因吧。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/760866.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

“吃饭大学”！中国大学食堂排行TOP10（含西电）

“吃饭大学”！中国大学食堂排行TOP10（含西电）

同学们们，考研择校考虑的因素除了学术，地理位置等方面，你们还会考虑哪些因素呢？小研作为一个吃货，必定会考虑的一个因素当然是大学的食堂美食啊~ 那中国超级好吃的大学食堂在哪？一起来看看有没有你的目标院…
阅读更多...
input调用手机摄像头实现拍照功能vue

input调用手机摄像头实现拍照功能vue

项目需要一个拍照功能&#xff0c;实现功能如下图所示:若使用浏览器则可以直接上传图片&#xff0c;若使用手机则调用手机摄像头拍照。 1.代码结构 <!--input标签--> <input ref"photoRef"type"file"accept"image/*"capture"envir…
阅读更多...
4-数据提取方法2（xpath和lxml）（6节课学会爬虫）

4-数据提取方法2（xpath和lxml）（6节课学会爬虫）

4-数据提取方法2&#xff08;xpath和lxml&#xff09;&#xff08;6节课学会爬虫&#xff09; 1&#xff0c;Xpath语法&#xff1a;&#xff08;1&#xff09;选择节点&#xff08;标签&#xff09;&#xff08;2&#xff09;“//”:能从任意节点开始选择&#xff08;3&#xf…
阅读更多...
业务链SFC简介

业务链SFC简介

目录 业务链&#xff08;SFC&#xff09;简介什么是业务链&#xff1f;业务链的体系架构业务链的基本工作流程PBRPBR实现的SFC工作流程 NSHNSH报文NSH实现的SFC工作流程 区别 业务链的应用 配置指南 业务链&#xff08;SFC&#xff09;简介 业务链是网络功能虚拟化&#xff08…
阅读更多...
华为DCN网络之：VXLAN

华为DCN网络之：VXLAN

VXLAN RFC定义了VLAN扩展方案VXLAN&#xff08;Virtual eXtensible Local Area Network&#xff0c;虚拟扩展局域网&#xff09;。VXLAN采用MAC in UDP封装方式&#xff0c;是NVO3&#xff08;Network Virtualization over Layer 3&#xff09;中的一种网络虚拟化技术。 VXLAN…
阅读更多...
【前端】从零开始学习编写HTML

【前端】从零开始学习编写HTML

目录 一、什么是前端 二、什么是HTML 三、HTML文件的基本结构 四、HTML常见标签 4.1 注释标签 4.2 标题标签 4.3 段落标签 4.4 换行标签 4.5 格式化标签 4.6 图片标签 4.7 超链接标签 4.8 表格标签 4.9 列表标签 4.10 表单标签 &#xff08;1&#xff09;form标…
阅读更多...
介绍两个压测工具pgbench\sysbench，可视化监控工具NMON

介绍两个压测工具pgbench\sysbench，可视化监控工具NMON

性能评估做不好&#xff0c;开会又领导点名叼了。/(ㄒoㄒ)/~~ /(ㄒoㄒ)/~~ /(ㄒoㄒ)/~~ 挨叼了&#xff0c;也要写文章&#xff0c;记录下我的笔记。 写篇文章 对数据库、OS性能的性能评估&#xff0c;需要选择合适的压测工具&#xff0c;给找出数据库的运行瓶颈 pgbench 这是…
阅读更多...
Redis 集群模式

Redis 集群模式

一、集群模式概述 Redis 中哨兵模式虽然提高了系统的可用性&#xff0c;但是真正存储数据的还是主节点和从节点&#xff0c;并且每个节点都存储了全量的数据&#xff0c;此时&#xff0c;如果数据量过大&#xff0c;接近或超出了 主节点 / 从节点机器的物理内存&#xff0c;就…
阅读更多...
【每日刷题】Day78

【每日刷题】Day78

【每日刷题】Day78 &#x1f955;个人主页&#xff1a;开敲&#x1f349; &#x1f525;所属专栏&#xff1a;每日刷题&#x1f34d; &#x1f33c;文章目录&#x1f33c; 1. 1608. 特殊数组的特征值 - 力扣&#xff08;LeetCode&#xff09; 2. 1385. 两个数组间的距离值 - …
阅读更多...
实现胶囊神经网络，识别手写MNIST数据集，谈谈实现及理解。

实现胶囊神经网络，识别手写MNIST数据集，谈谈实现及理解。

&#x1f3c6;本文收录于《CSDN问答解答》专栏&#xff0c;主要记录项目实战过程中的Bug之前因后果及提供真实有效的解决方案&#xff0c;希望能够助你一臂之力&#xff0c;帮你早日登顶实现财富自由&#x1f680;&#xff1b;同时&#xff0c;欢迎大家关注&&收藏&…
阅读更多...
在安装HDFS过程中遇见Permission denied

在安装HDFS过程中遇见Permission denied

HDFS Shell命令权限不足问题解决 问题 想必有同学在实战Shell的时候&#xff0c;遇到了&#xff1a; Permission denied: userroot, accessWRITE, inode"/":hadoop:supergroup:drwxr-xr-x 这种类似的问题。 问题的原因就是没有权限&#xff0c;那么为什么呢&#…
阅读更多...
C# OpenCvSharp 实现Reinhard颜色迁移算法

C# OpenCvSharp 实现Reinhard颜色迁移算法

C# OpenCvSharp 实现Reinhard颜色迁移算法 目录 效果 项目 代码 下载 效果 项目 Reinhard颜色迁移算法的步骤&#xff1a; 1、将参考图片和目标图片转换到LAB空间下 2、得到参考图片和目标图片的均值和标准差 3、对目标图片的每一个像素值&#xff0c;减去目标图像均值然后…
阅读更多...
【数据结构（邓俊辉）学习笔记】二叉搜索树04——AVL树

【数据结构（邓俊辉）学习笔记】二叉搜索树04——AVL树

文章目录 1.重平衡1.1 AVL BBST1.2 平衡因子1.3 适度平衡1.4 接口1.5 失衡 复衡 2. 插入2.1 单旋2.2 双旋2.3 实现 3. 删除3.1 单旋3.2 双旋3.3 实现 4. &#xff08;3 4&#xff09;-重构4.1 "34"重构4.2 "34"实现4.3 rotateAt4.4 综合评价 1.重平衡 1…
阅读更多...
自动雪深传感器的类型

自动雪深传感器的类型

TH-XL2随着科技的飞速发展&#xff0c;气象监测技术也在不断进步。在降雪天气频发的冬季&#xff0c;雪深数据对于保障道路交通、农业生产和电力供应等具有至关重要的作用。自动雪深传感器作为气象监测的重要工具&#xff0c;其类型多样、功能各异&#xff0c;为气象数据的准确…
阅读更多...
让GNSSRTK不再难【第17讲 RTK定位技术原理-站间单差浮点解--第1部分】

让GNSSRTK不再难【第17讲 RTK定位技术原理-站间单差浮点解--第1部分】

第17讲 RTK定位技术原理-站间单差浮点解 RTK技术其实就是在RTD技术的基础上增加载波观测值的使用。由于伪距的误差在分米量级&#xff0c;即使我们通过站间单差消除掉绝大部分的伪距误差&#xff0c;但受限于伪距的精度&#xff0c;我们也只能达到分米量级的定位水平。 但载波…
阅读更多...
.NET 矩阵6月红队工具和资源集合

.NET 矩阵6月红队工具和资源集合

01外网入口打点 1.1 Sharp4WbemScripting 1.2 ASP4Eval 1.3 Sharp4Web.config 1.4 Sharp4AddScript 02安全防御绕过 2.1 Sharp4DefenderStop 03搭建代理隧道 3.1 Sharp4suo5 04混淆加密防护 4.1 Obfuscar混淆器 4.2 Sharp4BatchGuard 05安全技术文档 5.1 .NET 通过Junction Fol…
阅读更多...
A股风格因子看板（2024.06 第11期）

A股风格因子看板（2024.06 第11期）

阅读更多...
对原生textarea加上:当前输入字数/最大输入字数

对原生textarea加上:当前输入字数/最大输入字数

源码: <!DOCTYPE html> <html lang"en"> <head> <meta charset"UTF-8"> <meta name"viewport" content"widthdevice-width, initial-scale1.0"> <title>Textarea Character Counter with Dragga…
阅读更多...
OpenCV 张正友标定法（二）

OpenCV 张正友标定法（二）

文章目录 一、简介二、实现代码三、实现效果参考资料一、简介 在之前的博客OpenCV 张氏标定法中，我们没有考虑镜头畸变等因素，因此计算出的内参与外参均是理想情况下的数值，而如果我们考虑到镜头的畸变： 我们就需要考虑使用最小二乘法最小化像素坐标的重投影误差（上述所求…
阅读更多...
Python中的数据结构

Python中的数据结构

一.堆 堆的建立可以通过导入heapq库来实现 在Python中建立的是最小堆 即heap[k]<heap[2*k1]and heap[k]<heap[2*k2] 下面是一些 堆使用的方法 heapq.heappush([],加入的元素) heapq.heappop(heap)弹出最小的元素 heapq.nlargest(3,heap)返回最大的三个元素 hea…
阅读更多...
最新文章

Copyright @ 2022~2023