执行ALTER TABLE语句时如何避免长时间阻塞并发查询

yzsDBA

发布于 2020-10-28 09:40:13

2.4K0

发布于 2020-10-28 09:40:13

文章被收录于专栏：PostgreSQL研究与原理解析

最近看到这样的案例：

1、应用需要为现有的表添加列

2、应用执行ALTER TABLE ADD COLUMN语句

3、其他每个查询都需要被阻塞几分钟甚至更长时间

为什么出现这种情况？如果避免？

首先看下执行ALTER TABLE ADD COLUMN时发生了什么？

# ALTER TABLE test ADD COLUMN whatever int4;
ALTER TABLE
TIME: 12.662 ms

可以看到该语句执行的非常快，在看下alter table获取的锁：

=# BEGIN;
BEGIN
 
=# ALTER TABLE test ADD COLUMN whatever2 int4;
ALTER TABLE
 
=# SELECT * FROM pg_locks WHERE pid = pg_backend_pid();
   locktype    | DATABASE | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | GRANTED | fastpath 
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------
 relation      |   165725 |    12143 |      |       |            |               |         |       |          | 3/2594             | 32470 | AccessShareLock     | t       | t
 virtualxid    |          |          |      |       | 3/2594     |               |         |       |          | 3/2594             | 32470 | ExclusiveLock       | t       | t
 transactionid |          |          |      |       |            |          1422 |         |       |          | 3/2594             | 32470 | ExclusiveLock       | t       | f
 relation      |   165725 |   166142 |      |       |            |               |         |       |          | 3/2594             | 32470 | AccessExclusiveLock | t       | f
(4 ROWS)
 
=# ROLLBACK;
ROLLBACK

需要注意，有一个AccessExclusiveLock，直到事务提交或者回滚才能释放，此间会锁表。

但是alter table add column只花费12ms，哪来的几分钟？这种事情发生在有其他查询在这个表上，然后在执行alter table，alter table需要等待之前的锁释放：

(SESSION 1) =# BEGIN;
BEGIN
 
(SESSION 1) =# SELECT COUNT(*) FROM test;
 COUNT 
-------
     0
(1 ROW)

会话1不关闭，同时不用关闭事务。

(SESSION 2) =# ALTER TABLE test ADD COLUMN whatever2 int4;

会话2执行alter 语句时由于需要等待会话1释放锁被阻塞，但是他已经获取这个表上的AccessExclusiveLock了，其他select不能执行了。

(SESSION 3) =# depesz=# SELECT * FROM test LIMIT 1；

会话3再执行select被hang住。

那么，是否存在这样的语句，执行添加列时不申请长时间锁表的锁？pg_reorg/pg_repack。

首先设置事务超时时间，然后执行alter table语句：

=$ printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n"
SET statement_timeout = 50;
ALTER TABLE test add column whatever2 INT4;

超时时间保证alter table语句执行不超过50毫秒，然后通过psql执行：

=$ time printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n" | psql -qX
ERROR:  canceling statement due to statement timeout
 
real    0m0.054s
user    0m0.000s
sys     0m0.002s
 
=$ echo $?
0

语句执行很快失败，但是返回结果是0，标记成功了，我们需要修改下：

=$ time printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n" | psql -qX -v ON_ERROR_STOP=1
ERROR:  canceling statement due to statement timeout
 
real    0m0.054s
user    0m0.002s
sys     0m0.000s
 
=$ echo $?
3

这样就合理了：

=$ printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n" > alter.sql

然后：

=$ while true; do date; psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break; sleep 1; done
Thu 26 Sep 2019 03:43:52 PM CEST
psql:alter.sql:2: ERROR:  canceling statement due to statement timeout
Thu 26 Sep 2019 03:43:53 PM CEST
psql:alter.sql:2: ERROR:  canceling statement due to statement timeout
Thu 26 Sep 2019 03:43:54 PM CEST
psql:alter.sql:2: ERROR:  canceling statement due to statement timeout
Thu 26 Sep 2019 03:43:55 PM CEST
psql:alter.sql:2: ERROR:  canceling statement due to statement timeout
Thu 26 Sep 2019 03:43:56 PM CEST
 
=$

While循环：

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

可以看到开始时间和结束时间。

需要注意，如果想对这个表进行更多操作，需要使用事务：

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;

但是需要注意，一旦获取了这个锁，其他链接就不能使用这个表了。

原文：

https://www.depesz.com/2019/09/26/how-to-run-short-alter-table-without-long-locking-concurrent-queries/

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-10-06，如有侵权请联系 cloudcommunity@tencent.com 删除

access