MySQL에서 사용하는 잠금은 크게 MySQL 엔진 레벨과 스토리지 엔진 레벨으로 나눌 수 있다.
MySQL 엔진 레벨의 잠금은 모든 스토리제 엔진에 영향을 미치지만,
스트리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.

MySQL 엔진 잠금

글로벌 락

MySQL 서버에 존재하는 모든 테이블에 잠금을 걸게되며, MySQL에서 제공하는 락의 범위중에 가장 크다.

1	FLUSH TABLES WITH READ LOCK

명령으로 락을 획득할 수 있고(기존에 실행중인 락이 있으면 기다린다),
모든 테이블 모든 레코드에 변경이 불가능하게 된다.
서버의 미치는 영향이 크기 떄문에 웹 서비스용으로 사용되는 MySQL에서는 사용하지 않는것이 좋다.
mysqldump 같은것이 우리가 모르는 사이에 내부적으로 이 명령을 실행하고 백업할 때도 있다.

테이블 락

개별 테이블 단위로 잠금을 거는 방식이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

묵시적 방법
MyISAM이나 Memory DB에서 데이터를 변경하는 쿼리를 실행하면 자동으로 테이블 락이 획득된다.
(쿼리가 실행되는 동안 자동으로 획득됬다가 쿼리가 완료되면 자동으로 해제된다.)
MyISAM이나 Memory의 경우 이 묵시적 LOCK이 해당된다.
InnoDB의 경우 레코드 기반 잠금을 사용하기 때문에 변경 쿼리를 실행해도 테이블 잠금이 발생하진 않지만, 스키마를 변경하는 DDL 쿼리를 수행할 경우 테이블 락을 묵시적으로 사용한다.
명시적 방법
InnoDB도 아래와 같이 명시적으로 선언하여 Table LOCK을 획득할 수 있다.
1
LOCK TABLES table_name [READ | WRITE]
READ든 WRITE든 걸게 되면 다른 트랜잭션에서 해당 테이블에 변경 작업을 할 수 없게 된다.
READ는 일관된 읽기를 위해 락을 거는 것이고, WRITE는 데이터 변경을 위해 락을 거는 것이다.
- Table READ 락의 경우 다른 트랜잭션에서 READ가 가능하지만 WRITE가 불가능하고,
  READ 락을 건 트랜젹션이 해당 테이블의 데이터 변경을 원한다면 다시 WRITE 락을 획득해야 한다.
  아니면 Table was locked with a READ lock and can't be updated 와 같은 에러가 발생한다.
- Table WRITE 락의 경우 락을 건 트랜잭션만이 해당 테이블에 접근 가능하고, 다른 트랜잭션은 접근 불가능하다.
  여기서 접근이란 read, write를 모두 포함하므로 Table WRITE 락이 걸린 테이블은 다른 트랜잭션에서 조회도 불가능하다.
1
UNLOCK TABLES
위의 명령을 통해 트랜잭션에서 획득한 테이블 락을 해제할 수 있다.

유저 락

GET_LOCK 함수를 통해 잠금을 획득할 수 있으며, 단순히 사용자가 지정한 문자열에 대해 락을 획득하고 반납한다.
문자열에 대해 잠금을 획득한다는게 정확히 이해가 안간다…
문자열은 어쩌피 immutable 할텐데 락을 걸어야 할 이유가 있을까?

네임 락

db 객체(테이블 등)의 이름을 변경하는 경우 획득하는 잠금이다.
명시적으로 획득하거나 해제할 수 있는것은 아니고,
RENAME TABLE a TO b 처럼 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

스토리지 엔진 잠금

MyISAM, MEMOERY 스토리지 엔진 잠금

자체적인 잠금을 가지고 있지 않고 MySQL 엔진에서 제공하는 테이블 락을 그대로 사용한다.

InnoDB 스토리지 엔진 잠금

InnoDB의 경우 레코드 기반 잠금 방식을 사용한다.
이로 인해 훨씬 뛰어난 동시성 처리를 제공할 수 있게된다.

잠금 방식

비관적 잠금
- 변경하고자 하는 레코드에 대해 잠금을 먼저 획득하고 변경 작업을 처리하는 방식
- 현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다는 비관적 가정을 하기 때문에, 먼저 잠금을 획득
- 높은 동시성 처리에 유리하며, InnoDB가 기본으로 채택하고 있는 방식임
낙관적 잠금
- 각 트랜잭션이 같은 레코드를 변경할 가능성은 희박할 것이라고 낙관적으로 가정
- 변경 작업을 먼저 수행하고, 마지막에 잠금 충돌이 있는지 확인
- 문제가 있었다면 ROLLBACK 처리

