用户抱怨当使用alter table add column…或者truncate table …时,异常缓慢,甚至直接hang在那里,session居高不下,用户便也无法完成一些操作, 猜测可能的远因是因为在高并发的环境下,被异动的表上,之前已经发生了一些其他查询或操作,然后 alter table 或者truncate table 必须等待获得lock,才能继续
我们模拟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——这是独占锁,直到锁消失前,其他session将不能在这个table上执行任何操作,在这个例子中是——直到与 alter table 的事务完成
接下来,我们开启一个事务(session 1),并执行test表上的select 操作,不关闭事务
(session 1) =# begin;
BEGIN
(session 1) =# select count(*) from test;
count
-------
0
(1 row)
开启另一个事务(session 2),然后执行alter table 操作
(session 2) =# alter table test add column whatever2 int4;
session 2卡住,因为他等候从session1获得lock,但是它已经获得test table的AccessExclusiveLock,因此它也阻塞随后其他的针对test table的操作,包含select操作
(session 3) =# depesz=# select * from test limit 1;
session 3也卡住了
怎么办? 解套的方法将是设计一个loop
1.建立一个sql脚本文件,供后续loop调用
=$ printf "SET statement_timeout = 50;\nALTER TABLE test add column whatever2 INT4;\n" > alter.sql
set statement_timeout =50 将确保sql语句在50 millisecond内完成alter table操作,否怎中止执行
接下来,设计一个loop,调用alter.sql文件,
while true
do
date
psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break
sleep 1
done
loop开始后即开始执行alter.sql文件,首先set statement_timeout=50,随后执行alter table指令,如果50毫秒内没有成功获得lock去执行alter table,即停止执行,然后sleep 1秒后再次loop,知道成功alter table
显然,添加该列现在可能需要很长时间——但在任何时间点,其他查询都不会被锁定超过 50 毫秒