online DDL (or NOWAIT DDL) in PostgreSQL
背景
为什么需要online DDL呢？或者nowait DDL呢？
PostgreSQL为了保证一个请求不至于无休止的等待锁，锁等待使用了队列机制，并且这个在等待队列中的锁也会和以后的会话将要请求的锁发生冲突。
虽然解决了无休止的等待问题，同时也带来一个比较棘手的问题。
例如，
当数据库中存在一个长事务时，如果你要对这个长事务持锁（假设是一个很小的锁）的对象执行DDL（获取AccessExclusive LOCK)，会进入等待，而后面发起的对这个对象的任何SQL都会等待，即会堵塞未来的SQL请求。
这种事情在高并发小事务应用的场景非常容易造成堵塞故障。
例子
postgres=# create table t1(id int);
CREATE TABLE
会话A：
postgres=# begin;
BEGIN
postgres=# insert into t1 values (1);
INSERT 0 1
会话B：
postgres=# alter table t1 add column info text;
waiting......
会话C，查看锁等待的情况：
postgres=# create or replace function f_lock_level(i_mode text) returns int as $$
declare
begin
case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
end case;
end;
$$ language plpgsql strict;
postgres=# with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.transactionid is not distinct from w.transactionid and
r.pid <> w.pid
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
锁等待如下，会话B请求的锁与会话A持有的锁发生冲突，等待。
-[ RECORD 1 ]-+-------------------------------------
locktype | relation
r_mode | RowExclusiveLock
r_user | postgres
r_db | postgres
relation | t1
r_pid | 16927
r_page |
r_tuple |
r_xact_start | 2015-06-18 15:30:07.578806+08
r_query_start | 2015-06-18 15:30:12.988851+08
r_locktime | 00:07:22.879003
r_query | insert into t1 values (1);
w_mode | AccessExclusiveLock
w_pid | 16881
w_page |
w_tuple |
w_xact_start | 2015-06-18 15:30:26.749872+08
w_query_start | 2015-06-18 15:30:26.749872+08
w_locktime | 00:07:09.117982
w_query | alter table t1 add column info text;
会话D：
此时，虽然会话B没有获得AccessExclusive锁，但是，PostgreSQL将它放入队列了，并且未来的SQL请求也要判断请求的锁是否会与之发生冲突。
postgres=# insert into t1 values (1);
waiting ......
查看锁信息，此时会话D也在等待中，只是这里通过视图看到的等待并不是等待会话B的锁。
我们可以使用trace lock来跟踪。
-[ RECORD 1 ]-+-------------------------------------
locktype | relation
r_mode | RowExclusiveLock
r_user | postgres
r_db | postgres
relation | t1
r_pid | 16927
r_page |
r_tuple |
r_xact_start | 2015-06-18 15:30:07.578806+08
r_query_start | 2015-06-18 15:30:12.988851+08
r_locktime | 00:08:47.723949
r_query | insert into t1 values (1);
w_mode | AccessExclusiveLock
w_pid | 16881
w_page |
w_tuple |
w_xact_start | 2015-06-18 15:30:26.749872+08
w_query_start | 2015-06-18 15:30:26.749872+08
w_locktime | 00:08:33.962928
w_query | alter table t1 add column info text;
-[ RECORD 2 ]-+-------------------------------------
locktype | relation
r_mode | RowExclusiveLock
r_user | postgres
r_db | postgres
relation | t1
r_pid | 16927
r_page |
r_tuple |
r_xact_start | 2015-06-18 15:30:07.578806+08
r_query_start | 2015-06-18 15:30:12.988851+08
r_locktime | 00:08:47.723949
r_query | insert into t1 values (1);
w_mode | RowExclusiveLock
w_pid | 17205
w_page |
w_tuple |
w_xact_start | 2015-06-18 15:38:41.204786+08
w_query_start | 2015-06-18 15:38:41.204786+08
w_locktime | 00:00:19.508014
w_query | insert into t1 values (1);
接下来我们打开锁DEBUG，需要修改一下头文件，打开LOCK_DEBUG宏定义，重新编译安装：
# vi src/include/pg_config_manual.h
/*
* Enable debugging print statements for lock-related operations.
*/
#define LOCK_DEBUG
# gmake distclean
# ./configure --prefix=/opt/pgsql9.5 --with-pgport=1922 --with-perl --with-python --with-tcl --with-openssl --with-pam --with-ldap --with-libxml --with-libxslt --enable-thread-safety --with-blocksize=32 --enable-debug
# gmake && gmake install
在postgresql.conf参数中开启锁跟踪
$ vi postgresql.conf
trace_locks = on
trace_userlocks = on
debug_deadlocks = on
重启数据库
$ pg_ctl restart -m fast
修改客户端的日志跟踪级别，同时开启代码定位：
postgres=# set client_min_messages=log;
postgres=# \set VERBOSITY verbose
会话A，测试一下锁获取和释放：
postgres=# begin;
BEGIN
postgres=# alter table t1 add column c1 int;
LOG: 00000: LockAcquire: lock [13181,16903] AccessExclusiveLock
LOCATION: LockAcquireExtended, lock.c:724
LOG: 00000: LockAcquire: new: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: new: proclock(0x7f79f82739a8) lock(0x7f79f1a620c8) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: LockCheckConflicts: no conflict: proclock(0x7f79f82739a8) lock(0x7f79f1a620c8) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: GrantLock: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=1 grant(0,0,0,0,0,0,0)=1 wait(0) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: lock [13181,16903] AccessExclusiveLock
LOCATION: LockAcquireExtended, lock.c:724
ALTER TABLE
postgres=# rollback;
LOG: 00000: LockReleaseAll: lockmethod=1
LOCATION: LockReleaseAll, lock.c:1949
LOG: 00000: LockReleaseAll: proclock(0x7f79f82739a8) lock(0x7f79f1a620c8) method(1) proc(0x7f7a0fec71b8) hold(100)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: LockReleaseAll: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=1 grant(0,0,0,0,0,0,0)=1 wait(0) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: UnGrantLock: updated: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: UnGrantLock: updated: proclock(0x7f79f82739a8) lock(0x7f79f1a620c8) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: LockReleaseAll: updated: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: CleanUpLock: deleting: proclock(0x7f79f82739a8) lock(0x7f79f1a620c8) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: CleanUpLock: deleting: lock(0x7f79f1a620c8) id(13181,16903,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockReleaseAll done
LOCATION: LockReleaseAll, lock.c:2196
LOG: 00000: LockReleaseAll: lockmethod=2
LOCATION: LockReleaseAll, lock.c:1949
LOG: 00000: LockReleaseAll done
LOCATION: LockReleaseAll, lock.c:2196
ROLLBACK
会话A：
postgres=# begin;
BEGIN
postgres=# insert into t1 values (1);
INSERT 0 1
会话B：
执行DDL，等待。
输出的含义，请参考对应的代码，已经输出了代码对应的位置。
postgres=# begin;
BEGIN
postgres=# alter table t1 add column c1 int;
LOG: 00000: LockAcquire: lock [13181,16903] AccessExclusiveLock -- 请求AEL锁
LOCATION: LockAcquireExtended, lock.c:724
LOG: 00000: LockAcquire: new: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(RowExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: new: proclock(0x7f79f8273960) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6ed0) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: GrantLock: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=1 grant(0,0,1,0,0,0,0)=1 wait(0) type(RowExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: found: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=1 grant(0,0,1,0,0,0,0)=1 wait(0) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: new: proclock(0x7f79f8273918) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: LockCheckConflicts: conflicting: proclock(0x7f79f8273918) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: WaitOnLock: sleeping on lock: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=2 grant(0,0,1,0,0,0,0)=1 wait(0) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: waiting on: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=2 grant(0,0,1,0,0,0,0)=1 wait(1) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: proclock(0x7f79f8273960) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6ed0) hold(8)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: DumpAllLocks: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=2 grant(0,0,1,0,0,0,0)=1 wait(1) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: proclock(0x7f79f8273918) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec71b8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: DumpAllLocks: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(8) req(0,0,1,0,0,0,0)=2 grant(0,0,1,0,0,0,0)=1 wait(1) type(INVALID)
LOCATION: LOCK_PRINT, lock.c:319
会话C：
执行DML，等待，这里可以清晰的看到等待的对象
postgres=# insert into t1 values (1);
LOG: 00000: LockAcquire: lock [13181,16903] RowExclusiveLock
LINE 1: insert into t1 values (1);
^
LOCATION: LockAcquireExtended, lock.c:724
LOG: 00000: LockAcquire: found: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,1,0,0,0,0)=3 grant(0,0,1,0,0,0,0)=2 wait(1) type(RowExclusiveLock)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: new: proclock(0x7f79f82737b0) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6be8) hold(0)
LINE 1: insert into t1 values (1);
^
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: WaitOnLock: sleeping on lock: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,2,0,0,0,0)=4 grant(0,0,1,0,0,0,0)=2 wait(1) type(RowExclusiveLock)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: waiting on: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,2,0,0,0,0)=4 grant(0,0,1,0,0,0,0)=2 wait(2) type(INVALID)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: proclock(0x7f79f82737b0) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6be8) hold(0)
LINE 1: insert into t1 values (1);
^
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: DumpAllLocks: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,2,0,0,0,0)=4 grant(0,0,1,0,0,0,0)=2 wait(2) type(INVALID)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: proclock(0x7f79f8273960) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6ed0) hold(108)
LINE 1: insert into t1 values (1);
^
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: DumpAllLocks: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,2,0,0,0,0)=4 grant(0,0,1,0,0,0,0)=2 wait(2) type(INVALID)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: DumpAllLocks: proclock(0x7f79f8273918) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec71b8) hold(0)
LINE 1: insert into t1 values (1);
^
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: DumpAllLocks: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,2,0,0,0,0)=4 grant(0,0,1,0,0,0,0)=2 wait(2) type(INVALID)
LINE 1: insert into t1 values (1);
^
LOCATION: LOCK_PRINT, lock.c:319
会话D：
使用LOCK 命令，同时使用 NOWAIT参数：
postgres=# begin;
BEGIN
postgres=# lock table t1 in access exclusive mode nowait;
LOG: 00000: LockAcquire: lock [13181,16903] AccessExclusiveLock
LOCATION: LockAcquireExtended, lock.c:724
LOG: 00000: LockAcquire: found: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,1,0,0,0,0)=3 grant(0,0,1,0,0,0,0)=2 wait(1) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockAcquire: new: proclock(0x7f79f82737b0) lock(0x7f79f1a62020) method(1) proc(0x7f7a0fec6be8) hold(0)
LOCATION: PROCLOCK_PRINT, lock.c:331
LOG: 00000: LockAcquire: conditional lock failed: lock(0x7f79f1a62020) id(13181,16903,0,0,0,1) grantMask(108) req(0,0,1,0,0,0,0)=3 grant(0,0,1,0,0,0,0)=2 wait(1) type(AccessExclusiveLock)
LOCATION: LOCK_PRINT, lock.c:319
LOG: 00000: LockReleaseAll: lockmethod=1
LOCATION: LockReleaseAll, lock.c:1949
LOG: 00000: LockReleaseAll done
LOCATION: LockReleaseAll, lock.c:2196
LOG: 00000: LockReleaseAll: lockmethod=2
LOCATION: LockReleaseAll, lock.c:1949
LOG: 00000: LockReleaseAll done
LOCATION: LockReleaseAll, lock.c:2196
ERROR: 55P03: could not obtain lock on relation "t1"
LOCATION: RangeVarGetRelidExtended, namespace.c:391
因为目前PostgreSQL DDL 语法没有NOWAIT选项，（除了set tablespace)。
所以要解决无休止等待的问题，需要另辟蹊径。
DDL NOWAIT方法
方法也比较多：
1. 使用lock_timeout
在执行DDL前，设置本地会话的锁超时为1毫秒。那么影响也只有1毫秒。
postgres=# set lock_timeout='1ms';
SET
postgres=# alter table t1 add column cc int; -- 等待1毫秒后如果无法获得锁，立即返回。
ERROR: canceling statement due to lock timeout
2. 在事务中叠加lock nowait命令,不需要执行下面的DDL。
postgres=# \h lock
Command: LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
例如,
postgres=# begin;
BEGIN
postgres=# lock table t1 in access exclusive mode nowait; -- 如果获得锁失败了，不需要执行下面的DDL。
LOCK TABLE
postgres=# alter table t1 add column cc1 int;
ALTER TABLE
postgres=# end;
COMMIT
个人还是推荐第一种方法，更方便，但是不要在全局参数中这么设置，只需要在当前维护操作的会话中设置。
3. 其他：
3.1. 如果要实现DDL的nowait，这里的一些锁函数需要调整一下，当使用nowait时LockAcquire传入dontWait=true；
或者增加一个GUC参数，控制是否NOWAIT(不过感觉和lock timeout有点冲突，没有必要)。
LockRelationOid
LockRelation
LockRelationIdForSession
LockRelationForExtension
XactLockTableInsert
XactLockTableWait
SpeculativeInsertionLockAcquire
SpeculativeInsertionWait
LockDatabaseObject
LockSharedObject
LockSharedObjectForSession
3.2. 事件触发器行么？– 不行，因为进入等待后你不能自己检测自己的状态。
例如，这样是行不通的。
CREATE OR REPLACE FUNCTION online_ddl_command()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
raise notice 'event trigger';
perform 1 from pg_stat_activity where pid=pg_backend_pid() and waiting;
if found then
RAISE EXCEPTION 'command % is waiting lock, I must abort it.', tg_tag;
end if;
END;
$$;
CREATE EVENT TRIGGER online_ddl ON ddl_command_start
EXECUTE PROCEDURE online_ddl_command();
参考
1. http://blog.163.com/digoal@126/blog/static/163877040201422083228624
2. src/include/pg_config_manual.h
3. http://www.postgresql.org/docs/devel/static/runtime-config-developer.html
4. http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid
5. src/backend/storage/lmgr/lock.c
如果要使用nowait, 在调用LockAcquire 时，dontWait传入true。
/*
* LockAcquire -- Check for lock conflicts, sleep if conflict found,
* set lock if/when no conflicts.
*
* Inputs:
* locktag: unique identifier for the lockable object
* lockmode: lock mode to acquire
* sessionLock: if true, acquire lock for session not current transaction
* dontWait: if true, don't wait to acquire lock
*
* Returns one of:
* LOCKACQUIRE_NOT_AVAIL lock not available, and dontWait=true
* LOCKACQUIRE_OK lock successfully acquired
* LOCKACQUIRE_ALREADY_HELD incremented count for lock already held
*
* In the normal case where dontWait=false and the caller doesn't need to
* distinguish a freshly acquired lock from one already taken earlier in
* this same transaction, there is no need to examine the return value.
*
* Side Effects: The lock is acquired and recorded in lock tables.
*
* NOTE: if we wait for the lock, there is no way to abort the wait
* short of aborting the transaction.
*/
LockAcquireResult
LockAcquire(const LOCKTAG *locktag,
LOCKMODE lockmode,
bool sessionLock,
bool dontWait)
{
return LockAcquireExtended(locktag, lockmode, sessionLock, dontWait, true);
}