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

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

MyISAM이나 MEMORY 스토리지 엔진은 자체적인 잠금을 가지지 않고 MySQL 엔진에서 제공하는 테이블 락을 그대로 사용한다. 그리고 MyISAM이나 MEMORY 스토리지 엔진에서는 쿼리 단위로 필요한 잠금을 한꺼번에 모두 요청해서 획득하기 때문에 데드락이 발생할 수 없다. 별도로 표기하진 않지만 여기서 언급되는 잠금은 MyISAM 스토리지 엔진뿐 아니라 MySQL에서 기본적으로 제공되는 MEMORY나 ARCHIVE, 그리고 MERGE 등과 같은 스토리지 엔진에도 동일하게 적용된다.

1. 잠금 획득

읽기 잠금

테이블에 쓰기 잠금이 걸려 있지 않으면 바로 읽기 잠금을 획득하고 읽기 작업을 시작할 수 있다.

쓰기 잠금

테이블에 아무런 잠금이 걸려 있지 않아야만 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 대기해야 한다.

2. 잠금 튜닝

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

mysql > SHOW STATUS LIKE 'Table%';
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| Table_locks_immediate    | 1151552      |
| Table_locks_waited       | 15324        |
+--------------------------+--------------+

위의 결과에서 "Table_locks_immediate"는 다른 잠금이 풀리기를 기다리지 않고 바로 잠금을 획득한 횟수이며, "Table_locks_waited"는 다른 잠금이 이미 해당 테이블을 사용하고 있어서 기다려야 했던 횟수를 누적해서 저장하고 있다. 현재 사용 중인 MySQL 서버에서 MyISAM이나 MEMORY 테이블을 사용하고 있다면 위의 상태 값을 조회해서 Table_locks_waited 값과 Table_locks_immediate의 비율을 비교해 보면 테이블 잠금을 대기하는 쿼리가 어느 정도인지 알아낼 수 있다.

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

