Home » SQL » POSTGRES » postgresql lock 확인하는 방법을 알아봅시다.

postgresql lock 확인하는 방법을 알아봅시다.

마이그레이션을 하다 보면, 의도치 않게 시간이 오래 걸리는 경우가 있습니다. 혹은 alter를 하려는데, 계속 로딩이 걸리고 있거나. 이 경우 저는 postgresql lock 때문에 안 될 수도 있다는 의심을 합니다. 제가 이 과정에서 확인하는 테이블들은 아래와 같습니다.

  • pg_stat_activity
  • pg_locks

이 글에서는 간단하게 fastapi 프로그램으로 lock을 계속 잡고 있는 상황을 재현해 보겠습니다. 그리고, 해당 lock을 어떻게 보는지 알아보도록 하겠습니다.


예제 프로그램

먼저 예제 프로그램을 간단하게 보도록 하겠습니다.

[그림 1] get_db 함수

먼저, db의 session 을 얻어오는 부분입니다. 이 함수를 어디에서 쓰는가? 당연하게도, api를 호출할 때 사용할 겁니다. 이 부분을 볼게요.

[그림 2] controller 부분

controller를 보면, session을 얻어와서 쿼리를 실행하는 데 쓰고 있어요. 그리고 나서 세션을 닫지 않고 있네요. get / 을 한 번 호출하고 나면 아래와 같은 상황이 될 겁니다.

message를 리턴하고 나서 세션이 닫히지 않았습니다. 그러면, 해당 세션은 리소스들을 해제하지 않고 무언가를 잡고 있을 것입니다.

이 상황에서 또 api를 호출하면 마찬가지일 겁니다. get_db 함수에서, 새로운 SessionLocal 객체를 생성했기 때문에 별개의 세션이 만들어지게 됩니다. 끝나고 나서 close가 되지 않았기 때문에, 해당 세션 역시 무언가를 잡고 있게 됩니다.

서버를 종료하지 않고, 마이그레이션을 시도해 보겠습니다.

[그림 3] 마이그레이션 파일

해당 파일을 보면, drop_column을 한 다음에, add_column을 합니다. alter 명령어가 들어가게 됩니다. 이 alter 중 대부분은 access exclusive lock을 필요로 합니다.

[그림 4] 마이그레이션 시도하기

마이그레이션을 시도해 봅시다. 그랬더니, freeze가 걸려 있습니다. 실제 서버였으면 배포 실패했을 겁니다. postgresql lock 이 걸려서 alter가 실행되지 않는 것으로 보이는데요. 자세히 보도록 하겠습니다.


postgresql lock 상황 보기

자세한 상황을 알아봅시다.

[그림 5] pg_stat_activity로 query state 보기

pg_stat_activity는 현재 실행되고 있는 쿼리들의 state를 볼 수 있습니다. 여기서 idle in transaction 상태를 볼 수 있는데요. 이는 트랜잭션에 있지만, 실행중이지 않다는 의미입니다. 이제 pg_lock 테이블을 보겠습니다.

[그림 6] pg_lock 테이블

pg_lock 테이블을 보면, database와 relation, pid, mode 등을 볼 수 있습니다. 여기서 mode는 잡고 있는 LOCK 종류를 의미합니다. relation은 테이블의 id입니다. 그런데, 이것만 봐서는 잘 모르겠습니다. 테이블에 대한 정보는 pg_stat_all_tables에 있습니다.

[그림 7] 테이블에 대한 정보 보기

이 테이블에는 2개의 정보가 있습니다. relid, 릴레이션의 id 값입니다. 그리고, relname은 테이블 이름입니다. 그러니 pg_lock 테이블과 pg_stat_all_tables를 조인해서 정보를 얻어오면 됩니다.

[그림 8] lock 정보를 얻어오는 쿼리

pg_lock에서는 locktype, page, virtualtransaction, pid, mode, granted를 얻어왔습니다. 그리고, pg_stat_all_tables에서는 relname만 얻어왔습니다. 이 두 테이블을 join 할 때, pg_locks의 relation과 pg_stat_all_tables의 relid만 같다는 조건을 주면 됩니다. 여기서 제가 볼 것은 pg_lock의 pid, mode, 그리고 pg_stat_all_tables의 relname 이 3개밖에 없습니다.

[그림 9] 얻어온 lock에 대한 정보

postgresql lock 정보를 얻어왔습니다. a라는 테이블에 AccessSharedLock이 걸려있다는 것을 알 수 있어요. 그런데, 이 23700은 어디서 얻어올 수 있을까요?

[그림 10] pg_stat_activity 테이블

pg_stat_activity를 보면 pid가 있어요. 23,700이 보이는데요. select a title from ~ where title like ~ 쿼리를 실행하고 있습니다. fastapi의 컨트롤러가 해당 쿼리를 실행하고 있습니다. 그리고 세션을 닫지 않았기 때문에, lock을 계속 들고 있습니다.


왜 마이그레이션이 안 되었는가?

아까 fastapi가 AcessSharedLock을 들고 있다고 했습니다.

이 lock은 select를 하기 위해 필요한 lock인 AccessSharedLock을 받아들입니다. 문제는 이 lock은 ACCESS EXCLUSIVE LOCK에 대해서만 배타적이라는 것입니다. 즉, 두 lock이 동시에 걸릴 수 없습니다. 고로, AccessSharedLock이 잡힌 상황에서 ACCESS EXCLUSIVE가 필요한 연산을 수행할 수 없습니다.

alter는 ACCESS EXCLUSIVE lock을 필요로 합니다. 당연하게도, access lock이 걸려있었던 상황이였기 때문에, migration 스크립트에 포함된 alter는 실행되지 못했습니다. 따라서, 마이그레이션이 실행되지 않았던 것입니다.

Leave a Comment

8 − 4 =