InnoDB 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서도 레코드 기반의 잠금 방식을 탑재하고 있다. 이 레코드 기반의 잠금 방식 덕분에 InnoDB는 MyISAM보다 더 뛰어난 동시성 처리를 진행할 수 있다. 하지만 트레이드 오프는 있는 법. 이원화된 잠금 처리 탓에 InnoDB 레벨에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 까다롭다.
하지만 최근 버전에서는 InnoDB의 트랜잭션과 잠금, 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입되었다. MySQL의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기중이고, 해당 잠금은 어느 트랜잭션이 갖고 있는지 확인할 수 있다. 그리고 장시간 잠금을 가진 클라이언트를 찾아서 종료할 수도 있다.
레코드 기반의 잠금에 대한 잠금 정보는 굉장히 작은 공간에서 관리된다. 때문에 레코드 락이 페이지 락, 테이블 락으로 레벨업 되는 경우(락 에스컬레이션이라고 한다.)는 없다.
한편 InnoDB는 일반 DBMS와는 좀 다르게, 레코드락 뿐만 아니라 레코드와 레코드 사이의 간격을 잠그는 갭(gap)락이란게 있다. 다음의 그림을 보자.
레코드락은 레코드 하나만 잠근다. InnoDB만의 특징이라고 하면, 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 것이다. 보이기에 인덱스가 하나도 없더라도 InnoDB는 내부적으로 클러스터링 인덱스를 생성해 사용하므로 이걸 잠근다. 사실 레코드 자체를 잠그냐, 인덱스를 잠그냐는 차이가 크다. 이는 다음에 다시 다루겠다.
InnoDB에서는 PK, 또는 유니크 인덱스에 의한 변경작업에서는 레코드락만 건다.
이건 레코드와 인접한 레코드 사이의 간격을 잠근다. 왜 이런짓을 할까? 레코드와 레코드 사이 간격에 새로운 레코드가 생성되는 것을 막는것이다. 이 자체로 어떤 역할이 있다기보다 후술할 넥스트 키 락과 함께 사용된다.
레코드락과 갭락이 합쳐지면 넥스트 키 락이 된다. 이건 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될때, 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는게 주 목적이다. 그런데 데드락을 자주 만들어내므로, 가능하다면 바이너리 로그 포맷을 STATEMENT 가 아니라 ROW 형태로 바꿔서 이 락을 줄이는게 좋다. 이 락은 바이너리 로그 포맷이 STATEMENT 여야 실행되기 때문.
참고로 8.0부터는 기본 포맷이 ROW 이다.
MySQL에서는 자동 증가하는 숫자값을 추출하기 위해 AUTO_INCREMENT 라는 칼럼 속성을 제공한다. AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되면 저장되는 각 레코드는 유니크하고, 저장된 순서대로 증가하는 일련번호 값을 가져야 하는데 InnoDB는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 락을 사용한다.