MVCC에 따른 MySQL의 특성

MySQL(InnoDB형 테이블)은 현재 DBMS의 주류가 된 'MVCC(Multi Versioning Concurrency Control)'기술을 사용한다. MVCC를 사용하기 때문에 다음 특성을 갖는다.

 1. 읽기를 수행할 경우 갱신 중이라도 블록되지 않는다(읽기와 읽기도 서로 블록되지 않는다.)

 2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.

 3. 갱신 시 배타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제로 잠금 하는 행의 범위가 다른 경우가 있다.

 4. 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 하라 때 블록된다. 일정 시간을 기다리며 그 사이에 잠금을 획득할 수 없는 경우에는 '잠금 타임아웃(Lock Timeout)'이 된다.

 5. 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 '롤백 세그먼트'라는 영역에 유지한다. 이 'UNDO 로그'는 용도가 2가진데, 첫 번째는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리는 것이고, 두 번째는 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다.(같은 행을 갱신할 때마다 UNDO 로그가 작성되어 같은 행에 대한 복수 버전이 존재하며 이에 의해 1과 2를 실현하고 있다.)

트랜잭션 격리 수준별 외관

> '2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다'고 했는데 이를 설명한다.

> MySQL 트랜잭션 격리 수준의 기본값은 '반복 읽기'이다. 

반복 읽기

 > 최초 쿼리를 실행한 시점에 커밋된 데이터를 읽어 들인다. 이 시점에서는 커밋된 읽기와 같다. 같은 쿼리를 복수 회 실행하면 최초 읽은 내용의 결과 세트가 반환된다. 복수 회의 쿼리 실행 사이에 다른 트랜잭션이 커밋했어도 그 내용은 반영되지 않는다.

커밋된 읽기

 > 커밋된 읽기는 쿼리를 실행한 시점에서 커밋된 데이터를 읽어 들인다. 같은 쿼리를 복수회 실행하면 그 사이에 다른 트랜잭션이 커밋할 때가 있는데, 이 경우 최신 쿼리의 실행 개시 시점에서 커밋된 데이터를 읽는다.

갱신을 수행하는 트랜잭션 자신

 > 갱신을 수행하는 트랜잭션 자신은 트랜잭션 격리 수준이나 COMMIT/ROLLBACK에 상관없이 자신이 수행했던 갱신을 즉시 볼 수가 있다.

잠금 타임아웃이란

'갱신'과 '참조'는 서로 블록하지 않지만, '갱신'과 '갱신'이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 된다. 잠금 해제를 기다리고 있는 쪽에서 잠금을 기다리거나 기다리지 않거나, 기다린다면 어느 정도 기다릴지를 (초수 지정이나 무한으로 기다린다)설정 할 수있다. MySQL의 경우는 'innodb_lock_wait_timeout'이란 시스템 변수로 설정할수 있고 기다리지 않은 설정은 없어서 유효값은 1 이상이어야 한다.

교착 상태란

트랜잭션 A가 테이블 a의 잠금을 얻고 트랜잭션 B가 테이블 b의 잠금을 얻었다고 해보자. 이 잠금을 유지한 채 서로 잠금을 건 자원에 잠금이 필요할 처리를 실행하면 아무리 기다려도 상황이 바꾸지 않는 상태가 된다. 이것을 '교착 상태'라고 한다.

교착 상태의 빈도를 낮추는 대책

- DBMS 전반적인 대책

 1. 트랜잭션을 자주 커밋한다. 이에 따라 트랜잭션은 더 작은 단위가 되어 교착 상태의 가능성을 낮춘다.

 2. 정해진 순서로 테이블(그리고 행)에 액세스 하게 한다. 예를 들어, 트랜잭션 A가 [테이블 a -> 테이블 b] 순으로 액세스했고 트랜잭션 B가 [테이블 b -> 테이블 a] 순으로 액세스 하고 있는데, 이를 어느 트랜잭션이라도 [테이블 a -> 테이블 b]처럼 동일한 순서로 액세스 하게 한다.

 3. 필요 없는 경우에는 읽기 잠금 획득의 사용을 피한다.

 4. 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 한다. 예를 들어, 행의 잠금을 사용할 수 있는 경우에는 사용한다. MySQL은 트랜잭션의 격리 수준을 되도록 '커밋된 읽기'로 한다.(InnoDB의 기본 격리 수준은 반복읽기)

 5. 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착 상태가 발생하기 쉽다. 동시에 많은 연결에서 갱신 때문에 교착 상태가 자주 발생한다면 테이블 잠금을 획득해 갱신을 직렬화하면 동시성은 떨어지지만 교착 상태는 회피할 수 있어서 전체 처리로 보면 좋은 예도 있다.

- MySQL(InnoDB)의 대책

 6. 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸리게 된다.