Ch14.3 InnoDB Transaction Model and Locking

InnoDB의 트랜잭션 모델의 목표는 다른 멀티버저닝 데이터베이스의 best 특성들을 두 가지 락킹으로 묶어 구현하는 것이다. InnoDB는 오라클처럼 row 레벨 락을 사용하며 쿼리 수행시 락을 유발하지 않는 consistent 읽기방식을 이용한다. 락 정보는 매우 공간 효율적으로 저장되기 때문에 lock 에스컬레이션이 필요없다. 몇몇의 유저가 매 row마다 락을 걸거나 어떤 임의의 집합 단위로 row 락을 걸어도 메모리 고갈이 유발되지 않는다.

InnoDB에서 모든 유저의 활동은 트랜잭션 안에 존재한다. 만약 자동커밋모드가 활성화되어 있으면 각각의 SQL문은 스스로 하나의 트랜잭션을 형성한다. 기본적으로 MySQL은 세션마다 자동커밋 모드를 활성화시킨 채 커넥션을 맺어주는데, 해당 세션에서 발생된 SQL문이 에러를 리턴하지 않는 한 항상 자동 커밋을 한다. 만약 에러를 리턴한다면 에러 종류에 따라 커밋을 할지, 롤백을 할지가 결정된다. 자세한 내용은 14.9.4 “InnoDB Error Handling”을 봐라.

자동커밋모드가 활성되어 있어도 여러 개의 SQL문을 묶어 하나의 트랜잭션으로 만들 수 있다. 명시적으로 START TRANSACTION 또는 BEGIN ~ COMMIT/ROLLBACK 문을 사용하면 된다. 자세한 내용은 13.3.1 “START TRANSACTION, COMMIT, and ROLLBACK Syntax”을 봐라.

만약 autocommit = 0 으로 설정하여 자동커밋 기능을 끈다면 해당 세션이 만든 트랜잭션은 항상 열려있게 된다. COMMIT이나 ROLLBACK 구문이 현 트랜잭션을 끄거나 시작할 수 있다.

COMMIT의 의미는 현 트랜잭션에 의해 수정된 내용이 영구적으로 저장되고 다른 세션에게도 보여지게 된다는 뜻이다. 반면 ROLLBACK은 현 트랜잭션에 의해 수정된 내용을 취소한다는 의미이다. COMMIT이나 ROLLBACK 모두 현 트랜잭션에 의해 잡혀있던 락을 놓아주게 된다.

SQL: 1992 트랜잭션 고립화 수준에 따르면 InnoDB는 REPEATABLE READ를 따르고 있다. InnoDB는 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE으로 설명되는 4가지 트랜잭션 고립화 수준을 모두 지원하고 있다.

유저는 SET TRANSACTION 구문을 통해 고립화 수준을 변경할 수 있다. 서버의 기본 고립화 수준을 설정하려면서버 시작시 –transaction-isolation 옵션을 이용하거나 옵션파일에서 설정할 수 있다.

행 단위 락킹에서 InnoDB는 next-key 락킹을 사용한다. InnoDB는 gap 락도 사용하는데 이것은 gap 사이에 인덱스화된 값을 insert할 수도 있는 구간 사이를 다른 세션이 insert하지 못하도록 구간을 락킹하는 것이다. next-key 락은 해당 레코드와 이전 레코드 구간에 대해 락을 거는 것이다.?? gap 락은 구간만 락을 거는 것이다.

row-level 락에 대해 궁금하다면, 그리고 gap locking이 무효화되는 환경이 궁금하다면 14.3.2 “InnoDB Record, Gap, and Next-Key Locks”을 봐라.

공간 인덱스와 관련된 동작에서 락을 통제하기 위해, repeatable read, resializable 트랜잭션 고립화 수준에서 next-key 락은 작동하지 않는다. 이 경우 예측 lock을 사용한다. 14.3.6의 “Predicate Locking for Spatial Indexes”을 봐라.

 

14.3.1 InnoDB Lock Modes

InnoDB는 두 가지 표준 row-level 락을 구현하고 있는데 shared lock(S)과 exclusive lock(X) 을 사용하고 있다. record락, gap락, next-key락등에 대해선 다음 절을 봐라.

  • Shared lock(S)은 트랜잭션이 row를 읽는 것을 허용한다.
  • Exclusive lock(X)은 트랜잭션이 update하거나 delete 하는 것을 허용한다.

만약 트랜잭션 T1이 row r에 대해서 S락을 갖고 있다면 동일한 row r에 대한 다른 트랜잭션 T2의 요청은 아래와 같이 처리된다.

  • T2가 S lock을 요청한다면 해당 락을 즉시 얻을 수 있다. 그 결과 T1와 T2는 모두 row r에 대해 S락을 얻게 된다.
  • T2가 X lock을 요청한다면 락을 즉시 얻을 수 없다.

