: InnoDB 스토리지 엔진은 내부에서 레코드 기반의 잠금 방식을 탑재, InnoDB는 레코드 기반의 잠금 방식 때문에 MyISAM보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있음
: 이원화된 잠금 처리는 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보를 MySQL 명령을 통해 접근하기가 힘들었으나 최근 버전에서 개선됨
1. InnoDB 스토리지 엔진의 잠금
: InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공, 잠금 정보가 상당히 작은 공간으로 관리되므로 레코드 락이 페이지락, 테이블락으로 레벨업되는 경우는 없음
: 레코드 락 외에도 레코드와 레코드 사이의 간격을 잠그는 갭 락이라는 것이 존재
1.
레코드 락
: 레코드 자체만을 잠그는 것, 다른 사용 DBMS의 레코드 락과 동일한 역할, 다만 InnoDB 스토리지 엔진은 레코드 자체가 아닌 인덱스의 레코드를 잠금
: 인덱스가 하나도 없는 테이블이어도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정, 레코드 자체를 잠그느냐 인덱스를 잠그느냐는 크고 중요한 차이를 만듦,
이는 추후 설명
2.
갭 락
: 차별화된 점, 갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것, 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 방지
: 이 외에도 넥스트 키 락의 일부로 자주 사용
3.
넥스트 키 락
: 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금으로 일반적으로 InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에 실행될 때 소스 서버에서
만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적, 그러나 의외에도 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는
일이 자주 발생, 가능한다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋음
4.
자동 증가 락
: MySQL에서는 자동 증가하는 숫자 값을 추출하기 위해 AUTO_INCREMENT라는 칼럼 속성을 제공, 저장되는 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가지
게 해줌, 이를 위해서 자동 증가 락이라고 하는 테이블 수준의 잠금을 사용
: INSERT나 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하며 UPDATE나 DELETE 등의 쿼리에서는 걸리지 않는다.
: 5.1 이상 버전부터는 innodb_autoinc_lock_mode라는 시스템 변수를 이용해 자동 증가 락의 작동 방식을 변경할 수 있음
•
innodb_autoinc_lock_mode=0
: MySQL 5.0과 동일한 잠금 방식으로 모든 INSERT, REPLACE 등, 자동 증가 락을 사용
•
innodb_autoinc_lock_mode=1
: 단순히 한 건 또는 여러 건의 레코드를 INSERT하는 SQL 중에서 MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때는 자동 증가 락 대신 훨신 빠른
래치(뮤텍스)를 이용해 처리, 개선된 래치는 자동 증가 락과 달리 아주 짧은 시간 동안만 잠금을 걸고 필요한 자동 증가 값을 가져오면 잠금이 즉시 해제됨
•
innodb_autoinc_lock_mode=2
: 자동 증가 락을 사용하지 않고 경량화된 래치를 사용하는 방법, 이 경우 하나의 INSERT 문장으로 INSERT되는 레코드라 하더라도 연속된 자동 증가 값을 보장하지 않음
: 이 설정에서는 다른 커넥션에서 INSERT를 실행하고 있더라도 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다. 다만 이 설정에서 작동하는 자동 증가 기능은
유니크한 값이 생성된다는 것만 보장, 이 경우 소스 서버와 레플리카 서버의 자동 증가 값이 달라질 수도 있기 때문에 주의해야 함
2. 인덱스와 잠금
: InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있기 때문에 자세히 살펴 보아야 함, InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리
즉 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 함
-- // 예제 데이터 베이스의 employees 테이블에는 아래와 같이 first_name 칼럼만 멤버로 담긴 ix_firstname이라는 인덱스가 준비되어 있음 KEY ix_firstname (first_name)
-- // employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며 first_name='Georgi'이고 last_name='Klassen'인 사원은 딱 한 명만 있는 것을 아래 쿼리로 확인할 수 있다.
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
253
mysql> SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
1
-- // employees 테이블에서 first_name='Georgi'이고 last_name='Klassen'인 사원의 입사 일자를 오늘로 변경하는 쿼리를 작성
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';
SQL
복사
: 이 경우 UPDATE 문장이 실행되면 1건의 레코드가 업데이트 되려면 first_name=’Georgi’인 모든 레코드에 잠금이 걸린다.
⇒ 해당 UPDATE 문장에서 인덱스를 사용할 수 있는 것은 first_name=’Georgi’이며 last_name은 인덱스가 없으므로..
: 만약 이 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하므로 모든 레코드를 잠그게 됨, 이것이 MySQL의 방식이며 인덱스 설계가 중요한 이유
3. 레코드 수준의 잠금 확인 및 해제
•
InnoDB 스토리지 엔진을 사용하는 테이블의 레코드 수준 잠금은 테이블 수준의 잠금보다는 조금 더 복잡하다.
•
테이블 잠금에서는 잠금의 대상이 테이블 자체이므로 쉽게 문제의 원인이 발견되고 해결될 수 있으나 레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 잠겨져도 잘 발견되지 않음
•
예전 버전의 MySQL 서버에서는 레코드 잠금에 대한 메타 정보를 제공하지 않았으나 5.1부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하므로 쿼리 하나만 실행해보면 바로 알 수 있음
// 커넥션 1
BEGIN;
UPDATE employees
SET birth_date=NOW()
WHERE emp_no=100001;
SQL
복사
//커넥션 3
UPDATE employees
SET hire_date=NOW(), birth_date=NOW()
WHERE emp_no=100001;
SQL
복사
//커넥션 2
UPDATE employees
SET hire_date=NOW()
WHERE emp_no=100001;
SQL
복사
•
각 트랜잭션이 어떤 잠금을 기다리고 있는지, 기다리고 있는 잠금을 어떤 트랜잭션이 가지고 있는지를 쉽게 메타 정보를 통해 조회할 수 있음
•
performance_schema의 data_locks, data_lock_waits 테이블에서 확인할 수 있다.
•
일단 SHOW PROCESS;를 통해 현재 실행중인 연결을 확인하고 data_locks 테이블과 data_lock_waits 테이블을 조인하여 잠금 대기 순서를 한 번 살펴보면 스레드 간의 순서를 확인할 수 있다. 더 상세히 확인하고 싶다면 data_locks 테이블을 살펴보면 된다.
