MY SQL RECORD LOCK PROCESS KILL 레코드 행락 킬하기

최근 작업하다가 MYSQL에서 INNODB RECORD LOCK이 생기는 현상이 있어서 이를 해결 했던 방법을 적으려 합니다

한행의 INDEX를 기준으로 LOCK을 걸어서 해당 행에 SELECT , UPDATE, DELETE 를 하지 못하도록 막아놓은 처리가 RECORD LOCK입니다.

이때 LOCK 이 걸리게되면 해당 트랜잭션이 처리가 될때까지 다른 세션에서는 해당 레코드에 어떠한 쿼리를 실행해도 대기 상태에 걸리게 되는데요 이를 강제적으로 처리하는게 해당 LOCK을 건 트랜잭션을 찾아서 KILL 해버리는 방법입니다

MY SQL 8.0버젼 부터는 performance_schema.data_locks 에 lock에 관한 정보가 조회되어 나옵니다.

 

 SELECT * 
   FROM performance_schema.data_locks;

위 쿼리를 실행하면 아래와 같이 lock에 걸린 트랜잭션 정보가 조회 되게 됩니다.

이때 WAITING 이 되어있는 STATUS 값을 찾고 해당 트랜잭션의 THREAD_ID를 찾습니다.

저는 조회 시 63이라는 THREAD_ID 가 조회 되었습니다.

위 63 이라는 ID 값을 performanc_schema.threads 테이블에서 조회를 하고

해당 결과의 PROCESSLIST_ID를 찾습니다.

 SELECT *
   FROM performance_schema.threads
  WHERE THREAD_ID = '63';

 

PROCESS_LIST_ID 값 찾기

그리고 해당 PROCESSLIST_ID 를 KILL 처리 해주면 RECORD_LOCK이 걸렸던 트랜잭션이 강제로 처리가 됩니다.

KILL 12;

 

테스트 방법은 아래와 같이 진행했습니다.

위 세션에서 FOR UPDATE를 붙여서 강제로 트랜잭션에 LOCK을 걸고 

다른 세션에서 해당 레코드를 똑같이 조회하여서 트랜잭션 락을 확인했습니다.

그리고 위에 작성한 방식대로 WAIT 상태의 레코드의 프로세스를 찾아 KILL 해주게 되면 LOCK 현상을 강제적으로 해결하는걸 확인 할수 있습니다.