배경
일련번호 생성 시 중복 일련번호가 발생하는 이슈를 해결했던 방법을 공유해보겠습니다.
기존 일련번호를 생성하는 로직은 흔히 MAX +1 라고 불리는 방법으로 현재 가장 큰 일련번호을
가져와 +1 하여 다음 일련번호를 생성하는 방식입니다.
이런 일련번호 생성 방식은 몇 가지 문제가 있는데 중복이 발생할 수 있다는 것 입니다.
그럼에도 사용하는 이유는 다음과 같습니다.
1.
중복이 발생해도 상관없다.
2.
중복이 발생할 확률이 거의 없다.
3.
구현이 간단하다
문제는 해당 시스템의 일련번호 생성이 빈번해지고 여러 사용하는 기능들이 생기면서
높은 확률로 중복이 발생할 수 밖에 없게 됐습니다.
중복이 거의 발생하지 않으려면 해당 일련번호를 사용하는 기능은 겹치게 실행되면 안됩니다.
문제를 해결하기 위해 기존 방식을 분석했을 때 결과는 다음과 같았습니다.
크게 2가지로 일련번호 생성을 활용하고 있었습니다.
1.
오늘 날짜 기준으로 순차적으로 일련번호 생성
예시> 202310290001… 202310290002… 202310290003….
2.
오늘 날짜 기준으로 순차적으로 일련번호 생성 + 비즈니스 로직 반영
기존 알고 있는 일련번호 생성 방법은 다음과 같습니다.
1 . 현재 기능과 같은 MAX+1
2.
DBMS Sequence 기능 사용
2번에서 Oracle 같은 DBMS는 Sequence 기능을 제공하지만 MySQL 이나 MariaDB(특정 버전 이상에
서 지원) Sequence 기능 대신 Auto Increment을 대체하여 사용할 수 있습니다.
개발 과정
•
테스트 환경
◦
Spring boot 2.x
◦
Mybatis 3.x
◦
MariaDB 10.1.x
기존 일련번호 생성 로직이 포함되어 있는 SQL 이 있고 이 SQL 문을 해당 SQL은 거의 모든 등록 기능
에서 사용하고 있습니다.
다음 Sudo Code는 각각 A, B 기능과 시퀀스 생성 createSequenceA, B SQL입니다.
<예시 A>
@Transactional
public void A {
long result = mapper.createSequenceA()
for (...) {
result++
...
}
}
Java
복사
SELECT MAX(no) + 1
FROM A
SQL
복사
<예시 B>
@Transactional
public void B {
for (...) {
...
long result = mapper.createSequenceB()
...
}
}
Java
복사
CASE
WHEN ... THEN ...
SELECT MAX(no) + 1
FROM A
WHEN ... THEN ...
END
SQL
복사
1차
처음에는 접근했던 방식은 시퀀스 용 테이블을 생성하고 SELECT ~ FOR UPDATE 문으로 Row Lock
을 걸어서 중복 문제를 해결하려고 했지만 부작용으로 부하가 걸리면 잦은 Row Lock 발생으로 Lock
Wait Timeout이 발생하였습니다.
그리고 Spring Framework 에서 제공하는 Transaction이 적용된 일부 기능에서 Transaction 이
Commit될 때 까지 Lock이 발생하는 부분이 있어 그런 기능의 자바 코드들도 손을 대야 했습니다.
따라서 수정 범위가 넓어지는 부작용이 추가됐습니다.
수정 비용을 최소화하기 위해 최대한 SQL을 사용하는 기능들의 서버 코드는 수정은 하지 않고 SQL
내부에서 해결하기로 결정했습니다.
수정 중 예시 A 같은 코드도 있었는데 MAX 번호를 for문 밖에서 가져온 후 리턴 받아 서비스 메서드
에서 for문을 돌면서 증가하면서 사용하고 있었습니다.
이런 경우 for문 마다 SQL을 호출하지는 않지만 해당 서비스 메서드가 Commit 되기 전에
다른 기능에서 Commit 된다면 이 서비스 레이어에서 사용하는 일련번호는 중복이 될 수 밖에
없습니다.
따라서 이런 코드들도 수정을 해야 했습니다.
2차
일련번호가 중복이 발생하지 않아야 한다는 점에서 트랙잭션 커밋, 롤백에 매몰 되어 있었는데
다시 정리해보면 일련번호를 생성해서 처리하는 기능들의 실패해도 실패 여부와 관계없이
다음 일련번호를 생성해서 반환하면 됩니다. 반드시 중간에 사용하지 않는 일련번호가 생긴다 해도
문제가 될 상황이 아닙니다.
•
MariaDB의 시퀀스 목적의 MyIsam 엔진 테이블
시퀀스 목적이므로 SEQ 정보를 가지는 컬럼만 있어도 됩니다.
이번 이슈에 경우 날짜에 대한 시퀀스 초기화가 필요하기 때문에 기준 날짜 컬럼을 추가합니다.
•
MariaDB의 MyISAM 테이블 엔진
InnoDB 엔진과 달리 MyISAM 엔진의 경우 트랜잭션을 사용하지 않으므로 MyISAM 엔진 사용.
MyISAM 엔진을 사용할 경우 주의할 점은 테이블 락을 사용하기 때문에 여러 기능을 위한 행을
생성해서 사용할 경우 테이블 락으로 대기하는 경우가 생길 수 있으므로 각각에 기능의
일련번호 사용에 따라 테이블은 N개 생성 해야 하고 행은 1개만 사용 합니다.
•
시퀀스 테이블의 일련번호들을 초기화, 증가 값, 현재 값 리턴 DB 함수
DB 프로시저의 경우 SQL 문 내부에 사용할 수 없지만
SQL 내부에서 상황에 따라 현재, 증가된 일련번호를 반환 해줘야 하므로 DB 함수 사용.
다음 구현된 코드를 확인해보겠습니다.
일련번호 테이블 생성 SQL
CREATE TABLE `test_seq` (
`SEQ` BIGINT(20) NOT NULL DEFAULT '1',
`REFERENCE_DATE` DATE NOT NULL
)
COMMENT='Test Sequence Table'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;
SQL
복사
현재 일련번호 반환 DB 함수
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_SEQ_CURRENT_VALUE`()
RETURNS bigint(20)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'return current value'
BEGIN
DECLARE val BIGINT;
DECLARE today INT;
SELECT DATEDIFF(REFERENCE_DATE, CURRENT_DATE()) INTO today FROM TEST_SEQ;
IF today < 0 THEN
UPDATE TEST_SEQ SET SEQ = 1, REFERENCE_DATE = NOW();
END IF;
SELECT SEQ INTO val FROM TEST_SEQ;
return val;
END
SQL
복사
다음 일련번호 반환 DB 함수
CREATE DEFINER=`root`@`localhost` FUNCTION `GET_SEQ_NEXT_VALUE`()
RETURNS bigint(20)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT 'return next sequence'
BEGIN
DECLARE val BIGINT;
DECLARE today INT;
SELECT DATEDIFF(REFERENCE_DATE, CURRENT_DATE()) INTO today FROM TEST_SEQ;
IF today < 0 THEN
UPDATE TEST_SEQ SET SEQ = 1, REFERENCE_DATE = NOW();
SET val = 1;
else
SELECT SEQ INTO val FROM TEST_SEQ;
SET val = val + 1;
UPDATE TEST_SEQ SET SEQ = val;
end if;
return val;
END
SQL
복사
테스트
INSERT INTO TEST_SEQ (SEQ, REFERENCE_DATE) VALUES (1, NOW()); // 초기화
SELECT GET_SEQ_CURRENT_VALUE(); // 현재 값 반환 1
SELECT GET_SEQ_NEXT_VALUE(); // 다음 값 반환 2
SQL
복사
호출 시 현재 값과 다음 값을 반환 하는 것을 확인할 수 있습니다.
이제 현재 날짜를 붙여서 리턴 할 수 있게 SQL 문 내부에서 사용해보겠습니다.
// 202311040001
SELECT CONCAT(DATE_FORMAT(NOW(), '%Y%m%d'), LPAD('000' + GET_SEQ_NEXT_VALUE(), 4, 0))
FROM DUAL
SQL
복사
날짜 앞에 특정 문자열 “ABC” 를 붙이고 뒤에 “000” 붙인 다음 일련번호를 붙여주겠습니다.
이런 식으로 상황에 따라 일련번호를 생성해줄 수 있습니다.
// ABC202311040001
SELECT CONCAT('ABC', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD('000' + GET_SEQ_NEXT_VALUE(), 4, 0))
FROM DUAL
SQL
복사
트랜잭션을 사용하지 않는 엔진이기 때문에 해당 모듈을 사용하는 기능들은
성능적으로 개선이 되었고 특히 Lock Wait Timeout 나 중복 문제도 발생하지 않습니다.
마무리
상황에 따라 DB의 기능들을 이용할지 서버 언어를 통해 해결을 할지 여러가지 측면에서
많이 생각해본 이슈였습니다.
포스팅에 사용한 모든 코드는 Github에 있습니다.
추가
MariaDB 10.3 버전부터 Sequence 가 도입됐다고 합니다.
만들어서 사용도 가능하지만 10.3 이상 버전부터는 해당 기능을 사용하면 더 편하게 구현이
가능합니다.
