MySQL 트랜잭션 중간의 DDL 한 줄, ROLLBACK은 무엇을 되돌렸나

트랜잭션 안에 끼워 넣은 명령 한 줄이 ROLLBACK 범위를 바꿀까
운영 데이터를 고칠 때는 순서를 정해 두고 작업합니다. 백업 테이블은 트랜잭션을 시작하기 전에 CREATE TABLE ... LIKE와 INSERT ... SELECT로 만들고, 백업이 끝난 것을 확인한 뒤에 데이터를 수정합니다. 컬럼 추가처럼 DDL과 DML이 함께 필요한 작업은 DDL을 먼저 끝낸 뒤 DML을 수행합니다.
이 순서가 무너지면 무슨 일이 생기는지 직접 확인해 보았습니다. 상황은 이렇습니다. START TRANSACTION으로 시작한 작업 도중에 인덱스 생성, 백업 테이블 생성, 통계 수집 같은 명령이 한 줄 끼어 있고, 마지막에 ROLLBACK을 실행합니다. 이때 무엇이 되돌아가고 무엇이 남는지, 명령 일곱 가지를 같은 틀에 넣어 비교했습니다.
포인트 지갑 일곱 행과 같은 틀로 짠 TC 일곱 개
테스트는 Windows의 MySQL Community Server 8.4.11에서 Workbench로 진행했습니다. 세션 설정은 autocommit=1, 격리 수준 REPEATABLE-READ, completion_type은 NO_CHAIN입니다. 가상의 포인트 지갑 테이블에 TC마다 한 행씩, 잔액 1000과 memo init으로 일곱 행을 넣었습니다. DDL이 건드릴 대상으로는 별도의 tc_side 테이블을 두었습니다.
CREATE TABLE point_wallet (
id INT NOT NULL PRIMARY KEY,
tc_name VARCHAR(40) NOT NULL,
balance INT NOT NULL,
memo VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE tc_side (
id INT NOT NULL PRIMARY KEY,
v INT NOT NULL
) ENGINE=InnoDB;

모든 TC는 같은 다섯 단계로 진행합니다. 트랜잭션을 열고, 1단계 UPDATE로 잔액을 700으로 바꾸며 memo를 step1로 적습니다. 이어서 가운데 명령을 실행하고, 2단계 UPDATE로 memo를 step2로 바꾼 뒤 ROLLBACK합니다. 단계 사이마다 information_schema.INNODB_TRX에서 이 세션의 트랜잭션 ID와 행 수를 세션 변수에 담아 두었습니다. 아래는 TC2 블록입니다.
START TRANSACTION;
UPDATE point_wallet SET balance = balance - 300, memo = 'step1' WHERE id = 2;
SET @tc2_b = NULL;
SELECT trx_id INTO @tc2_b FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id = CONNECTION_ID();
ALTER TABLE tc_side ADD COLUMN memo VARCHAR(20) NULL; -- 가운데 명령
SELECT COUNT(*) INTO @tc2_m FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id = CONNECTION_ID();
UPDATE point_wallet SET memo = 'step2' WHERE id = 2;
SET @tc2_c = NULL;
SELECT trx_id INTO @tc2_c FROM information_schema.INNODB_TRX
WHERE trx_mysql_thread_id = CONNECTION_ID();
ROLLBACK;
나머지 TC는 가운데 명령만 다릅니다.
| TC | 가운데 명령 |
|---|---|
| TC1 | DO SLEEP(0); (아무 일도 하지 않는 기준) |
| TC2 | ALTER TABLE tc_side ADD COLUMN memo VARCHAR(20) NULL; |
| TC3 | CREATE TABLE point_wallet_bak AS SELECT * FROM point_wallet; |
| TC4 | CREATE TABLE tc_side (id INT); (이미 있는 이름이라 실패) |
| TC5 | CREATE TEMPORARY TABLE tmp_calc (id INT PRIMARY KEY, amt INT); |
| TC6 | ANALYZE TABLE tc_side; |
| TC7 | SAVEPOINT sp1; 뒤에 CREATE INDEX ix_v ON tc_side (v); |
ROLLBACK을 마친 뒤 남아 있던 값

두 행의 공통점은 가운데 명령이 트랜잭션을 끝내지 않았다는 것입니다. 나머지 다섯 행은 잔액 700에 memo step2로 남았고, 1단계와 2단계가 모두 확정됐습니다.
테스트 전에는 DDL 앞의 1단계만 커밋되고 2단계는 ROLLBACK으로 되돌아갈 것으로 봤습니다. 결과는 예상과 달랐습니다. DDL 뒤에 실행한 UPDATE까지 남아 있었고, 이 경우 ROLLBACK은 되돌릴 것이 하나도 없었던 셈입니다.
트랜잭션 ID로 따라가 본 커밋 시점

1단계 뒤의 트랜잭션 ID는 모든 TC에서 잡혔습니다(692629, 692633, 692648, 692663, 692669, 692673, 692682). 갈림길은 가운데 명령 직후입니다. TC2, TC3, TC4, TC7은 이 세션의 트랜잭션이 0개로 줄었습니다. START TRANSACTION으로 연 트랜잭션이 가운데 명령에서 커밋되고 끝난 것입니다.
2단계 UPDATE가 남은 이유는 그다음 열에서 보입니다. 트랜잭션이 끝난 세션은 autocommit=1 상태로 돌아갑니다. 그래서 2단계 UPDATE는 트랜잭션 없이 실행돼 문장이 끝나는 즉시 커밋됐습니다. 직후에 조회한 trx_id_step2가 NULL인 것도 이 때문입니다. 반면 TC1과 TC5는 2단계 뒤에도 같은 트랜잭션이 이어져 same_trx가 true입니다. TC1과 TC5의 trx_id_step2가 BLOB으로 표시된 것은 Workbench가 세션 변수 값을 그렇게 보여 주기 때문이고, 값이 있다는 뜻입니다.
TC2에서는 ROLLBACK을 실행하기 전에 2번 행을 한 번 더 조회해 두었습니다. 이 시점에 700과 step2가 보였습니다. 같은 시점의 trx_id_step2가 NULL이었으므로, 작업자가 아직 트랜잭션 안에 있다고 믿는 동안 이 값은 이미 커밋된 상태였습니다.

TC6의 ANALYZE TABLE은 결과가 엇갈렸습니다. 명령 직후 INNODB_TRX에 이 세션의 행이 1개 남아 있었습니다. 그런데 2단계 뒤의 trx_id_step2는 NULL이고 최종 상태도 700과 step2입니다. START TRANSACTION으로 연 트랜잭션은 끝났지만 무언가가 잠시 남아 있었던 것으로 보이며, 그 행이 무엇이었는지는 이번에 확인하지 못했습니다. 이 결과는 실무에서도 의미가 있습니다. INNODB_TRX에 행이 보인다는 것만으로는 내 트랜잭션이 아직 열려 있다고 판단할 수 없다는 뜻이기 때문입니다.
실패한 CREATE TABLE, 백업 테이블, 사라진 SAVEPOINT
TC4의 CREATE TABLE tc_side (id INT);는 Error 1050: Table 'tc_side' already exists로 실패했습니다. 테이블은 만들어지지 않았지만, 앞의 1단계 UPDATE는 이미 커밋됐습니다. 직후 이 세션의 트랜잭션은 0개였고, 최종 상태도 다른 DDL과 같은 700과 step2입니다. 오류가 났다고 해서 트랜잭션이 그대로 보존되는 것은 아니었습니다.
TC3의 백업 테이블에는 이 흐름이 그대로 찍혀 있습니다. point_wallet_bak에서 3번 행은 700과 step1로 복사됐습니다. 원본을 보존하려고 만든 백업에, 지금 막 바꾸려던 값이 커밋된 채로 들어간 것입니다. 이 시점의 2번 행도 700과 step2였습니다. 바로 앞 TC2의 변경이 ROLLBACK과 상관없이 이미 확정돼 있었다는 뜻입니다.
백업을 트랜잭션 밖에서 미리 만들고 백업이 끝난 것을 확인한 뒤 수정에 들어가는 순서라면, 이 문제가 끼어들 자리가 없습니다. CREATE TABLE ... LIKE 역시 DDL이므로, 같은 백업이라도 트랜잭션 안에서 만들면 TC3와 같은 결과가 날 것으로 봅니다. LIKE 방식은 이번에 따로 확인하지 않았습니다.
TC7에서는 1단계 직후 SAVEPOINT sp1을 걸고 인덱스를 만들었습니다. 이후 ROLLBACK TO SAVEPOINT sp1은 Error 1305: SAVEPOINT sp1 does not exist로 거부됐습니다. 저장점은 트랜잭션에 속해 있기 때문에, 트랜잭션이 커밋되면서 함께 사라진 것입니다.
DDL 자체도 ROLLBACK 대상이 아니었습니다. 모든 TC가 끝난 뒤 SHOW CREATE TABLE tc_side를 보면 TC2에서 추가한 memo 컬럼과 TC7에서 만든 ix_v 인덱스가 그대로 있습니다.
CREATE TABLE `tc_side` (
`id` int NOT NULL,
`v` int NOT NULL,
`memo` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_v` (`v`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
TC5의 임시 테이블은 INNODB_TEMP_TABLE_INFO에 #sql1d94_1c_e라는 이름으로 남아 있었습니다. 임시 테이블 자체는 ROLLBACK 뒤에도 남았지만 트랜잭션을 끝내지는 않아서, 1단계와 2단계 UPDATE는 함께 롤백됐습니다.
DDL 뒤의 UPDATE까지 붙잡아 두려면
이번 결과에서 피해가 커진 것은 두 가지가 겹쳤기 때문입니다. 암묵적 커밋이 앞의 작업을 확정했고, 세션의 autocommit=1이 뒤의 작업을 한 문장씩 확정했습니다. 앞의 것은 세션 설정으로 막을 수 없지만, 뒤의 것은 줄일 수 있습니다.
문서상 autocommit=0인 세션에서는 암묵적 커밋 뒤의 문장이 새 트랜잭션으로 묶이므로, 2단계는 마지막 ROLLBACK에 걸리게 됩니다. 다만 이번 테스트는 autocommit=1에서만 진행했기 때문에 이 경우의 결과는 확인하지 않았습니다. 그리고 이 설정을 써도 DDL 앞의 1단계는 여전히 되돌릴 수 없습니다.
그래서 작업 스크립트를 실행하기 전에 START TRANSACTION과 COMMIT·ROLLBACK 사이의 구간에 아래 명령이 있는지 먼저 찾아보는 편이 확실합니다. 이번 테스트에서 실제로 트랜잭션을 끝낸 명령과, 끝내지 않은 명령을 함께 표시했습니다.
| 스크립트에서 찾을 명령 | 이번 결과 |
|---|---|
ALTER TABLE, CREATE INDEX | 앞 작업 커밋, CREATE INDEX 뒤 SAVEPOINT 소멸 |
CREATE TABLE ... AS SELECT | 앞 작업 커밋, 백업에 바뀐 값이 들어감 |
실패한 CREATE TABLE | 오류가 나도 앞 작업 커밋 |
ANALYZE TABLE | 앞 작업 커밋, 직후 INNODB_TRX에 행 1개 남음 |
CREATE TEMPORARY TABLE | 트랜잭션 유지, ROLLBACK 정상 |
표에 없는 명령도 같은 성질을 가질 수 있습니다. 판단이 서지 않는 명령이라면, 이번처럼 1단계 UPDATE 뒤에 그 명령을 넣고 ROLLBACK한 다음 행이 1000과 init으로 돌아오는지 개발 환경에서 한 번 돌려 보면 됩니다. 일곱 행짜리 테이블 하나로 바로 답이 나옵니다.




댓글 0
첫 댓글을 남겨보세요.