만약 트랜잭션 T1이 row r에 대해 X락을 얻었다면 T2는 S락이나 X락 모두 즉시 얻을 수 없다. T2는 T1이 row r에 대해 락을 놓을 때까지 기다려야 한다.

Intention Locks

덧붙여서 InnoDB는 레코드에 대한 락과 테이블에 대한 락이 공존하는 ‘multiple granularity lock”을 지원한다. 이것을 실제적으로 지원하기 위해 intention 락이라는 타입을 사용한다. intention락은 일종의 테이블 락인데 트랜잭션이 테이블 내부 row에 대해 어떤 종류의 락(S 혹은 X)을 걸고 작업할 것인지를 알려주는 역할을 한다. InnoDB에는 두 가지의 intention 락이 존재한다.

  • Intention shared(IS): 트랜잭션 T가 테이블 row에 대해 S락을 걸 것임을 알림
  • Intention Exclusive(IX): 트랜잭션 T가 row에 대해 X락을 걸 것임을 알림

SELECT … LOCK IN SHARE MORE는 IS 락을 거는 것이며
SELECT … FOR UPDATE는 IX 락을 거는 것이다.

intention 락 프로토콜은 아래와 같다.

  • 트랜잭션이 테이블t 내부 row에 대해 S락을 걸려면, 먼저 테이블 t에 대해 IS락이나 더 강한 락을 획득해야 한다.
  • row에 대해 X락을 걸려면 테이블 t에 대해 반드시 IX락을 먼저 획득해야 한다.

이상 설명한 rule은 lock type 호환표로 정리할 수 있다.

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

현재 걸려있는 락과 호환 가능하면 트랜잭션은 락을 얻을 수 있다. 그러나 현재 걸려있는 락과 충돌이 발생하면 얻을 수 없다. 그렇게 되면 락을 얻으려는 트랜잭션은 락이 풀릴 때까지 대기해야 한다. 만약 락 충돌이 발생하고 데드락 때문에 즉시 락을 얻을 수 없는 것이라면 에러가 발생한다.

그러므로 intention 락은 Full 테이블 요청(LOCK TABLES … WRITE)을 제외하고는 어떠한 것도 막지 않는다. IX와 IS 락의 가장 큰 목적은 누군가가 row 락을 걸어놨거나 락을 걸으려 한다는 것을 보여주려고 함에 있다.

Deadlock Example

아래 예제는 lock 요청이 어떻게 데드락을 발생시키는지 보여준다. 클라이언트 A와 B가 있다고 해보자. 먼저 A는 1개의 row를 갖는 테이블을 만들고 트랜잭션을 시작하였다. 트랜잭션에서 A는 row에 S락을 설정하였다.

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

그리고 클라이언트 B는 트랜잭션을 시작하면서 이 값을 지우려고 하고 있다.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

삭제 작업은 X 락이 필요하다. 이 락은 A가 쥐고 있는 S 락과 호환되지 않기 때문에 대기하게 된다. 따라서 해당 요청은 row과 block과 함께 lock request 큐로 옮겨가게 된다.

그 후 A가 delete를 시도하려고 할 때 아래와 같은 메시지를 보게 된다.

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

A가 삭제를 하려면 X 락이 필요하게 되므로 데드락이 발생한다. B는 A가 잡고 있는 S락이 풀리기를 기다리며 A는 B가 잡은 X락이 풀리기만을 기다리고 있으므로 충돌이 발생하는 것이다. 결과적으로 InnoDB는 클라이언트 중 한 명에게 에러를 뿌리며 락을 놓아주게 된다. 해당 클라이언트는 아래와 같은 메시지를 받게 된다.

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

그러면 다른 한 명은 락을 획득하여 row를 삭제할 수 있게 된다.

 

14.3.2 InnoDB Record, Gap, and Next-Key Locks

InnoDB는 레코드락 외에도 gap 락, next-key 락등 여러 락을 가지고 있다.

  • 레코드 락: 인덱스 레코드를 잠그는 것(인덱스란 clustered index)
  • Gap 락: 인덱스 레코드 사이를 잠그는 것
  • 레코드 락과 gap 락을 함쳐놓은 것

Record Locks

테이블을 인덱스 없이 생성했어도 레코드 락은 항상 인덱스 레코드에 대해서 잠금을 한다. 왜냐하면 InnoDB는 클러스터드 인덱스를 보이지 않게 가지고 있기 때문에 이 인덱스에 레크드 락킹을 구현하는 것이다.

Next-key Locks

기본적으로 InnoDB는 REPEATABLE READ 트랜잭션 고립화 수준을 유지한다. 이 경우 InnoDB 유저는 인덱스를 스캔하는데 next-key 락을 사용하여 팬텀 row 를 방지할 수 있게 된다.

