mysql Insert lock wait timeout 조정해보기 with Spring

MySQL insert 실패 테스트해 보기

user 테이블 생성

CREATE TABLE `user` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Lock 테스트

세션 두 개를 열어서 테스트를 진행합니다. 세션 1에서 인덱스 레코드 락을 걸고, 세션 2에서 같은 인덱스로 insert를 실행시킬 겁니다. 이때 락은 얼마나 잡고 있을 건지는 innodb_lock_wait_timeout 설정값에 따라 다릅니다.

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; 쿼리를 이용해서 락 대기 시간이 몇 초인지 확인합니다. MySQL 5.7 기준 기본값은 50초입니다. 

세션1	세션2
start transaction;	start transaction;
insert into user(id, name, age) values(1, "monny", 30);
	insert into user(id, name, age) values(1, "monny", 30);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

Lock 테스트(2) - lock_wait_timeout 시간 조정

innodb_lock_wait_timeout 시간 조정해서 테스트합니다.

세션1	세션2
start transaction;	start transaction;
insert into user(id, name, age) values(1, "monny", 30);
	SET SESSION innodb_lock_wait_timeout = 10;
	insert into user(id, name, age) values(1, "monny", 30);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

Database Connection

저는 현재 kotlin + spring + jpa 스펙으로 프로젝트를 세팅하고 테스트 중입니다. 여기서 MySQL Driver 설정하는 법을 정확히는 잘 모르겠는데 Datasource에서 커넥션을 만들 때 lock_wait_timeout을 설정할 수 있는 방법이 있습니다.

lock_wait_timeout 세팅 x

innodb_lock_wait_timeout 세팅하지 않고 테스트를 해봅니다. 저는 뭐 아래처럼 대충 코드에다가 아래처럼 하드 코딩해 놓고 controller로 호출해 주었습니다. 

fun insert(userName: String, age: Int) {
    log.info("start insert")
    userRepository.save(User(id = 1, name = userName, age = age))
    log.info("end insert")
}

호출하기 전에 위의 테스트에서 했던 대로 터미널에서 세션 열어서 insert 시키는 것 까먹지 마세요. 테스트 결과 50초가 소요됩니다. 

lock_wait_timeout 세팅

spring:
  jpa:
    database: mysql
    show-sql: true
  datasource:
    hikari:
      connection-init-sql: SET SESSION innodb_lock_wait_timeout = 10
      username: root
      password:
      jdbc-url: jdbc:mysql://localhost/johnny_db?serverTimezone=Asia/Seoul
      driver-class-name: com.mysql.cj.jdbc.Driver

위처럼 프로퍼티를 통해 타임아웃 설정을 하니 적용이 된 것을 볼 수 있었습니다.

 

@Transactional의 timeout 사용

Database Connection 설정은 모든 커넥션에 적용되기 때문에 개별적인 쿼리의 타임아웃을 조정하기 위해서는 QueryHint를 사용해야 합니다. 다만 테스트 중인 insert의 경우 QueryHint 에노테이션을 이용할 수 없기 때문에 spring transactional의 timeout을 사용할 수 있을 것 같습니다. javax.transaction의 Transactional이 아님에 유의하세요

@org.springframework.transaction.annotation.Transactional(timeout = 10)
fun insertWithTimeout(userName: String, age: Int) {
    log.info("start insert")
    userRepository.save(User(id = 1, name = userName, age = age))
    log.info("end insert")
}

테스트 결과는 아래와 같습니다.

 

재사용성 높이기

갑자기 생각났는데 모든 개별적인 함수에 Transactional 어노테이션을 붙이는 것보다 미리 정의해 놓고 사용하면 도움이 될 것 같네요.

@Service
class TransactionHandler {

    @Transactional(timeout = 10)
    fun <T> executeShortTimeout(fn: ()-> T): T {
        return fn()
    }

    @Transactional(timeout = 60)
    fun <T> executeLongTimeout(fn: ()-> T): T {
        return fn()
    }
}

이런 식으로 미리 타임아웃을 정의해 놓고

코드를 아래와 같이 수정해 보고 다시 테스트해 보겠습니다.

fun insertWithTimeout(userName: String, age: Int) {
    log.info("start insert")
    transactionHandler.executeShortTimeout {
        userRepository.save(User(id = 1, name = userName, age = age))
    }
    log.info("end insert")
}

fun insertWithLongTimeout(userName: String, age: Int) {
    log.info("start insert")
    transactionHandler.executeLongTimeout {
        userRepository.save(User(id = 1, name = userName, age = age))
    }
    log.info("end insert")
}

10초로 타임아웃을 설정한 결과는 여전히 잘 동작하는데 60초로 타임아웃을 설정한 결과는 50초에 타임아웃이 납니다. 

EntityManager 사용하기

엔티티 매니저를 이용해서 타임아웃을 지정할 수 있습니다. 위에서 50초 만에 타임아웃이 난 이유는 innodb_lock_wait_timeout 때문일 텐데 그렇다면 위 설정을 60초로 바꾼 다음에 insert문을 실행하기만 하면 됩니다.

@Transactional
fun insertWithEM(userName: String, age: Int) {
    entityManager.createNativeQuery("SET SESSION innodb_lock_wait_timeout = 60")
        .executeUpdate()

    entityManager.createNativeQuery("insert into user(id, name, age) values(1, ?, ?)")
        .setParameter(1, userName)
        .setParameter(2, age)
        .executeUpdate()
}

이렇게 한 후 테스트를 해보면 mysql의 디폴트 값(50초) 보다 더 오래 타임아웃을 가질 수 있으면서도 개별적인 함수에 대해서 타임아웃을 지정해줄 수 있습니다.

마치며

블로그 글로 쓸거라면 솔직히 좀 더 깊게 연구하고 써야하는데 현재 프로젝트 중인 관계로 시간이 정말 부족하네요ㅠ 사실상 이번 포스트에서는 키워드 정도만 대충 알아봤고, 좀 더 시간적으로 여유가 될 때 좀 더 리서치를 해보도록 하겠습니다.