안녕하세요. 최근 신규 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에서는 발생하지 않던 문제라 너무 궁금해서 질문 올려봅니다.
제 무식함을 좀 깨우쳐 주세요. 미리 감사드립니다.
|