next-key 락킹은 인덱스 row 락과 gap 락을 합친 것이다. InnoDB는 테이블을 스캔할 때 이런 방식으로 row 수준 락킹을 적용한다. 따라서 쿼리에 따라 마주치는 레코드에 대해 shared 또는 exclusive 락을 걸어둔다. 결국 row 수준 락은 인덱스 레코드 락이라 할 수 있다. 덧붙여서 인덱스 레코드 에 대한 next-key 락은 해당 레코드에 대한 gap에도 영향을 미친다. 왜냐하면 next-key 락은 레코드 락과 레코드 앞에 있는 대상에 락을 거는 gap락을 합친 것이기 때문이다. 만약 어떤 세션이 레코드 R 에다가 shared, 또는 exclusive 락을 걸었다면 다른 세션은 레코드 R보다 인덱스 순서상 앞에 있는 레코드에 대해 새로운 값을 insert 할 수 없다.

인덱스가 값 10, 11, 13, 20을 가지고 있다고 해보자. 이 인덱스에 대해 next-key를 설정할 수 있는 구간은 아래와 같다. ] 표시는 해당 숫자까지 포함됨을 의미한다.

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

마지막 구간에서는 인덱스에서 가장 큰 값(20)과 어떤 값보다도 큰 가상의 “supremum”이라는 가상 레코드 구간 사이를 잠근다. 이 “supremum”은 사실 인덱스에 존재하지 않는 값이기 때문에 ??

Gap Locks

위에서 본 next-key 예제은 gap의 구간이 하나의 인덱스 값이 될 수도 있으며, 여러 개의 인덱스 값 구간이 될 수도 있고, 심지어 비어있을 수도 있다는 것을 보여준다.

Gap 락킹은 유니크 인덱스로 단일의 유니크 row를 검색하는 SQL구문에는 필요없다. 예를 들어 id 컬럼이 유니크 인덱스를 가질 때, 아래와 같은 값이 100인 row에 인덱스 레코드 락이 사용되며 다른 세션에서 이 값보다 작은 값을 insert하는 것을 허용한다.

SELECT * FROM child WHERE id = 100;

만약 id 가 인덱스를 갖고 있지 않거나 유니크하지 않다면 SQL은 앞선 gap구간에 락을 건다. 여기서 호환되지 않는 락이 다른 트랜잭션에 의해 얻어질 수도 있음을 유념해야 한다. 예를 들어 트랜잭션 B가 exclusive gap 락을 걸어놓은 구간에 트랜잭션 A가 shared gap 락을 걸수도 있다는 것이다. gap 락이 동시에 걸릴 수 있는 이유는 만약 어떤 레코드가 인덱스에서 삭제되면, 다른 트랜잭션에 의해 걸려있던 gap 락과 합쳐져야 하기 때문이다..?????

InnoDB에서 gap 락은  “순전히 억제하는” 기능을 갖고 있다. 즉 gap 사이에 다른 트랜잭션이 insert 하는 것을 막는 역할을 하는 것이다. 다른 트랜잭션이 같은 구간에 또 다른 gap 락을 거는 것을 막지 않는다. 그러므로 gap X-lock은 gap S-lock과 똑같은 효과를 보인다.

gap 락의 한 종류 중 insert intention gap 락이라고 불리는 gap 락은 insert 구문에서 row에 값을 삽입하기 전에 설정된다. 이 락은 insert시에 여러 개의 트랜잭션이 동일한 구간 내에서 똑같은 위치에 insert하는 것만 아니라면 서로가 대기하는 현상이 필요없음을 알려준다. insert 레코드가 4부터 7까지 있는 것을 생각해보자. 각각의 트랜잭션이 값 5와 6을 insert하려고 한다고 해보면, 각각은 insert하려는 row에 대한 exclusive 락을 얻기 전에 4부터 7 구간에 대해 insert intention 락을 건다. 그러나 서로가 insert 하려는 row가 다르기 때문에 충돌을 일으키지 않는다.

아래 예제는 값을 insert하기 위한 exclusive 락을 얻기 이전에 insert intention 락을 얻는 과정을 보여준다.

클라이언트 A가 두 개의 레코드(90과 102)가 있는 테이블을 만든 후 id가 100보다 큰 레코드에 대해 exclusive 락을 얻으려고 하고 있다. 이 exclusive 락은 레코드 102 이전에 대해 gap lock을 포함한다.

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

클라이언트 B가 이 구간 사이로 값을 insert하려고 한다고 해보자. 이 트랜잭션은 insert intention lock을 얻지만 exclusive lock을 얻기 위해 대기하게 된다.

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

insert intention lock을 보기 위해 SHOW ENGINE INNODB STATUS 명령을 쳐봐라. TRANSACTIONS 부분에 아래와 같은 내용을 볼 수 있을 것이다. (생략)

Disabling Gap Locking

gap 락은 명시적으로 해제시킬 수 있다. 트랜잭션 고립화 수준을 READ COMMITTED로 변경하던지innodb_locks_unsafe_for_binlog 파라미터(지금은 비권장하는)를 활성화시켜라. 이런 환경에서는 검색이나 인덱스 탐색에서는 gap 락이 쓰이지 않게 되며, FK 제약조건 체크나 중복키 를 체크하는 환경에서만 쓰이게 된다.

