Mysql Lock

MySQL에서는 여러가지 종류의 락이 있다. 락은 쿼리 성능에도 많은 영향을 미치고 이러한 개념을 모른다면 쿼리 튜닝할때도 상당한 제약 사항이 있을것 같다.  그리하여 MYSQL에서 사용하는 락은 어떤것들이 있는지 정리해 본다.

MYSQL엔진의 잠금

1. 글로벌락 

   - FLUSH TABLE WITH READ LOCK 명령으로만 획득 가능한데 실행과 동시에 서버에 존재하는 모든 테이블에 잠금을 건다. 

   - MYSQL에서 제공하는 락 가운데 가장 범위가 큰 락이다.

   - 한 세션에서 글로벌 락을 획득한다면 다륵 세션에서 SELECT를 제외한 DDL DML문장을 실행하는 경우 글로벌 락이 해제될때 까지 대기 상태

2. 테이블락 : 

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

  - 테이블 락에는 명시적락 과 묵시적 락이 있다.

   

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

            UNLOCK TABLES로 해제 할수 있으며 글로벌락과 동일하게 상당한 영향을 끼친다.   

 묵시적 : MYISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생 한다.

            INNODB에서는 DDL경우에만 영향을 미친다. 

1) LOCK TABLES READ MODE

2) SELECT는 가능 함.

 

3) READ 모드에서 INSERT 실행

     

4) show processlist 로 보면 insert문이 대기하는것을 알 수 있다.       

   

5) 설정된 time이 지나면 에러 발생 후 종료

6) 이번엔 WRITE MODE로 TABLE LOCK 실행

7) SELECT 문 실행

8) shoow processlist로 확인하면 select문도 대기하는것을 알 수 있다.

9) 설정된 time이 지나면 에러 발생 후 종료

               

3. 유저락 

 - GET_LOCK함수를 이용해 임의로 잠금을 설정한다.

 - 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다.

 - 많은 레코드를 변경하는 트랜잭션의 경우 유용하게 사용 가능하다.  

1) 1번 세션에서 test 문자열에  GET_LOCK을 획득             

2) 2번 세션에서 똑같이 test 문자열에 GET_LOCK을 획득하려고 하면 획득이 안됨.

3) 프로세스를 확인하면 User Lock이 잡힌것을 확인 할 수 있다.

             

4.  네임락

 -  데이터베이스 객체(테이블이나 뷰 등)의 이름을 변경하는 경우 획득하는 잠금이다.

 -  명시적으로는 획득이 불가능 하다. 

 -  RENAME TABLE tab_ TO tab_b같이 테이블 이름을 변경하는 경우 발생한다.

스토리지엔진의 잠금

스토리지엔진에서 자주 사용되는 INNODB 스토리지 엔진의 잠금에는 어떤것이 있는지 알아보자.

잠금 방식에는 비관적 잠금과 낙관적 잠금 방식이 있다.

비관적 잠금 

 - 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식이다. 즉 선잠금후작업 방식이다. INNODB는 이 방식을 

   채택하고 있다.

낙관적 잠금  

 - 기본적으로 각 트랜잭션이 같은 레코드를 변경할 가능성은 상당희 희박할 것이라고 가정한다.  우선 변경을 하고 마지막에 잠금 충돌이 있었는지 확인해

   서 문제가 있었다면 ROLLBACK 러치한다. 선작업후롤백 방식이다.

INNO DB 잠금 방식의 종류

레코드 락 

 - 레코드 자체만을 잠그는 락이다. 다른 DBMS와 중요한 차이는 레코드 자체에 락을 거는게 아니라 인덱스에 락을 건다. 그래서 인덱스가 없을 경우에는 

   MySql이 기본적으로 제공하는 클러스터 인덱스에 락을 건다.

갭 락

 -  레코드와 레코드 사이에 잠금을 말한다. 즉 레코드와 레코드 사이 간격에 락을 걸어 새로운 레코드가 생성되는것을 제어한다.

넥스트 키 락

 - 레코드 락과 갭락을 합쳐놓은 방식이다. innodb에서는 바이너리 로그를 사용하여 슬레이브로 복제할때 동기를 맞추기 위해서 사용하는 목적이 있다.

자동 증가락

- 테이블 컬럼 중 autoIncrement를 사용하여 pk를 생성한 경우 유니크한 값을 보장하기 위해 새로운 값이 생성될 때 다른 트랜잭션에 영향을 미치면 안되

  므로 락을 건다. 즉 auto_increment를 가져오는 순간에만 테이블 락 수준의 잠금을 사용한다.

위에서 알 수 있듯이 Mysql에서는 크게 MYSQL 엔진에서 사용하는 잠금과 스토리지 엔진에서 사용하는 잠금이 있고 MYSQL엔진에서 사용하는 락의 종류는 글로벌락, 테이블락, 유저락, 네임락 등이 있으며 스토리지 엔진에서 사용되는 잠금방식에는 낙관적 잠금과 비관적 잠금이 있는데 INNODB 스토리지 엔진에서는 비관적 잠금 방식을 사용하며 그 종류에는 레코드락, 갭락, 넥스트 키락, 자동 증가락이 있다. 

