MySQL innodb_rollback_on_timeout 설정
흔히 transaction 이라 하면 자동적으로 떠오르는게 트랜잭션의 특성인 ACID 입니다.
MySQL의 Innodb도 transaction을 지원하는 storage engine으로 당연히 ACID 또한 지켜질텐데요.
그런데 Innodb에서 이 ACID에도 예외사항이 있다는 사실을 알고 계셨나요?
공식 manual 의 문서대로 innodb는 timeout이 발생한 transaction에 대해
옵션에 따라 전체 트랜잭션을 rollback 하거나, 마지막 커맨드만 rollback (default)을 합니다.
그리고 이것을 결정 짓는 파라미터가 바로 innodb_rollback_on_timeout 인데요
지금부터 innodb_rollback_on_timeout 설정에 따른 rollback 테스트를 살펴보겠습니다.
innodb_rollback_on_timeout=off (default)
test 는 아래와 같이 진행했습니다.
- 세션 두개 준비
- 세션1에서 update rollback_test set name=‘kimdubi2’ where a=2 수행
- 세션2에서 update rollback_test set name=‘kimdubi3’ where a=3 수행
- 세션1에서 update rollback_test set name=‘kimdubi2’ where a=3 수행해서 세션2와 락 경합 유발
- 세션1에서 innodb_lock_timeout 발생하면 commit 수행
- 트랜잭션 반영 결과 살펴보기
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
### 세션 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update rollback_test set name='kimdubi2' where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### 세션 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update rollback_test set name='kimdubi3' where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### 세션 1
mysql> update rollback_test set name='kimdubi2' where a=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
### 세션 1
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from rollback_test;
+---+----------+
| a | name |
+---+----------+
| 1 | parkdubi |
| 2 | kimdubi2 |
| 3 | leedubi |
+---+----------+
3 rows in set (0.00 sec)
=> innodb_rollback_on_timeout = off 일 때는
lock timeout 으로 실패한 세션 1의 update rollback_test set name=‘kimdubi2’ where a=3;
커맨드만 rollback 수행하고 update rollback_test set name=‘kimdubi2’ where a=2; 는 반영되었습니다.
timeout 발생한 transaction 에서는 All or Nothing 이라는 transaction의 특성과는 다른 예외상황이 발생하였습니다.
innodb_rollback_on_timeout=on
mysql> set global innodb_rollback_on_timeout=on;
ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable
=> dynamic 변수가 아니므로 my.cnf 에서 변경 후 재기동 합니다.
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+
1 row in set (0.02 sec)
### 세션 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update rollback_test set name='kimdubi2' where a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### 세션 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update rollback_test set name='kimdubi3' where a=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
### 세션 1
mysql> update rollback_test set name='kimdubi2' where a=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
### 세션 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from rollback_test;
+---+----------+
| a | name |
+---+----------+
| 1 | parkdubi |
| 2 | choidubi |
| 3 | leedubi |
+---+----------+
3 rows in set (0.00 sec)
=> innodb_rollback_on_timeout = on 일 때는
off 일때와 달리 timeout 발생한 트랜잭션에서도 모두 rollback 되는 All or Nothing 이 지켜지는 트랜잭션의 결과를 확인할 수 있었습니다.
이렇게 별 생각도 못했던 사실을 살펴보았는데요
아무리 innodb_rollback_on_timeout=off 가 default 설정이라고는 하지만 어플리케이션 단에서 트랜잭션의 일부가 실패했는데 commit 이 수행되는 그런 로직은 없어야겠죠?