Auto Increment 에 대한 생각해보기

그동안 MYSQL에서 InnoDB와 MyISAM 두 스토리지를 선택해서 사용했는데, 8.0 이후로는 InnoDB 기반으로 다 넘어갔다고 한다.

따라서 해당 글은 InnoDB 스토리지 기반의 MYSQL 기준으로 작성했다.(MYSQL 8.0 이후)  

이전버전에는 MyIsam 스토리지도 사용해서, 조금 다른 결과가 나올수 있다.

 

Auto Increment는 보통은 id값에 pk와 함께 많이들 거는 제약조건이다.

 

사용자에게 받기보다는 DB에게 알아서 중복되지 않도록 값을 증가시키려한다.

 

이렇게만 보면 문제 생길 것이 없는데, 그럼에도 생각해볼거리가 있다!!

---

데이터를 삭제 하고 난 뒤에 데이터를 추가하면 

Auto-increment가 걸린 필드값에 공백이 생기는데 안생기게 할 수는 없나?

 

 

해당 테이블은 10개의 데이터가 있었고 9번과 10번을 삭제한 상태다.

 

이후 다시 10개를 삽입한다.

DB가 마지막 값을 10으로 인지한 상태여서, 11부터 20 값이 들어간다.

 

왜 이런 결과가 나왔을까? 설정으로 9부터 다시 넣을 수는 없을까?

 

결론은 불가능하다. 물론 수동으로 삽입할수야 있지만 데이터의 무결성 때문에 권장되지 않는다고 한다.

 

InnoDB 의 Auto_Increment counter 초기화 방식

InnoDB는 기본적으로 메모리기반 스토리지 방식이라서,
MYSQL 서버가 구동되고난 후 insert문이 실행되는 시점에 

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

다음과같은 쿼리를 날려 사용할 counter에 쿼리결과+1 값을 초기화해둔다. 

 

---

Auto-Increment 도 결국엔 counter라는 공유자원을 사용하게 되는데,  동시성 문제 해결은?

 

트랜잭션 1 : 데이터 100000 개 삽입도하고, 데이터 100000000개 조회도 해야돼!

트랜잭션 2: (늦게 도착해서) 나 2개만 추가하면 되는데....

잘 생각해보면 counter라는 존재때문에 insert문에는 Auto-Increment  테이블 전체에 락을 걸어주는게 이상적이다.

 

하지만 그렇게되면 저 1번 트랜잭션이 어떤 작업을 할줄 알고, 무작정 대기하는건 굉장히 비효율적일 것이다.

 

그래서 InnoDB에는 Auto-Increment를 위한 Lock이 따로 존재한다.

 

레코드 락

우리가 일반적인 데이터를 삽입,수정,삭제할 때 쓰이는 락이다. 레코드 단위만 잠긴다.

 

Auto-increment 락

테이블 레벨의 락이지만
락이 풀리는 단위는 트랜잭션이 아닌, 쿼리(Statement) 단위다.

 

즉 위 예시에서 트랜잭션 1번의 삽입 과정만 끝나고 트랜잭션 2번도 접근하게 설정할 수 있다.

아래에서 자세히 살펴보자.

---

InnoDB Auto-Increment Lock Mode

여기엔 간단히 적었지만 이곳에 가면 더 자세히 파악할 수 있다.

 

1. traditional lock mode

• innodb_autoinc_lock_mode = 0
• 트랜잭션이 끝날때까지 적용되는 lock이 아닌 해당 쿼리 실행시까지만 유지되는 lock
• Insert가 붙는 모든 쿼리에 적용된다.

2. consecutive lock mode(default)

• innodb_autoinc_lock_mode = 1
• 모든 insert가 아닌, 간단한 insert values 할당 과정에서만 동시접근을 제어한다. -> 사실상 안전성과 동시성을 두마리를 잡기에 가장 최적화 되어있다

3. interleaved lock mode

• innodb_autoinc_lock_mode = 2
• 테이블수준에 락을 걸지 않는다.
• 가장 동시성이 높지만, 그만큼 쿼리가 어떤 순서로 진행될지 모른다 

 

 

Reference

 

- https://myinfrabox.tistory.com/122

- https://velog.io/@inhwa1025/MySQL-autoincrement-locking-%EC%84%A4%EC%A0%95-t2dj0741

- http://intomysql.blogspot.com/2010/12/innodb-lock_2880.html

- https://www.letmecompile.com/mysql-innodb-auto-increment-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94/