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

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=#

2. T2 시간에 트랜젝션 B에서 lock_timeout 을 1ms로 설정 후 DDL을 수행

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초에 펀칭해 줍니다.