MySQL / Oracle 다음 시퀀스 값 가져오기

db 테이블에서 Oracle sequence 나 MySql auto_increment 속성으로 설정된 컬럼에서 다음 값을 파악해야 하는 경우가 있다.

[Oracle]
# 시퀀스 생성
create sequence id_seq start with 1 increment by 1 maxvalue 9999999999;
# 다음 시퀀스 파악
select nextval() from table_a;

nextval() 할 때마다 자동 증가된 값을 파악할 수 있다.
멀티쓰레드 환경에서 다수의 db 커넥션으로 수행해도 겹치지 않는 증가 값을 가져올 수 있다.

[MySQL]
# 테이블 생성시 컬럼 속성을 auto_increment 로 지정
create table table_a (
seq_id bigint(19) not null AUTO_INCREMENT, # (자동증가)
col1 int(9) default null,
col2 int(9) default null,
PRIMARY KEY (seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 최근에 inset 된 후 id 값 파악
# last_insert_id() 를 통해 가장 최근에 insert id 를 파악할 수 있다.
select last_insert_id();


하지만 mysql 의 경우 db 커넥션 마다 독립된 id 값을 별도로 관리하기 때문에
다수의 db 커넥션에서 last_insert_id() 시 같은 값을 가질 수 있다.
예를 들면...
A, B 가 각각 다른 db 커넥션을 가지고 같은 테이블에 insert 를 한다고 했을때
A가 insert 후 last_insert_id() 하면 1
B가 insert 후 last_insert_id() 하면 1
즉 세션마다 유니크한 값을 유지할 수는 있지만 모든 세션에서 유닉크한 값은 될 수 없다.

실제 테스트 해보니 커넥션마다 독립적으로 마지막으로 insert 한 id(auto increment)값을 파악할 수 있었다.
다음과 같은 순서로 쿼리했을때
커넥션1로 insert 3번
커넥션2로 insert 1번
커넥션1에서 select last_insert_id();  -> 3
커넥션2에서 select last_insert_id();  -> 1
와 같이 원하는 값을 얻을 수 있었다.


다음과 같은 다른 방법을 생각해 볼 수 있다.

# 자동 커밋을 안되게 하여 트랜잭션 처리를 가능하게 한다.
set autocommit=0;

# InnoDB 에서의 락 방법
# for update 는 쿼리(트랜잭션)를 실행한 세션을 제외한 모든 세션은 row 에 대한 접근 대기 상태가 된다. (for update 외에 select 는 가능한 lock in share mode 가 있다.)
# 쿼리가 종료되면 락은 풀린다.
# insert 하기전 미리 자동 증가 값을 파악할 수 있다.
select ifnull(max(seq_id)+1, 1) from table_a for update;

# insert 하면 자동증가 값은 위에서의 max(seq_id)+1 이 된다.
insert into tabe_a(col1, col2) values(1, 2);