Postgresql Table Lock 테스트 방법

PostgreSQL은 sql을 모두 transaction 내에서 실행시킨다.

• transaction block : BEGIN 명령으로 시작하여 COMMIT 명령(또는 ROLLBACK)으로 끝나는 그룹

LOCK 명령은 Transaction block 안에서만 사용할 수 있다.

아래와 같이 BEGIN 명령 없이 LOCK을 사용하면 error가 발생한다.

 

테이블 LOCK 걸기 (ACCESS EXCLUSIVE)

테이블 LOCK 걸기 (ACCESS EXCLUSIVE)

insight_gateway=# BEGIN WORK;
BEGIN
insight_gateway=# LOCK TABLE public.tasks IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
 
--  LOCK 적용 중. table lock 테스트 진행하면 된다.
 
insight_gateway=# COMMIT WORK;
COMMIT
 
-- transaction block이 끝났으므로 LOCK 해제 됨

 

테이블 LOCK 종류

💡 select lock 테스트 시에는 access exclusive를, update, delete, insert lock 테스트 시에는 row exclusive를 사용한다.

1. ACCESS SHARE
   1. ACCESS EXCLUSIVE 락 모드와 충돌남
   2. SELECT 명령어는 참조하는 테이블에 이 모드의 락을 획득합니다. 일반적으로, 테이블에서 수정은 하지 않고 오직 읽기만을 하는 모든 쿼리들은 이 락 모드를 획득합니다.
2. ROW SHARE
   1. 이것은 EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.
   2. SELECT FOR UPDATE 와 SELECT FOR SHARE 명령어들은 대상 테이블에서 이 모드의 락을 획득합니다.
   3. (추가적으로 ACCESS SHARE 는 selected FOR UPDATE/FOR SHARE 가 아닌 참조되어지는 다른 모든 테이블을 락 합니다. )
3. ROW EXCLUSIVE
   1. 이것은 SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE 와 ACCESS EXCLUSIVE 락 모드와 상충됩니다.
   2. UPDATE, DELETE, INSERT 명령어는 대상 테이블에서 이 락 모드를 획득합니다. (추가적으로 ACCESS SHARE 는 다른 참조되어지는 모든 테이블을 락 합니다. 일반적으로, 이 모드는 테이블에서 데이터를 수정하는 모든 명령어들에 의해서 이 모드는 획득되어 집니다.
4. SHARE UPDATE EXCLUSIVE
   1. SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 VACUUM 실행과 동시적 스키마 변화에 대해서 테이블을 보호합니다.
   2. VACUUM( FULL 제외), ANALYZE, CREATE INDEX CONCURRENTLY, 그리고 특정한 ALTER TABLE 폼에 의해서 획득됩니다.
5. SHARE
   1. ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE 그리고 ACCESS EXCLUSIVE 락 모드와 상충됩니다. 이 모드는 동시적으로 데이터를 변경하는 것에 대해 테이블을 보호 합니다.
   2. CREATE INDEX(CONCURRENTLY 제외) 에 의해서 획득되어 집니다.
6. SHARE ROW EXCLUSIVE
   1. ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 동시적 데이터 변경과 특정시점에서 한 세션이 그것을 가지고 있기위해서 자체 exclusive(self-exclusive) 한 것에 대해서 테이블을 보호 합니다.
   2. 이 락 모드는 PostgreSQL의 어떤 명령어로도 자동적으로 획득되어지지 않습니다.
7. EXCLUSIVE
   1. ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 락 모드와 상충 됩니다. 이 모드는 오직 동시적 ACCESS SHARE 락만을 허용합니다. 예를들어, 테이블로부터 읽을때에 한 트랜잭션이 이 락 모드로 홀딩되면 다중으로 실행을 할수 있습니다.
   2. 이 락 모드는 PostgreSQL의 어떤 명령어로도 테이블에 자동적으로 획득되어지지 않습니다.
8. ACCESS EXCLUSIVE
   1. 모든 모드의 락과 상충됩니다. 이 모드는 어떤 방법으로든 트랜잭션이 테이블에 접근하는 것을 보장합니다.
   2. ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 그리고 VACUUM FULL 명령어에 의해서 획득되어 집니다. 또, 이 모드는 LOCK TABLE 구문에 대한 기본적인 락 모드 입니다.
   3. Tip: 오직 ACCESS EXCLUSIVE 는 SELECT 구문을 블럭하는 락입니다.
   4. 한번 획득되어진 락은 트랜잭션이 끝날때가지 들고 있습니다. 하지만 만약에 저장시점이 설립된 이후에 락이 획득되어진 것이라면, 저장시점이 롤백되는 즉시 해제 됩니다.

---

참고 

 

https://www.postgresql.org/docs/9.4/sql-lock.html

https://www.postgresql.org/docs/9.1/sql-begin.html