그리고 트랜잭션 고립화 수준을 READ COMMITTED로 변경하는 것이나 innodb_locks_unsafe_for_binlog 파라미터를 활성화 시켰을 경우 발생하는 또 다른 특이사항이 있다. 바로 SQL의 where 조건에 매칭되지 않는 row는 즉각 레코드 lock이 풀린다는 것이다.  update 구문에서 InnoDB는 “반정합성” read를 한다. 즉 마지막으로 커밋된 버전을 리턴하기 때문에 update절에서 where 조건에 부합되는지를 판단할 수 있는 것이다.

 

14.3.3 Avoiding the Phantom Problem Using Next-Key Locking

phantom 읽기라고 일컫는 문제는 똑같은 쿼리를 다른 시간(T1과 T2)에 수행했을 때 결과 집합이달라지는 것을 말한다. 예를 들어 SELECT문이 두 번 수행됐을 때 첫 번째 읽어을 때는 없던 값이 두 번째 읽을 때는 보이는 것을 의미하며 이것을 “유령” row라고 한다.

child 테이블의 id 컬럼에 PK가 구성되어 있고 아래와 같이 100보다 큰 값 이상에 대해 차후 업데이트할 의향으로 조회하고 락을 걸고 싶다고 가정해보자.

SELECT * FROM child WHERE id > 100 FOR UPDATE;

이 쿼리는 id가 100보다 큰 첫 번째 값을 시작점으로 인덱스를 스캔하기 시작할 것이다. 여기서 테이블에 id 값이 90과 102가 있다고 가정해보자. 만약 스캔한 인덱스 레코드 중 gap 구간(여기서는 90부터 102까지를 의미함)에 대한 insert를 막지 않는다면, 다른 세션이 id=101을 가지고 insert를 할 수 있을 것이다. 그래서 당신이 이 트랜잭션 안에서 똑같은 SELECT를 다시 한다면 id=101이라는 새로운 row(“phantom”)을 보게 될 것이다. 이 row 결과값을 데이터 항목이라고 한다면 새로운 phantom row로 인해, 트랜잭션은 항상 실행 가능해야 하고 트랜잭션 내에서 읽은 값은 변하면 안된다는  원칙을 위반하는 것이다.

phantom 현상을 막기 위해서 InnoDB는 next-key 락킹이라고 부르는 매커니즘을 사용하는데 이것은 index-row 락킹과 gap 락킹을 합쳐 놓은 것이다. InnoDB는 테이블 인덱스를 검색하거나 스캔할 때, 마주치는 레코드에 shared 락이나 exclusive 락을 걸어놓는 방식으로 row레벨 락킹을 구현하고 있다. 그러므로 row level 락킹은 사실상 인덱스 레코드 락이라고 할 수 있다. 이 말은 next-key 락은 index-record 락 더하기 인덱스 앞 레코드와의 gap에 대한 gap 락이라고 할 수 있다.만약 어떤 세션이 index 레코드 R에 대해서 shared나 exclusive 락을 걸었을 때, 다른 세션은 인덱스 순서상 R보다 앞에 있는 구간에 대해 새로운 값을 즉시 insert할 수 없다.

InnoDB가 인덱스를 스캔하면서 인덱스의 마지막 값 다음 구간(gap)에 대해 락을 걸 수도 있다. 아까 보았던 예제에서 이런 현상이 발생할 수 있다: id가 100보다 큰 값으로 insert 하려는 시도를 막으려면 id=102 부터의 구간에 대해 락을 걸어야 한다.

어플리케이션에서 유일성 체크를 구현하기 위해 next-key 락킹을 사용할 수 있다:  만약 당신이 share 모드에서 데이터를 읽고 insert하려고 할 때 중복된 값을 발견하지 않아 안전하게 해당 row를 insert할 수 있고 내가 insert 하려는 값 이후의 구간에 대해 next-key 락이 걸려있기 때문에 아무도 insert하거나 중복시킬 수 없다는 것을 알고 있다. 그러므로 next-key 락킹은 테이블에 존재하지 않는 값에 대해 락을 거는 셈이다.

gap 락킹을 비활성화 하는 것을 앞장에서 배웠다. 이것을 비활성화하면 다른 세션이 새로운 row를 insert할 수 있기 때문에 phantom 문제가 발생할 수 있다는 것을 명심해라.

 

14.3.4 Consistent Nonlocking Reads

일관성 읽기란 InnoDB가 멀티 버저닝을 이용하여 쿼리가 발행된 시점에서의 데이터베이스의 스냅샷을 구성하여 쿼리해오는 것을 의미한다. 쿼리는 해당 시점 전에 commit 된 트랜잭션으로 인해 변경된 값을 본다. 그리고 쿼리가 날라온 시간 이후에 변경된 데이터나 아직 커밋되지 않은 데이터는 읽지 않는다. 이 법칙의 예외사항이 하나 있는데 동일 트랜잭션 안에서 먼저 수행된 SQL과 다른 변경사항을 보는 것이다. 이 예외사항은 아래와 같은 이유 때문에 발생한다: 만약 테이블에서 어떤 rows를 update하면 SELECT 구문은 update된 마지막 버전의 row를 보게된다. 그러나 다른 예전 버전을 보게 될 수도 있다. (?? 이게 몬소리?) 만약 다른 세션이 동시에 해당 테이블을 업데이트하였다면, 데이터베이스에서 존재하지 않았던 테이블의 상태를 보게 되는 것이다.

