신규컬럼 추가 시 default value에 의한 lock 발생현상 및 회피요령

신규컬럼 추가 시 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