Jiniya

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 값 파악
select last_insert_id() from table_a;

last_insert_id() 를 통해 가장 최근에 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
즉 세션마다 유니크한 값을 유지할 수는 있지만 모든 세션에서 유닉크한 값은 될 수 없다.
이와 같은 동시성 해결을 위해 다음과 같은 방법을 생각해 볼 수 있다.

# 자동 커밋을 안되게 하여 트랜잭션 처리를 가능하게 한다.
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);