企业营销型网站制作多少钱,wordpress 批量设置标签,jsp做网站的书,营销页面制作目录结构 注#xff1a;提前言明 本文借鉴了以下博主、书籍或网站的内容#xff0c;其列表如下#xff1a; 1、参考书籍#xff1a;《PostgreSQL数据库内核分析》 2、参考书籍#xff1a;《数据库事务处理的艺术#xff1a;事务管理与并发控制》 3、PostgreSQL数据库仓库… 目录结构 注提前言明 本文借鉴了以下博主、书籍或网站的内容其列表如下 1、参考书籍《PostgreSQL数据库内核分析》 2、参考书籍《数据库事务处理的艺术事务管理与并发控制》 3、PostgreSQL数据库仓库链接点击前往 4、日本著名PostgreSQL数据库专家 铃木启修 网站主页点击前往 5、参考书籍《PostgreSQL中文手册》 6、参考书籍《PostgreSQL指南内幕探索》点击前往 7、Using Transaction Chaining to Reduce Server Round-Trips点击前往 1、本文内容全部来源于开源社区 GitHub和以上博主的贡献本文也免费开源可能会存在问题评论区等待大佬们的指正 2、本文目的开源共享 抛砖引玉 一起学习 3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关 4、大家可以根据需要自行 复制粘贴以及作为其他个人用途但是不允许转载 不允许商用 写作不易还请见谅 5、本文内容基于PostgreSQL master源码开发而成 深入理解PostgreSQL数据库之transaction chain的使用和实现 文章快速说明索引功能使用背景说明功能实现源码分析源码调试案例分析 文章快速说明索引
学习目标
做数据库内核开发久了就会有一种 少年得志年少轻狂 的错觉然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在每每想到于此皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发近段时间 将着重于学习分享Postgres的基础知识和实践内幕。 学习内容详见目录
1、深入理解PostgreSQL数据库之transaction chain的使用和实现 学习时间
2024年07月01日 20:25:11 学习产出
1、PostgreSQL数据库基础知识回顾 1个 2、CSDN 技术博客 1篇 3、PostgreSQL数据库内核深入学习 注下面我们所有的学习环境是Centos8PostgreSQL masterOracle19CMySQL8.0
postgres# select version();version
------------------------------------------------------------------------------------------------------------PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)postgres##-----------------------------------------------------------------------------#SQL select * from v$version; BANNER Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
BANNER_FULL Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
CON_ID 0#-----------------------------------------------------------------------------#mysql select version();
-----------
| version() |
-----------
| 8.0.27 |
-----------
1 row in set (0.06 sec)mysql功能使用背景说明
使用 PostgreSQL 或任何关系数据库实现业务应用程序通常相当于执行一系列事务。给定事务以 COMMIT 还是 ROLLBACK 结束并不重要因为在这两种情况下下一个事务都会在前一个事务完成后立即开始。此外对于大多数应用程序来说前一个事务和下一个事务具有相似的特征例如它们的 ISOLATION LEVEL。本质上您最终得到的样式如下所示
START TRANSACTION;
-- workload of 1st transaction
COMMIT;
START TRANSACTION;
-- workload of 2nd transaction
ROLLBACK;
START TRANSACTION;
-- workload of 3rd transaction
COMMIT;在上面的 SQL 脚本中您会看到三个后续事务和总共六个语句每个事务两个语句但您可以想象更长的事务序列。问题是每个语句都需要单独的服务器往返才能执行。没有办法解决这个问题即使三个示例事务为空不包含任何语句也没有什么区别。 监控连接状态
为了进行此实验我们需要与同一 PostgreSQL 服务器建立两个单独的连接例如两个 psql 会话。第一个连接使用 postgres 数据库第二个连接使用demo数据库。
好了现在我们可以在第一个连接上使用 pg_stat_activity属于 pg_catalog 模式的内置视图来询问 PostgreSQL 使用演示数据库的第二个连接的状态
postgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
-------idle
(1 row)如您所见由于没有执行任何操作因此该连接当前处于空闲状态。但是如果我们在第二个连接上启动事务我们可以看到连接的状态立即变为idle in transaction。
demo# START TRANSACTION;
START TRANSACTIONpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
---------------------idle in transaction
(1 row)这证明 START TRANSACTION 语句导致了与服务器的往返因为如果没有第一个连接将无法看到第二个连接的状态变化。如果我们使用 COMMIT 或 ROLLBACK 完成事务情况也是一样的在这种情况下连接会立即恢复到空闲状态
demo*# COMMIT;
COMMITpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
-------idle
(1 row)往返开销
长话短说事务的开始和结束可能会带来巨大的开销。具体来说如果满足以下任何条件
往返服务器的时间很慢。通常如果客户端和服务器之间的网络距离很远情况就会如此。许多事务的平均运行时间很短。这是因为对于较短的事务开销占总运行时间的百分比较高。
那么我们可以做些什么来减少开销并提高性能呢 事务链
SQL 标准有一个由 PostgreSQL 实现的内置解决方案AND CHAIN 参数。此参数可用于 COMMIT 和 ROLLBACK 语句并具有以下效果…
如果提供了 AND CHAIN 参数则提交或回滚当前事务此外立即启动具有相同特征例如ISOLATION LEVEL的后续事务。
因此如果我们将其应用于原始示例我们可以将服务器往返次数减少基本上 50%从 n 减少到 n/21。
START TRANSACTION;
-- workload of 1st transaction
COMMIT AND CHAIN;
-- workload of 2nd transaction
ROLLBACK AND CHAIN;
-- workload of 3rd transaction
COMMIT;我们可以运行相同的实验来证明它按预期工作。我再次使用两个连接一个使用 postgres 数据库另一个使用演示数据库。最初演示连接处于空闲状态但是一旦我们开始新的事务其状态就会更改为idle in transaction。
demo# START TRANSACTION;
START TRANSACTIONpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
---------------------idle in transaction
(1 row)那么如果demo连接执行 COMMIT AND CHAIN 语句会发生什么正如我所说PostgreSQL 立即启动后续事务因此我们看不到状态变化。
demo# COMMIT AND CHAIN;
COMMIT AND CHAINpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
---------------------idle in transaction
(1 row)当我们执行 ROLLBACK AND CHAIN 时我们得到完全相同的行为 — 没有明显的状态改变。
demo# ROLLBACK AND CHAIN;
ROLLBACK AND CHAINpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
---------------------idle in transaction
(1 row)最后当我们发出正常的COMMIT或ROLLBACK时状态就会变回初始的空闲状态。
demo# COMMIT;
COMMITpostgres# SELECT state FROM pg_stat_activity WHERE datnamedemo;state
-------idle
(1 row)功能实现源码分析
其语法格式如下
// src/backend/parser/gram.y/******************************************************************************* Transactions:** BEGIN / COMMIT / ROLLBACK* (also older versions END / ABORT)******************************************************************************/TransactionStmt:ABORT_P opt_transaction opt_transaction_chain{TransactionStmt *n makeNode(TransactionStmt);n-kind TRANS_STMT_ROLLBACK;n-options NIL;n-chain $3;n-location -1;$$ (Node *) n;}...| COMMIT opt_transaction opt_transaction_chain{TransactionStmt *n makeNode(TransactionStmt);n-kind TRANS_STMT_COMMIT;n-options NIL;n-chain $3;n-location -1;$$ (Node *) n;}| ROLLBACK opt_transaction opt_transaction_chain{TransactionStmt *n makeNode(TransactionStmt);n-kind TRANS_STMT_ROLLBACK;n-options NIL;n-chain $3;n-location -1;$$ (Node *) n;}...;TransactionStmtLegacy:...| END_P opt_transaction opt_transaction_chain{TransactionStmt *n makeNode(TransactionStmt);n-kind TRANS_STMT_COMMIT;n-options NIL;n-chain $3;n-location -1;$$ (Node *) n;};opt_transaction_chain:AND CHAIN { $$ true; }| AND NO CHAIN { $$ false; }| /* EMPTY */ { $$ false; };示例一如下 示例二如下 源码调试案例分析
接下来我们调试一下 重点看一下上面的示例二如下 如上begin READ ONLY;是在上图将guc参数transaction_read_only设置为真 XactReadOnly true函数堆栈如下
set_config_with_handle(const char * name, config_handle * handle, const char * value, GucContext context, GucSource source, Oid srole, GucAction action, _Bool changeVal, int elevel, _Bool is_reload) (\home\postgres\postgres\src\backend\utils\misc\guc.c:3758)
set_config_option(const char * name, const char * value, GucContext context, GucSource source, GucAction action, _Bool changeVal, int elevel, _Bool is_reload) (\home\postgres\postgres\src\backend\utils\misc\guc.c:3361)
SetPGVariable(const char * name, List * args, _Bool is_local) (\home\postgres\postgres\src\backend\utils\misc\guc_funcs.c:320)
standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\postgres\postgres\src\backend\tcop\utility.c:619)
ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\postgres\postgres\src\backend\tcop\utility.c:523)
PortalRunUtility(Portal portal, PlannedStmt * pstmt, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, QueryCompletion * qc) (\home\postgres\postgres\src\backend\tcop\pquery.c:1158)
PortalRunMulti(Portal portal, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\home\postgres\postgres\src\backend\tcop\pquery.c:1315)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\home\postgres\postgres\src\backend\tcop\pquery.c:791)
exec_simple_query(const char * query_string) (\home\postgres\postgres\src\backend\tcop\postgres.c:1274)
PostgresMain(const char * dbname, const char * username) (\home\postgres\postgres\src\backend\tcop\postgres.c:4680)
BackendMain(char * startup_data, size_t startup_data_len) (\home\postgres\postgres\src\backend\tcop\backend_startup.c:105)
postmaster_child_launch(BackendType child_type, char * startup_data, size_t startup_data_len, ClientSocket * client_sock) (\home\postgres\postgres\src\backend\postmaster\launch_backend.c:265)
BackendStartup(ClientSocket * client_sock) (\home\postgres\postgres\src\backend\postmaster\postmaster.c:3593)
ServerLoop() (\home\postgres\postgres\src\backend\postmaster\postmaster.c:1674)
PostmasterMain(int argc, char ** argv) (\home\postgres\postgres\src\backend\postmaster\postmaster.c:1372)
main(int argc, char ** argv) (\home\postgres\postgres\src\backend\main\main.c:197)接下来这里将直接INSERT报错如下 后续处理如下 此时的函数堆栈如下
AtEOXact_GUC(_Bool isCommit, int nestLevel)
AbortTransaction()
AbortCurrentTransactionInternal()
AbortCurrentTransaction()
PostgresMain(const char * dbname, const char * username)
BackendMain(char * startup_data, size_t startup_data_len)
postmaster_child_launch(BackendType child_type, char * startup_data, size_t startup_data_len, ClientSocket * client_sock)
BackendStartup(ClientSocket * client_sock)
ServerLoop()
PostmasterMain(int argc, char ** argv)
main(int argc, char ** argv)注如上 在INSERT报错之后该事务对应的上述GUC被重置如下
// src/backend/utils/misc/guc.c/** Do GUC processing at transaction or subtransaction commit or abort, or* when exiting a function that has proconfig settings, or when undoing a* transient assignment to some GUC variables. (The name is thus a bit of* a misnomer; perhaps it should be ExitGUCNestLevel or some such.)* During abort, we discard all GUC settings that were applied at nesting* levels nestLevel. nestLevel 1 corresponds to the main transaction.* * 在事务或子事务提交或中止时或在退出具有 proconfig 设置的函数时或在撤消对某些 GUC 变量的临时分配时执行 GUC 处理* 因此这个名字有点用词不当也许应该是 ExitGUCNestLevel 或类似的名字* 在中止期间我们会丢弃在嵌套级别 nestLevel 处应用的所有 GUC 设置* nestLevel 1 对应于主事务*/
void
AtEOXact_GUC(bool isCommit, int nestLevel);于是在接下来的commit and chain;中XactReadOnly仍是假如下 此时函数堆栈如下
EndTransactionBlock(_Bool chain)
standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc)
ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc)
PortalRunUtility(Portal portal, PlannedStmt * pstmt, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, QueryCompletion * qc)
PortalRunMulti(Portal portal, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc)
exec_simple_query(const char * query_string)
...接下来我们调试一下上面的示例二的另一种情况因为当前会话已经设置该GUC参数为真(将要被rollback或者commit的事务)接下来的rollback and chain的处理 如下 UserAbortTransactionBlock(_Bool chain) (\home\postgres\postgres\src\backend\access\transam\xact.c:4262)
standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc)
ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc)
PortalRunUtility(Portal portal, PlannedStmt * pstmt, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, QueryCompletion * qc)
PortalRunMulti(Portal portal, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc)
exec_simple_query(const char * query_string)
...在UserAbortTransactionBlock函数中的处理如下
s-blockState: TBLOCK_INPROGRESS - TBLOCK_ABORT_PENDING
s-chain: false - true然后进入如下的处理逻辑
CommitTransactionCommandInternal()
CommitTransactionCommand()
finish_xact_command()
exec_simple_query(const char * query_string)
.../** Here we were in a perfectly good transaction block but the user* told us to ROLLBACK anyway. We have to abort the transaction* and then clean up.*/case TBLOCK_ABORT_PENDING:AbortTransaction();CleanupTransaction();s-blockState TBLOCK_DEFAULT;if (s-chain){StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}break;首先进入AbortTransaction函数因为在这种情况下guc_stack_list ! NIL在如下的堆栈处理中 该参数XactReadOnly被置为假
AtEOXact_GUC(_Bool isCommit, int nestLevel)
AbortTransaction()
CommitTransactionCommandInternal()
CommitTransactionCommand()
finish_xact_command()
exec_simple_query(const char * query_string)
...接着进入CleanupTransaction函数进行清理 接下来进入今天的重点如下 if (s-chain){StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}因为chain为真这里还是再重启一个事务。不过这种类似于XactReadOnly的guc参数在StartTransaction过程中仍被赋值默认值该参数的重新修改如下 至此rollback and chain 操作回滚了上一个事务并开启新的事务 且XactReadOnly true得以保留 同上面rollback and chain操作一样其他几种 如下
// src/backend/access/transam/xact.c/** CommitTransactionCommandInternal - a function doing an iteration of work* regarding handling the commit transaction command. In the case of* subtransactions more than one iterations could be required. Returns* true when no more iterations required, false otherwise.*/
static bool
CommitTransactionCommandInternal(void)
{.../** We are completing a COMMIT command. Do it and return to the* idle state.*/case TBLOCK_END:CommitTransaction();s-blockState TBLOCK_DEFAULT;if (s-chain) // here{StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}break;.../** Here we were in an aborted transaction block and we just got* the ROLLBACK command from the user, so clean up the* already-aborted transaction and return to the idle state.*/case TBLOCK_ABORT_END:CleanupTransaction();s-blockState TBLOCK_DEFAULT;if (s-chain) // here{StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}break;.../** Here we were in a perfectly good transaction block but the user* told us to ROLLBACK anyway. We have to abort the transaction* and then clean up.*/case TBLOCK_ABORT_PENDING:AbortTransaction();CleanupTransaction();s-blockState TBLOCK_DEFAULT;if (s-chain) // here{StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}break;.../** The user issued a COMMIT, so we end the current subtransaction* hierarchy and perform final commit. We do this by rolling up* any subtransactions into their parent, which leads to O(N^2)* operations with respect to resource owners - this isnt that* bad until we approach a thousands of savepoints but is* necessary for correctness should after triggers create new* resource owners.*/case TBLOCK_SUBCOMMIT:do{CommitSubTransaction();s CurrentTransactionState; /* changed by pop */} while (s-blockState TBLOCK_SUBCOMMIT);/* If we had a COMMIT command, finish off the main xact too */if (s-blockState TBLOCK_END){Assert(s-parent NULL);CommitTransaction();s-blockState TBLOCK_DEFAULT;if (s-chain) // here{StartTransaction();s-blockState TBLOCK_INPROGRESS;s-chain false;RestoreTransactionCharacteristics(savetc);}}else if (s-blockState TBLOCK_PREPARE){Assert(s-parent NULL);PrepareTransaction();s-blockState TBLOCK_DEFAULT;}elseelog(ERROR, CommitTransactionCommand: unexpected state %s,BlockStateAsString(s-blockState));break;...
}对应非特殊的guc参数能否可以继承呢如下
[postgreslocalhost:~/test/bin]$ ./psql
psql (17beta2)
Type help for help.postgres# show timezone;TimeZone
---------------------America/Los_Angeles
(1 row)postgres# begin;
BEGIN
postgres*# set timezone PRC;
SET
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*# commit and chain; ## commit 提交 继承
COMMIT
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*# rollback and chain; ## 无东西可以回滚
ROLLBACK
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*#[postgreslocalhost:~/test/bin]$ ./psql
psql (17beta2)
Type help for help.postgres# show timezone;TimeZone
---------------------America/Los_Angeles
(1 row)postgres# begin;
BEGIN
postgres*# desc a error;
2024-07-01 06:19:56.969 PDT [34810] ERROR: syntax error at or near desc at character 1
2024-07-01 06:19:56.969 PDT [34810] STATEMENT: desc a error;
ERROR: syntax error at or near desc
LINE 1: desc a error;^
postgres!# show timezone;
2024-07-01 06:20:00.721 PDT [34810] ERROR: current transaction is aborted, commands ignored until end of transaction block
2024-07-01 06:20:00.721 PDT [34810] STATEMENT: show timezone;
ERROR: current transaction is aborted, commands ignored until end of transaction block
postgres!#
postgres!# commit and chain; ## commit 这里相当于先回滚 继承
ROLLBACK
postgres*# show timezone;TimeZone
---------------------America/Los_Angeles
(1 row)postgres*# set timezone PRC;
SET
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*# commit and chain; ## commit 提交 继承
COMMIT
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*# rollback and chain; ## 无东西可以回滚
ROLLBACK
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*# reset timezone;
RESET
postgres*# show timezone;TimeZone
---------------------America/Los_Angeles
(1 row)postgres*# rollback and chain; ## rollback 这里相当于先回滚 继承
ROLLBACK
postgres*# show timezone;TimeZone
----------PRC
(1 row)postgres*#
