[MariaDB] Auto Increment 된 ID값 반환하기

 

 

최근 진행중인 프로젝트에서 트러블 슈팅했던 경험을 통해 알게 된 점을 적어볼까 한다.

 

개발했던 로직 flow를 간단하게 설명하자면, 여러 시스템에 있는 고객을 추출하여 중복 고객을 통합하고 신규 시스템에 이관하는 ETL 작업이다. 작업 환경은 고객사의 DB 서버를 사용하는데 CentOS 7에 MariaDB를 rpm으로 설치하여 5.x 버전으로 설치가 되어 있었다.

 

문제는 신규 고객을 Insert 하는 과정에서 발생했는데..

그 전에 잠깐 예전에도 비슷한 프로젝트를 한 적이 있었는데 그 당시 고객사 DB Server는 Oracle을 사용했어서, 시퀀스를 생성하여 nextval, currval로 ID를 return 받을 수 있었다.

 

당연히 이번에도 시퀀스를 생성해서 ID를 받아야지 했지만...

 

MariaDB 공식 Document (https://mariadb.com/kb/ko/sequence-overview/)

 

그랬다.. MariaDB는 시퀀스를 10.3 버전 이후에 지원을 하였다.😂

 

그래서 몇가지 대책을 세워 해결 방법을 찾아보았다.

 

 

 

 

1. 고객사에 MariaDB 버전 상향 요청 ❌

 

 우선 최신 버전이라면, 시퀀스 생성이 가능하니 간단하게 해결할 문제였다. 10.3 부터 시퀀스를 지원하니 그 이후 버전 중에 10.6 LTS 버전을 사용하면 되겠다 싶어 요청을 드렸다. 하지만 자세한 이유는 모르지만 (고객사가 공기업이라 그럴지도?) 정해진 개발 환경을 변경한다는 것이 쉽지 않았다. 변경이 되더라도 그 절차는 굉장히 오래걸릴 것을 인지하여, 다른 방법을 찾아 보았다.

 

 

2. select max(id) from user; ❌

 

단순하게 max값을 가져오면 될까 했지만, 다른 클라이언트에서도 고객 Insert가 일어나기 때문에 동시성 문제로 사용하기엔 위험했다.

 

 

3. ID 발급에 대해 다른 클라이언트는 Auto Increment를 사용하고, ETL 로직은 높은 번호(예를 들어 1억)부터 시작 🔺

 

이번엔 경력이 많은 팀원분께 도움을 구했더니, ID를 따로 관리하는게 어떻겠냐는 피드백을 받았다. 다른 곳에서 발생하는 고객 Insert는 DB의 Auto Increment를 이용하고, ETL에서는 별도의 시퀀스를 관리하여 높은 번호부터 차례대로 부여하라는 것이었다. 

좋은 방법은 아닐 것이라 하셨고 나 또한 그렇게 생각되었지만, 우선 대안을 찾아야 하니 시도를 해보았다. 테스트 결과 Auto Increment는 제일 큰 값을 추적하여 +1을 하기 때문에 Auto Increment값보다 더 큰 값으로 관리는 힘들었다. 

그래서 반대로 Auto Increment를 큰 값으로 설정하고, ETL에서 1부터 시작하는 시퀀스를 관리하면 가능하단 것을 알게 되었다.

 

 

4. LAST_INSERT_ID() ✅

 

더 만족스러운 방법을 찾기 위해 MariaDB 도큐먼트 리스트를 살펴 보던 중 LAST_INSERT_ID라고 써있는 것을 보고 직감적으로 '아 이거다!' 속으로 외쳤다.

링크 : https://mariadb.com/kb/en/last_insert_id/

 

문서에 있는 말을 인용하여 간략하게 무엇인지 설명하자면 아래와 같다.

"가장 최근에 실행된 INSERT 문의 결과로 AUTO_INCREMENT 열에 성공적으로 삽입된 첫 번째 자동 생성 값을 반환"

 

 

 

다만 주의할 점은 예시로 나와있는 쿼리문을 살펴보면,

INSERT INTO t(f) VALUES('a');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

INSERT INTO t(f) VALUES('b');

INSERT INTO t(f) VALUES('c');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

INSERT INTO t(f) VALUES('d'),('e');

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

 

위처럼 단일 Insert문을 실행하고, ID를 받게 되면 정상 수행되지만, 다중 Insert문을 실행하게 되면 두번째 이후의 Insert 결과가 반영되지 않는다. 때문에 정확한 값을 전달받기 위해선 단일 Insert 문을 활용해야 한다.

 

하나 더 걱정 되었던 점은, 여러 클라이언트에서 Insert를 실행할 때, 과연 정확한 값을 반환해주냐는 것이었다.

답은 아래 mysql 공식 문서에서 찾을 수 있었다.

링크 : https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

 

또 다시 문서에 나와있는 말을 번역하여 인용하자면,

 

생성된 ID는 연결별로 서버에서 유지 관리됩니다. 이것은 주어진 클라이언트에 대한 함수에 의해 반환된 값이 해당 클라이언트에 의해 AUTO_INCREMENT 열에 영향을 미치는 가장 최근 명령문에 대해 생성된 첫 번째 AUTO_INCREMENT 값임을 의미합니다. 이 값은 다른 클라이언트가 자체 AUTO_INCREMENT 값을 생성하더라도 영향을 받을 수 없습니다. 이 동작을 통해 각 클라이언트는 다른 클라이언트의 활동에 대한 걱정 없이 잠금이나 트랜잭션이 필요 없이 자신의 ID를 검색할 수 있습니다.

 

즉 다른 클라이언트를 고려하지 않아도 된다는 것이다. 다만 Last_Insert_Id()를 사용하기 위해선, 같은 DB Connection 을 사용하고 있어야 한다는 점만 고려하면 되었다.