만약 트랜잭션 고립화 수준이 REPEATABLE READ로 되어 있다면, 해당 트랜잭션에서 발행된 첫 번째 read 에 대해 snapshot을 만들고 이 snapshot을 읽음으로써 트랜잭션 안에서의 일관성 읽기가 가능하다. 만약 새로운 버전의 스냅샷을 만들고 싶다면 현 트랜잭션을 commit하고 다시 새로운 쿼리를 발행하면 된다.

READ COMMITTED 고립화 수준에서는 트랜잭션 내부의 일관적 읽기는 각각의 새로운 스냅샷을 생성하고 참조한다.

일관성 읽기는 InnoDB의 기본 모드이고 READ COMMITTED나 REPEATABLE READ 고립화 수준에서 SELECT 구문을 사용할 때 항상 사용되는 모드이다. 일관성 읽기는 테이블에서 억세스되는부분에 대해 락을 걸지 않으며 일관성 읽기가 수행되는 시점에서 테이블을 수정하는 것이 가능하다.

REPEATABLE READ 읽기 모드에서 일반적인 SELECT 구문을 날렸다고 가정해보자. InnoDB는 당신이 데이터베이스를 쿼리하고 있는 그 시점에 따른 timepoint를 가지고 트랜잭션을 생성한다. 만약 다른 트랜잭션이 row를 삭제하고 commit해도 당신은 row가 삭제된 것을 보지 못한다. insert나 update도 비슷하게 작용한다.

Note
데이터베이스의 스냅샷은 트랜잭션 내부에서 SELECT 구문에서 생성되며 DML 구문에서는 생성되지 않는다. 만약 당신이 트랜잭션에서 어떤 row를 insert하거나 update하고 commit 했는데 다른 트랜잭션이 DELETE나 UPDATE를 발행한다면 방금 당신이 commit한 그 row가 영향 받을 수도 있다. 어떤 트랜잭션이 다른 트랜잭션에 의해 이미 commit된 row를 update하거나 insert 했을 때 이 변경사항을 바로 눈으로 확인할 수 있다.

현재의 트래잭션을 commit하고 다른 SELECT나 START TRANSACTION WITH CONSISTENT SNAPSHOT 문을 명시함으로써 timepoint를 갱신할 수 있다.

이것을 MVCC(멀티버전 동시성 제어)라고 한다.

아래 예제에서 세션 A는 B가 row를 insert하고 커밋함과 더불어 A 세션 역시 커밋을 해야 B가 insert한 값을 볼 수 있음을 나타낸다. 따라서 timepoint는 B가 이전에 발행한 commit 시점으로 갱신된 것이다.

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

만약 가장 최근 상태의 데이터베이스를 보려면 READ COMMITTED 고립화 수준을 사용하던지 locking read를 사용해라.

SELECT * FROM t LOCK IN SHARE MODE;

READ COMMITTED 고립화 수준에서 트랜잭션 내부의 일관성 읽기 쿼리는 각각 최신본의 스냅샷을 셋팅하고 읽는다. LOCK IN SHARE MODE에서 락 읽기는 이렇게 된다: SELECT 하면서 만든 가장 최신본의 row의 트랜잭션이 끝날 때까지 다른 세션들을 block한다.

일관적 읽기는 명확한 DDL 구문에서는 동작하지 않는다.

  • 일관성 읽기는 drop table에서 작동하지 않는다. 왜냐하면 MySQL이 테이블을 drop하고 InnoDB가 테이블을 파괴하기 때문(destroy)
  • ALTER TABLE에서도 작동하지 않는다. 왜냐면 ALTER문은 임시 테이블을 구성하고 작업이 완료되면 원본 테이블을 지워버리기 때문이다. 만약 당신이 트랜잭션 안에서 일관성 읽기를 재발행해도 스냅샷에 row가 존재하지 않기 때문에 새로운 테이블의 row가 보이지 않게 된다. 이러한 경우 트랜잭션은 에러를 보낸다: ER_TABLE_DEF_CHANGED “Table definition has changed, please retry transaction”.

