欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 新闻 > 社会 > PostgreSQL 事务读取行 不使用行锁 真的? 利弊双刃剑

PostgreSQL 事务读取行 不使用行锁 真的? 利弊双刃剑

2024/11/13 20:41:04 来源:https://blog.csdn.net/liuhuayang/article/details/143701372  浏览:    关键词:PostgreSQL 事务读取行 不使用行锁 真的? 利弊双刃剑

开头还是介绍一下群,如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题,有需求都可以加群群内有各大数据库行业大咖,可以解决你的问题。加群请联系 liuaustin3 ,(共2580人左右 1 + 2 + 3 + 4 +5 + 6 + 7+8) 新人进7群,开8群)

最近一个同学问我一个问题,这也是练习题上的问题,对于答案,他提出了一个异议,为什么最终的答案是PostgreSQL 读取数据不需要锁? 其实也不奇怪,其他部分常用的数据库在读取数据会产生行的读锁(行读的共享锁),为什么PostgreSQL不会产生读锁。

同时随着越来越多的人熟悉了 PostgreSQL 一部分人对PG 的MVCC的设计问题提出了一些观点,希望PG 和ORACLE ,MySQL 一样,能通过undo 集中式管理方法,来解决Vacuum的问题。我个人看法,任何事务都有利弊,PG这样设计对于数据的提取是有利的,降低了在读取数据时产生锁的开销,对于读取数据非常有利,通过事务号和表中每行数据的特殊标注,完成了读取数据中,判断那些行是否可读取,那些行时再本事务不可读取的,这是一种大智慧体现,(弊端不说了)。

咱们分两个部分说

1 原理 

2 代码 代码在下面给出查看源代码的链接位置


原理:PostgreSQL MVCC 原理

我们调整好工具 pageinspect,通过PostgreSQL提供的页面检索的工具,把这个事情说清楚,同时还有一些其他的工具会在下面说明。

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# select lp,lp_off,t_xmin,t_xmax,t_ctid,t_oid,t_data from heap_page_items(get_raw_page('actor', 0));lp  | lp_off | t_xmin | t_xmax | t_ctid  | t_oid |                                       t_data                                      -----+--------+--------+--------+---------+-------+-----------------------------------------------------------------------------------
-1 |   8136 |    824 |      0 | (0,1)   |       | \x010000001350656e656c6f7065114775696e6573730000002037f5e29e8001002 |   8080 |    824 |      0 | (0,2)   |       | \x020000000b4e69636b135761686c626572670000000000002037f5e29e8001003 |   8032 |    824 |      0 | (0,3)   |       | \x030000000745640d43686173650000002037f5e29e8001004 |   7976 |    824 |      0 | (0,4)   |       | \x04000000134a656e6e696665720d446176697300000000002037f5e29e8001005 |   7920 |    824 |      0 | (0,5)   |       | \x050000000f4a6f686e6e791b4c6f6c6c6f627269676964612037f5e29e8001006 |   7864 |    824 |      0 | (0,6)   |       | \x060000000d4265747465154e6963686f6c736f6e000000002037f5e29e8001007 |   7808 |    824 |      0 | (0,7)   |       | \x070000000d47726163650f4d6f7374656c000000000000002037f5e29e8001008 |   7752 |    824 |      0 | (0,8)   |       | \x08000000114d617474686577154a6f68616e73736f6e00002037f5e29e8001009 |   7704 |    824 |      0 | (0,9)   |       | \x09000000094a6f650d5377616e6b00002037f5e29e80010010 |   7648 |    824 |      0 | (0,10)  |       | \x0a0000001543687269737469616e0d4761626c65000000002037f5e29e800100

先说结论:

1 PostgreSQL 的表为当前数据和历史数据的存放地,一张表中需要能够存储整体数据操作中的所有历史数据和当前事务操作中的最新数据。

2 数据的读取根据的是事务的快照,基于PG的表中包含,他历史的行+现在事务正在操作的行=整体的表。其中通过事务快照的视图,将该事务在其生命周期内可以看到的所有数据库版本标明。事务快照的关键是包含了当前事务能看到所有已经提交得事务ID(Transaction ID)。

在做下面的实验和说明读取行,并不需要锁的实验中,我们要说明我们要使用的一些命令、函数。

1  heap_page_items 函数

2  get_raw_page 函数

3  txid_current 函数

4  txid_current 函数

5  txid_current_snapshot  函数

6  语句 (通过下面的两个语句来完成一些信息的获取)

