4.2 Mysql 엔진의 잠금

Mysql 에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 Mysql 엔진 레벨로 나눠볼 수 있다. Mysql 엔진은 Mysql 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, Mysql 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

4.2.1 글로벌 락 (Global Lock)

글로벌락은 “flush tables with read lock” 명령으로만 획득할 수 있으며, Mysql에서 제공하는 잠금 가운데 가장 범위가 크다. 글로벌 락이 영향을 미치는 범위는 Mysql 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르다 하더라도 동일하게 영향을 미친다.

4.2.2 테이블 락 (Table Lock)

개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다. 명시적으로는 “lock tables table_name [READ | WRITE]” 명령으로 특정 테이블의 락을 획득할 수 있다. 테이블 락은 MyISAM뿐 아니라 InnoDB 스토리지 엔진을 사용하는 테이블도 동일하게 설정할 수 있다. 명시적으로 획득한 잠금은 “Unlock Tables” 명령으로 잠금을 반납(해제) 할 수 있다.

4.2.3 유저 락 (User Lock)

Get_lock() 함수를 이용해 임의로 잠금을 설정할 수 있다. 이 잠금의 특징은 대상이 테이블이나 레코드 또는 Auto_increment 와 같은 데이터베이스 객체가 아니라는 것이다. 유저락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다. 여러 클라이언트가 상호 동기화를 처리해야 할때 데이터베이스의 유저 락을 이용하면 쉽게 해결할 수 있다.

또한 유저락은 많은 레코드를 한번에 변경해야 하는 트랜잭션의 경우에 유용하게 사용할 수 있다. 배치 프로그램처럼 한번에 많은 레코드를 변경하는 경우 데드락의 원인이 되곤 한다. 이때 동일한 데이터를 변경하는 프로그램끼리 분류해서 유저 락을 걸고 쿼리를 실행하면 아주 간단히 해결할 수 있다.

4.2.4. 네임 락 (Name Lock)

데이터베이스 객체의 이름을 변경하는 경우 획득하는 잠금이다. 네임 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 “Rename table tab_a To tab_b” 같은 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

4.3 MyISAM 과 MEMORY 스토리지 엔진의 잠금

MyISAM 이나 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고 MYSQL 엔진에서 제공하는 테이블 락을 그대로 사용한다. 그리고 MyISAM 이나 MEMORY 스토리지 엔진에서는 쿼리 단위로 필요한 잠금을 한꺼번에 요청해서 획득하기 때문에 데드락이 발생할 수 없다.

4.3.1 잠금 획득

  • 읽기 잠금
    테이블에 쓰기 잠금이 걸려 있지 않으면 바로 일기 잠금을 획득하고 읽기 작업을 시작할 수 있다.
  • 쓰기 잠금
    테이블에 아무런 잠금이 걸려있지 않아야만 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 대기해야 한다.

4.3.2 잠금 튜닝

테이블 락에 대한 작업 상황은 MYSQL 의 상태 변수를 통해 확인할 수 있다.

1
2
3
4
5
6
7
8
mysql> show status like 'Table%';

+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1551552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Table_locks_immediate 는 다른 잠금이 풀리기를 기다리지 않고 바로 잠금을 획득한 횟수이며, Table_locks_waited 는 다른 잠금이 이미 해당 테이블을 사용하고 있어서 기다려야 했던 횟수를 누적해서 저장하고 있다.

잠금 대기 쿼리 비율 = Table_locks_waited / (Table_locks_immediate + Table_locks_waited) * 100;

위의 수치들을 대입해보면 약 1.31% 의 수치를 얻을 수 있으며, 이는 100개의 쿼리중 1개는 잠금 대기를 겪고 있다는 것을 알 수 있다.

4.3.3 테이블 수준의 잠금 확인 및 해제

MYISAM 이나 MEMORY 등과 같은 스토리지 엔진을 사용하는 테이블은 모두 테이블 단위의 잠금이므로 테이블을 해제하지 않으면 다른 클라이언트에서 그 테이블을 사용하는 것이 불가능하다. MYSQL 에서 현재 어떤 테이블이 잠겨있는지 확인하는 방법을 알아보자

MYSQL에서 테이블의 잠금을 획득하는 방법은 LOCK TABLES 명령을 이용해 명시적으로 획득하는 방법과, 쿼리문을 통한 묵시적 잠금획득 방법이 있다. 명시적인 방법은 UNLOCK TABLES 명령으로 해제하기 전에는 자동으로 해제되지 않는다.

아무런 옵션 없이 SHOW OPEN TABLES 명령을 바로 실행하면 MYSQL 서버의 모든 테이블에 대해 잠금 여부를 보여주고, FROM DB명 을 추가하면 해당 DB에 생성된 테이블에 대해 잠금 상태를 표시한다. SHOW OPEN TABLES 명령의 결과로 출력되는 in_use 값은 해당 테이블을 잠그고 있는 클라이언트 수와 테이블의 잠금을 기다리는 클라이언트의 수까지 더해서 출력된다. 그리고 “Name_locked” 는 테이블 이름에 대한 네임 락이 걸려 있는지를 표시한다.

어떤 클라이언트의 커넥션이 잠금을 기다리고 있는지 보려면 SHOW PROCESSLIST 명령을 사용해야 한다.