위의 결과를 이 수식에 대입해 보면 (15324/(1151552+15324)*100 = 1.31%, 즉, 쿼리 100개 중에서 1개는 잠금 대기를 겪고 있다는 것을 알 수 있다. 만약 이 수치가 높고 테이블 잠금 때문에 경합(Lock contention)이 많이 발생하고 있으면 자연히 처리 성능이 영향을 받고 있음을 의미하므로 테이블을 분리하거나 InnoDB 스토리지 엔진으로 변환하는 방법을 고려해 보는 것이 좋다. InnoDB 스토리지 엔진의 경우에는 레코드 단위의 잠금을 사용하기 때문에 집계에 포함되지 않는다. 집계된 수치는 MyISAM이나 MEMORY 또는 MERGE 스토리지 엔진을 사용하는 테이블이 대상이 된다.

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

MyISAM이나 MEMORY 등과 같은 스토리지 엔진을 사용하는 테이블은 모두 테이블 단위의 잠금이므로 테이블을 해제하지 않으면 다른 클라이언트에서 그 테이블을 사용하는 것이 불가능하다. 하나의 테이블에서 전혀 다른 레코드라 하더라도 동시에 변경하는 것은 불가능하기 때문에 쿼리 처리의 동시성이 떨어지게 된다. MySQL에서 현재 어떤 테이블이 잠겨 있는지 확인하는 방법을 간단히 알아보자.

MySQL에서 테이블의 잠금을 획득하는 방법은 LOCK TABLES 명령을 이용해 명시적으로 획득하는 방법과 SELECT나 INSERT 또는 DELETE, 그리고 UPDATE 등(또는 DDL 명령)과 같은 쿼리 문장을 이용해 묵시적으로 획득하는 방법이 있다. 묵시적으로 잠금을 획득하는 방법은 쿼리가 실행되는 동안만 잠금을 획득하며(MyISAM이나 MEMORY 같이 테이블 수준의 잠금을 사용하는 스토리지 엔진은 모두 트랜잭션을 지원하지 않으므로 하나의 쿼리가 실행되는 동안만 락이 걸렸다가 쿼리가 완료되면서 즉시 락이 해제되는 것이다), 명시적인 방법은 UNLOCK TABLES 명령으로 해제하기 전에는 자동으로 해제되지 않는다. 다음은 명시적인 잠금 방법에 대한 예제다.

커넥션 1	커넥션 2	커넥션 3
LOCK TABLES employees READ;
	UPDATE employees SET hire_date=NOW() WHERE emp_no=100001;
		UPDATE employees SET birth_date=NOW(), hire_date=NOW() WHERE emp_noe=100001

위와 같이 3개 커넥션에 순차적으로 각 쿼리를 실행해 보자. 커넥션 1의 LOCK TABLES 명령은 테이블의 잠금을 설정하고 바로 반환될 것이다. 하지만 커넥션 2와 커넥션 3은 employees 테이블의 잠금이 해제되기를 기다린다. 이 상태에서 MySQL에 접속해 SHOW OPEN TABLES 명령을 실행해 보자.

mysql > SHOW OPEN TABLES FROM employees;
+-----------+----------------+---------+-------------+
| Database  | Table          | In_use  | Name_locked |
+-----------+----------------+---------+-------------+
| employees | employees      | 3       | 0           |
| employees | dept_manager   | 0       | 0           |
| employees | departments    | 0       | 0           |
| employees | titles         | 0       | 0           |
| employees | employee_name  | 0       | 0           |
| employees | tb_sal1        | 0       | 0           |
| employees | salaries       | 0       | 0           |
| employees | dept_emp       | 0       | 0           |
| employees | tb_sal         | 0       | 0           |
+-----------+----------------+---------+-------------+

mysql > SHOW OPEN TABLES FROM employees LIKE 'employees';
+-----------+----------------+---------+-------------+
| Database  | Table          | In_use  | Name_locked |
+-----------+----------------+---------+-------------+
| employees | employees      | 3       | 0           |
+-----------+----------------+---------+-------------+

아무런 옵션 없이 "SHOW OPEN TABLES" 명령을 바로 실행하면 MySQL 서버의 모든 테이블에 대해 잠금 여부를 보여주고, 위의 첫 번째 예제와 같이 "FROM DB명"을 추가하면 해당 DB에 생성된 테이블에 대해 잠금 상태를 표시한다. 그리고 두 번째 예제와 같이 "FROM DB명 LIKE '패턴'"을 추가하면 해당 DB의 테이블 이름 가운데 패턴이 일치하는 테이블의 잠금 여부가 출력된다. "SHOW OPEN TABLES" 명령의 결과로 출력되는 "In_use" 값은 해당 테이블을 잠그고 있는 클라이언트의 수뿐만 아니라 그 테이블의 잠금을 기다리는 클라이언트의 수까지 더해서 출력된다. 그리고 "Named_locked"는 테이블 이름에 대한 네임 락이 걸려 있는지를 표시한다. 네임 락은 ALTER TABLE이나 RENAME 등과 같은 명령에 의한 잠금을 의미한다.

위 결과를 보면 어떤 테이블이 잠겨있는지 알 수 있지만 어떤 클라이언트의 커넥션이 잠금을 기다리고 있는지는 보여주지 않는데, 이를 확인하려면 "SHOW PROCESSLIST" 명령을 사용해야 한다. 다음 내용은 SHOW PROCESSLIST의 결과를 조금 보기 좋게 편집한 것이다.

mysql > SHOW PROCESSLIST;
+-----+------+-----------+------+----------+------+---------+----------------------------------------+
| Id  | User | Host      | db   | Command  | Time | State   | Info
+-----+------+-----------+------+----------+------+---------+----------------------------------------+
| 1   | root | localhost | test | Sleep    | 46   |         | NULL                                   |
| 3   | root | localhost | test | Query    | 22   | Locked  | UPDATE employees SET hire_date=now()   |
|     |      |           |      |          |      |         | WHERE emp_no=100001                    |
| 4   | root | localhost | test | Query    | 7    | Locked  | UPDATE employees                       |
|     |      |           |      |          |      |         | SET birth_date=now(), hire_date=now()  |
|     |      |           |      |          |      |         | WHERE emp_no=100001                    |
| 5   | root | localhost | NULL | Query    | 0    | NULL    | show processlist                       |
+-----+------+-----------+------+----------+------+---------+----------------------------------------+

위 결과를 보면 Id가 3번인 클라이언트나 4번인 클라이언트의 State가 "Locked"라는 것으로 보아 테이블락을 기다리고 있다는 것을 알 수 있으며, Id가 1번인 클라이언트는 지금 아무것도 하고 있지 않다. 3번과 4번 클라이언트가 업데이트하고자 하는 테이블이 동일하게 employees인 것으로 보아 3번과 4번 클라이언트가 employees 테이블 잠금을 가지고 있지는 않다는 것을 알 수 있다. 그러므로 1번 클라이언트가 이 잠금을 가지고 있는 것이며, 이 경우에는 Id가 1번인 커넥션을 종료시키면 3번과 4번 커넥션이 차례대로 처리를 진행할 수 있다. 클라이언트를 종료시키는 방법은 "KILL QUERY 클라이언트_Id" 명령으로 클라이언트가 실행하고 있는 쿼리만 종료시키거나 "KILL 클라이언트_Id" 명령으로 클라이언트 커넥션을 종료시킬 수 있다.

참고

• Real MySQL