채번하는 부분을 해당 레코드에 Lock을 걸고 MAX+1하도록 처리하셔야 합니다.

물론 이렇게 하면 성능이 저하됩니다.

(1)

SELECT seq  INTO max_seq  FROM test  WHERE seq = (SELECT MAX (seq) FROM test)FOR UPDATE; -- 해당 레코드에 lock을 걸어서 직렬화한다. (다른 세션에서 동일한 SELECT...FOR UPDATE가 들어오면 Lock을 대기해야 합니다.)

(2)
INSERT INTO test
     VALUES (max_seq + 1);  --max_seq는 위의 SELECT에서 얻은 최종 순번

(3)

COMMIT;  -- 트랜잭션이 완결되고 위의 Lock이 모두 해제 된다.

이 방법보다는 시퀀스를 사용하는게 성능상 훨씬 좋습니다.

저 하나 궁금한 것이 있는데요...

이 경우 자동 증가하려는 컬럼이 기본키가 아닌가 봅니다?

만약 기본키라면 무결성 에러가 날거니까요...

김주현님께서 답변하신 글도 아마 키가 아니라고 간주하신 거 같아요? 맞나요?

그런데... 키가 아닌데 유일키 순번을 주는 경우가 있나요?

만약 기본키는 아니라도 유일한 순번을 준다는 것 자체가 최소한 후보키라는 것이고 그렇다는 것은 유니크가 걸려야 한다는 뜻이라 생각하는데요....

제가 김주현님의 답변이 키가 아닌 경우라고 가정하는 이유는

만약 키라면 굳이 lock 걸 필요 없이 중복되면 자연히 에러가 나기 때문이고요...

lock을 걸어도 동시 트랜잭션의 두번째 insert 하는 세션은 어차피 에러가 나기 때문에 트랜잭션이 실패하므로 lock을 거는 것이 의미가 없기 때문입니다.

즉 유일키이기만 하면 insert ~ select nvl(max (),0) + 1 만 해도 원하는 것을 성취하고 두번째는 자동 실패하고 두번째를 실패하지 않게 하려면 시퀀스를 쓰든가 no wait를 사용하여 loop를 태워야 합니다.

그리고 제가 키가 아니라는 전제하여 테스트를 한 것을 올립니다.

만약 키가 아니면 최초 insert에 운나쁘게 동시 트랜잭션이 되면 중복이 발생할 수 있음을 보여줍니다.

create table t_lock
(
    id number
)
/
create or replace procedure pr_1
is
    max_seq         number;
    dt1             date;
begin
    begin
    SELECT nvl(id,0)  INTO max_seq  FROM t_lock  WHERE id = (SELECT MAX (id) FROM t_lock)FOR UPDATE;
    exception when no_data_found then
        max_seq :=0;
    end;
    dt1 := sysdate;
    for v in 1..1000000 loop
        exit when ( sysdate - dt1 > 1/24/60/60 * 30) ;
    end loop;
    insert into t_lock(id) values (nvl(max_seq,0) + 1);
    commit;
end;
/
create or replace procedure pr_2
is
    max_seq         number;
begin
    begin
    SELECT nvl(id,0)  INTO max_seq  FROM t_lock  WHERE id = (SELECT MAX (id) FROM t_lock)FOR UPDATE;
    exception when no_data_found then
        max_seq :=0;
    end;
    insert into t_lock(id) values (nvl(max_seq,0) + 1);
    commit;
end;
/
 

이렇게 만들고

두개의 SQLPLUS 세션을 열어서

하나에서는

exec pr_1

다른 하나에서는

exec pr_2

을 실행시키면

중복 데이타가 발생합니다

원리는 이렇습니다.

select for update는 행을 lock 겁니다.

즉 아무것도 없을 때는 lock 걸 행도 없습니다.

그러므로

pr_1이 lock 거는 행은 없습니다.

그런 상태에서 loop를 타며 대기할 때

pr_2 가 들어오면

pr_2 도 lock 걸 행이 없습니다.

결국 pr_2가 먼저 수행되어 한행이 생깁니다

그 다음 대기중이던 pr_1이 수행되면 행이 2개 생깁니다.

이런 케이스는 제가 일부러 만들기는 했지만 불가능한 시나리오는 아닙니다

자꾸 써서 죄송합니다.

제 친구가 왜 lock을 걸어도 에러가 나냐고 물어보아서 혹시 싶어서 설명을 드리려고 들어 왔습니다.

select for update를 쓰든 아니든 모든 select는 내부적인 cursor가 열리는 시점의 스냅샷에서 일관된 읽기를 합니다.

즉 거의 동시 트랜잭션이 발생하여 먼저 테이블에 lock을 건 세션이 아직 insert 후에 commit을 하지 않은 상태일 때 두번째 세션이 select를 하면 lock wait 상태가 되기는 하지만 읽게되는 값은 insert 하기 전 값을 읽습니다.

즉 둘다 같은 값을 읽게 되는 것입니다.

이 것을 테스트하는 방법은 간단합니다

먼저 위의 테이블을 만드는데...

에러를 내기 위해 유니크로 만듭니다.

create table t_lock
(
    id number primary key
)
/
그리고 프로시져를 만듭니다,

create or replace procedure pr_i
is
    max_seq         number;
begin
    begin
    SELECT nvl(id,0)  INTO max_seq  FROM t_lock  WHERE id = (SELECT MAX (id) FROM t_lock)FOR UPDATE;
    exception when no_data_found then
        max_seq :=0;
    end;
    insert into t_lock(id) values (nvl(max_seq,0) + 1);
    commit;
end;
/
 

그리고 한 행 insert

insert into t_lock (id) values(1);

commit;

그 다음 세션을 세개 준비합니다.

먼저 제1 세션에서

다음과 같이 update를 하고 commit;을 하지 않습니다.(그래야 lock이 걸려서 동시 상황을 연출합니다.)

update t_lock

set id = id + 1;

그런 다음 나머지 두개의 세선에서

각각

exec pr_i

exec pr_i

를 실행합니다

그러면

두 세션은 대기 상태가 됩니다.

그 상태에서 첫번째 update를 친 세션을

commit; 합니다.

그러면

나머지 두개의 세션중 하나는 insert 가 성공하고 두번째는 실패하게 됩니다.