MySQL select insert Deadlock

최근 업무 중에 시스템에 아래와 같은 에러 로그가 발생한 것을 보았습니다.

java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction Query: update …

에러 로그가 발생한 쿼리는 아래의 예시와 같은 단순한 update 구문이었습니다.

UPDATE `TABLE` SET `column1` = `column1` WHERE `column2` = `column2`

운영환경에서 사용하는 DB는 MySQL이었고 모든 설정은 default 설정으로 되어 있었습니다.

MySQL에서 deadlock 관련 로그를 확인해보니 에러가 발생한 update 구문의 테이블과 동일한 테이블을 사용하는 SELECT INSERT문이 원인이었습니다.

MySQL은 기본적으로 Repeatable Read Isolation level의 트랜젝션 모델을 사용합니다. 일반적인 조회에서는 Row에 Lock을 사용하지 않습니다. 하지만 select로 읽어들인 결과(Result Set)을 테이블에 적재(Insert)하는 작업을 할 경우에는 Shared Lock을 사용하게 됩니다. CREATE TABLEA AS SELECT 혹은 INSERT SELECT 수행 시 Shared Lock을 사용함에 따라 select는 허용되지만 변경은 허용되지 않게 됩니다.

그럼 CTAS 혹은 INSERT SELECT시에 왜 Shared Lock이 발생하는 것일까요?

여러 문서를 살펴보면 그 이유는 복구 및 복제 데이터의 정합성을 보장하기 위한 것이라고 되어있습니다. Lock을 사용하지 않고 위의 명령문들을 실행하는 동안 다른 트랜잭션이 tab_source를 업데이트하면 데이터 복구 결과나 Replication 결과가 잘못될 수 있기 때문입니다.