[Learning SQL] 트랜잭션

트랜잭션에 대해 고민해보자.
애초에 트랜잭션이라는 건 다음과 같습니다.

모든 작업을 성공하거나 실패하는 기능이라고 하는데...
SQL에서 트랜잭션이라고 하면
한개라도 실패를 하면 모든 sql은 실패가 떨어진다는 이야기인것 같습니다.

그림을 보면 sql쿼리 3개가 존재한다는 것을 알 수 있습니다.
트랜잭션의 정의에 따른다면,
첫 번째 sql 은 성공 이지만 두 번째 sql이 실패라면 데이터베이스 상에는 아무것도 추가되지 않습니다.
왜냐하면 실패했기 때문이죠. 당연한 이야기죠.

이런 경우는 단일 사용자일때 발생되는 현상입니다.

그렇다면 사용자가 늘어나면 어떻게 될까요?

간단히 그림을 그리면

환 공포증

이 모든 sql문이 통과해야 합니다.
결국 a라는 유저가 sql 테스트에 성공한다면
b라는 유저도 sql 테스트도 성공해야한다는 뜻입니다.
물론, 이 상황은 동시에 데이터베이스에 접근한다는 뜻 입니다.
지금 위 그림은 총 48개의 유저가 동시에 특정 데이터베이스에 접근을 했습니다.
만약, 하나라도 실패한다면 트랜잭션 정의에 의해 실패입니다.

그래요. 실패한다는 건 알겠어요.
그러면 성공하는 경우를 생각해봅시다.
a라는 유저, b,c라는 유저가 있고, 동시에 특정 데이터베이스에 접근을 했다고 가정해 보죠.
근데 누가 먼저일까요? 
어떻게 우선순위를 지정해줘야 하는 걸까요?
만약에 a라는 유저가 데이터베이스를 사용하고 있다면 나머지는 데이터베이스에 접근을 할 수 없습니다.
왜냐하면 a라는 유저가 점유하고 있기 때문이죠.
이럴때 사용되는 것이 "잠금"이라는거라고 합니다.
잠금은 데이터베이스 서버가 데이터 자원을 동시 사용을 제어하는 데 사용하는 매커니즘이라고 합니다.

대부분 데이터베이스 서버는 2가지 방법중 하나 혹은 두 개를 채택 하고 있다는 군요.
1. 수정하기 위해서는 쓰기 잠금을 서버에 요청 수신하고, 조회하기 위해서는 읽기 잠금을 요청 수신합니다.
    -  여러 사용자가 동시에 데이터를 읽을 수 있지만, 각 테이블에 대해 한번에 하나씩의 쓰기만 제공하고
        쓰기 잠금이 해체될때 까지는 읽기 잠금 요청이 차단 됩니다.
2. 수정하기 위해서는 쓰기 잠금을 서버에 요청 수신하지만, 조회하기 위해서는 잠금이 필요하지 않습니다.
    - 대신 서버는 쿼리가 시작될 때부터 쿼리가 완료될 때까지 reader에게 데이터에 대한 일관된 보기를 제공
     -> 버전 관리라고 한다

첫 번째의 문제점은 동시 읽기와 쓰기 요청이 많으면 대기 시간이 길어 질 수도 있고,
두 번째는 데이터를 오래 실행하는 쿼리가 있으면 문제가 될 수 있다고 합니다.

첫번째 방식을 채택하는 db 서버 : 마이크로 소프트 sql, mysql
두번째 방식을 채택하는 db 서버: 오라클, mysql

잠금의 단위는 총 3가지로 구분되어 있습니다.
테이블 잠금 : 여러 사용자가 동일한 테이블을 동시에 수정하지 못하도록 합니다.
                  - 사용자 수가 증가함에 따라 대기 시간이 빠르게 증가
                  - sql 서버
페이지 잠금 : 여러 사용자가 테이블을 동일한 페이지의 데이터를 동시에 수정하지 못하도록 합니다.
                  - sql 서버