FOR UPDATE나 LOCK IN SHARE MODE를 명시하지 않은 SELECT를 이용 다양한 구문들, 가령
INSERT INTO .. SELECT
UPDATE … (SELECT)
CREATE TABLE … SELECT

  • 기본적으로 InnoDB는 강한 락을 사용하며 SELECT 부분을 READ COMMITTED로 수행한다. 비록 같은 트랜잭션 내에서라도 해당 일관성 읽기는 최신본의 스냅샷을 생성하고 참조한다.
  • 이런 경우에 일관성 읽기를 사용하려면 innodb_locks_unsafe_for_binlog 파라미터를 활성화하고 트랜잭션 고립화 수준을 SERIALIZABLE 외에 3가지 중 아무거나 하나로 설정해라. 그러면 select한 테이블에서 어떤 row에도 락을 걸지 않는다.

 

14.3.5 Locking Reads (SELECT … FOR UPDATE and SELECT … LOCK IN SHARE MODE)

만약 당신이 동일 트랜잭션에서 데이터를 조회하고 관련된 값을 업데이트하고자 한다면, 단순히 SELECT 구문은 안정성을 보장하기에는 충분치 않다. 다른 트랜잭션이 당신이 쿼리했던 똑같은 row를 update하거나 delete할 수 있다. 그래서 InnoDB는 추가적인 안정장치를 위해 두 가지의 locking read를 제공하고 있다.

  • SELECT … LOCK IN SHARE MORE
    읽은 row에 shared 모드의 락을 걸어둔다. 다른 세션은 읽을 수는 있으나 당신이 commit하기 전까지는 데이터를 수정할 수 없다. 만약 다른 트랜잭션이 commit하지 않은 채 데이터를 수정했다면 이 트랜잭션이 끝날 때까지 당신이 기다려야 한다.
  • SELECT … FOR UPDATE 는 인덱스 레코드에 락을 걸어 update한 것과 마찬가지의 효과를 준다. 다른 트랜잭션은 해당 row를 업데이트할 수 없으며 SELECT … LOCK IN SHARE MODE로 읽거나 특정 고립화 수준에서 읽는 것도 막히게 된다. 그러나 일관성 읽기는 레코드의 락을 무시한다 (레코드의 구버전 데이터는 락에 걸릴 수 없다. 구버전 데이터는 메모리 상에 존재하는 레코드의 복사본인 undo log로부터 재구성된 것이다)

이 장은 tree 구조나 graph 구조의 데이터, 혹은 단일 테이블이나 데이터가 여러 개의 테이블에 걸쳐 있는 경우 특히 유용할 것이다. ??

LOCK IN SHARE MODE나 FOR UPDADE로 인해 생긴 락 세트는 commit하거나 롤백할 때 해제된다.

Note
SELECT FOR UPDATE는 autocommit이 비활성화 되었을 때만 적용된다 (혹은 START TRANSACTION으로 시작했을 때) 만약 autocommit이 활성화되면 해당 row에 락이 걸리지 않는다.

Usage Examples

테이블 child에 새로운 값을 insert 한다고 가정해보자. 그리고 child의 부모key에 해당하는 parent 테이블이 있다고 해보자. 당신의 어플리케이션 코드는 참조 완결성이라는 것을 지켜야 한다.

첫 번째로, PARENT  테이블을 읽관성 읽기를 하고 row 세트가 존재함을 확인한다. 이때 당신은 안심하고 child 테이블로 insert 할 수 있겠는가? 아니다. 왜냐하면 당신의 select와 insert 동작 사이에 누군가 parent 테이블의 값을 삭제했을 수도 있기 때문이다.

이러한 잠재적인 문제를 방지하기 위해, LOCK IN SHARE MODE로 select를 수행해라.

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

LOCK IN SHARE MODE로 parent 테이블의 ‘Jone’을 쿼리한 후에 당신은 child 테이블에 안전하게 insert하고 commit할 수 있다. 당신이 수정하려고 하는 parent 테이블의 row를 읽거나 write하려고 하는 어떤 트랜잭션도 당신이 완료할 때까지 기다려야 한다. 즉 모든 테이블의 데이터가 일관적인 상태에 놓인 것이다.

다른 예를 들어보자. CHILD_CODES라는 테이블에 정수를 카운트하는 필드가 존재하며 이것이 CHILD 테이블에 존재하는 각각의 row에 대해 유니크한 식별자를 부여하는 역할을 하고 있다고 해보자. 이때 일관적 읽기나 SHARE MODE 읽기로 카운터의 현재 값을 읽으면 안된다. 왜냐면 두 유저가 카운터의 같은 값을 볼 가능성이 있으며 child 테이블의 row에 대해 똑같은 식별자를 중복생성할 수 있기 때문에 duplicate-key 에러가 발생하게 된다.

여기서는 LOCK IN SHARE MODE는 좋은 선택이 아니다. 왜냐면 두 유저가 카운터를 동시에 읽었을 경우 적어도 한 명은 카운터를 업데이트하려고 시도하는 데드락 상황에 빠지게 된다.

카운터를 읽고 증가시키는 것을 구현하기 위해서는 FOR UPDATE로 일단 읽고 카운터를 증가시켜야 한다.

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT … FOR UPDATE는 가장 최신의 데이터를 읽고 읽은 row에 X락을 걸어둔다. 그러므로 이것은 UPDATE가 row에 걸어둘 락과 동일한 락을 걸어두는 것이다.

