Deadlock
스포츠 사이트 유지보수중 대회기간만 되면 계속 Deadlock이 발생되는 일이 생겼다.
처음에는 내 쪽 파트가 아니어서 무관심하다 저녘 마다 전화가 와서 결국 트러블슈팅을 하게 됐다.
문제
서버가 죽는다고 전화가 와 서버 로그를 확인해보니특정 프로시저만 실행 후 로그가 멈춰
was를 죽이니 해당로그 발견 했다.
20-Jun-2023 21:46:28.031 정보 [main] org.apache.catalina.core.StandardWrapper.unload 서블릿 [action]을(를) 위해, [135]개의 인스턴스(들)이 할당 해제되기를 기다립니다.
20-Jun-2023 21:46:30.277 경고 [main] org.apache.catalina.loader.WebappClassLoaderBase.clearReferencesThreads 웹 애플리케이션 [ROOT]이(가) 여전히 완료되지 않은 요청을 처리하고 있습니다. 이는 메모리 누수를 유발할 가능성이 높습니다. 표준 컨텍스트 구현의 unloadDelay 속성을 이용하여, 요청 완료 허용 시간을 통제할 수 있습니다. 요청 처리 쓰레드의 스택 트레이스:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
뭔가 이상했다. 프로시저가 실행된 후 서버가 죽었다기보단 lock이 걸린 느낌이 강했다.
해당사항들을 바탕으로 찾아본 결과
1. MySQL에서 전체 데이터를 Scan 하는 쿼리를 질의하여 서비스에 큰 영향이 발생할 수 있다.
- 이미 프로시저 내에서 INSERT SELECT 문이 돌고 있음.
2. 현재 Isolation Level 이 REPEATABLE-READ 이다.
- REPEATABLE-READ(MySQL Default) 상태에서 Insert into Select 혹은 Create Table As Select 로 전체 테이블 참조
쿼리 실행 시 참조 테이블에 데이터 변경 작업이 “대기” 상태에 빠지는 현상이 있다.
참고 문헌 : https://gywn.net/2012/05/mysql-transaction-isolation-level/현재 내 상황은 2번에 해당하는 것 같다.
원인
위 2번 사항대로 원인은
REPEATABLE-READ(MySQL Default) 상태에서 Insert into Select 혹은 Create Table As Select 로 전체 테이블 참조
쿼리 실행 시 참조 테이블에 데이터 변경 작업이 “대기” 상태에 빠지는 현상이 있다.
프로시저 내에서 돌고 있는 insert select 문이었다. 예선대진표가 프로시저 내부에서 돌고 있는 도중 다른관리자 쪽에서 select 가 이루어지고 있는 것이었다.
해결
DB 접속 후 Isolation Level (참고하시면 좋습니다.) 수정.
set transaction_isolation = 'READ-COMMITTED';영구적으로 transaction isolation 변경시
$ vi /etc/my.cnf
## [mysqld] 설정에 추가
transaction-isolation = READ-COMMITTED참고
(설정 자체가 READ COMMITTED에서는 발생하지 않음)
해결방안 적용시 아래의 문제가 생길 가능성 있다면 아래 문헌 참고.
(binlog 관련 - 추후 문제 발생 시 이 글을 업데이트 예정)