为什么在一个表中写可以防止另一个表出现真空？

本文介绍了为什么在一个表中写可以防止另一个表出现真空？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 具有READ COMMITTED隔离级别，执行写操作的空闲事务将阻止真空清除该事务写入的表的死行。Having READ COMMITTED isolation level, idle transactions that have performed a write operation will prevent vacuum to cleanup dead rows for the tables that transaction wrote in.用于由仍在进行中的事务写入的表。 在这里，您可以找到很好的解释。That is clear for tables that were written by transactions that are still in progress. Here you can find a good explanation.但是我不清楚这个限制为什么还会影响任何其他表。But it is not clear to me why this limitation affects also to any other tables.示例：事务T开始并更新表B，在T处于事务空闲状态时对表A执行清理。在这种情况下，为什么不能删除A中的死行？For example: transaction T is started and it updates table B, vacuum is executed for table A while T is in "idle in transaction" state. In this scenario, why dead rows in A cannot be removed?这是我所做的：# show default_transaction_isolation; default_transaction_isolation------------------------------- read committed(1 row)# create table a (v int);CREATE TABLE# create table b (v int);CREATE TABLE# insert into a values (generate_series(1,1000));INSERT 0 1000这时我进行了更新以生成新的1000条死行At this point I do an update to generate new 1000 dead rows# update a set v = v + 1;UPDATE 1000抽真空将按预期方式将其删除：Vacuuming will remove them as expected:# vacuum verbose a;INFO: vacuuming "public.a"INFO: "a": removed 1000 row versions in 5 pagesINFO: "a": found 1000 removable, 1000 nonremovable row versions in 9 out of 9 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 0 unused item pointers.0 pages are entirely empty.CPU 0.00s/0.00u sec elapsed 0.00 sec.VACUUM我现在开始在表b中写事务T：I now start transaction T writing in table b:# begin;BEGIN# insert into b values (generate_series(1,1000));INSERT 0 1000我在另一个事务T1中又产生了更多的死行T：I generate more dead rows again in a different transaction T1 that started after T:# begin;# update a set v = v + 1;# commit;在不同交易中：# vacuum verbose a;INFO: vacuuming "public.a"INFO: "a": found 0 removable, 2000 nonremovable row versions in 9 out of 9 pagesDETAIL: 1000 dead row versions cannot be removed yet.There were 34 unused item pointers.0 pages are entirely empty.CPU 0.00s/0.00u sec elapsed 0.00 sec.VACUUM这是相关部分：详细信息：1000个死行版本不能如果我提交事务T并再次执行真空操作，则会按预期方式删除死行：If I commit transaction T and execute again vacuum I get dead rows removed as expected:# vacuum verbose a;INFO: vacuuming "public.a"INFO: "a": removed 1000 row versions in 5 pagesINFO: "a": found 1000 removable, 1000 nonremovable row versions in 9 out of 9 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 34 unused item pointers.0 pages are entirely empty.CPU 0.00s/0.00u sec elapsed 0.00 sec.VACUUM推荐答案通过 Twitter 。当前（至少直到PostgreSQL 9.6）的行为是：Current (at least up to PostgreSQL 9.6) behavior is:在 any 表中执行写操作的实时事务将防止清理启动的已提交事务生成的死行在任何其他表中进行首次实时交易后。Live transactions performing a write operation in any table will prevent vacuuming dead rows generated by commited transactions that started after first live transaction in any other table.即使从概念的角度来看并不需要此限制，这也是当前算法的原理为检查死行原因而实施。Even this limitation is not required from the conceptual point of view, it is how current algorithm is implemented for performance on checking dead rows reasons. 这篇关于为什么在一个表中写可以防止另一个表出现真空？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！