MySQL MVCC 언두 로그(Undo log) 실습

MySQL, InnoDB 엔진에서 채택하는 기본 격리 수준은 REPEATABLEREAD 이다. 아래 명령어로 확인해볼 수 있다.

mysql> SELECT @@SESSION.transaction_isolation;
+---------------------------------+
| @@SESSION.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ                 |
+---------------------------------+
1 row in set (0.00 sec)

REPEATABLE READ 격리 수준에서는 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션에서는 동일한 결과를 보여줄 수 있게 보장한다.

 

트랜잭션 격리 수준에 대한 정리는 이전 포스팅에 있으니 참고할 수 있다.

 

 

REAL MySQL 책을 읽어보면 아래와 같은 내용이 있다.

모든 InnoDB의 트랜잭션은 고유한 트랙잭션 번호(순차 증가)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. 아래는 REPEATABLE READ 격리 수준이 작동하는 방식을 보여준다.

 

REAL MySQL 책 중에서

사용자 B가 트랜잭션(10)을 시작했고 사용자 B가 트랜잭션(12)을 통해 데이터를 변경했다. 이 때, 언두 로그에는 변경 전 데이터가 저장되어 있기 때문에 사용자 B가 데이터를 조회했을 때 같은 값이 나온다는 것이다.

 

 

실제로 이렇게 동작하는지 실습해보자. 두 개의 트랜잭션을 열기 위해 shell 두 개를 열어서 각각의 세션을 만들어주면 된다.

 

REPEATABLE READ 동작 실습

[1] member 테이블을 생성하고 데이터 한 개를 추가한다.

mysql> CREATE TABLE member (
    ->     mbr_no BIGINT PRIMARY KEY,
    ->     mbr_nm VARCHAR(200));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO member (mbr_no, mbr_nm)
    -> VALUES (1, '홍길동');
Query OK, 1 row affected (0.00 sec)

 

 

[2] MySQL은 기본적으로 autocommit 옵션이 활성화되어 있기 때문에 해당 옵션을 끄도록 한다.

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

 

 

[3] 기본적인 REPEATABLE READ가 동작하는지 확인 먼저 해보자. 먼저 세션A에서 데이터를 읽는다.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT * FROM member WHERE mbr_no = 1;
+--------+-----------+
| mbr_no | mbr_nm    |
+--------+-----------+
|      1 | 홍길동    |
+--------+-----------+
1 row in set (0.00 sec)

 

 

[4] 세션B에서 데이터를 업데이트하고 커밋하자.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> UPDATE member
    -> SET mbr_nm = '성현'
    -> WHERE mbr_no = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

 

 

[5] 세션A에서 다시 데이터를 조회해보자.

mysql> SELECT * FROM member WHERE mbr_no = 1;
+--------+-----------+
| mbr_no | mbr_nm    |
+--------+-----------+
|      1 | 홍길동    |
+--------+-----------+
1 row in set (0.00 sec)

동일 트랜잭션에서 조회했으니 이름이 여전히 '홍길동'으로 남는다. REPEATABLE READ가 잘 동작한다.

 

 

언두 로그(Undo log) 확인해보기

InnoDB의 Undo 로그는 디스크 파일로 존재하지만, 내부 바이너리 포맷으로 관리되기 때문에 사용자가 SQL이나 파일 접근을 통해 직접 내용을 확인하는 것은 불가능하다. 대신 History list length, undo tablespace 크기 변화와 같은 지표를 통해 간접적으로 동작을 확인할 수 있다.

 

[1] undo log 파일을 확인해보자.

mysql> SHOW VARIABLES LIKE 'innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | ./    |
| innodb_undo_log_encrypt  | OFF   |
| innodb_undo_log_truncate | ON    |
| innodb_undo_tablespaces  | 2     |
+--------------------------+-------+
4 rows in set (0.01 sec)


mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| datadir       | /opt/homebrew/var/mysql/ |
+---------------+--------------------------+
1 row in set (0.01 sec)

 

