MySQL Lock System

Lock(잠금) 이란 무엇일까?

여러 스레드가 하나의 자원에 접근 하는 것을 방지하기 위해 만든 개념이다.

예를 들면 하나의 화장실에 누군가 들어가 있다면 자물쇠로 잠궈두고, 그 사람이 해제하기 전까지 다른 사람은 접근하지 못하도록 하는 개념이 간단하게 Lock 의 개념이다. 이를 통해 우리는 하나의 자원에 대한 동기화를 진행할 수 있다.

 

데이터베이스 시스템에서도 이와 같은 개념이 필요한데, 왜냐하면 여러 사용자가 하나의 데이터에 대해 실시간으로 동시에 접근할 수도 있기 때문이다. 그래서 데이터를 보존해주기 위해 여러 개념들이 도입되어 있다. 예를 들면 트랜잭션도 하나의 서로 다른 사용자가 데이터에 접근할때 어떻게 보여주는가? 에 대한 개념을 구현한 것 중 하나라고 생각한다.

 

여하튼 MySQL 에서도 이런 데이터에 대한 수정/쓰기 시나 Lock 이 필요한 상황을 위해서 Lock 이라는 시스템을 도입하였는데, MySQL 에서 사용하는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나누어 볼 수 있다.

 

MySQL 엔진 레벨은 모든 스토리지 엔진 레벨에 영향을 끼치게 되지만, Storage Engine Level 의 잠금은 상호 스토리지 엔진간에는 영향을 미치지 않는다. (이 내용이 잘 이해되지 않는다면, MySQL Architecture 에 대해 더 공부하고 오길 바란다.)

 

GLOBAL LOCK

글로벌 락은 "FLUSH TABLES WITH READ LOCK" 이라는 명령으로 획득 할 수 있으며, MySQL 에서 제공하는 잠금 가운데 가장 범위가 크다. 하나의 세션에서 글로벌락을 획득한다면, 다른 세션에서 SELECT 를 제외한 DDL 이나 DML 문장을 실행할 경우 GLOBAL LOCK 이 해제 될때까지 모두 대기 상태로 남는다.

 

위에서 설명했듯이 GLOBAL LOCK 의 영향범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향을 미친다.

TABLE LOCK

개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

명시적으로는 "LOCK TABLES table_name [ READ | WRITE ]" 명령으로 특정 테이블의 락을 획득할 수 있다.

명시적으로 획득한 LOCK 은 "UNLOCK TABLES" 명령으로 잠금을 반납할 수 있다.

 

묵시적인 TABLE LOCK 은 MyISAM 이나 Memory 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

InnoDB 에서는 레코드 기반으로 Lock 을 제공하기 때문에 단순 데이터 변경 쿼리로 Table Lock 이 걸리지는 않는다.

다만 스키마를 변경하는 쿼리(DDL) 의 경우에는 테이블락이 설정될 수 있다.

USER LOCK

GET_LOCK() 함수를 이용해 임의로 잠금을 설정할 수 있다.

이 LOCK 의 특징은 특정 테이블이나 레코드와 같은 데이터베이스 객체가 아니라는 것이다.

유저 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.

 

SELECT GET_LOCK('mylock', 2); - 명시적 획득

SELECT IS_FREE_LOCK('mylock'); - 명시적 잠금이 걸려있는지 여부

SELECT RELEASE_LOCK('mylock'); - 명시적 LOCK 반납

 

여러 클라이언트가 상호 동기화를 처리해야 할때 유저 락을 이용하면 쉽게 해결할 수 있다.

NAME LOCK

NAME LOCK 은 명시적으로 획득하거나 해제할 수 있는 것이 아니고

"RENAME TABLE roach TO roachhhi" 와 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

 

예를 들어 두개의 테이블의 이름을 바꿔본다고 해보자.

 

mysql > RENAME TABLE A TO B;

mysql > RENAME TABLE B TO A;

 

위와 같이 쿼리를 작성하면 아주 짧은 순간이지만, A 테이블이 없는 순간이 존재하게 된다.

그래서 아래와 같이 쿼리를 작성하는 것이 좋다.

 

mysql > RENAME TABLE A TO B, B TO A;

 

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 MySQL 에서 제공하는 잠금과는 별개로 엔진 내부에 레코드 기반의 잠금 방식을 탑재하고 있다.

