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

POSTGRESQL 2018. 12. 3. 10:11

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


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

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


# default value구문으로 컬럼 추가

?
1
2
3
4
5
6
7
postgres=# select pg_backend_pid();
 pg_backend_pid
----------------
           3232
(1 row)
 
postgres=# alter table tb_default_val_test drop column last_update;


# lock 확인

?
1
2
3
4
5
6
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은 "여기"를 참조)

?
1
2
3
4
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 수행이 불가합니다.

?
1
2
3
4
5
6
7
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 조회 결과

?
1
2
3
4
5
6
7
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) 예시 입니다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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' 카테고리의 다른 글

gdb를 통한 postgresql 소스 디버깅  (1) 2018.12.05
replication 자동구성 스크립트(테스트 only)  (0) 2018.12.04
DDL 구문으로 인한 테이블 읽기/쓰기 lock 발생현상과 회피요령  (0) 2018.11.29
pgbadger 로그파일로부터 SQL 정보 수집하기  (0) 2015.06.24
JDBC 테스트 소스  (0) 2015.06.24
,

DDL 구문으로 인한 테이블 읽기/쓰기 lock 발생현상과 회피요령

POSTGRESQL 2018. 11. 29. 16:23

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 을 수행

?
1
2
3
postgres=# alter table items add column last_update timestamptz;
 
-- 대기

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

?
1
2
3
4
5
postgres=# select pid, mode from pg_locks where relation = (select oid from pg_class where relname='items');<p></p>
-------+---------------------
 56617 | AccessExclusiveLock
 26915 | AccessShareLock
(2 rows)<p></p>

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

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

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

- 트랜잭션 A종료

?
1
2
3
4
5
6
7
8
9
10
postgres=# begin;
BEGIN
postgres=# select * from items;
 item_no | item_name 
---------+-----------
(0 rows)
 
postgres=# commit;
COMMIT
postgres=#

- 트랜잭션 B 종료

?
1
2
3
postgres=# alter table items add column last_update timestamptz;
ALTER TABLE
postgres=#

- 트랜젝션 C 종료

?
1
2
3
4
5
postgres=# select * from items;
---------+-----------+-------------
(0 rows)
 
postgres=#

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
postgres=# begin;
BEGIN
postgres=# select pg_backend_pid();<p></p>
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=#
2. T2 시간에 트랜젝션 B에서 lock_timeout 을 1ms로 설정 후 DDL을 수행
?
1
2
3
4
5
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초에 펀칭해 줍니다.


저작자표시

'POSTGRESQL' 카테고리의 다른 글

replication 자동구성 스크립트(테스트 only)  (0) 2018.12.04
신규컬럼 추가 시 default value에 의한 lock 발생현상 및 회피요령  (0) 2018.12.03
pgbadger 로그파일로부터 SQL 정보 수집하기  (0) 2015.06.24
JDBC 테스트 소스  (0) 2015.06.24
pg_trgm 을 이용한 전후위 like 검색  (0) 2015.04.21
,

pgbadger 로그파일로부터 SQL 정보 수집하기

POSTGRESQL 2015. 6. 24. 15:19

pgbadger를 이용하여 로그파일로부터 SQL정보를 수집할 수 있습니다. postgresql.conf 파일상의 로깅관련 설정값을 변경해야 합니다..

 

1. pgbadger 다운로드합니다.

?
1
$ git clone https://github.com/dalibo/pgbadger.git

 

2. postgresql.conf 파일에서 로깅관련 파라메터 수정 후 reload 합니다.
?
1
2
3
4
5
6
7
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를 실행할 수 있습니다.
?
1
2
3
$ ./pgbadger logfile_name
$ ls -lrt
-rw-r--r-- 1 postgres dba 1030734 2014-10-03 02:20 out.html
저작자표시

'POSTGRESQL' 카테고리의 다른 글

신규컬럼 추가 시 default value에 의한 lock 발생현상 및 회피요령  (0) 2018.12.03
DDL 구문으로 인한 테이블 읽기/쓰기 lock 발생현상과 회피요령  (0) 2018.11.29
JDBC 테스트 소스  (0) 2015.06.24
pg_trgm 을 이용한 전후위 like 검색  (0) 2015.04.21
prepare statements 와 casting  (0) 2015.04.21
,
1 2 3 4 5 6 ··· 10

티스토리툴바