언두 로그는 디스크 파일에 기록되므로 위 경로를 찾아가 확인해봤다.

$ /opt/homebrew/var/mysql | stable  ls -lh undo_*

-rw-r-----@ 1 shjo  admin    16M  1  9 18:03 undo_001
-rw-r-----@ 1 shjo  admin    16M  1  9 18:03 undo_002

 

파일 내용을 실제로 확인봐도 바이너리 데이터여서 직접 확인은 불가능하다는 것을 알 수 있다.

$ /opt/homebrew/var/mysql | stable  hexdump -C undo_001 | head

00000000  5f 1b cf 5b 00 00 00 00  00 01 3a 17 00 00 00 01  |_..[......:.....|
00000010  00 00 00 00 01 2a 6c d0  00 08 00 00 00 00 00 00  |.....*l.........|
00000020  00 00 ff ff ff ef ff ff  ff ef 00 00 00 00 00 00  |................|
00000030  04 00 00 00 01 40 00 00  00 00 00 00 00 1c 00 00  |.....@..........|
00000040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000050  00 01 00 00 00 00 01 3e  00 00 00 00 01 3e 00 00  |.......>.....>..|
00000060  00 04 00 00 00 00 00 9e  00 00 00 00 01 16 00 00  |................|
00000070  00 00 00 00 01 1a 00 00  00 03 00 00 00 02 00 26  |...............&|
00000080  00 00 00 ae 00 26 00 00  00 01 00 00 01 04 00 26  |.....&.........&|
00000090  00 00 01 04 00 26 00 00  00 00 00 00 00 00 ff ff  |.....&..........|

 

 

[2] 세션 A에서 데이터를 조회해보자.

mysql> SELECT * FROM member WHERE mbr_no = 1;
+--------+--------+
| mbr_no | mbr_nm |
+--------+--------+
|      1 | 성현   |
+--------+--------+
1 row in set (0.00 sec)

A 트랜잭션이 열렸다.

 

 

[3] 세션 B에서 데이터를 변경하고 커밋한다.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> UPDATE member
    -> SET mbr_nm = '성현222222'
    -> WHERE mbr_no = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

 

 

[4] 세션 A에서 데이터를 다시 확인한다. REPEATABLE READ가 동작한다.

mysql> SELECT * FROM member WHERE mbr_no = 1;
+--------+--------+
| mbr_no | mbr_nm |
+--------+--------+
|      1 | 성현   |
+--------+--------+
1 row in set (0.00 sec)

 

 

[5] undo log의 상태를 확인하기 위해 SHOW ENGINE INNODB STATUS\G 명령어를 사용한다.

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2026-01-09 18:29:34 0x16ca53000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 145200 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 149
OS WAIT ARRAY INFO: signal count 145
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3356
Purge done for trx's n:o < 3354 undo n:o < 0 state: running but idle
History list length 2

- Trx id counter 3356: 다음에 할당될 트랜잭션 ID를 말한다. MySQL InnoDB에서는 트랜잭션 번호가 순차 증가한다고 했다.

- Purge done for trx's n:o < 3354 undo n:o < 0 state: running but idle: trx id 3354 아래 undo 로그는 purge 됐다는 것을 말한다.

- History list length 2: 세션 A의 트랜잭션이 진행 중이고 세션 B의 변경 전 데이터가 undo log에 남아있다는 것을 의미한다. 두 개의 트랜잭션이 undo log를 참조하고 있다.

 

 

[6] 세션 A를 커밋하고 다시 undo log 상태를 확인해보자.

SHOW ENGINE INNODB STATUS\G

trx id 3356 미만의 undo log는 purge 됐다는 것을 알 수 있고 세션 A,B가 참조하는 undo log가 0개가 되었다는 것을 알 수 있다.

 

---

 

이렇게 실제로 Undo log → MVCC → REPEATABLE READ 동작으로 이어지는 실습을 해보았다.