mysql - 트랜젝션 level 정보

InnoDB 트랜젝션 모델의 목적은 가장 좋은 다중-버전(multi-versioning) 데이터베이스의 특
성을 전통적인 2-상(two-phase) lock과 조화시키는 것이다.
InnoDB는 row level로 lock하며, 읽기에서는 non-lock인 것처럼 쿼리를 실행하는 것이 디폴트이다.(이것이 Oracle 스타일이다)
InnoDB에서 lock table은 매우 공간 효율적으로 저장하므로 lock escalation(lock의 단계적 확장)이 필요하지 않다: 
  데이터베이스에서 every  row, 또는 여러 row로 이루어진 random subset, InnoDB가 실행되는 중에
메모리 부족이 없이 여러 사용자에 의해서 lock이 허용된다.
InnoDB에서 모든 사용자는 잠재적 트랜젝션이 발생한다. 만약 autocommit 모드가 필요하다
면, 각자의 SQL 문에서 하나씩의 트랜젝션이 되어야 한다. MySQL에서 각자가 시작할 때,
autocommit 모드는 on이다.
autocommit 모드를 off하려면, SET AUTOCOMMIT=0로 함으로써 그 사용자는 트랜젝션이 열리
게 된다. COMMIT이나  ROLLBACK으로 현재의 트랜젝션을  끝내거나 새 트랜젝션이  시작된다.
InnoDB에서 COMMIT과 ROLLBACK은 현재의 트랜젝션 동안에 설정된 lock을 해지한다.  COMMIT
은 현재의 트랜젝션에서 변경된 사항을  아주 변경하여 다른 사용자가 볼  수 있게 하지만,
ROLLBACK은 현재의 트랜젝션에서 변경된 사항을 취소한다.
AUTOCOMMIT=1이라면, 여러 문장의 트랜젝션을 BEGIN에서 시작되고 COMMIT과 ROLLBACK에서
끝나게 된다.
SQL-92 트랜젝션 isolation level 규약에 따라 InnoDB는 REPEATABLE READ가 디폴트이다.
InnoDB에서는 모두 4개의 다른 트랜젝션 isolation level을 지원하며, 'my.cnf'의 [mysqld]에
디폴트 isolation level을 다음과 같이 설정할 수 있다.

        transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                                | REPEATABLE-READ | SERIALIZABLE}

사용자가는 single session의 isolation level을 변경하거나 또는 새로  들어오는 모든 접속에
대한 isolation level을 다음과 같이 변경할 수 있다.

        SET [SESSION |GLOBAL] TRANSACTION ISOLATION LEVEL
                {READ UNCOMMITTED | READ COMMITTED
                | REPEATABLE READ | SERIALIZABLE}

SQL 문장에서 level 이름은 hyphen이 없어야 하고, GLOBAL을 지정하면 새로 들어오는 접
속의 level이 초기 isolation으로 결정되지만, old  접속의 isolation level을 변경하지 못한다.
심지어 어떤 사용자는 트랜젝션 중간에도 자신의 session에 대한 isolation level을 변경하기
도 한다.
다음과 같이 global과 session의 트랜젝션 isolation level을 쿼리할 수 있다:
       
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ      |
+-----------------------+
1 row in set (0.31 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql>

row level lock에서 InnoDB는 소위 next-key lock을 사용한다. 이는  비록 인덱스 레코드일
지라도, InnoDB는 'gap'을 lock을 할 수 있는데 이것은  일전의 인덱스 레코드와 다른 사용
자가 인덱스에 삽입하는 것을 차단하기 전의 'gap'을 lock하는 것을 의미한다.
InnoDB의 각 isolation level을 설명하면 다음과 같다.

· READ UNCOMMITTED   

 non-lock한 SELECT가 실행되기 때문에 변경되기 전의 레코드를 읽을 수 없기 때문에 이
isolation level에서는 일관된 읽기(consistent read)가 안 된다. 그러므로 'dirty read'라 한다.

· READ COMMITTED   

 이는 Oracle과 같은  isolation level이다.  SELECT ... FOR  UPDATE나 SELECT  ... LOCK IN
SHARE MODE 문으로 된 모든  문장은 반드시 인덱스 레코드를  lock하지만, gap은 lock이 안
되므로 lock된  레코드 다음에  새 레코드를 삽입하도록  허용한다. unique 인덱스에서의
UPDATE와 DELETE에 의해서 발견된 인덱스 레코드만 lock되며, gap은 lock되지 안 는다.
그러나 아직도 UPDATE나 DELETE에서  InnoDB는 next-key 또는  gap lock을 설정해야
하고 다른 사용자가  gap에 삽입하는  것을 차단해야  한다. 이는  MySQL replication이나
recovery 작업 때에 'phantom row'가 차단되어야 하기 때문에 필요하다.

· REPEATABLE READ   

InnoDB의 디폴트 isolation level로써  SELECT ... FOR  UPDATE, SELECT ...  LOCK IN SHARE
MODE, unique 인덱스를 사용하는 UPDATE와 DELETE에서 발견된 인덱스 레코드만 lock하
고 gap는 lock하지 않는다. 다시 말해서, next-key lock을 사용한다. consistent read에서 앞
서의 isolation level과 확연히 다르다. 이  레벨에서는 동일한 트랜젝션 read 내에서의 모든
consistent read는 처음 읽은 것과 동일하다. 이렇게 함으로써, 평범하게 반복되는 동일한 트
랜젝션에서의 SELECT 문의 결과는 항시 같게 되는 이점이 있다.

· SERIALIZABLE   

이 레벨은 앞의 것과 같지만,  모든 평범한 SELECT 문은  묵시적으로 SELECT ...  LOCK  IN
SHARE MODE로 전환된다.