그러면 실제 사용되는 쿼리에서 락이 어떻게 걸리고 어떤 종류에 락이 걸리는지 알아보자.

SELECT 

 -  SELECT에서는 명시적으로 락을 걸지 않는 이상 락이 걸리지 않는다. 명시적으로 락을 사용하는 방법은 2가지가 있다,

1. SELECT * FROM tb_table LOCK IN SHARE MODE;

2. SELECT * FROM tb_table FOR UPDATE;

첫 번째 락은 읽기 전용 락이며 두 번째 락은 쓰기 모드의 락이다. MYSQL의 기본 격리수준은 REPEATABLE_READ이다. REPETABLE_READ 이하의 격리수준에는 락리 걸리지 않지만 SEREALIZABLE에서는 자동으로 LOCK IN SHARE MODE가 붙게된다. 

읽기 전용 락은 레코드에 락을 걸면 다른 트랜잭션에서 상호 호환이 가능하다. 그런데 쓰기모드 락은 변경하기 위한 잠금이며 다른 트랜잭션과 호환이 되지 않아 쓰기 잠금을 사용하면 COMMIT이나 ROLLBACK이 될 때까지 다른 트랜잭션은 대기하게 된다. 

UPDATE

업데이트에서는 기본적으로 쓰지 잠금을 사용하지만 필요에 따라 읽기 잠금도 사용한다. WHERE절에 조회한 조건만큼 레코드를 가져와 락을 건다  이때 조회한 레코드들은 베타적 넥스트 키락이 걸린다. 그래서 UPDATE문이나 DELETE문에서 INDEX 생성이 중요한 이유는 인덱스가 생성이 되지 않으면 모든 레코드에 베타적 넥스트 키락이 걸릴수 가 있어 데드락이 발생할 수 있다. 

UPDATE...WHERE

 - WHERE 조건에 일치하는 레코드를 찾기 위해 참조한 모든 레코드에 배타적 넥스트 키락을 걸게 된다. 

 - 레코드만 잠그지 않고 간격까지 잠그느넛은 레코드의 팬텀 리드의 발생을 막기 위함이다.

1) 트랜잭션1 에서 tbl테이블에 UPDATE하기 위해 범위 지정을 한다.

2) 배타적 넥스트 키 락이 잡혀있는지 확인하기 위해 트랜잭션2에서 UPDATE를 실행

3) show processlist를 확인하면 LOCK가 걸린것을 확인할 수 있다.

4) LOCK를 조회해 보면 lock_mode를 보면 X(배타적)잠금이 걸려 있는것을 확인 할 수 있다.

5) 트랜잭션2에서 update 실행한 쿼리는 에러 발생

6) 이번엔 갭락을 알아보기 위해 트랜잭션2에서 ID 2와 4 사이에 INSERT를 실행함.

7) lock_mode를 확인하면 GAP락이 걸린것을 확인 할 수 있다.

UPDATE tb_test1 a, tb_test2 b ON... SET a.column = b.column..

 - UPDATE되는 컬럼 즉 tb_test1 테이블에 포함된 모든 레코드에는 베타적 넥스트 키락이 걸린다.

 - 단순 참조용 tb_test2와 같은 테이블은 공유 넥스트 키락이 설정된다

 - 참조용 테이블에 공유 넥스트 키락을 설정하는 이뉴는 팬텀 레코드의 발생을 방지하고  복제에서 마스터 슬레이브의 데이터 동기화를 유지하기 위해서이다.

DELETE

 - DELETE는  UPDATE와 동일하다.

INSERT

 - 인서트에서는 좀 특이하게 인서트 인텐션 락이 걸린다. 인서트 인텐션 락은 갭락의 일종으로 서로 호환이 되는 락이다. INSERT 하는 모든 쿼리는 인서트 인텐션 락을 획득해야 하며 다른 트랜잭션에서 갭락을 걸고 있다면 인텐션 락을 걸기 위해 대기해야 한다.

INNODB에서 갭락을 사용하는 이유는 갭락으로 인한 동시성 감소이다.

INSERT INTO...ON DUPLICATE KEY UPDATE

- INSERT하려는 레코드에 대해 중복된 키 값이 이미 있는지 판단하기 위해 공유 잠금을 걸어야 함. 

 - 레코드가 존재한다면 배타적 잠금을 걸고 업데이트를 수행

 - 존재하지 않는다면 인서트 인텐션 락을 걸고 INSERT 실행, INSERT된 레코드에 대해서 배타적 잠금을 획득

REPLACE..

중복된 키값이 이미 있는지 판단하기 위해 공유 잠금을 건다

- 중복 레코드가 존재 한다면 배타적 잠금을 걸고 레코드 삭제한다.

- 나머지 과정은 INSERT와 동일하게 처리됨

INSERT INTO tb_new ... SELECT ...FROM tb_old...

 - 읽어오는 레코드는 공유 넥트스 키락을 건다. 

 - INSERT되는 레코드에 대해 배타적 레코드 락을 획득한다.

 - 일반적으로 하나의 테이블에서 다른 테이블로 복사할 때 읽어오는 테이블의 수정이 빈번하다면 문제가 될 수 있다.