위 예제는 다만 어떻게 SELECT … FOR UPDATE가 이용되는지를 보여준 것이다. 사실 MySQL에서 유일 식별자를 생성하는 것은 테이블의 단일 접근을 통해 구현할 수 있다.

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

이 SELECT 부분은 단지 식별자 정보를 받아올 뿐이며 (현재의 커넥션에 한해서) 테이블에 접근하지는 않는다.

 

14.3.7 Locks Set by Different SQL Statements in InnoDB

락모드로 읽기나 update, delete 같은 구문들은 인덱스 스캔 중에 만나는 모든 row에 index 레코드 락을 건다. 심지어 where 조건에 해당하지 않는 레코드에도 락을 건다. InnoDB는 where 조건을 기억하지 못하며 스캔한 인덱스 범위만을 알고있다. 락은 보통 next-key 락을 의미하며 이것은 레코드보다 앞에 있는 구간에 대해 insert를 하지 못하도록 즉각적으로 막는 역할을 한다. gap 락킹은 명시적으로 비활성화할 수 있으며 이 경우 next-key 락킹도 사용하지 못하게 된다. 트랜잭션 고립화 수준 역시 어떤 락이 세팅될지 영향을 준다.

만약 세컨더리 인덱스를 탐색하고 인덱스 레코드 락에 X락이 걸린다면 InnoDB는 관련된 클러스터드 인덱스 레코드를 복구하고 락을 건다.

만약 SQL 구문에 적당한 인덱스가 없어서 전체 테이블을 스캔해야 한다면 테이블의 모든 row에 락이 걸리게 되며, 다른 유저가 테이블에 insert 하는 것을 막게 된다. 따라서 테이블에 적당한 인덱스를 잘 만들어놔야 불필요하게 많은 row를 스캔하지 않게 된다.

SELECT … FOR UPDATE나 SELECT … LOCK IN SHARE MODE 같은 쿼리는 scan한 row에 대해 락을 걸며 결과 집합에서 조건에 해당하지 않는 row에 대해선 락을 해제한다. 그러나 몇몇의 케이스에서 보았듯이 쿼리 실행동안에 원본 소스와 결과 집합간의 관계가 실종되기 때문에 락이 바로 해제되지 않을 수 있다. 예를 들어, UNION 동작은 테이블을 스캔하고 (그리고 락을 건다) row를 임시 테이블에 넣어 결과세트를 평가하는 작업을 한다. 이런 환경에서 임시  테이블에 있는 row와 원본 테이블에 있는 관계가 끊어지게 되며 앞단의 row들은 쿼리 실행이 끝날 때까지 lock이 풀리지 않을 수 있다.

InnoDB는 락의 타입을 아래와 같이 정의하고 있다.

  • SELECT … FROM
    일관성 읽기이며 데이터베이스로부터 스냅샷을 만든다. 트랜잭션 고립화 수준이 SERIALIZABLE이 아닌 이상 락을 걸진 않는다. SERIALIZABLE인 경우 row에 shared next-key 락을 건다.
  • SELECT … FROM …LOCK IN SHARE MODE
    검색하는 과정에서 마주친 모든 레코드에 shared next-key 락을 건다.
  • SELECT … FROM … FOR UPDATE 구문은 LOCK IN SHARE MODE나 특정 트랜잭션 고립화 수준에서의 읽기와 호환되지 않는다. 일관성 읽기는 락 셋팅을 무시한다.
  • UPDATE … WHERE … 는 exclusive next-key 락을 건다.
  • DELETE FROM … WHERE … 도 마찬가지로 exclusive next-key 락을 건다.
  • INSERT는 insert된 row에 exclusive 인덱스레코드 락을 건다. (next-key 락이 아니며 이말은 gap lock도 없다는 것을 의미) 다른 세션이 insert된 row 보다 앞에 insert하는 것을 막지 않는다.insert 하기에 앞서 insertion intention gap 락이라 불리는 gap 락이 셋팅된다. 이것은 다른 여러 트랜잭션이 구간내에 똑같은 값으로 insert 하지만 않는다면 대기할 필요없이 진행할 수 있다는 것을 알려주는 일종의 시그널이다. 인덱스 레코드가 4와 7이 있다고 생각해보자. 서로 다른 트랜잭션이 5와 6일 insert 하려고 하며 insert 하기 위한 X락을 얻기 전에 각각 4와 7 구간을 lnsert intention lock을 걸게 된다. 이 경우 서로 insert 하려는 값이 다르기 때문에 서로 막지 않게 된다.

    만약 중복키 에러가 발생한다면 해당 중복된 인덱스 레코드에 S락이 걸린다. S락은 건 후 여러 개의 세션이 똑같은 값을 insert하려고 시도하면서 서로 X락을 대기하는 현상 때문에 S락은 데드락을 유발할 수도 있다. 데드락은 어떤 세션이 값을 삭제하면서도 생길 수 있다. 테이블 T1이 아래와 같은 구조를 가지고 있다고 생각해보자.

    CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

    3개의 세션이 차례대로 아래와 같은 작업을 하고 있다.

    session 1:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    session 2:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);
    session 3:
    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    session 1:

    ROLLBACK;

    세션 1에 의한 첫 번째 동작에 의해 해당 row에 대해 X락을 걸린다. 세션 2와 세션 3에 의한 동작은 중복키 에러가 발생하게 되며 두 세션 모두 row에 대해 shared 락을 요구하게 된다. 세션 1이 rollback했을 때 row에 대해 X락을 풀게 되어 세션2와 3이 S락을 얻을 수 있게 된다. 이제 세션 2와 3이 데드락 상황에 빠지게 된다. 둘 다 S락을 얻은 상태이기 때문에 X락을 얻을 수 없다.

    비슷한 상황으로 테이블이 이미 1을 가지고 있고 세 개의 세션이 아래와 같은 동작으로 수행하려 한다고 가정해보자.

    session 1:

    START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