행 잠금 : 여러 사용자가 테이블에서 동시에 수정하지 못하도록 합니다.
              - 많은 부기가 필요하다.
              - 많은 사용자가 각자 다른 행에 대해 작업을 한다면 동일한 테이블을 수정할 수  있습니다.
              - sql 서버
              - 오라클

mysql 도 모두 지원하지만, 페이지 또는 행잠금이라는 것으로 봐서 둘 중 하나를 선택해야하는 것 같다.
행 -> 페이지 -> 테이블로 잠그는 것을 에스컬레이션이라고 하며,
sql서버는 지원하지만, 오라클은 지원하지 않습니다.

페이지라는게 테이블과 행의 중간 느낌인 것 같습니다.

위에서 트랜잭션이 어떤것인지 설명했습니다.
그런데 db서버에서는 이것들을 자동으로 해줄까요?
자동으로 할 수도 있지만, 어찌되었든 이것도 사람이 만든거라 한계점은 반드시 존재합니다.
그럴때는 수동으로 트랜잭션을 적용할 필요가 있습니다.

성공한다면, commit
실패한다면, rollback을 하게됩니다.
여기서 rollback은 다시 원상 복귀한다는 의미로 실패했다는 뜻입니다.
만약 트랜잭션이 완료하고, commit이 실행했지만, 변경 사항이 영구 스토리지에 적용되기 전에 서버가 종료되는 경우 데이터베이스 서버는 서버가 다시 시작돨때 변경 사항을 다시 적용해야 한다고 합니다.
이러한 속성을 지속성이라고 합니다.
어찌 되었든 commit을 때리게 되면 서버가 죽더라도 영구 스토리지에 적용된다는 의미인것 같습니다.

이제 트랜잭션을 시작해보죠.

데이터베이스 서버는 2가지 방법으로 트랜잭션을 생성한다고 합니다.
 - 활성 트랜잭션은 항상 데이터베이스 세션과 연결되어 있으므로 명시적으로 트랜잭션을 시작할 필요나 방법이 없다.
현재 트랜잭션이 종료되면 서버는 자동으로 새션에 대한 새 트랜잭션을 시작합니다. (오라클)

- 명시적으로 트랜잭션을 시작하지 않는 한 개별 SQL 문은 서로 독립적으로 자동 커밋됩니다. 트랜잭션을 시작하려면 먼저 시작 명령어를 실행해야합니다. (sql서버, mysql)

결국은 첫 번째방식은 start transaction을 이용해서 수동으로 처리한다는 것 같구,
두 번째방식은 db서버에서 자동으로 커밋해준다는 것 같습니다.

물론 sql 서버와 mysql에서 자동커밋 모드를 종료한다면 오라클 처럼 사용 할 수 있다고 합니다.

이제 트랜잭션을 종료해봅시다.
위에서 언급한 것 처럼 commit과 rollback을 이용해 결정합니다.
하지만 만약 교착 상태에 발생한다면 어떻게 될까요?
교착 상태란 두 개의 서로 다른 트랜잭션이 다른 트랜잭션이 현재 보유하고 있는 리소스를 대기할 때 발생합니다.
두 트랜잭션이 동일한 페이지 혹은 행을 수정할 경우, 각 트랜잭션은 다른 트랜잭션이 완료되고 필요한 리소스를 확보할 때까지 영원히 기다립니다.
어떻게 보면 이것이 합리적으로 생각이 듭니다.
왜냐하면 교착 상태에서 데이터가 저장이 된다면, 어떤 일이 발생할지 아무도 모르기 때문이죠.

스토리지 엔진
MyISAM : 테이블 잠금을 사용하는 넌트랜잭션 엔진
MEMORY: 인메모리 테아블에 사용되는 넌트랜잭션 엔진
CSV : 데이터를 쉼표로 구분해서 파일에 저장하는 트랜잭션 엔진
InnoDB : 행 수준 잠금을 사용하는 트랜잭션 엔진
Merge : 여러개의 MyISAM 테이블을 단일 테이블로 표사하는 특수 엔진
Archive : 주로 보관 목적으로 대량의 인덱싱되지 않은 데이터를 저장하는 특수 엔진

아 힘들다. 트랜잭션은 쉽지만 어려운것 같습니다.