InnoDB 는 이원화된 잠금 처리탓에 엔진에 대한 정보를 얻기가 힘들었는데 플러그인이 도입되면서 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 가지고 있는지 등등 정보 획득이 가능하다.

InnoDB 의 잠금 방식

비관적 잠금 / 낙관적 잠금 이라는 용어가 있다.

 

비관적 잠금이란 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식을 뜻한다.

왜 비관적이라고 하냐면 이 데이터를 다른 세션에서 변경할 수도 있어 그러니까 비관적으로 생각해서 내가 락을 걸고 바꿀꺼야 라고

해서 비관적 잠금이라고 하며, 일반적으로 높은 동시성 처리에서는 비관적 잠금이 유리하다고 알려져있다.

InnoDB 또한 비관적 잠금 방식을 채택하고 있다.

 

반대로 낙관적 잠금이란 기본적으로 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이다. 라고 행복회로를 돌리는 것이다..

그치만 만약에라도 충돌이 난다면 ROLLBACK 처리하는 방식을 뜻한다.

Record Lock

레코드 자체만을 잠구는 것은 레코드 락이라고 한다. InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.

만약 인덱스가 하나도 없는 테이블이라 하더라도, 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

(이 부분도 이해가 가지 않는 다면 InnoDB의 동작방식에 대해 공부하고 오면 좋다.)

대부분 보조 인덱스를 이용한 변경 작업은 이어서 설명할 넥스트 키 락 또는 갭락을 이용한다.

GAP LOCK

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.

갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.

Next Key Lock

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE_READ 격리 수준을 사용해야 한다.

해당 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.

 

바이너리 로그를 쉽게 설명하면 DDL, DML 등 쿼리에 대한 기록을 Binary 로 남기는 Log 가 있는데

해당 로그는 Reflication 이나 복구시에 이용할 수 있는 Log 로서 이용된다.

Auto Increment lock

MySQL 에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT 속성을 제공한다.

INSERT 요청이 동시에 들어온다고 하면 DB 에서는 각 레코드를 저장된 순서대로 증가한 일련번호 값을 가져야 하므로 이를 위해서 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 이용한다.

Index 와 잠금

위에서 얘기했듯이 InnoDB 의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다.

 

KEY ix_firstname (first_name)

위 처럼 first_name 에 인덱스가 걸려있다고 가정해보자.

 

만약 아래의 쿼리를 한번 보자.

mysql > SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi'; -> 253

 

즉 first_name 이 Georgi 인 사원이 253개 있다는 뜻을 의미한다.

 

mysql > SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi'  AND last_name = 'Klassen'; -> 1

 

만약 이제 여기서 first_name 이 Georgi 이고 last_name 이 Klassen 인 열의 고용일(hire_date) 를  지금으로 바꿔야 한다고 해보자.

 

mysql > UPDATE employees SET hire_date = NOW() WHERE first_name = 'Georgi'  AND last_name = 'Klassen';

 

아까도 위에서 얘기했듯이 first_name = 'Georgi'  AND last_name = 'Klassen' 인 컬럼은 딱 1개이다.

하지만 저 UPDATE 쿼리를 실행하기 위해서는 몇개 레코드에 락을 걸까? 정확히 253개 레코드에 락을 건다.

그 이유는 last_name 은 인덱스가 없기 때문이다.

 

이런 부분을 이해하지 못한다면 InnoDB 를 이용하게 사용할 확률이 높다. 위의 예제와 같은 상황이 발생한다면 동시성 처리에서 아주 낮은 성능을 보여줄 것이다. 이것이 InnoDB 의 인덱스 설계가 매우 중요한 이유이다.

 

위와 같은 현상이 발생하는 이유는 InnoDB 의 Next Key Lock 때문이다.

즉 InnoDB 의 Gap Lock 과 Next Key Lock 을 줄일 수 있다는 뜻은 사용자의 쿼리 요청을 동시에 더 많이 처리할 수 있음을 의미한다.

 

따라서 InnoDB 에서는 인덱스 설계를 매우 잘해야 한다.

 

오늘 이 내용을 공부하면서 많은 걸 느꼈다.. 앞으로 InnoDB 는 테이블 설계 뿐만 아니라 인덱스 설계도 매우 잘해야 겠다는 느낌

언제 6장까지 이렇게 정리하면서 읽을 수 있을지는 모르겠지만, 이렇게 한번하면 큰 도움이 될것 같다는 생각이 든다.