InnoDB 3

●UPDATE/DELETE할 때의 lock

MyISAM에서는 UPDATE/DELETE할 때 테이블에 lock을 건다. 

InnoDB에서는 스캔된 레코드에 대해서 lock이 걸리게 된다. 

InnoDB에서는 SELECT...FOR UPDATE때 와 마찬가지로 UPDATE/DELETE에 있어서도 

스캔된 레코드 전부에 lock이 걸리게 된다. 

인덱스를 가진 컬럼을 WHERE문에 사용해서 스캔되는 행을 한정해야 한다. 

●Next-Key Locking

InnoDB에서는   팬텀리드현상이 갱신할 때 발생하기않게 하기 위해서 Next-key Locking이라는 lock을 도입하고 있다.  예를 들어 하나의 레코드를 갱신한다고 치자. 

mysql＞ SELECT a FROM t WHERE b＜10;

mysql＞UPDATE t SET a=100 WHERE b＜10;

이 때, 다른 쓰레드가 b＜10조건을 만족하는 레코드를 삽입했다고 하자. 

그러면 2개의 레코드가 변경될 가능성이 나오게 된다. 

팬텀(환상, 유령) 레코드가 생길 수 밖에 없다. 

이것을 방지하기 위해서 InnoDB에서는  레코드를 삽입할려고 하는 다른 스레드를 lock대기시킨다.  InnoDB에서는 디폴트로 Next-key Locking이 유효화 되어있다. 

이것을 해제하기 위해서는 MySQL 기동할 때 innodb_locks_unsafe_for_binlog옵션을 설정한다. 

※MyISAM의 INSERT할 때의 lock

MyISAM에서는 레코드를 추출할 때 테이블에 읽기 전용 lock을 건다. 

다만, 테이블 도중에 삭제된 레코드가 없다면 레코드를 추출중에도 레코드 삽입이 가능하다.

(동시 삽입)

InnoDB 2

○ FLUSH TABLES WITH READ LOCK

모든 테이블에 READ lock을 건다. 

UNLOCK TABLES를 실행하면 lock이 해제된다.

MySQL은 이 명령이 실행되었을 때  모든 테이블을  닫는다. 

모든 테이블에 공유lock을 건다. 

mysql> FLUSH TABLES WITH READ LOCK;

○ SELECT ... LOCK IN SHARE MODE

InnoDB에서만 동작한다. 

SELECT로 스킨하는 레코드에 대해서 공유lock을 건다. 

SELECT로 스킨하는 레코드에 공유lock을 걸기

mysql>SELECT * FROM t WHERE a<10>

주의점으로 위 예의 경우에는 a필드에 인덱스를 만들어 놓지 않으면 스킨된 모든 레코드에 lock을 걸게 되는 점이다.  SELECT추출조건에 사용하는 필드에는 인덱스를 만들어놓지 않으면 안된다는 것이다. 

a의 인덱스는 유일키가 아니어도 된다.

○ SELECT ... FOR UPDATE

InnoDB에서만 동작한다. 

대상이 되는 레코드에 대해서 트랜잭션이 끝날 때까지 lock을 건다. 

다른 클라이언트가 갱신을 하려고 하면 그 클라이언트는 lock의 해제를 기다려야 한다. 

5.1.12-beta현재,  lock경합시에 에러를 반환하는 구문(오라클의 SELECT NOWAIT문)은 지원되지 않는다. 

 

대상 레코드를 갱신용으로 lock을 걸기

mysql> SELECT * FROM t WHERE a<10>

SELECT ... LOCK IN SHARE MODE와 같이 a 필드에 인덱스를 만들어 놓지 않으면 조사하는 모든 레코드에 lock이 걸리므로 조심해야한다. 

InnoDB - Lock

MyISAM에서는 테이블단위로만 lock이 되지않지만 InnoDB는 row level lock을 서포트한다. 

InnoDB가 서포트하는 row level lock은 대상이 되는 레코드만 lock을 거는 방법이다. 

row level lock을 잘 동작시키기 위해서는  최소한 InnoDB에 유일키나 프라이머리 키가 필요하다. 

InnoDB 에서 lock을 거는 방법에는 다음과 같다. 

• InnoDB가 자동으로 건다.(갱신 쿼리인 경우 자동적으로 걸린다.)
• 오퍼레이터가 명시적으로 건다.

            SELECT... FOR UPDATE 문                            : 배타lock

            SELECT... LOCK IN SHARE MODE문           : 공유lock

            LOCK TABLES문

            FLUSH TABLES WITH READ LOCK문

한편, MyISAM에서는 다음과 같이 lock을 건다.

• MyISAM이 자동으로 건다.(갱신 쿼리인 경우 자동적으로 걸린다.)
• 오퍼레이터가 명시적으로 건다. 

            LOCK TABLES문

            FLUSH TABLES WITH READ LOCK문

LOCK TABLES/ UNLOCK TABLES

LOCK TABLES문은 트랜잭션에 안전하지는 않다. 

LOCK TABLES는 테이블에 READ LOCK, WRITE LOCK(배타 LOCK)을 건다.

READ에서는 자기가 lock을 건 테이블을 다른 사람은 읽는 것을 가능하지만 자기자신도 다른 사람도 변경하는 것은 불가능하다. 

WRITE에서는 자기자신만이 lock을 건 테이블을 변경가능하다. 

다른 사람은 읽는 것도, 변경하는 것도 불가능하다. 

또,  lock을 걸고 있는 중에는 lock을 건 테이블만 접근가능하다. 

LOCK TABLES을 실행중에 클라이언트가 세션을 끊거나  현재의 스레드가 LOCK TABLES을 발행하면 서버는 lock을 해제한다. 

lock을 해제할 때는 UNLOCK TABLES를 실행한다.  모든 lock이 해제된다. 

LOCK TABLES 테이블명 {READ|WRITE} [,테이블명 {READ|WRITE}]

a테이블에는 공유lock, b테이블에는 배타lock을 거는 법

mysql> LOCK TABLES a READ, b WRITE;