[MySQL] innodb_rollback_on_timeout 알아보기 (Lock wait timeout exceeded; try restarting transaction)

- 목차

 

들어가며.

innodb_rollback_on_timeout 은 MySQL InnoDB 스토리지 엔진에서 트랜잭션이 잠금 대기 시간 초과(lock wait timeout) 에 도달했을 때, 해당 트랜잭션을 롤백하는 방식을 결정하는 시스템 변수입니다.

innodb_rollback_on_timeout 설정값은 OFF 또는 ON 을 가집니다.

기본값은 OFF 입니다.

 

innodb_rollback_on_timeout 이 ON 으로 설정되었을 때에,

SQL Session 이 Lock 을 획득하기 위한 대기 시간이 innodb_lock_wait_time 으로 설정된 시간을 초과한다면,

SQL Session 이 Rollback 되며 Transaction 이 종료됩니다.

 

반면에 innodb_rollback_on_timeout 이 OFF 으로 설정된다면,

innodb_lock_wait_time 시간이 초과된 이후에 Session 의 Transaction 이 종료되지 않습니다.

그리고 사용자가 commit 또는 rollback 을 선택할 수 있습니다.

 

이어지는 내용에서 실습을 통해 innodb_rollback_on_timeout 에 대한 동작을 알아보도록 하겠습니다.

 

innodb_rollback_on_timeout.

innodb_rollback_on_timeout 설정에 대해서 알아보기 위해서 MySQL Instance 를 실행합니다.

MySQL Instance 를 실행하기 위한 Docker 명령어는 아래와 같습니다.

 

cat <<'EOF'> /tmp/custom.cnf
[mysqld]
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = ON
EOF

docker run -d --rm --platform linux/amd64 \
  --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/custom.cnf:/etc/mysql/conf.d/custom.cnf \
  mysql:8.0.30

 

 

실행된 MySQL Docker Container 내부에 innodb_rollback_on_timeout 설정의 활성화 여부를 조회할 수 있습니다.

 

docker exec mysql mysql -e "show variables like 'innodb_rollback_on_timeout';"

 

그리고 innodb_lock_wait_timeout 설정 또한 아래와 같이 10초로 확인됩니다.

 

docker exec mysql mysql -e "show variables like 'innodb_lock_wait_timeout';"

 

우선 하나의 Table 과 Row 를 생성합니다.

 

CREATE TABLE mysql.test_timeout (
    id INT PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(100)
  ) ENGINE=InnoDB;

INSERT INTO mysql.test_timeout (value) VALUES ('Initial Value');

 

test_timeout 테이블의 Primary Key 를 기준으로 Lock Timeout 과 관련된 테스트를 진행합니다.

그리고 아래와 같이 총 4개의 MySQL Connection 을 실행한 후에 Lock 을 획득하기 위한 SQL 를 실행합니다.

START TRANSACTION; 
UPDATE mysql.test_timeout SET value = 'Locked Value' WHERE id = 1;

 

아래의 이미지와 같이 총 4개의 SQL Connection 중에서 첫번째 MySQL Connection 이외에 나머지 3개의 Transaction 은 모두 Lock 획득을 실패합니다.

그리고 "Lock wait timeout exceeded; try restarting transaction" 에러를 생성합니다.

 

 

이는 innodb_lock_wait_timeout 인 10초 동안 Lock 을 획득하지 못하면 10초 이후에 모든 Transaction 이 실패하게 됩니다.

 

 

2개 이상의 DML Query 요청.

"innodb_rollback_on_timeout=ON" 상황에서 여러개의 DML Query 가 실행 중인 Transaction 은 모든 쿼리들이 다 같이 Rollback 됩니다.

 

아래의 쿼리는 3개의 Insert Query 와 1개의 Update Query 입니다.

Transaction 동안에 마지막 쿼리인 Update Query 에 의해서 Lock Wait Timeout 이 발생하게 됩니다.

그리고 innodb_lock_wait_timeout 이후에 "Lock wait timeout exceeded; try restarting transaction" 결과가 발생합니다.

 

START TRANSACTION; 
INSERT INTO mysql.test_timeout (value) VALUES ('Second Value');
INSERT INTO mysql.test_timeout (value) VALUES ('Third Value');
INSERT INTO mysql.test_timeout (value) VALUES ('Fourth Value');
UPDATE mysql.test_timeout SET value = 'Locked Value' WHERE id = 1;

 

 

 

그리고 test_timeout 테이블을 조회하게 되면 초기 상태로 Rollback 됨을 확인할 수 있습니다.

 

 

 

innodb_rollback_on_timeout=OFF.

innodb_rollback_on_timeout 설정이 OFF 인 상황에서는 innodb_lock_wait_timeout 이후에 Transaction 이 종료되지 않습니다.

 

cat <<'EOF'> /tmp/custom.cnf
[mysqld]
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = OFF
EOF

docker run -d --rm --platform linux/amd64 \
  --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/custom.cnf:/etc/mysql/conf.d/custom.cnf \
  mysql:8.0.30

START TRANSACTION; 
INSERT INTO mysql.test_timeout (value) VALUES ('Second Value');
INSERT INTO mysql.test_timeout (value) VALUES ('Third Value');
INSERT INTO mysql.test_timeout (value) VALUES ('Fourth Value');
UPDATE mysql.test_timeout SET value = 'Locked Value' WHERE id = 1;

 

그리하여 Lock Wait Timeout 이후에 commit 명령을 실행하게 되면 Lock 과 무관한 DML Query 들을 정상적으로 반영되게 됩니다.

왜냐하면 Transaction 이 종료되지 않은 상태이기 때문입니다.