为什么在一个表中写可以防止另一个表出现真空？ [英] why writes in a table prevent vacuums in another?
问题描述
具有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 pages
INFO: "a": found 1000 removable, 1000 nonremovable row versions in 9 out of 9 pages
DETAIL: 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 pages
DETAIL: 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 pages
INFO: "a": found 1000 removable, 1000 nonremovable row versions in 9 out of 9 pages
DETAIL: 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
推荐答案
通过< a href = https://twitter.com/alostale/status/906034092189159424 rel = nofollow noreferrer> 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.
这篇关于为什么在一个表中写可以防止另一个表出现真空？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
