database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 10086 게시물 읽기
No. 10086
자식 table drop constraint 시 부모 table에 대한 relation lock 발생관련 문의입니다.
작성자
김은경(iskylove79)
작성일
2019-06-25 14:52
조회수
2,979

안녕하세요. 최근 신규 table을 생성을 하면서 발생한 relation lock 관련하여
살짝 이해가 안되는 부분이 있어 고수님들의 의견을 여쭤봅니다. 

- table 설명 
. 부모 table_name = p_table / 1천만 Row 존재 / dml 이 초당 1~100qps 존재
. 자식 table_name = c_table / 신규 table로 Row = 0 / dml 없음

- A session 
  . 트랜잭션을 열고 p_table select query 실행 
  . commit 을 하지 않아 pg_stat_activity.state = 'idle_in_transaction'으로 확인됨

- B session
  . 트랜잭션을 열고 신규 c_table을 create 함 ========================> 바로 생성 완료 
  . alter table c_table add constraint 로 p_table에 대한 fk 를 설정함.======> 바로 생성 완료
  . alter table c_table drop constraint 로 p_table에 대한 fk를 삭제함.======> pg_stat_activity.state = 'active' / wait_event_type = 'Lock' / wait_event = 'relation' 으로 대기함

- C session 
  . select p_table (B session에서 create table & add constraint를 할때까지는 정상적 select 가 가능함.)
  . select p_table (B session에서 drop constraint를 진행함 ========================> pg_stat_activity.state = 'active'/ wait_event_type = 'Lock' / wait_evetn = 'relation'으로 대기함)


[증상]
빈 table인 c_table에 add constraint를 할때는 발생하지 않던 relation lock 이 drop constraint를 할때 
p_table에 대한 select 만 존재하는 idle_in_transaction session 으로 relation lock이 발생을 하며 대기를 하였습니다. 
그러다가 A session의  idle_in_transaction이 commit 되자 마자 B/C Session의 relation lock이 해제 되었습니다. 
해당 상황을 재현 하고 pg_lock을 체크해보니 p_table에 아래처럼 lock 이 확인 되었습니다. 
- c_table add constraint시 : p_table shareRowExclusiveLock
- c_table drop constraint 시 : p_table ExclusiveLock
정리해보면 c_table drop constraint 시 p_table 에 ExclusiveLock 이 걸림으로 B session drop constraint 이후 세션은 모두 대기인것!!

[질문]
fk add 시 data 정합성 체크를 위해 RowExclusiveLock 획득은 그나 이해를 해볼수있을것같은데
fk drop 시는 메타 정보만 날리면서 관계를 끊으면 될것같은데 왜 p_table의 대한 ExclusiveLock을 획득하는걸까요?

mysql에서는 발생하지 않던 문제라 너무 궁금해서 질문 올려봅니다. 
제 무식함을 좀 깨우쳐 주세요. 미리 감사드립니다. 
 



 

 

이 글에 대한 댓글이 총 2건 있습니다.

아래,

http://database.sarang.net/index.php?inc=read&aid=10082&criteria=pgsql&subcrit=qna&id=&limit=20&keyword=&page=1

게시물에서 나눴던 이야기랑 연결되는 것 같네요.

 

alter table drop constraint  의 문제가 아니라,  pg의 트랜잭션 정책이라고 봐야할 듯합니다.

 

그래서, PostgreSQL에서 idle in transaction 세션의 관리가 중요합니다.

 

김상기(ioseph)님이 2019-06-26 00:44에 작성한 댓글입니다.

먼저 답변 너무 감사합니다.
링크 주신 URL 내용은 확인했습니다.
pg의 경우 DDL도 롤백이 가능함으로 select 트랜잭션에 대해서도 lock 정책이 하드하게 적용되는건 이해가 됩니다.
그런제 제가 문의 드린 case는 alter table add constraint 일 경우는 shareRowExclusive 이고
alter table drop constraint 일 경우면 Exclusive 인가에 대한 질문이였습니다.
alter 의 경우 트랜잭션 정책에 의해 Exclusive로 lock을 걸려야한다면 둘다 같아야하거나
아님 오히려 add 될때가 아닌가하는거죠.

제가 뭘 잘못생각하는걸까요?

김은경(iskylove79)님이 2019-06-27 19:51에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
10090pg_controldata로 보는 인스턴스 정보관련 질문입니다. [4]
김은경
2019-07-04
2807
10089pg_basebackup hang 문의 [1]
카비
2019-07-03
2706
10087Windows Server에 설치된 PostgreSQL 테이블스페이스 위치 변경 [1]
완전골치
2019-06-26
3197
10086자식 table drop constraint 시 부모 table에 대한 relation lock 발생관련 문의입니다. [2]
김은경
2019-06-25
2979
10084포스트그레 sql 튜닝용 힌트 [1]
오노
2019-06-19
3433
10083주소에 한단어나 두 단어 이상 검색 어떻게 해야되나요? [4]
이기자
2019-06-19
3046
10082[질문] 아래 질문 에 대한 재질의... [4]
이한철
2019-06-12
3222
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.052초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다