Deadlock found when trying to get lock; try restarting transaction

데드락이 발생했다. 결론부터 말하자면, 데드락을 유발하는 INSERT+SELECT 와 같은 쿼리를 분리하길 바란다. 

 

### Error updating database.  Cause: java.sql.SQLTransactionRollbackException: (conn=551) 

Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters

### SQL: INSERT INTO TableA   

( ID , name  ,  phone)   SELECT   ?,   ?,   ?  IFNULL(MAX(id_sequence), 0) + 1     

FROM TableA   WHERE name = ?
### Cause: java.sql.SQLTransactionRollbackException: (conn=551) 

Deadlock found when trying to get lock; try restarting transaction

; ]; (conn=551) Deadlock found when trying to get lock; try restarting transaction; 

nested exception is java.sql.SQLTransactionRollbackException: (conn=551) 

Deadlock found when trying to get lock; try restarting transaction
org.springframework.dao.DeadlockLoserDataAccessException: 

 

위와 같은 로그를 어플리케이션단에서 본다면, 아마 나와 같은 상황을 겪고 있을것이다. 

필자는 SELECT 이후 INSERT를 하는 쿼리 사용시 발생하였다. 

 

데드락을 해결하기 위한 방법을 알아보기전, 

싱글 DB인지 / 이중화된 DB인지 환경에 따라 해결방법이 다르다. 

 

■ 싱글 DB

  ▷ Transaction Isolation Levels 셋팅

     MySQL Documentation에서 isolation으로 서치를 하면 아래와 같은 Transaction의 4가지 격리 레벨을 찾을 수 있다. 

    · REPEATABLE READ (MySQL - default)

      - 동일한 트랜잭션 내에서 첫 번째 읽기에 의해 설정된 스냅샷을 읽는다. (기본설정으로 대량 쿼리시 동시성 문제가 발생할 수 있음)

    · READ COMMITTED

      - commit된 상태를 읽을 수 있음. UPDATE 혹은 DELETE문에만 행에 대한 잠금을 보유하고 있음. 

      - deadLock 발생 확률이 크게 줄어들지만 여전히 발생할 수 있음. 

    · READ UNCOMMITTED

      - 다른 트랜잭션의 커밋전 상태를 읽을 수 있으며, 이러한 읽기가 일관되지 않음. (dirty read)

    · SERIALIZABLE

      - 다른 트랜잭션이 완료될 때 까지 SELECT 포함하여 행에 대한 잠금을 보유함. (가장 높은 격리 레벨)

 

  ▷  필자의 경우는 로컬DB 테스트시 아래와 같은 방법으로 트렌젝션의 레벨을 조회한 이후, 'READ-UNCOMMITEED'로 셋팅을 변경하여

      테스트를 진행해보니 deadLock은 더이상 발생하지 않았다. 

* 기본 설정은 REPEATABLE-READ 이다. 

  ▷  로컬테스트를 위해 set tx_isolation을 하였지만, 영구적으로 반영을 하려면 my.cnf (Linux) 혹은 my.ini (Windows)

       설정 파일을 변경해 주어야 한다. isolation의 레벨은 환경과 상황에 알맞은 레벨을 선택하여 추가 하도록 하자.

    · [mysqld]

    · transaction-isolation = READ-UNCOMMITTED 

      * 필자는 테스트를 위해 READ-UNCOMMITEED를 하였다.

 

  ▷  SHOW ENGINE INNODB STATUS;  로 LATEST DETECTED DEADLOCK , 트랜잭션ID 등 상세정보를 확인할 수 있다.    

 

■ 이중화된 DB  (중요)

  ▷ Transaction Isolation Levels을 변경시 이중화를 가능케 하는 MySQL 바이너리 로그 포맷에 영향을 주게 된다. 

    · Isolation Level을 READ-COMMITTED로 변경시, binlog_format이 MIXED 이상으로 설정해야 한다. 

    · MySQL document에는 READ-COMMITTED와 BINLOG_FORMAT=MIXED 함께 사용시, 자동적으로 row-based 로깅

     이 적용된다고 가이드 하고 있다. 

    · 위와 같이 Isolation Level 과 MIXED로깅(statement + row) 를 사용하였을때, 스텐바이서버에 데이터가 제대로 

     동기화 된다.

    · 따라서, 이중화된 DB에 deadLock이 발생했다면, 유지보수 하는 입장에서 실제 DB서버에 설정을 변경하는 일도

     쉽지 않을것같다. 결국 insert+select 쿼리 구문을 어플리케이션단에서 쿼리를 분리 처리 하는것이

    근본적인 해결에 도움이 될 것으로 생각한다. 

 

    https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.1 Transaction Isolation Levels

 

필자는 Mybatis 환경에서 SELECT 이후 INSERT 하는 쿼리로 분리 처리 하여 잘 해결되었다. 

seq = sampleDao.selectSeq();

contents.setSeq(seq);

sampleDao.insertSeq(); 

 

JPA 환경에서는 findById(seq); → save(seq); 

애초에 분리 되어 있기 때문에 문제가 발생하지 않을 것으로 판단된다. 

 

 

끝.

 

 

 

※ 테스트 결과 (600명 세션연결 후, 10명이 동시에 20번 같은 로직 수행)

	Synchronized 처리	SELECT 쿼리에 FOR UPDATE 쿼리 추가	Synchronized + FOR UPDATE	SELECT / INSERT 쿼리 분리 처리
데드락 발생	X	X	X	X
수행시간	97초	76초	87초	60초
SEQ 정합성	O	중복발생	O	중복발생

 * SELECT 쿼리에 FOR UPDATE를 추가 했음에도 불구하고 SEQ INSERT 시 중복이 발생했다. 그 이유는 잘 모르겠다... 

   어쨋든 SEQ DUPL에 대한 사이드 이펙트가 없기 때문에 결국에 SELECT / INSERT 쿼리 분리로 처리는 완료되었지만, 

   테스트를 하고 보니 유의미한 결과를 많이는 얻지 못한것 같아 아쉽다. 

   Synchronized가 동시성 문제를 해결해주긴 하지만 확실히 어플리케이션 단에서 수행시간이 늘어나는 만큼, 다른 기능이 동일 스레드를

   사용하고 있다면 다른기능에서 지연현상이 발생할 것으로 보인다. 

 

 

 

 

본문 내용이 도움이 되셨다면, 아래의 공감버튼을 눌러주세요. 

정보 공유를 위한 포스팅에 큰 힘이 됩니다. 

만약 틀린 정보가 있으면 댓글 달아주세요. 수정하도록 하겠습니다. 

읽어주셔서 감사합니다. 오늘도 좋은 하루 보내세요.