트랜잭션 예제와 DB락

계좌이체 예제

예제의 기본 데이터

set autocommit true;
delete from member;
insert into member(member_id, money) values ('memberA',10000);
insert into member(member_id, money) values ('memberB',10000);

계좌이체 성공 SQL

set autocommit false;
update member set money=10000 - 2000 where member_id = 'memberA';
update member set money=10000 + 2000 where member_id = 'memberB';

• a의 계좌에서 b의 계좌로 2000원을 송금하는 sql 작성
• 아직 커밋이 진행되지 않았기 때문에 기존 데이터가 조회되고 commit을 호출시 데이터가 업데이트 된다.

계좌이체 문제상황

• 실수로 member_idddd라는 필드 오류가 발생하게된 경우

set autocommit false;
update member set money=10000 - 2000 where member_id = 'memberA'; //성공
update member set money=10000 + 2000 where member_iddd = 'memberB'; //쿼리 예외 발생

• 실행되는 세션에서는 a는 2000원이 빠져나가지만 b의 경우 필드 오류가 발생해서 2000원이 추가되지 않게된다.
  • 하지만 여기서 commit을 하게되면 그냥 이대로 a만 2000원이 없어지는 치명적인 문제가 발생
  • 만약 이렇게 문제가 발생하면 rollback을 통해서 트랜잭션 시작 시점으로 원복해야한다.

만약 이상황에서 자동 커밋 모드 였으면 어떻게 됬을까?

• 쿼리를 하나 실행할때마다 커밋이 바로바로 발생하기 때문에 A의 돈만 줄어버리는 치명적인 문제가 발생해버린다.
  • 이 경우를 피하기 위해서 수동 커밋모드를 사용해서 수동으로 커밋, 롤백 할 수 잇도록 해야함.
    • 이렇게 자동커밋모드에서 수동 커밋모드로 전환하는 것을 트랜잭션을 시작한다고 표현함.

DB 락

• 세션 1이 트랜잭션을 시작하고 데이터를 수정하는 동안 아직 커밋을 수행하지 않았을 때, 만약 세션 2에서 동시에 같은 데이터를 수정하게 된다면 여러가지 문제가 발생하게 된다.
  • 트랜잭션의 원자성이 깨지게 됨.
  • 세션1이 중간에 롤백을 하게되면 세션2는 잘못된 데이터를 수정하는 문제가 발생함.

이러한 문제를 해결하기 위해 락이라는 개념을 제공

DB락 획득과 트랜잭션 과정 예시

1. 세션 1은 트랜잭션을 시작.

2. 세션 1이 memberA의 money를 500으로 변경을 시도 -> 해당 로우의 락을 먼저 획득해야함. 
   1. 락이 남아있으므로 세션 1은 락을 획득(이 문제의 경우 세션2보다 1이 조금 더 빨리 요청을 했다는 가정)
   
3. 세션1이 락을 획득했으므로 해당 로우에 updata sql 수행

4. 세션1이 아직 사용하고 있는 상황에서 세션2가 트랜잭션을 시작하여 memberA의 money 데이터 변경을 시도하는데, 이때 해당 락을 획득하려고 시도하지만 이미 세션 1이 사용중이여서 락을 획득하기 위해서 대기
   1. 만약 여기서 락 대기시간을 넘어가면 락 타임아웃 오류가 발생
   
5. 세션1이  커밋을 수행 -> 트랜잭션이 종료되어 락을 반납
6. 대기하던 세션2가 락을 획득하여 update sql 실행
7. 세션2는 커밋을 수행하고 트랜잭션이 종료되어 락을 반납한다.

락 타임 아웃의 경우

SET LOCK_TIMEOUT milliseconds : 락 타임아웃 시간을 설정

• SET LOCK_TIMEOUT 10000 -> 10초의 락 대기시간안에 락을 얻지 못하면 락 타임 아웃 오류 발생

DB 락 - 조회

• 보통의 데이터를 조회할 때는 락을 획득하지 않고 바로 데이터를 조회할 수 있음.

데이터 조회시에도 락을 획득하고 싶을 때

• select for update 구문을 사용하면 됨. -> 마찬가지로 조회시점에 락을 가져가기 때문에 다른 세션에서 해당 데이터를 변경할 수 없음.
• 트랜잭션을 커밋시에 락 반납

조회시점에 락이 필요한 경우

• 트랜잭션 종료 시점까지 해당 데이터를 다른 곳에서 변경하기 못하도록 강제로 막아야할 때 사용
  • 금액을 조회한 다음에 이 금액정보로 애플리케이션에서 어떤 계산을 수행할 때 돈과 관련된 중요한 계산이라 계산이 완료할 때까지 금액을 다른곳에서 변경하면 안되는 경우

트랜잭션과 락은 데이터마다 실제 동작하는 방식이 조금씩 다르기 때문에, 해당 데이터베이스 메뉴얼을 확인한다음에 의도한대로 동작하는지 테스트한 이후에 사용하도록 하자.