MySQL Sequence 시퀀스

Real MySQL 1판 922페이지

create table jpa_sequences ( name varchar(16) NOT NULL, curval bigint not null, primary key(name)) ENGINE=MyISAM;

insert into jpa_sequences set name='books', curval=(@v_current_value:=1) on duplicate key update curval=(@v_current_value:=curval+1);

select @v_current_value


        final EntityManager entityManager = getEntityManager();
        final Query generateNextSequenceQuery = entityManager.createNativeQuery("insert into jpa_sequences set name=?, curval=(@v_current_value\\:=1) on duplicate key update curval=(@v_current_value\\:=curval+1);");
        generateNextSequenceQuery.setParameter(1, sequenceName);
        generateNextSequenceQuery.executeUpdate();
        final Query nextSequenceQUery = entityManager.createNativeQuery("select @v_current_value;");
        Object nextSequenceObject = nextSequenceQUery.getSingleResult();

        if(nextSequenceObject instanceof BigInteger) {
            return ((BigInteger)nextSequenceObject).longValue();
        }

        return (Long)nextSequenceObject;

MyISAM과 InnoDB를 섞어서 사용할 경우 Bakcup/Recovery에 문제가 발생할 수 있다고 한다. 따라서 실전에서는 시퀀스 전용 스키마로 독립시키지 않는한 사용하기 힘들다.

• Stored Function to generate Sequences 참조.

• InnoDB 기반 시퀀스는 시퀀스마다 별도의 테이블을 만들고 AUTO_INCREMENT PRIMARY KEY 컬럼을 두고 계속해서 null을 insert 하고 SELECT LAST_INSERT_ID()를 리턴하면 된다.
• AUTO_INCREMENT는 멀티 트랜잭션 상황에서도 항상 새로운 값이 리턴되는 것을 보장해준다.
• MyISAM에서 처럼 단일 테이블에 여러 시퀀스를 두려고 할 경우, Transaction 상황에 따라 동일 값이 리턴되는 경우가 발생할 수 있다.
• 비록 숫자만 저장하는 테이블이지만 tablespace 크기가 매우 증가할 수 있다. 지속적으로 데이터를 삭제해줘면 삭제된 공간을 재사용해서 증가하지 않게 된다.

• Efficient sequences with MySQL : 단일 InnoDB 테이블로 여러 Sequence를 생성하는 방법.
• Storage Procedure가 필요하다.
• ISOLATION Level (MySQL InnoDB 참조)을 REPEATABLE_READ 이상으로 조정하지 않으면 서로 다른 트랜잭션에도 동일 값을 리턴하는 사태가 발생할 수 있다.
• Transaction 이 길어질 경우에 무슨 사태가 발생하는지 철저한 테스트가 필요하다.