테마
숫자·날짜 타입과 AUTO_INCREMENT 2
AUTO_INCREMENT는 INSERT마다 1씩 커지는 일련번호를 자동으로 매기는 칼럼 속성이다. 번호 하나 주는 일이 단순해 보여도, 여러 세션이 동시에 INSERT하면 누가 어떤 번호를 받을지 정하는 잠금이 필요하다. 그 잠금 방식이 동시 삽입 성능과 복제(replication) 안전성을 좌우하고, 번호에 빈틈이 생기는 이유도 여기서 나온다.
왜 쓰나
AUTO_INCREMENT는 업무 의미가 없는 번호로 행을 구분하는 대리 키(surrogate key)를 만들 때 쓴다. 보통 PK(Primary Key, 기본 키)로 쓰는데, InnoDB는 행을 PK 순서로 정렬해 저장하므로 계속 커지는 번호는 늘 클러스터링 인덱스의 맨 끝에 붙는다. 그래서 중간에 끼워 넣느라 저장 공간을 쪼개는 일이 적어 삽입이 효율적이다. 번호는 기본적으로 칼럼의 현재 최댓값 + 1이고 1부터 시작하며, 음수 값은 지원하지 않는다.
잠금 모드
동시에 여러 INSERT가 번호를 받으려 할 때 쓸 잠금을 innodb_autoinc_lock_mode1가 정한다.
| 값 | 이름 | 동작 |
|---|---|---|
| 0 | traditional | 문장이 끝날 때까지 테이블 단위 AUTO-INC 잠금을 쥔다. 가장 안전하지만 동시성이 낮다 |
| 1 | consecutive | 행 수를 미리 아는 단순 INSERT는 짧은 뮤텍스(mutex)만, 행 수를 모르는 대량 INSERT는 테이블 단위 잠금 |
| 2 | interleaved | 테이블 단위 잠금을 쓰지 않는다. 가장 빠르지만 한 문장이 받는 번호가 연속이 아닐 수 있다 |
AUTO-INC 잠금은 번호를 나눠 주는 동안 같은 테이블의 다른 INSERT가 번호를 받지 못하게 막는 특수한 테이블 수준 잠금이다. 모드 1에서 말하는 "행 수를 모르는 대량 INSERT"는 INSERT ... SELECT나 LOAD DATA처럼 실행해 봐야 몇 행이 들어갈지 아는 문장이다. 이런 문장이 테이블 단위 잠금을 쥐면 한 문장이 받는 번호가 1, 2, 3처럼 연속으로 보장된다. 모드 2는 이 잠금을 없애 여러 문장이 번호를 번갈아 받게 한다.
MySQL 8.0부터 기본값은 2이고, 그 전(5.7)은 1이었다. 복제의 기본 형식이 SQL 문장을 그대로 보내는 방식에서 바뀐 행 값을 보내는 행 기반(row-based)2 방식으로 바뀐 것을 반영한 변화다. 바이너리 로그(binary log)가 행 값을 그대로 적으면 레플리카(replica)는 받은 번호를 그대로 쓰므로 번호가 어긋날 일이 없다. 반대로 SQL 문장을 적는 문장 기반(statement-based) 형식이라면 레플리카가 문장을 다시 실행하며 번호를 새로 매기므로, 모드 2에서는 원본과 다른 번호가 나올 수 있다. 이때는 원본 서버와 레플리카 모두 innodb_autoinc_lock_mode를 0이나 1로 둔다. 두 형식의 차이는 바이너리 로그 포맷과 동기화 방식에서 다룬다.
번호에는 빈틈이 생긴다
어떤 모드든 한 번 발급한 번호는 트랜잭션(transaction)을 롤백(rollback)해도 돌려받지 않는다. 번호를 되돌리려면 그 번호를 받은 뒤의 모든 INSERT를 기다려야 해서 동시성이 무너지기 때문이다. 이 밖에도 빈틈이 생기는 경우가 있다.
- 없으면 넣고 있으면 고치는 업서트(upsert), 즉
INSERT ... ON DUPLICATE KEY UPDATE는 행을 넣을지 고칠지 나중에 정해진다. 번호를 먼저 받았다가 고치는 쪽으로 가면 그 번호는 버려진다. VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')처럼 번호를 일부만 직접 준 문장은 모드 1에서 문장의 행 수(네 행)만큼 번호를 한 번에 잡아 두지만, 실제로 번호가 필요한 행은 NULL인 두 행뿐이라 남은 두 번호는 버려진다.- 모드 2에서는 대량 INSERT가 다른 INSERT와 동시에 돌 때 번호가 띄엄띄엄 나갈 수 있다.
그래서 AUTO_INCREMENT 값은 "유일하고 대체로 커진다"만 믿는다. 빈틈 없는 연속 번호(예: 회계 전표 번호)가 업무 규칙이면 별도 번호 테이블을 잠금 읽기(locking read)로 읽어 증가시키는 식으로 따로 만든다.
재시작과 카운터
카운터는 "다음에 줄 번호"다. 5.7까지는 카운터가 메모리에만 있어서, 재시작 후 첫 INSERT 때 SELECT MAX(id) ... FOR UPDATE와 같은 방식으로 다시 계산했다. 그래서 최댓값 행을 지운 뒤 재시작하면 지운 번호가 다시 발급될 수 있었다. 삭제된 주문 번호가 새 주문에 다시 붙는 식의 혼동이 생길 수 있었던 것이다.
8.0부터는 카운터가 바뀔 때마다 리두 로그(redo log)에 적고, 체크포인트(checkpoint)마다 데이터 딕셔너리(data dictionary)에 저장한다. 그래서 정상 재시작이든 장애 뒤 복구든 카운터가 이어진다. 단, 카운터를 적은 리두 로그가 디스크에 쓰이기 전에 서버가 죽으면 이미 나간 번호가 다시 발급될 수 있다고 매뉴얼은 적는다.
한 가지 더, 8.0.17에서 폐기 예정이 된 뒤 8.4.0부터는 FLOAT·DOUBLE 칼럼에 AUTO_INCREMENT를 붙일 수 없다. 이런 칼럼이 있으면 8.4로 올리기 전에 정수 타입으로 바꿔야 한다.
면접 포인트
- 잠금 모드: 0 traditional(문장 끝까지 테이블 잠금) · 1 consecutive · 2 interleaved(8.0+ 기본, 가장 빠름).
- 모드 2가 기본인 이유: 복제 기본 형식이 행 기반이라 번호가 레플리카에서 다시 매겨지지 않는다. 문장 기반이면 0·1.
- 번호는 롤백해도 반납하지 않는다. 업서트·일부 번호 지정·대량 INSERT도 빈틈을 만든다.
- 5.7은 재시작 때 최댓값으로 다시 계산해 번호가 재사용될 수 있었고, 8.0부터 카운터가 리두 로그·데이터 딕셔너리로 유지된다.
참고