잠금 종류

레코드 락(Record Lock)
레코드 자체만을 잠그는 행위를 말한다.
다른 상용 DBMS의 레코드 락과 달리 InnoDB의 경우 인덱스를 참조하여 레코드를 잠근다는 큰 특징이 있다.
갭 락(Gap Lock)
레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 말한다.
레코드와 레코드 사이 간격에 새로운 레코드가 생성되는 것을 제어하기 위함이다.
개념일 뿐 자체적으로 사용되지는 않고, 넥스트락의 일부로 사용된다.
넥스트 키 락(Next Key Lock)
레코드락과 갭 락을 합쳐놓은 형태의 잠금을 말한다.
자동 증가 락(Auto Increment Lock)

인덱스와 잠금

위에서 언급했듯이 InnoDB의 잠금은 레코드를 바로 잠그는 것이 아니라, 인덱스를 사용하여 레코드를 잠근다.
아래와 같은 상황이 있다고 하자.

-- index : ix_firstname(firstname에 대한 index)  

-- 250건
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi';

-- 1건  
SELECT COUNT(*) FROM employees WHERE first_name = 'Georgi' AND last_name = 'Klasen';

이 상황에서 아래와 같은 쿼리를 실행하게 되면,

1	UPDATE employees SET hire_date = NOW() WHERE first_name = 'Georgi' AND last_name = 'Klasen';

업데이트 될 레코드는 1건이지만, 인덱스로 필터할 수 있는 레코드의 개수는 250개가 한계이다.
last_name에 대한 인덱스는 없고, first_name에 대한 인덱스만 있기 때문이다.
즉, 최종적으로 first_name = 'Georgi' 에 해당하는 250건의 레코드가 모두 잠기는 현상이 발생한다.

이러한 특징 떄문에 UPDATE나 DELETE 문장을 위한 적절한 인덱스가 준비되어 있어야 한다. 그렇지 않으면 동시성이 상당히 떨어져서 한 세션에서 변경작업을 하는 중에는 다른 세션에서는 그 테이블을 변경하지 못하고 기다려야 하는 상황이 발생할 것이다.

인덱스가 없는 컬럼을 조건으로 변경 작업을 하게 될 경우, 테이블의 모든 레코드에 대해 내부 클러스터드 인덱스를 이용해 락을 걸게된다.
즉 last_name = 'Klasen'과 같은 조건으로 update 문을 실행하게 되면 외부에서 다른 아무 데이터도 수정할 수 없는 상황이 발생하게 되는 것이다.

이러한 특징 때문에 MySQL Client Tool(workbench 등)에서 기본적으로 index가 없는 컬럼으로 변경쿼리를 못 날리게 되어있는 것 같다(safe update 거리면서…)

해결법?(정확하지 않음)

이런 불필요한 레코드 잠금 현상은 InnoDB의 넥스트 키 락 때문에 발생하는 것이다.
넥스트 키 락의 경우 MySQL의 기본 isolation level인 REPETABLE READ에서 디폴트로 사용하는 잠금 방식이다.
여기서 isolaton level을 READ COMMITTED로 바꿔주면 불필요한 잠금 대신 실제 변경하는 레코드만 락을 거는 방식을 사용할 수 있게 된다.

그런데 MySQL 5.1 이상에서는 바이너리 로그가 활성화되면 최소 REPETABLE READ 이상의 격리 수준을 사용하도록 강제되고 있다.
그러므로 바이너리 로그를 사용하지 않아도 되는 상황이면, 바이너리 로그를 사용하지 않도록 설정하고 isolation level을 READ COMMITTED로 바꾸는 방법도 고려해 볼만하다.

참고로 READ COMMITTED로 불필요한 잠금이 없어졌다고 해서 엄청난 성능향상이 있는것은 아니다.
인덱스로 조회된 레코드에 모두 락을 거는 방식은 똑같은데, 이후에 바로 불필요한 부분에 대해서 락을 해제하는 식으로 동작하기 때문이다.
그러므로 최대한 인덱스를 사용헐 수 있게 튜닝해주는 것이 좋다.

참고 : 이성욱, 『Real MySQL』, 위키북스(2012)