MySQL AUTOCOMMIT 관찰하기

안녕하세요.
오늘은 MySQL AUTOCOMMIT을 관찰해보겠습니다.

이 글을 쓰게된 계기는 친구의 연락인데, 친구가 하는말이,
"AUTOCOMMIT이 켜져있는 상태에서, START TRANSACTION을 실행하면 AUTOCOMMIT이 꺼진다! 그리고, 그 상태에서 COMMIT 혹은 ROLLBACK을 해줘야 다시 AUTOCOMMIT이 켜진다!" 였습니다.
저는 이것을 모르고있었습니다..

저는 질문이 생겼습니다.

1. AUTOCOMMIT 변수가 가지는 값이 아예 1에서 0으로 바뀌는건가?

2. START TRANSACTION 으로 인해 AUTOCOMMIT이 꺼진다면, 동시에 실행되고 있던 다른 트랜잭션도 직접 COMMIT 을 입력해줘야 하는건가?

3. START TRANSACTION 으로 인해 AUTOCOMMIT이 꺼지고, COMMIT이나 ROLLBACK을 빠뜨리면 테이블에 적용되지 않은 쿼리가 계속 쌓이는건가?

간단히 정답을 말씀드리면,

1. 아닙니다.

2. 아닙니다.
   AUTOCOMMIT은 세션에 독립적이므로, AUTOCOMMIT값을 바꾼다고 해서 다른 트랜잭션의 AUTOCOMMIT설정값에 영향을 주지 않습니다.

3. 그렇습니다.
   COMMIT이나 ROLLBACK을 해줄때까지 모든 쿼리는 테이블에 확정으로 반영되지 않습니다. 다만, 1번의 대답에서 알 수 있듯이 다른 세션은 영향받지 않습니다.

이 질문의 정답을 아래 실험으로 확인해보겠습니다.

테스트 DB는 MySQL 8.2.0 이고, tx isolation level은 REPEATABLE-READ 입니다.

1번 질문먼저 확인해보겠습니다.

아래 사진처럼, AUTOCOMMIT 변수 값이 달라지지는 않습니다.

2번 질문을 확인해보겠습니다.

총 3개의 세션으로 확인해보겠습니다.

1번 세션이 START TRANSACTION 때문에 AUTOCOMMIT이 꺼진 상태일 때,
2번 세션에서 COMMIT 명령어 호출 없이 데이터를 INSERT를 하고,
2번 세션의 변경사항이 3번 세션에서 보여지는지를 확인하겠습니다.

한 세션의 AUTOCOMMIT 설정 변경이 다른 세션에 영향을 준다면,
2번 세션의 변경사항이 3번 세션에 보여지지 않겠죠?

테스트 순서는 아래와 같습니다.

1. 1번 세션에서 START TRANSACTION을 하고, 테이블에 값을 INSERT 한다.
   COMMIT은 하지 않은 상태로 둔다.
2. 1번 세션에서 INSERT한 값이 2번 세션에서 읽어지지 않는 것을 확인한다.
3. 2번 세션에서 START TRANSACTION없이 바로 INSERT를 한다.
4. 2번 세션에서 INSERT 된 것을 확인하기 위해 3번 세션에서 SELECT를 한다.

실험 테이블(POST)은 아래와 같이 자료형이 int인 단일 필드를 가지고 있습니다.

아래와 같이 테스트 순서 1번을 실행해보겠습니다.

우측 상단의 숫자는 세션 번호입니다.

START TARNSACTION -> INSERT 를 1번 세션에서 실행한 모습입니다.

---

다음은, 아래와 같이 테스트 순서 2번을 실행해보겠습니다.

위와 같이 1번 세션에서 COMMIT을 하지 않았으므로 1번 세션에서는 해당 데이터가 보이지 않는 상태입니다. 이것으로, 1번 세션에서의 INSERT문은 AUTOCOMMIT이 아님을 확인할 수 있습니다.

---

다음은, 아래와 같이 테스트 순서 3번을 실행해보겠습니다.

2번 세션에서 START TRANSACTION없이 바로 INSERT를 한 모습입니다.

---

다음은, 아래와 같이 테스트 순서 4번을 실행해보겠습니다.

3번 세션에서 2번 세션이 DB에 날린 INSERT문이 적용되어보이는 것을 확인할 수 있습니다.

이것으로 2번의 질문의 정답을 알게되었습니다.
START TRANSACTION으로 꺼진 AUTOCOMMIT은 해당 트랜잭션만이고, 다른 세션의 트랜잭션은 영향받지 않습니다.

위 과정중, 3번에서 아래와 같은 질문이 생길 수 있습니다.
"1번 세션에서 INSERT하고 COMMIT하지 않은, 같은 데이터를 2번 세션에서 INSERT하면 어떻게 되는가?"

정답은, "2번 세션의 INSERT문은 1번 세션의 INSERT문이 COMMIT 혹은 ROLLBACK되기 전까지 기다린다" 입니다. 1번 세션에서 해당 데이터에 LOCK을 걸기 때문에 LOCK이 풀리기 전까지는 2번 세션에서 같은 데이터를 INSERT 할 수 없기때문입니다.

아래에서 확인해보겠습니다.

위 사진에서 숫자는 쿼리 실행 순서입니다.

1. 1번 세션에서 START TRANSACTION 을 합니다.
2. 1번 세션에서 INSERT 를 합니다. COMMIT은 하지 않은 채로 둡니다.
3. 2번 세션에서 1번 세션이 INSERT한 데이터와 같은 데이터를 INSERT합니다.
4. 일정 시간이 지나도 해당 데이터의 LOCK이 풀리지 않아서, 2번 세션의 INSERT요청이 TIME OUT 에러가 발생하는 모습입니다.

3번 질문을 확인해보겠습니다.

1번 세션에서 START TRANSACTION 을 하고 INSERT문을 여러개 날린 뒤,
오른쪽 상단에 보이는 2번 세션에서 SELECT를 했을 때의 모습입니다.

1번 세션의 INSERT문이 커밋되지 않은 상태이므로,
2번 세션의 SELECT문은 UNDO영역에서 데이터를 조회합니다.

3번은 막상 실험해보고 나니, 당연한 결과인 것 같습니다.

이것으로 포스팅 마무리 하겠습니다.

읽어주셔서 감사합니다.