신규컬럼 추가 시 default value와 함께 DDL 구문을 수행할 경우 exclusive lock을 점유한 채 테이블 내 모든 컬럼을 rewrite 하므로 후속트랜젝션은 해당테이블 읽기/쓰기가 불가합니다.

# 예시를 위한 테이블 데이터 생성

create table tb_default_val_test as select x.id from generate_series(1,10000000) as x(id);

# default value구문으로 컬럼 추가

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           3232
(1 row)

postgres=# alter table tb_default_val_test drop column last_update;

# lock 확인

postgres=# select pid, mode from pg_locks 
            where relation = (select oid from pg_class where relname='tb_default_val_test');           
 pid  |        mode         
------+---------------------
 3232 | AccessExclusiveLock
(1 row)

피해갈 수 있는 가장 간단한 방법은 DDL 구문와 DML 구문으로 분리하여 실행하는 것입니다.

(온라인 상태에서 DDL 구문 실행TIP은 "여기"를 참조)

alter table tb_default_val_test add column last_update timestamptz;
ALTER TABLE

postgres=# update tb_default_val_test set last_update = now();

여기서 문제가 될 수 있는 또한가지는 아래처럼 테이블 전체 update 가 완료될때까지 DML 수행이 불가합니다.

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
           3327
(1 row)

postgres=# update tb_default_val_test set last_update = now() where id = 1;

# lock 조회 결과

postgres=# select pid, mode from pg_locks 
            where relation = (select oid from pg_class where relname='tb_default_val_test') order by pid;
 pid  |       mode       
------+------------------
 3232 | RowExclusiveLock
 3327 | RowExclusiveLock
 3327 | ExclusiveLock

이럴경우 배치로 후속트랜젝션에 대한 간섭을 최소화할 수 있습니다.

아래는 스크립트(Python / psycopg2) 예시 입니다.

updateStr = """ update TB_DEFAULT_VAL_TEST
                   set last_update = now()
                 where ID in (select ID from TB_DEFAULT_VAL_TEST
                               where last_update is null
                               limit 5000)"""
updatedRowCnt = 0
while(True):
        count = 1
        cur.execute(updateStr)
        print("{0} number of rows updated".format(str(cur.rowcount)))
        count += 1
        updatedRowCnt += cur.rowcount
        if cur.rowcount < 1:
                print("END : {0} number of rows updated".format(updatedRowCnt))
                break

postgreSQL 에서 모든 lock 요청이력은 큐로 관리되며, 순차적으로 처리됩니다.

예를들어 트랜젝션 A 가 특정테이블에 락을 확보한 상태에서 트랜젝션B가 상위수준의 LOCK 을 요청할 경우 A트랜젝션이 선점했던 락을 해제할때까지 대기하게 되는데 여기서 주의해야 할 점은 트랜젝션B에서 DDL을 실행할 경우 exclusive lock을 요청한 상태로 대기하게 되고  후속 트랜젝션C는 선행트렌젝션 B에 의한 LOCK이 해제될때 까지 읽기/쓰기가 제한됩니다.

아래는 예시입니다. ( 버젼 : 11.1 )

1. 시간 T1에 트랜잭션 A에서 SELECT를 수행

 postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          26965
(1 row)

postgres=# begin; --long term SELECT / begin 구문으로 상황 연출
BEGIN
postgres=# select * from items;
 item_no | item_name | last_update 
---------+-----------+-------------
(0 rows)

postgres=# select pid,mode from pg_locks where relation = (select oid from pg_class where relname='items');
  pid  |      mode       
-------+-----------------
 26965 | AccessShareLock
(1 row)

2. 시간 T2에 트랜젝션 B에서 DDL 을 수행

postgres=# alter table items add column last_update timestamptz;

-- 대기

3. 트랜젝션B에 의한 AcessExclusiveLock 대기

postgres=# select pid, mode from pg_locks where relation = (select oid from pg_class where relname='items');

-------+---------------------
 56617 | AccessExclusiveLock
 26915 | AccessShareLock
(2 rows)

4. 시간 T3에 트랜젝션C에서 SELECT 요청

postgres=# select * from items;-- 대기

5. 시간T1 에 트랜젝션A에서 수행했던 SELECT 문 처리 종료 시 트랜젝션B 와 C 가 순차적으로 처리

- 트랜잭션 A종료

postgres=# begin;
BEGIN
postgres=# select * from items;
 item_no | item_name 
---------+-----------
(0 rows)

postgres=# commit;
COMMIT
postgres=#

- 트랜잭션 B 종료

postgres=# alter table items add column last_update timestamptz;
ALTER TABLE
postgres=#

- 트랜젝션 C 종료

postgres=# select * from items;
---------+-----------+-------------
(0 rows)

postgres=#

결론적으로 선행 트랜젝션이 종료되지 않은 상태에서 access exclusive lock을 요청했을 경우 후행 트랜젝션 처리가 불가하며, 해당 테이블에 대한 읽기/쓰기가 제한되는 상황이 발생할 수 있습니다.

이런 상황을 방지하기 위해 특정시간(혹은 즉시)동안 lock확보에 실패할 경우 대기큐에서 강제로 소멸시키는 방법을 사용합니다.

1. T1시간에 트랜젝션 A에서 shared lock 점유

postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();

pg_backend_pid
----------------
56617
(1 row)

postgres=# select * from items;
 item_no | item_name | last_update
---------+-----------+-------------
(0 rows)

postgres=# select pid, mode from pg_locks where relation = (select oid from pg_class where relname='items');
  pid  |      mode       
-------+-----------------
 56617 | AccessShareLock
(1 row)

postgres=#

postgres=# set lock_timeout to '1ms';
SET
postgres=# alter table items drop column last_update;
ERROR:  canceling statement due to lock timeout -- lock_timeout 으로 강제종료
postgres=#

필자의 경우 해당 테이블에 선행되는  lock 이 없을때까지 1초에 펀칭해 줍니다.

log_min_duration_statement = 0 # 단위 : 1/1000초,  0 : 모든쿼리를 로깅
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u '

 $ ./pgbadger logfile_name
 $ ls -lrt
 -rw-r--r-- 1 postgres dba 1030734 2014-10-03 02:20 out.html