다음과 같은 관계에서 사용자가 비밀번호를 변경할 때 최근 사용한 N개의 비밀번호와 일치하는지 확인하는 로직은 어떻게 구현할까?
아래와 같이 결과집합을 뽑아서 애플리케이션에서 비교하거나, 한단계 더 쿼리를 감싸 과거 비밀번호와 변경할 비밀번호를 비교할 수 있다.
@Transactional method
{
select * from
(select * from password_his where user_id = :user_id order by rev desc)
where rownum <= N;
update user set password = :new_password where user_id = :user_id;
insert into password_his(rev, user_id, password)
select max(rev)+1,
user_id,
:new_password
from password_his where user_id = :user_id
}
하지만 스프링은 멀티쓰레드 방식으로 클라이언트 콜을 받기 때문에 두 개이상의 비밀번호 변경 요청이 들어온다면 첫번째로 처리한 요청은 정상적으로 비밀번호를 비교하고, 변경할 수 있지만 그 다음에 들어오는 요청들은 별도로 처리해야한다.
두 가지 정도의 문제가 있다.
문제
1. 두번째부터 들어온 요청이 첫번째 요청이 커밋되기 전이라면 최근 N차수가 아닌 N-M(M == 동시에 들어온 요청의 수)를 비교한다.
2. 동일한 REV+PASSWORD를 가진 데이터가 생길 수 있다.
해결법
1. PASSWORD_HIS의 PK를 REV+USER_ID로 지정해 PK 무결성을 활용
두 개 이상의 요청이 들어왔을 때 SQL Exception 발생시키고 예외 처리
2. SELECT FOR UPDATE 활용
오라클 DBMS는 조회시 동시성을 높이기 위한 MVCC 모델을 활용한다.
DML 문과 다르게 SELECT 문은 읽는 데이터가 다른 트랜잭션에서 변경이 진행중이여도 배타락과 상관없이 공유락을 사용하기 떄문에 읽을 수 있고, 무엇보다 트랜잭션이 시작한 시점으로 돌려서 읽는다.
하지만 읽은 데이터를 가공하고 UPDATE 할때는 트랜잭션이 시작하는 시점의 블록이 아닌 가장 최근의 블록에 저장하기 때문에 해당 작업을 수행하는 트랜잭션은 역설적이게 MVCC 모델이 데이터의 정합성을 떨어트린다.
예를 들어 최근 사용한 3개의 비밀번호를 비교하고 사용했던 비밀번호를 사용하지 못한다고 가정해본다.
5번 비밀번호를 변경한 유저가 의도치않게 동시에 3번의 비밀번호 요청을 수행하면 첫번째 요청은 (3,4,5)번째 비밀번호를 비교하고 6번째 비밀번호가 추가되지만, 그 뒤의 두개의 요청은 해당 트랜잭션이 커밋되기 전에 시작하면 (4,5,6), (5,6,7)번째 비밀번호를 비교하는게 아니라 모든 요청이 (3,4,5)번째 비밀번호를 비교하고 변경할 수도 있고 세 개의 요청이 모두 6차수를 가지고 commit 될 수도 있다.
서버가 하나라면 synchronized 키워드를 활용할 수 있지만, 서버가 이중화되어있고 동일한 DB를 활용하기에 SELECT에 배타락을 활용하는 SELECT FOR UPDATE를 활용했다. 해당 쿼리는 WHERE 조건을 활용하면 특정 로우에 락을 걸기 때문에 소모되는 비용도 줄일 수 있다.
특정 유저의 로우에 락을 걸고 시작한다면 해당 트랜잭션의 시작에 락을 걸기 때문에 멀티쓰레드에 물린 DB커넥션들을 직렬화 시킬 수 있다.
@Transactional method
{
select * from user where user_id = :user_id for update
/* select * from
(select * from password_his where user_id = :user_id order by rev desc)
where rownum <= N;
update user set password = :new_password where user_id = :user_id;
insert into password_his(rev, user_id, password)
select max(rev)+1,
user_id,
:new_password
from password_his where user_id = :user_id
*/
}
