【PostgreSQL】PostgreSQL零宕机变更schema：lock_timeout和retries(设置超时和重试)

 

问题描述

当你部署数据库schema变更时，即使有非常高级别的自动化，但是没有使用非常地的lock_timeout（或 statement_timeout）值来获取对可能发生变化且不实现某种重试逻辑的数据库对象进行变更时，也无法保证系统不会宕机。

创建一张只有单行记录的表作为演示：​

?

1	create table test as select 1 as i;

接下来，我们需要打开三个psql终端：

1.第一个，开启事务(测试的事务隔离级别是已提交读)，读取表中的数据，保持会话处于"idle in transaction"状态：

?

1 2	begin; select * from test;

2.第二个，我们会尝试部署一些DDL操作。例如，增加not null约束(显然这个操作会被阻塞，等待上一个select事务结束：提交或者回滚)：

?

1	alter table test alter column i set not null;

3.最后，第三个psql终端，我们再次尝试读数据：

?

1	select * from test;

这可能会让人吃惊，但是最后一个操作会hang在那里，select不会返回任何结果，为什么呢？

打开第四个psql终端，使用lock analysis snippet返回锁的树：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

\timing on set statement_timeout to '400ms';   with recursive activity as (   select     pg_blocking_pids(pid) blocked_by,     *,     age(clock_timestamp(), xact_start)::interval(0) as tx_age,     age(clock_timestamp(), state_change)::interval(0) as state_age   from pg_stat_activity   where state is distinct from 'idle' ), blockers as (   select     array_agg(distinct c order by c) as pids   from (     select unnest(blocked_by)     from activity   ) as dt(c) ), tree as (   select     activity.*,     1 as level,     activity.pid as top_blocker_pid,     array[activity.pid] as path,     array[activity.pid]::int[] as all_blockers_above   from activity, blockers   where     array[pid] <@ blockers.pids     and blocked_by = '{}'::int[]   union all   select     activity.*,     tree.level + 1 as level,     tree.top_blocker_pid,     path || array[activity.pid] as path,     tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above   from activity, tree   where     not array[activity.pid] <@ tree.all_blockers_above     and activity.blocked_by <> '{}'::int[]     and activity.blocked_by <@ tree.all_blockers_above ) select   pid,   blocked_by,   tx_age,   state_age,   replace(state, 'idle in transaction', 'idletx') state,   datname,   usename,   format('%s:%s', wait_event_type, wait_event) as wait,   (select count(distinct t1.pid) from tree t1 where array[tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd,   format(     '%s %s%s',     lpad('[' || pid::text || ']', 7, ' '),     repeat('.', level - 1) || case when level > 1 then ' ' end,     query   ) from tree order by top_blocker_pid, level, pid   \watch 1

可以看到类似这样的结果：

?

1 2 3 4 5 6

pid | blocked_by |  tx_age  | state_age | state  | datname  | usename  |       wait        | blkd |                         format -----+------------+----------+-----------+--------+----------+----------+-------------------+------+---------------------------------------------------------   48 | {}         | 14:26:52 | 14:26:50  | idletx | postgres | postgres | Client:ClientRead |    2 |    [48] select * from test;   78 | {48}       | 14:26:43 | 14:26:43  | active | postgres | postgres | Lock:relation     |    1 |    [78] . alter table test alter column i set not null;  224 | {78}       | 14:26:40 | 14:26:40  | active | postgres | postgres | Lock:relation     |    0 |   [224] .. select * from test; (3 rows)

我们遇到了什么问题？我们的DDL无法在表上获得锁。它在等待其它事务完成。有趣的是，事务(pid=48)没有修改数据，在表上持有了一个access share锁，通过pg_locks可以看出来：

?

1 2 3 4 5 6

=# select locktype, relation, virtualxid, transactionid, virtualtransaction, pid, mode, granted from pg_locks where pid = 48;   locktype  | relation | virtualxid | transactionid | virtualtransaction | pid |      mode       | granted ------------+----------+------------+---------------+--------------------+-----+-----------------+---------  virtualxid |          | 3/11       |               | 3/11               |  48 | ExclusiveLock   | t  relation   |    24869 |            |               | 3/11               |  48 | AccessShareLock | t (2 rows)

DDL需要在表上施加access exclusive锁，它与access share锁冲突了。可以看下图：

 

所以，如果目标表上有长时间运行的事务(即使是select)，这个事务也会阻塞DDL。这可能会引起问题，尤其是你把DDL封装到一个事务内的话，而事务内其它操作已经获取了一些锁，这些锁会被一直持有，从而可能会影响其它会话。这就是为什么数据库的SCHEMA变更事务应该仔细设计和测试的原因。

这里另外还有一个有意思的问题：看第三个会话(pid=224)

?

1 2 3 4

pid | blocked_by |  tx_age  | state_age | state  | datname  | usename  |       wait        | blkd |                         format -----+------------+----------+-----------+--------+----------+----------+-------------------+------+---------------------------------------------------------  224 | {78}       | 14:26:40 | 14:26:40  | active | postgres | postgres | Lock:relation     |    0 |   [224] .. select * from test; (3 rows)

DDL会话在获取access exclusive锁的同时，它开始阻塞别人了。基本上，对这个表做任何操作都不可能了。

如果你在繁忙的库上执行了DDL，如果没有使用严格的超时设置来保护，某天你可能会发现你的系统宕机了。如何想避免该问题的，都要施加超时保护，这个保护可以通过超时设置来实现(要么lock_timeout、要么statement_timeout)

优雅的schema变更：lock_timeout和retries

1.不要让DDL执行长时间处于等待状态：

?

1	set statement_timeout = '50ms';

2.现在既然DDL可能会失败，就增加尝试的机会

?

1 2 3 4 5

while true; do     date     psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break     sleep 1 done

当然，某些情况下，我们需要允许我们的DDL执行时间超过50ms，在这种场景下，我们可以显式地在表上加锁(lock table ...)，然后修改statement_timeout的值为0，或者：

?

1 2 3 4 5 6 7 8

begin;     set statement_timeout = 50;     lock table only test in ACCESS EXCLUSIVE MODE;     set statement_timeout = 0;       alter table test ....;     -- do whatever you want, timeout is removed. commit;

从9.3开始，我们可以使用lock_timeout代替statement_timeout，lock_timeout只会影响我们尝试获得的锁，相当方便。

如果我们需要将多个DDL合并到一个事务中呢？这一点，PostgreSQL很强大，支持将任何DDL放到一个事务内执行，实现原子性。但是，在这种场景下，如果我们成功的完成了一个操作，但是下一个ddl没有获得锁，意味着我们整个事务要回滚。

可以通过以下方式来改进这一点：

·在事务的开通，获取所有的锁，显式加锁。如果不能获取任何一个，事务就失败，重启尝试。这样可以避免回滚操作发生，以下是示例：

?

1 2 3 4 5 6 7 8 9

begin;   set local lock_timeout to '50ms';     lock table only t1 in access exclusive mode;   lock table only t2 in access exclusive mode;     alter table t1 ...;   alter table t2 ...; commit;

·使用子事务

显式地使用关键子savepoint、或者在pl/pgsql中使用语句块begin/exception when ../end，

这样只是回滚部分修改。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

DO $do$ DECLARE    lock_timeout CONSTANT text := '50ms';    max_attempts CONSTANT INT := 1000;    ddl_completed BOOLEAN := FALSE; BEGIN      PERFORM set_config('lock_timeout', lock_timeout, FALSE);      FOR i IN 1..max_attempts LOOP       BEGIN          EXECUTE 'ALTER TABLE test add column whatever2 INT4';          ddl_completed := TRUE;          EXIT;       EXCEPTION          WHEN lock_not_available THEN            NULL;       END;    END LOOP;      IF ddl_completed THEN       RAISE INFO 'DDL has been successfully completed';    ELSE       RAISE EXCEPTION 'Failed to perform DDL';    END IF; END $do$;

不过，子事务可能隐含着风险。

子事务可能存在的风险

有些情况下，子事务可能会导致性能下降。简单点说，即使很小的事务id(xid，32位的事务id)被分配给未完成的子事务，伴随着很长时间的主事务，可能会导致standby发生灾难性的性能降级。

如果我们只是使用一个简单的savepoint，也可能会遇到问题？答案是：可能。让我们来看看如果有一个savepoint会发生什么，实验包含多次尝试：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

test=# create table test(i int8); CREATE TABLE test=# select pg_current_xact_id(); -- txid_current() in older PG versions  pg_current_xact_id -------------------- test=# drop table test; DROP TABLE test=# create table test(i int8); CREATE TABLE test=# select pg_current_xact_id(); -- txid_current() in older PG versions  pg_current_xact_id --------------------          1551685670 (1 row)   test=# begin; BEGIN test=*# select pg_current_xact_id();  pg_current_xact_id --------------------          1551685671 (1 row)   test=*# savepoint s1; SAVEPOINT test=*# insert into test select 1; INSERT 0 1 test=*# rollback to s1; ROLLBACK test=*# insert into test select 2; INSERT 0 1 test=*# rollback to s1; ROLLBACK test=*# insert into test select 3; INSERT 0 1 test=*# commit; COMMIT test=# select pg_current_xact_id();  pg_current_xact_id --------------------          1551685675 (1 row)

可以看到，全局的xid值在我们的事务期间增长超过1，子事务每次都执行回滚操作，都会分配一个新的xid。让我们来检查一下隐藏列xmin，使用扩展pageinspect，还可以看到死的元组：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

test=# select xmin, * from test;         | i ------------+---  1551685674 | 3 (1 row)   test=# create extension pageinspect; CREATE EXTENSION test=# select lp, t_xmin, t_xmax, t_ctid, t_data from heap_page_items(get_raw_page('test', 0));  lp |   t_xmin   | t_xmax | t_ctid |       t_data ----+------------+--------+--------+--------------------   1 | 1551685672 |      0 | (0,1)  | \x0100000000000000   2 | 1551685673 |      0 | (0,2)  | \x0200000000000000   3 | 1551685674 |      0 | (0,3)  | \x0300000000000000 (3 rows)

可以看到，使用了三个xmin：1551685672、1551685673、1551685674，事务本身的xid=1551685671。每次回滚到savepoint，都会分配新的xid。

索引，使用子事务的分线是，即使我们只有一个保存点，也会使用多个xid。不过只会对tps比较的系统才会造成影响，跟环境有关。

基于子事务多次尝试的另一个副作用是，相比整个事务尝试，前者对autovacuum清理死的元组影响更大。

总结一下,潜在的弊端是：1.浪费xid；2.影响autovacuum

话虽如此，我不能说我会建议每个人都完全不使用子事务。在部署DDL时，我仍然发现部分回滚以重试的想法有用且可行。但是，例如，如果你有一个系统10,000 TPS 和长时间运行的事务（例如，1 分钟或更长时间）的风险——我肯定会非常小心使用子事务。如果你有 PostgreSQL 13+，好消息是pg_stat_slru会很有帮助——如果你在standby上看到读Subtrans SLRU，这是一个强烈的信号，表明SLRU已溢出，并且standby服务器的性能下降（如果有的话）是由它引起的。

 

假设我们有一个只读负载库，且所有的读都是对test表，时间都是亚毫秒级别的。也就是说，平均是0.1ms。我们打开一个事务，执行对test表的select，并将该事务处于“idle in transaction”状态。现在我们开始做一些DDL操作，并将lock_time设置为50ms。对平均亚秒级别的只读查询会有什么影响呢？会被阻止50毫秒。

但是，如果我们每次尝试都加个delay呢？比如说，1秒，这样就只会有部分select被阻塞。

但是，如果我们在每次尝试后添加延迟(例如1秒),则只有部分SELECT会受到影响。粗略地说，我们将每秒引入一次“有压力的”锁定获取尝试，时间仅为50毫秒(更准确地说，这将每约1050毫秒发生一次)所以大部分SELECT根本不会受到影响，它们的平均持续时间只会略有上升。

这些延迟也有助于我们增加成功的机会。我们有有限的尝试次数，之后我们将事务中止，因此我们声明部署失败并将其推迟一段时间（或立即重复 - 取决于我们定义的策略）。

 

·对于负载高、或者有长时间运行的事务的系统，要谨慎执行DDL。建议使用一个较低的lock_timeout设置和重新尝试来优雅地执行DDL变更。

·对于子事务要小心使用。子事务会非常有用，但是：

--可能会突然引起性能问题。如果你使用了子事务，且是PostgreSQL13或更新的版本，可以使用pg_stat_slru来监控系统，并观测subtrans slru读

--如果包含子事务的事务持续很久，可能会导致表膨胀

 

https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries#on-the-risks-related-to-subtransactions