SELECT pl.pid, pl.locktype, pl.relation::regclass, pl.page, pl.tuple, pl.virtualxid, pl.transactionid, pl.mode, pl.granted, pl.fastpath
FROM pg_locks pl
LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid
ORDER BY pl.pid;select lp,lp_off,t_xmin,t_xmax,t_ctid,t_oid,t_data from heap_page_items(get_raw_page('test', 0));

实验的主题

通过事务和非事务中对一个表中的行进行查看不同的状态,来分析当前的读取数据的语句是否,会对表,或行产生锁。

我们先在测试机上,打开两个进程,且创建一张表,并写入两条数据。

postgres=# \c dvdrental 
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# create table test (id int,name varhcar(20));
dvdrental=# insert into test (id,name) values (1,'Tom'),(2,'Jim');
INSERT 0 2dvdrental=# \d testTable "public.test"Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------id     | integer               |           |          | name   | character varying(20) |           |          | dvdrental=# select * from test;id | name 
----+------1 | Tom2 | Jim
(2 rows)
d337a032d1588345402a3851c9cf5a90.png

我们多次对上述两个进程中执行同样的命令查看结果

好我们已经有表和两条数据,我们将基于这两条数据来进行下面的实验和证明。

0dcf465d9983573cbe8bf961dc4a70d7.png

我们通过这张图中可以看到如下的内容

1 当前事务的ID,在事务内和事务外是不同的 2 当前事务snapshot 可见的范围

accdf5d5124d2fd83dd6caaabcccddc1.png

从第二张图,我们看到,当前的t_xmin,都为980,此时无论是事务内,还是事务外,对于test表中的数据行都是可见的。

下一步,我们将对事务中数据行进行update操作

6174d5251f37b2f908b1d85c68c5b86e.png

我们参见上图,外部的当前事务ID已经是983,但事务内部的事务ID依然是982。

d12efe77b553c651f143555c161dbf10.png

到这里从实际的情况上,事务外部看不到,事务内部的变化后的数据,事务内只能看到自己变化后的数据。

dvdrental=# SELECT pl.pid, pl.locktype, pl.relation::regclass, pl.page, pl.tuple, pl.virtualxid, pl.transactionid, pl.mode, pl.granted, pl.fastpath
FROM pg_locks pl
LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid  ORDER BY pl.pid;pid  |   locktype    |         relation          | page | tuple | virtualxid | transactionid |       mode       | granted | fastpath 
-------+---------------+---------------------------+------+-------+------------+---------------+------------------+---------+----------23395 | relation      | test                      |      |       |            |               | RowExclusiveLock | t       | t23395 | virtualxid    |                           |      |       | 4/3        |               | ExclusiveLock    | t       | t23395 | relation      | pg_database_oid_index     |      |       |            |               | AccessShareLock  | t       | f23395 | relation      | pg_authid_rolname_index   |      |       |            |               | AccessShareLock  | t       | f23395 | relation      | pg_authid_oid_index       |      |       |            |               | AccessShareLock  | t       | f23395 | relation      | pg_authid                 |      |       |            |               | AccessShareLock  | t       | f23395 | transactionid |                           |      |       |            |           982 | ExclusiveLock    | t       | f23395 | relation      | pg_database               |      |       |            |               | AccessShareLock  | t       | f23395 | relation      | pg_database_datname_index |      |       |            |               | AccessShareLock  | t       | f23395 | relation      | pg_stat_activity          |      |       |            |               | AccessShareLock  | t       | t23395 | relation      | test                      |      |       |            |               | AccessShareLock  | t       | t23594 | relation      | pg_database_datname_index |      |       |            |               | AccessShareLock  | t       | f23594 | relation      | pg_locks                  |      |       |            |               | AccessShareLock  | t       | t23594 | virtualxid    |                           |      |       | 6/13       |               | ExclusiveLock    | t       | t23594 | relation      | pg_authid_rolname_index   |      |       |            |               | AccessShareLock  | t       | f23594 | relation      | pg_stat_activity          |      |       |            |               | AccessShareLock  | t       | t23594 | relation      | pg_authid                 |      |       |            |               | AccessShareLock  | t       | f23594 | relation      | pg_database               |      |       |            |               | AccessShareLock  | t       | f23594 | relation      | pg_authid_oid_index       |      |       |            |               | AccessShareLock  | t       | f23594 | relation      | pg_database_oid_index     |      |       |            |               | AccessShareLock  | t       | f
(20 rows)dvdrental=#

此时我们将查看,当前数据库中分配锁的语句执行,且将结果贴到上面。这里已经发现了行的排它锁,那个锁就是我们进行Update 操作的时候发生的锁。但我们并未发现我们读取语句是的行读锁。

为什么?结果可以肯定的是,在读取数据的时候,并未有行的读锁,这里我们可以在单独给出条件,对数据进行单行的读取,此时我们再次运行查看行锁的语句,依然没有对应的行的lock。

