10
暂无图片
暂无图片
暂无图片

postgresql(update ≈ delete insert) -m6米乐安卓版下载

原创 多米爸比 2021-08-17
1683

pg更新表元组(数据行)时不会直接修改该元组,它会写入新版本元组并保留旧版本元组,这样来提高并发读取的请求,旧版本的"死元组"稍后由vacuum来清理。

因而如果我们删除一行数据并且插入一行新数据,数据库里是有一个死元组和一个新的活元组。所以我们通常会这样向别人解释pg里的"update":

update ≈ delete   insert

下面我们通过实验来理解这个"约等于"的差异。

创建测试表并插入一条数据:

create table test_update (
   id int2 primary key,
   val int2 not null
);
insert into test_update values (1, 100);

接下来的两个测试,我们使用两个并发会话发起语句,如下图。

重置表后开始第二次测试

truncate test_update;
insert into test_update values (1, 100);

第二次测试我们使用delete和insert操作来进行测试,如下图

对上面两个测试结果差异的解释可以参考官方文档

update, delete, select for update, and select for share commands behave the same as select in terms of searching for target rows: they will only find target rows that were committed as of the command start time. however, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. in this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). if the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. if the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. the search condition of the command (the where clause) is re-evaluated to see if the updated version of the row still matches the search condition. if so, the second updater proceeds with its operation using the updated version of the row. in the case of select for update and select for share, this means it is the updated version of the row that is locked and returned to the client.

上面解释了第一个测试返回一行数据的原因,而在第二个测试中,删除的行和新插入的行之间没有联系(第一个测试里通过pageinspect可以观察ctid值),所以第二种情况下并没有得到结果。

参考文章:
https://www.cybertec-postgresql.com/en/is-update-the-same-as-delete-insert-in-postgresql/

最后修改时间:2021-08-17 18:27:18
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论

网站地图