online DDL (or NOWAIT DDL) in PostgreSQL

最新推荐文章于 2024-06-05 06:00:27 发布
最新推荐文章于 2024-06-05 06:00:27 发布
阅读量1.8k 收藏 4
点赞数
分类专栏： 转载 数据库管理 文章标签： postgresql 数据库

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

  • Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
  • Postgres-XL的项目发起人Mason Sharp
  • pgpool的作者石井达夫(Tatsuo Ishii)
  • PG-Strom的作者海外浩平(Kaigai Kohei)
  • Greenplum研发总监姚延栋
  • 周正中(德哥), PostgreSQL中国用户会创始人之一
  • 汪洋，平安科技数据库技术部经理
  • ……
 
  • 2015年度PG大象会报名地址：http://postgres2015.eventdove.com/
  • PostgreSQL中国社区： http://postgres.cn/
  • PostgreSQL专业1群： 3336901（已满）
  • PostgreSQL专业2群： 100910388
  • PostgreSQL专业3群： 150657323



为什么需要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)。
所以 要解决无休止等待的问题，需要另辟蹊径。
方法也比较多：
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
个人还是推荐第一种方法，更方便，但是不要在全局参数中这么设置，只需要在当前维护操作的会话中设置。

其他：
1. 如果要实现DDL的nowait，这里的一些锁函数需要调整一下，当使用nowait时LockAcquire传入dontWait=true；
或者增加一个GUC参数，控制是否NOWAIT(不过感觉和lock timeout有点冲突，没有必要)。 

  
  

   
   LockRelationOid
  
  

  
  

   
   LockRelation
  
  

  
  

   
   LockRelationIdForSession
  
  

  
  

   
   LockRelationForExtension
  
  

  
  

   
   XactLockTableInsert
  
  

  
  

   
   XactLockTableWait
  
  

  
  

   
   SpeculativeInsertionLockAcquire
  
  

  
  

   
   SpeculativeInsertionWait
  
  

  
  

   
   LockDatabaseObject
  
  

  
  

   
   LockSharedObject
  
  

  
  

   
   LockSharedObjectForSession

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();

[参考]
2. src/include/pg_config_manual.h
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);
  
  

  
  

   
   }
