[MySQL] 잠금

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

트랜잭션 vs 잠금

---

트랜잭션은 작업의 완전성을 보장하는 기능으로, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상 (Partial Update)이 발생하지 않게 해주는 기능.

잠금은 동시성을 제어하기 위한 기능이고, 트랜잭션은 데이터의 정합성을 보장하기 위한 기능.

잠금 → 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 수행.

트랜잭션 주의사항

트랜잭션은 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.

• 일반적으로 데이터베이스 커넥션의 개수는 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션의 개수는 줄어들고, 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수 있다.

MySQL 엔진의 잠금

---

글로벌 락

FLUSH TABLE WITH READ LOCK

다음 명령으로 글로벌 락을 획득할 수 있다.

• mysqldump 같은 백업 프로그램은 우리가 알지 못하는 사이에 이 명령을 내부적으로 실행하고 백업할 때도 있다.
• InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없다.
  • MySQL 8.0 버전부터는 Xtrabackup 이나 Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도입됐다.

백업 락

백업 락은 일반적인 테이블의 데이터 변경은 허용된다.

테이블 락

명시적으로는

LOCK TABLES table_name [READ | WRITE] // 테이블 잠금 UNLOCK TABLES // 잠금 해제

다음 명령으로 특정 테이블의 락을 획득할 수 있다.

• InnoDB 스토리지 엔진에서는 다른 MyISAM이나 MEMORY 테이블과 다르게 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지는 않는다.
• 더 정확히는 InnoDB 테이블에도 테이블 락이 설정되지만 대부분의 DML 쿼리에서는 무시되고 DDL의 경우에만 영향을 미친다.

네임드 락

GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득하는 잠금. ex) RENAME TABLE a to b

InnoDB 스토리지 엔진 잠금

---

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.

• 덕분에 MyISAM보다는 훨씬 뛰어는 동시성 처리를 제공할 수 있다.

모니터링

• 최근 버전에서 InnoDB의 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션의 목록을 조회할 수 있는 방법이 도입됐다.
• MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있으며, 또한 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료시킬 수도 있다.

레코드 락

• InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠금 다는 차이가 있다.
• InnoDB에서는 대부분 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하지만 PK 또는 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않고 레코드 자체에 대해서만 락을 건다.

갭 락

• 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것.
• 레코드와 레코드 사이의 간격에 새로운 레코드가 생성 (INSERT)되는 것을 제어하는 것.

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 것을 넥스트 키 락이라고 한다.
• STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE READ 격리 수준을 사용해야 한다.
• InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적이다.
• 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.
• 가능하다면 바이너리 로그 포맷을 ROW 형태로 바꿔서 넥스트 키 락이나 갭 락을 줄이는 것이 좋다.

자동 증가 락

• AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다.
• InnoDB 스토리지 엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT 락이라고 하는 테이블 수준의 잠금을 사용한다.
• 아주 짧은 시간 걸렸다가 해제되는 잠금이라 대부분의 경우 문제가 되지 않는다.