f857a5d010eea1fbcf32f00f07dec601.png

好了结论我们已经有了,的确在题目中给出的PG读数据不需要加行锁,是对的。


为什么,他是怎么解决这个问题的。

1  MVCC 多版本控制 

2  基于PG将历史行和当前行存储在一个表的机制 

3  丰富的数据行隐藏的字段如 xmin,xmax,ctid,infomask,infomask2等丰富的事务行标记

4  通过读取snapshot 镜像来保证事务的特性,哪些数据可以显示,哪些数据不能显示 

5  在运行SQL的时候,通过判断程序来,完成判断读取的数据是否可见工作

所以在此基础上PostgreSQL 在我们实验中的 read committed 隔离模式下,不需要读行锁来对数据行在读取期间加锁。

读取事务的规则

t_xmin 为数据插入时的事务号

t_xmax 为数据删除或更新时的事务号

一个总结简单规则,通过当前事务快照 txid_current_snapshot()中的号范围来作为基准判断你是否可以看到某些行,或者看不到某些行。

5af1f6435883ef9cc555b86941053209.png

1  我们以上图为例,在一个事务中,t_xmin是之前某个事务创建的,则我们可以看到他。典型的就是980,我们当前的事务号是982 ,则980是可以看见的。

2  t_max 中存在数字,则说明这个行已经被删除或被更新过,如果你的事务号和他的t_xmax是一致的,则我们不能看到他。

3  在行中可以看到,t_ctid,存储数据更新后的新的物理位置,并进行行物理位置的指示

所以根据上面部分的查看逻辑,我们能看到的行是 lp_off 数字为 8160,8096等两行数据。

dvdrental=*# select lp,lp_off,t_xmin,t_xmax,t_ctid,t_oid,t_data from heap_page_items(get_raw_page('test', 0));lp | lp_off | t_xmin | t_xmax | t_ctid | t_oid |       t_data       
----+--------+--------+--------+--------+-------+--------------------1 |   8160 |    980 |      0 | (0,1)  |       | \x0100000009546f6d2 |   8128 |    980 |    982 | (0,3)  |       | \x02000000094a696d3 |   8096 |    982 |      0 | (0,3)  |       | \x0200000009323232
(3 rows)

如果想了解更深的关于如何判断的代码方面的知识,可以通过下面的连接来查看源代码 https://doxygen.postgresql.org/heapam__visibility_8c.html

当然这里还有一些复杂的部分我们没有讲到,基于时间的原因。比如infomask infomask2 字段的含义,这两个字段尤其infomask 是行的标记信息

如他可以标记如下的功能,

1 行操作删除失败 

2 行操作删除成功 

3 行被锁定 

4 行删除或更新是多事务操作 

5 行已经提交 

6 插入行无效 

7 插入行被锁定

等等

总结:PostgreSQL 基于原理,在读取数据行的时候,不需要使用常见的只读行锁来对读取的数据加锁,而是通过snapshot+ 判断的方式来去解决读取数据时的行的可见性等问题。

这样操作的好处也是显而易见,在一些数据库上,如SQL SERVER上我们经常提到的其他的数据库都没有的锁升级。这证明锁本身开启是有开销的,(其实SQL SERVER的锁升级也可以讲讲,不过今天算了吧)MSSQL 正是因为无法承受大量行读取时的行锁的开销上涨(其中一个原因),而产生了一个解决方案,锁升级。这也是一种解决行锁过多的一种方案,但相对于PostgreSQL的方案,我觉得还是PostgreSQL 的方案更好。

5ab2fdebf054393bc0fe9e3bcf939b8a.png

PostgreSQL 相关文章

病毒攻击PostgreSQL暴力破解系统,防范加固系统方案(内附分析日志脚本)

PostgreSQL 远程管理越来越简单,6个自动化脚本开胃菜

PostgreSQL 稳定性平台 PG中文社区大会--杭州来去匆匆

PostgreSQL 如何通过工具来分析PG 内存泄露

PostgreSQL  分组查询可以不进行全表扫描吗?速度提高上千倍?

POSTGRESQL --Austindatabaes 历年文章整理

PostgreSQL  查询语句开发写不好是必然,不是PG的锅

PostgreSQL  字符集乌龙导致数据查询排序的问题,与 MySQL 稳定 "PG不稳定"

PostgreSQL  Patroni 3.0 新功能规划 2023年 纽约PG 大会 (音译)

PostgreSQL   玩PG我们是认真的,vacuum 稳定性平台我们有了

PostgreSQL DBA硬扛 垃圾 “开发”,“架构师”,滥用PG 你们滚出 !(附送定期清理连接脚本)

