왜 테이블 생성은 잘 되는데, 외래 키 추가는 무한로딩이죠?

Metadata lock 때문이었다....

 

새로운 테이블들을 생성하는 쿼리들과, 기존 테이블과 외래 키를 연결하는 쿼리들을 실행하고 있는데

유독 외래 키 추가만 무한로딩 걸리는 상황이 발생했다

 

member 테이블은 원래 있던놈이고

post는 새로 생긴 놈이다

 

	ALTER TABLE post
    	ADD CONSTRAINT fk_post_member_id
        FOREIGN KEY (member_id) REFERENCES member(member_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

 

 

처음엔 테스트 서버로 쓰는 라즈베리 파이에 너무 띄워진 서버가 많아서 힘들어하는 줄 알았다;;

근데 AWS 메인 서버에도 똑같이 무한로딩이 걸리는 것이었다.

 

무한로딩을 기다리고 있었는데

???: 회원가입 api 무한로딩 걸려

 

구글링 해가지고 db에 show processlist; 쿼리를 때려보았는데

waiting for table metadata lock이 세개다.

외래 키를 연결하는 세션을 끄니까 회원가입 잘 된다...

 

 

메타데이터 락(Metadata Lock)은 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나

구조를 변경하는 경우에 획득하는 잠금이다. - 리마큐

 

일반적으로 메타데이터 락은 테이블 단위로 걸린다 - GPT

 

아니 뭐가 메타데이터 락을 가지고 있는데~하면서 조회해봤는데 아무것도 안나왔다.

 

그래서 지선생한테 도움 요청했는데 천재였다.

 

 

2번 정황을 살펴보려고 아래 쿼리로 Running중인 트랜잭션을 조회해보니까 60분째 실행중인 트랜잭션이 있었다;;

	SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id, trx_query
		FROM information_schema.INNODB_TRX;

 

대충 어떤 서버의 실행시각과 일치하는 것을 파악해서 해당 서버의 코드를 찾아내 고쳤다.

서버가 실행될때부터 db 커넥션을 물고있던 것이었다.....

 

 

정리

테이블에 외래 키를 연결하는 DDL 작업이 요청되었다.

하지만, 이미 해당 테이블에서 진행 중인 긴 트랜잭션이 있었다.

이미 있던 트랜잭션으로 인해, MySQL은 메타데이터 락을 즉시 획득할 수 없기 때문에 DDL 작업이 대기 상태에 들어갔다.

이후에 들어온 테이블에 대한 DML 작업(Insert)도 메타데이터 락을 기다리며 대기 상태에 들어갔다.

MySQL은 데이터베이스의 일관성을 유지하기 위해 DDL 작업(테이블 구조 변경)을 우선적으로 처리해야만 DML 작업(데이터 삽입, 수정, 삭제)을 안전하게 처리할 수 있다.

이 때문에 DDL 작업이 완료되기 전까지는 DML 작업도 무한 로딩처럼 보이는 대기 상태에 빠지게 되었다.