앞선 InnoDB 스토리지 엔진 잠금 중에 레코드 락은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리 됨.
즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 함.
예시를 보자.
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
+--------+
| 253 |
+--------+
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
+--------+
| 1 |
+--------+
mysql> UPDATE employees SET hire_data=NOW() WHERE first_name='Georgi' AND last_name='Klassen';
위 예시에서, employees 테이블에는 first_name 칼럼만 ix_firstname 이라는 인덱스가 걸려있음.
first name 이 ‘Georgi’ 인 사원은 전체 253명임
first name 이 ‘Georgi’ 이면서 last name 이 ‘Klassen’인 사원은 1명임
first name 이 ‘Georgi’ 이면서 last name 이 ‘Klassen’인 사원의 입사 일자를 오늘로 변경하는 쿼리를 실행하는 상황임.
UPDATE 쿼리가 실행되면 1건의 레코드가 업데이트 됨.
first_name 만 인덱스가 걸려있으므로 이 1건의 업데이트를 위해 first_name=’Georgi’ 인 레코드 253건에 대한 레코드가 모두 잠긴다.
위 그림에서, 예시의 UPDATE 문장이 어떻게 변경 대상 레코드를 검색하고, 실제 변경이 수행되는지 보여줌.
이 예제에서는 몇 건 안되는 레코드만 잠그지만, UPDATE 문장을 위해 적절한 인덱스가 준비돼 있지 않으면 각 클라이언트 간 동시성이 상당히 떨어지게됨.
만약 이 employees 테이블에 인덱스가 하나도 없다면, 테이블 풀 스캔을 하면서 UPDATE 작업을 한다.
이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠근다.
이게 MySQL 의 방식임.
MySQL 의 InnoDB 에서 인덱스 설계까 중요한 이유가 바로 이것임.
레코드 수준의 잠금 확인 및 해제
InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준 잠금보다 좀 더 복잡함.
테이블 잠금은 대상이 테이블 자체이므로 쉽게 문제의 원인을 파악하고 해결할 수 있음.
레코드 잠금은 그 레코드가 자주 사용되지 않으면 오랜 시간 동안 잠겨진 상태로 남아 있어서 잘 발견되지 않음.
예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보를 제공하지 않아서 더더욱 문제를 파악하기 어려움
5.1 버전 부터 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행하면 잠금과 잠금 대기를 바로 확인할 수 있음. (메타정보를 통해 조회)
information_schema 라는 DB 에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 통해 확인이 가능함.
8.0 버전부터 information_schema 정보가 조금씩 제거되고 있음.
대신 performance_schema 의 data_locks 와 data_lock_waits 테이블로 대체되고 있음.
mysql> SHOW PROCESSLIST;
위 명령어를 통해 MySQL 서버에 실행된 명령어에 대한 상태의 프로세스 목록을 확인할 수 있음.
performance_schema 의 data_locks, data_lock_watis 테이블을 조인해서 잠금 대기 순서를 조회할 수도 있다. (174p 상세 예시 설명되어 있음)
특정 스레드가 어떤 잠금을 가지고 있는지 더 상세히 확인하고 싶으면 data_locks 테이블이 가진 칼럼을 모두 확인해보면 됨.
mysql> KILL {스레드 번호};
강제로 잠금을 해제하려면 위와 같이 KILL 명령으로 MySQL 서버 프로세스를 강제로 종료하면 됨.