Postgresql中国用户会
关注
专栏目录
Debezium系列之：使用Debezium采集PostgreSQL数据库14版本数据到Kafka Topic的详细步骤，支持PostgreSQL数据库分区表的数据采集
zhengzaifeidelushang的博客
08-14 96
Debezium系列之：使用Debezium采集PostgreSQL数据库14版本数据到Kafka Topic的详细步骤，支持PostgreSQL数据库分区表的数据采集
【PostgreSQL】PostgreSQL日常运维操作手册
tttzzzqqq2018的博客
11-15 711
【代码】PostgreSQL日常运维操作手册。
PGSQL-DDL
weixin_44324300的博客
09-13 319
记录一些常用的基于PGSQL的DDL语句
postgresql ddl说明
weixin_30337157的博客
03-29 246
检查隔离级别： 　　show transaction_isolation; 删除默认值： 　　alter table ${table_name} alter column ${column_name} drop default; 添加默认值： 　　alter table ${table_name} alter column ${column_name} set default ...
Postgresql - 执行DDL - 锁问题的避免
chuckchen1222的博客
11-18 876
加列，减列很快，几乎没影响 Version 10 alter table test01 add column col11 varchar(100) alter table test01 alter COLUMN col12 set default 'abc'; Version 11 alter table test01 add column col11 varchar(100) default...
对已有的大表做ddl操作
lijian965644856的博客
09-24 296
1，如加索引 通常在postgresql创建普通索引如下 create index idx_table_name_x1 on table_name(col_name); 在一次生产变更时，执行以上语句后在创建索引期间会阻塞 dml，如果是在比较繁忙的系统或者大表上执行该语句的话，估计很快就会有人问候你了。 所以应该使用 concurrently 参数： create index concurrently idx_table_name_x1 on table_name(col_name); 同样 .
PostgreSQL数据库锁机制——FastPathStrongRelationLocks简介
肥叔菌的博客
10-23 350
快路径强关闭表锁 static volatile FastPathStrongRelationLockData *FastPathStrongRelationLocks 快路径强关闭表锁，用来记录是否有事务已经获得这个对象的强锁。从锁的相容性矩阵可以看出，AccessShareLock、RowShareLock、RowExclusiveLock这3个锁是不互相冲突的，是相容的，而这几个锁主要用于事务的DML，因此可以把这几个锁定义为弱锁，而其他5个锁则定义为强锁。快路径强关闭表锁是一个FastPathStr
PostgreSQL
Littleluck_Dream的博客
05-27 747
PostgreSQL是一个功能强大的 **开源** 的关系型数据库。底层基于C实现。 PostgreSQL的开源协议和Linux内核版本的**开源**协议是一样的。BDS协议，这个协议基本和MIT开源协议一样，说人话就是你可以对PostgreSQL进行一些封装，然后商业化是收费。 PostgreSQL的名字咋来的。之前叫Ingres，后面为了解决一些ingres中的一些问题，作为后面的ingres，就起名叫postgre。
Postgresql安装
u010425095的博客
07-08 406
操作系统：CentOS7 Postgresql版本：10.10 端口：5432 备注：下文的安装步骤中，【】包围的部分需根据自己的环境来配置 1 源码安装   将安装包拷贝到服务器目录，并通过cd命令进入该目录，然后执行以下命令 tar -zxvf postgresql-10.10.tar.gz cd postgresql-10.10 ./configure make make install 2 创建用户、数据目录、日志归档目录并授权 adduser postgres mkdir -p /home/pg
PostgreSQL 10 - 理解事务和锁
weixin_43364172的博客
12-20 2500
PostgreSQL 10 - 理解事务和锁使用PostgreSQL事务处理事务内部的错误使用SAVEPOINTTransactional DDLs理解基本锁避免典型错误和明确地锁考虑替代方案使用FOR SHARE和FOR UPDATE理解事务隔离级别考虑SSI事务死锁和类似的问题应用advisory locks优化存储和管理清除配置VACUUM和autovacuum深入事务的wraparound...
postgreSql执行状态与锁冲突问题排查
welcome to zdsg's csdn
08-24 748
postgresql锁表问题解决
Sqlserver_Oracle_Mysql_Postgresql不同关系型数据库的select和ddl(alter,drop)是否互相堵塞的验证
lusklusklusk的博客
07-06 1296
所以这个过程会堵塞DML但是不堵塞select，如果DDL也不想堵塞DML，则就是需要使用online DDL，online DDL原理：先创建一张临时表，MySQL会自动把原表数据拷贝到临时表、再拷贝原表数据到临时表的过程中将所有对原表的DML操作记录在一个日志文件，再把日志文件中的数据写入到临时表，再交换表名、再删除旧表。select不堵塞DDL(0级锁不堵塞6级锁),DDL会堵塞select(6级锁堵塞0级锁，但是和传统理解中的写不堵塞读不是一个概念)会话2被堵塞，堵塞事件是LCK_M_SCH_M。
MySQL 到底能不能online ddl index PG VS MYSQL
最新发布
sql server的专栏
06-05 781
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2350人左右 1 + 2 + 3 + 4 +5 + 6）5群接近480 已经停止申请，新人将进入6群上周一篇关于MySQL 拜托...
PostgreSQL入门基本语法之DDL-(user、database、schema)
qq_39727113的博客
05-06 3311
目录 前言 1、用户对象操作 1.1新增用户 1.1.1 语法 1.1.2 常用例子 1.2修改用户 1.2.1 语法 1.2.2 常用例子 1.3删除用户 1.3.1 语法 1.3.2 常用例子 2.数据库对象操作 2.1创建数据库 2.1.1 语法 2.1.2 常用例子 2.2 修改数据库 2.2.1 语法 2.2.2 常用例子 2.3 删除数据库 ...
postgresql表中间加列_如何数据库表加列
weixin_36397256的博客
01-13 405
X-Engine 如何实现 Fast DDLX-Engine是阿里巴巴自研的存储引擎，作为阿里云 RDS MySQL 的一个可选引擎，除了主打高性能和低成本，还增加了不少惠及用户的新功能。本文将详细介绍 MySQL(X-Engine) 如何近乎瞬时完成传统数据库需要数小时完成的DDL操作。1.数据库DDL操作面临的问题互联网业务发展迅速...文章skin7782020-06-28226浏览量快人一...
在线修改表结构DDL
小小郭
10-29 436
表的数据比较大时，如果直接修改会阻塞表，影响业务数据。有两个工具可以实现： pt-online-schema-change 和 gh-ost 这次使用pt-online-schema-change 参考：https://segmentfault.com/a/1190000014924677 一.安装 进入官网选择好对应的版本，下载到服务器 https://www.percona.com/downl...
PostgreSQL数据库序列的DDL获取和细节处理
SUOYUEのBLOG
04-28 1233
PostgreSQL数据库序列的DDL获取和细节处理
PostgreSQL 各类生成建表sql语句DDL方法收集， 函数获取数据库大小
秋̶᭄ꦿ攻城狮࿆ྂ
08-17 3634
PostgreSQL生成建表语句 生成的sql指定scheme为：【sch_租户id】，不同scheme生成规则或者不需要指定scheme直接修改下相关代码即可使用。 CREATE OR REPLACE FUNCTION "public"."findattname"("dbinstancename" varchar, "namespace" varchar, "tablename" varchar, "ctype" varchar) RETURNS "pg_catalog"."varchar" AS $
PostgreSQL 并行vacuum patch - 暨为什么需要并行vacuum或分区表
weixin_33763244的博客
04-18 374
标签 PostgreSQL , vacuum , freeze , 分区表 , 并行vacuum 背景 我们之前做过一个这样的测试，单表数据从1000万到10亿，对其进行增删改查的压测，性能几乎没有衰减。 数据量 写入吞吐 查询tps 更新tps 1000万 58万行/s 67万 23.1万 1亿 53.2万行/s 63.4万 24.5...
alter table t optimize partition p0 有什么风险？是否 online ddl
03-25
A：此命令用于优化表的某个特定分区。执行此命令时，可能会发生数据丢失或数据...ALTER TABLE命令是DDL(数据定义语言)语句，所以不支持在线(ddl)操作，通常需要对表进行锁定，对于大表来说，可能会出现较长的停机时间。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值