mariadb에서 id값을 auto_increment로 설정했을 때 인서트를 실패해도 id값이 증가된다. 그 이유는 무엇일까?
1. 기본 개념
AUTO_INCREMENT는 새로운 행을 넣을 때 자동으로 증가하는 숫자를 만들어 주는 기능이다.
예를 들어, id 컬럼을 AUTO_INCREMENT로 하면 id값이 자동으로 올라간다.
문제는 insert가 실패해도 id는 자동으로 올라간다는 것이다.
2. 왜 이런 일이 발생하는가?
이건 AUTO_INCREMENT 값이 언제 증가되느냐와 관련 있다.
MariaDB(MySQL 계열)는 실제로 데이터를 테이블에 넣기 전에 미리 번호를 할당해 둔다.
왜냐하면 여러 사용자가 동시에 INSERT를 할 때, 중복되지 않고 고유한 번호를 빠르게 배정하려고 즉, 동시성 이슈를 해결하기 위해서이다.
1. 새로운 행을 넣으려는 순간 → id 번호를 미리 가져옴
2. 나중에 INSERT가 성공하면 저장됨
3. 실패하면 저장 안 되지만, 번호는 이미 "소모됨"
3. innodb_autoinc_lock_mode와의 관계
auto_increment_lock_mode는 AUTO_INCREMENT 값 생성 시 락 전략을 결정한다.
- 0 (traditional)
옛날 방식. INSERT 할 때마다 테이블을 잠가서(auto_increment 충돌 방지) 번호를 하나씩 안전하게 배정한다.
그래도 실패하면 번호는 여전히 올라간다. - 1 (consecutive, 기본값)
일반적인 단일 INSERT는 번호를 미리 할당하고 즉시 락을 해제한다. 다중 INSERT(INSERT ... SELECT 같은 경우)는 실행하는 동안 테이블을 잠가서 번호를 연속적으로 준다.
그래도 실패하면 번호가 건너뛴다. 락은 빠르게 해제되어 성능이 향상된다. - 2 (interleaved)
가장 자유로운 모드. 동시에 여러 INSERT가 들어오면 각 커넥션이 필요한 만큼 번호를 "미리 뽑아" 쓴다.
그래서 실패하거나 롤백돼도 번호가 크게 점프할 수 있다.
최고 성능과 최고 동시성을 가졌지만 값의 순서가 보장되지 않을 수 있다.
4. 실무적 영향
장점
- 높은 동시성과 처리량
- 데드락 위험 감소
- 단순한 구현
단점
- ID 값에 갭(gap) 발생
- ID 값의 연속성 보장 안 됨
5. 결론
MariaDB는 ID의 완벽한 연속성보다 성능과 동시성을 우선시하는 설계를 채택했다. 만약 연속적인 ID가 반드시 필요하다면, 애플리케이션 레벨에서 별도 시퀀스 관리 로직을 구현해야 한다.
진짜 데이터의 연속적인 번호가 필요하다면 auto_increment를 사용하지 않고 어플리케이션 레벨에서 별도의 id를 만들어 직접 관리하는 방식을 택해야겠다.
1) 저장 프로시저를 만들어 관리
sequence_id의 MAX값을 읽어와 1 증가시킨 후 v_next_seq 변수에 담아서 인서트해준다.
- COALESCE(MAX(sequence_id), 0) + 1 INTO v_next_seq
FOR UPDATE는 **행 레벨 락(Row-level Lock)**을 걸어서 다른 트랜잭션의 접근을 제어하는 구문. 동시성 문제를 해결한다.
- 조회한 행들에 **배타적 락(Exclusive Lock)**을 건다
- 다른 트랜잭션이 해당 행을 수정하거나 FOR UPDATE로 조회하지 못하게 막는다
- 현재 트랜잭션이 COMMIT 또는 ROLLBACK 될 때까지 락 유지
-- AUTO_INCREMENT 대신 직접 ID 관리
DELIMITER //
CREATE PROCEDURE insert_user_simple(
IN p_name VARCHAR(100),
IN p_email VARCHAR(100)
)
BEGIN
DECLARE v_next_seq BIGINT;
START TRANSACTION;
-- 다음 sequence_id 계산
SELECT COALESCE(MAX(sequence_id), 0) + 1 INTO v_next_seq
FROM users FOR UPDATE;
-- INSERT
INSERT INTO users (sequence_id, name, email)
VALUES (v_next_seq, p_name, p_email);
COMMIT;
-- 방금 추가한 데이터 반환
SELECT id, sequence_id, name, email, created_at
FROM users
WHERE sequence_id = v_next_seq;
END//
DELIMITER ;
-- 사용
CALL insert_user_simple('Alice', 'alice@example.com');
-- 결과가 바로 출력됨
2) 트리거를 사용하는 방법
트리거를 본격적으로 사용해본 적이 없어서 문법을 이해하는 데 시간이 걸렸다.
동시성 문제가 발생할 수 있으니 maxk값 조회 시 FOR UPDATE로 락을 걸 수 있다.
=> 이 경우 insert가 반드시 트랜잭션 안(BEGIN ... COMMIT 블록 안)에서 실행되어야 한다.
-- 테이블 구조 (동일)
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
sequence_id BIGINT UNIQUE NOT NULL,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
DELIMITER //
-- INSERT 트리거
CREATE TRIGGER before_insert_users_simple
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
DECLARE v_max_seq BIGINT;
IF NEW.sequence_id IS NULL OR NEW.sequence_id = 0 THEN
-- 직접 MAX 값 조회
SELECT COALESCE(MAX(sequence_id), 0) + 1 INTO v_max_seq FROM users;
SET NEW.sequence_id = v_max_seq;
END IF;
END//
-- DELETE 후 자동 정리는 필요 없음 (다음 INSERT 시 자동으로 MAX+1)
DELIMITER ;
트리거 기본문법
CREATE TRIGGER 트리거이름
트리거_시점 (BEFORE | AFTER)
트리거_이벤트 (INSERT | UPDATE | DELETE)
ON 테이블이름
FOR EACH ROW
BEGIN
-- 실행할 SQL 문
END;키워드 설명
- BEFORE / AFTER
- BEFORE: 데이터가 실제로 테이블에 들어가기 전에 실행됨.
- AFTER: 데이터가 테이블에 들어간 후에 실행됨.
- INSERT / UPDATE / DELETE
- 어떤 이벤트에 반응할지를 정함. (새로운 행이 들어오거나 수정되거나 삭제될 때)
- FOR EACH ROW
- 행 단위로 실행됨. 즉, INSERT 한 건마다 트리거가 한 번씩 실행됨.
- NEW / OLD
- NEW: 새로 들어오는 값(INSERT 시에는 새 레코드 값)
- OLD: 기존에 있던 값(UPDATE나 DELETE 시에만 사용 가능)