DBA 失职导致 PostgreSQL 日志疯涨

OceanBase 相关文章

聚焦SaaS类企业数据库选型(技术、成本、合规、地缘政治)

OceanBase 学习记录-- 建立MySQL租户,像用MySQL一样使用OB

OceanBase  学习记录 -- 安装简易环境

OceanBase  学习记录 --  开始入门

数据库最近第一比较多,OceanBase 定语加多了?

临时工访谈:OceanBase上海开大会,我们四个开小会 OB 国产数据库破局者

临时工说:OceanBase 到访,果然数据库的世界很卷,没边

数据库信息速递  阿里巴巴的分布式数据库OceanBase旨在进军中国以外的市场 (翻译)

PolarDB 相关文章

PolarDB 并行黑科技--从百套MySQL撤下说起 (感谢8018个粉丝的支持)

PolarDB 杀疯了,Everywhere Everytime Everydatabase on Serverless

POLARDB  从一个使用者的角度来说说,POALRDB 怎么打败 MYSQL RDS

PolarDB 最近遇到加字段加不上的问题 与 使用PolarDB 三年感受与恳谈

PolarDB 从节点Down机后,引起的主从节点强一致的争论

PolarDB serverless 真敢搞,你出圈了你知道吗!!!!

PolarDB VS PostgreSQL  "云上"性能与成本评测 -- PolarDB 比PostgreSQL 好?

临时工访谈:PolarDB  Serverless  发现“大”问题了  之 灭妖记 续集

临时工访谈:庙小妖风大-PolarDB 组团镇妖 之 他们是第一

PolarDB for PostgreSQL  有意思吗?有意思呀

PolarDB  Serverless POC测试中有没有坑与发现的疑问

临时工说:从人性的角度来分析为什么公司内MySQL 成为少数派,PolarDB 占领高处

POLARDB  到底打倒了谁  PPT 分享 (文字版)

POLARDB  -- Ausitndatabases 历年的文章集合

PolarDB for PostgreSQL  有意思吗?有意思呀

PolarDB  搞那么多复杂磁盘计费的东西,抽筋了吗?

MongoDB 相关文章

数据库 《三体》“二向箔”  思维限制 !8个公众号联合抽奖送书 建立数据库设计新思维

MongoDB  是外星人,水瓶座,怎么和不按套路出牌的他沟通?

17000多张MongoDB表的锅 自动分析删除表数据难题--从头到尾的处理过程(文尾有MongoDB开发规范)

MongoDB 插入更新数据慢,开发问哪的问题?附带解决方案和脚本

MongoDB 不是软柿子,想替换就替换

MongoDB  挑战传统数据库聚合查询,干不死他们的MongoDB 2023纽约 MongoDB 大会 -- 我们怎么做的新一代引擎 SBE Mongodb 7.0双擎力量(译)

MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模

MongoDB  双机热备那篇文章是  “毒”

MongoDB   会丢数据吗?在次补刀MongoDB  双机热备

MONGODB  ---- Austindatabases  历年文章合集

MySQL相关文章

MySQL timeout 参数可以让事务不完全回滚

"DBA 是个der" 吵出MySQL主键问题多种解决方案

MySQL 让你还用5.7 出事了吧,用着用着5.7崩了

MySQL 的SQL引擎很差吗?由一个同学提出问题引出的实验

用MySql不是MySQL, 不用MySQL都是MySQL 横批 哼哼哈哈啊啊

MYSQL  --Austindatabases 历年文章合集

阿里云系列

阿里云数据库产品权限设计缺陷 ,六个场景诠释问题,你可以做的更好?

阿里云数据库--市场营销聊胜于无--3年的使用感受与反馈系列

阿里云数据库产品 对内对外一样的卷 --3年阿里云数据库的使用感受与反馈系列

阿里云数据库使用感受--客户服务问题深入剖析与什么是廉价客户 --3年的使用感受与反馈系列

阿里云数据库使用感受--操作界面有点眼花缭乱 --3年的使用感受与反馈系列

SQL SERVER 系列

SQL SERVER 如何实现UNDO REDO  和PostgreSQL 有近亲关系吗

SQL SERVER 2022 针对缓存扫描和Query Store 的进步,可以考虑进行版本升级

临时工访谈系列

临时工说: 实际实例揭穿AI, 上云就不用DBA的谎言

临时工说:DBA 7*24H 给2万的工作,到底去不去?

国内最大IT服务公司-招聘DBA “招聘广告”的变化--分析与探讨

临时工说:  网友问35岁就淘汰,我刚入行DBA 怎么办?

c088f5e88093f024cc74c3d5ced60f8a.png

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com