session 2:
    START TRANSACTION;
INSERT INTO t1 VALUES(1);

session 3:

    START TRANSACTION; INSERT INTO t1 VALUES(1);

    session 1:
    COMMIT;

    세션 1은 row에 대해 X락을 얻게된다. 세션 2와 3의 동작은 중복키 에러를 발생하면서 row 대해 S락을 기다리게 된다. 세션1이 commit을 하면서 X락이 풀리고 세션 2와 3은 큐에 대기하고 있던 S락을 획득하게 된다. 이 시점에서 세션 2와 3에 데드락이 걸리게 된다. 두 세션 모두 서로가 갖고 있는 S락으로 인해 X락을 획득할 수 없다.

 

  • INSERT … ON DUPLICATE KEY UPDATE
    X락을 거는 단순한 INSERT와는 좀 다르다. 중복키로 인해 update가 되는 부분에서는 S락이걸린다.
  • REPLACE
    유일키에 대해 충돌되지 않으면 INSERT와 동일하며 중복키에 대해 X락이 걸린다.
  • INSERT INTO T SELECT … FROM S WHERE …
    테이블 T에 대해 insert 되는 row에 대해서는 exclusive 인덱스 레코드 락이 걸린다(gap lock이 아님) 만약 트랜잭션 고립화 수준이 READ COMMITTED이거나 innodb_locks_unsafe_for_binlog가 활성화되고 고립화 수준이 SERIALIZABLE이 아니면 S에 대해서는 일관성 읽기를 한다(락을 걸지 않음) 그렇지 않으면 S에 대해 S next-key 락을 건다.

 

14.3.8 Implicit Transaction Commit and Rollback

기본적으로 MySQL은 autocommit 이 활성화된 상태로 커넥션을 맺어준다. 그래서 쿼리 결과에 오류만 없으면 항상 커밋된 결과를 돌려받게 된다. 만약 에러가 있다면 커밋 또는 롤백하게 된다.

그리고 만약 autocommit 이 비활성화된 상태에서 명시적인 commit없이 종료된 세션에 대해서자동 롤백된다.

 

14.3.9 Deadlock Detection and Rollback

InnoDB는 데드락을 자동적으로 감지하여 한개 혹은 다수의 트랜잭션을 롤백시킬 수도 있다. 먼저 규모가 작은 트랜잭션을 골라 롤백시키는데 규모의 기준은 insert혹은 update 혹은 delete 하는 row의 숫자를 보고 판단한다.

InnoDB는 innodb_table_locks = 1 / autocommit = 0 이 설정된 경우 테이블의 락을 감지하게 되며 MySQL 레이어 윗단에서는 row-lelvel의 락 여부도 알 수 있다. 반면에 InnoDB는 MySQL이 설정한 LOCK TABLES구문이나 InnoDB가 아닌 다른 스토리지 엔진에 거린 데드락의 현황에 대해서는 알지 못한다. 이 문제를 해결하기 위해선 innodb_lock_wait_timeout 파라미터를 조절해야 한다.

InnoDB가 트랜잭션에서 롤백을 완료하면 트랜잭션에 의해 잡혔던 모든 락이 해제된다. 그러나 단일 SQL구문이 에러에 의해 롤백되었다면 이 구문에 의한 몇몇의 락은 여전히 걸려있을 수도 있다. 이 현상은 락이 어떤 구문에 의해 걸렸는지 나중에 알 수 없는 형식으로 InnoDB가 락 정보를 저장하기 때문이다.

만약 SELECT 구문이 스토어드 펑션을 call하였는데 해당 펑션 내부에 있는 SQL구문에서 fail이 발생했다면 해당 SQL 구문은 롤백된다. 게다가 만약 롤백이 그 이후에 수행된다면 전체 트랜잭션이 롤백된다.

 

14.3.10 How to Cope with Deadlocks

 

 

 

 

 

댓글 남기기