[Real MySQL] 4. 트랜잭션과 잠금

트랜잭션

트랜잭션이란, 논리적인 작업 셋(Set)을 완벽하게 처리하거나 반대로 실패할 경우 원 상태로 전부 복구하는 기능이다.
그래서 작업의 일부만 적용되는 Partial Update 가 일어나지 않도록 한다.

MySQL 에서의 트랜잭션

스토리지 엔진 중 MyISAM 이나 MEMORY 같은 스토리지 엔진은 트랜잭션을 지원하지 않는다.
트랜잭션이 없어서 사용하기 단순할 것 같지만, 오히려 더 많은 문제를 만들어낸다.
일부 과정이 실패했을 때 생기는 Partial Update 같은 문제는 더 복잡한 후속조치 과정을 양산할 뿐이다.

InnoDB 스토리지 엔진은 트랜잭션을 지원하기 때문에 논리적인 작업들의 단위가 전부 반영되거나, 전부 반영되지 않는 것을 보장한다.

트랜잭션 적용 시 주의사항

• 트랜잭션은 트랜잭션이 필요한 기능 범위에만 최소한으로 적용해야 한다.
  • 일반적인 DB 커넥션은 개수가 제한적이기 때문에 각 커넥션을 프로그램이 오래 물고 있을수록 병목이 발생할 확률이 커진다.
• 메일 전송, FTP 파일 전송, 네트워크 통신과 같은 외부 자원과의 연결 작업은 제거해야 한다.
  • 해당 작업이 실패하거나 시간이 오래 걸리는 경우 프로그램 전체가 위험할 수 있다.
• 가능하다면 트랜잭션 각각의 범위 크기도 최소화해야 한다.
  • 모든 작업에 트랜잭션이 필요하다고 해서 한 단위로 묶는 것은 좋지 않다. 여러 개의 트랜잭션으로 쪼개서 각 범위를 최소화한다.

---

MySQL 엔진의 잠금

잠금과 트랜잭션은 서로 비슷한 기능 같지만, 사실 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.

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

MySQL 엔진 레벨의 잠금

• 글로벌 락
  • 가장 범위가 큰 잠금이다. 여러 DB 에 존재하는 MyISAM 이나 MEMORY 테이블에 대해 mysqldump 로 일관된 백업을 받아야 하는 경우 사용한다.
• 테이블 락
  • 개별 테이블 단위 잠금이다. 글로벌 락과 동일하게 온라인 작업에 큰 영향을 끼치기 때문에 애플리케이션에서 사용할 일은 없다.
  • InnoDB 테이블에도 테이블 락이 설정되지만 DML 쿼리에서는 무시되고 DDL 쿼리에만 영향을 끼친다.
• 유저 락
  • 사용자가 지정한 문자열(String)에 대해 획득하고 반납하는 잠금이다.
  • 여러 클라이언트가 상호 동기화를 처리해야 할 때 사용하면 좋다.
• 네임 락
  • DB 객체(테이블, 뷰 등)의 이름을 변경하는 경우 획득하는 잠금이다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 MySQL 에서 제공하는 잠금과 별개로 레코드 기반의 잠금 방식을 탑재하여 뛰어난 동시성 처리를 제공한다.
InnoDB 는 비관적 잠금 방식을 채택하고 있다.

비관적 잠금 : 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 먼저 획득하고 변경 작업을 처리하는 방식
낙관적 잠금 : 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 없었는지 확인해 문제가 있었다면 ROLLBACK 하는 방식

InnoDB 의 잠금 종류는 다음과 같은 것들이 있다.

• 레코드 락
  • 레코드 자체만을 잠그는 락이다.
  • 중요한 것은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다. 인덱스가 하나도 없는 테이블이라도 자동 생성되는 클러스터링 인덱스를 이용해 잠금을 설정한다.
• 갭 락
  • 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것이다.
  • 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.
• 넥스트 키 락
  • 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다.
• 자동 증가 락
  • AUTO_INCREMENT 가 설정된 칼럼에 대해 적용하는 테이블 수준의 잠금이다.

InnoDB 의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식이다.
즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다.

UPDATE sample_tbl SET col3='updated' WHERE col1='value1' AND col2='value2';

위와 같은 쿼리에서 col1 에만 인덱스가 생성되어 있다면, 실제로는 하나의 레코드만 update 된다고 할지라도 어떤 레코드가 변경될지 모르기 때문에 col1 이 value1 인 모든 인덱스의 레코드를 잠금 처리하게 된다.

따라서 UPDATE 문장을 위한 인덱스가 적절하게 준비되어 있지 않다면, 클라이언트 간 동시성이 상당히 떨어질 것이다.
만약 테이블에 인덱스가 하나도 없다면 전체 테이블의 레코드를 모두 잠금처리하는 일이 일어날 것이다.

---

격리 수준

트랜잭션의 격리 수준이란 동시에 여러 트랜잭션이 실행될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다.

MySQL 의 격리 수준

네 가지의 격리 수준 (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) 중 Dirty Read 라고도 하는 READ UNCOMMITTED 나 SERIALIZABLE 은 거의 사용하지 않는다.
4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시에 동시성도 떨어지는 것이 일반적이다.

• READ UNCOMMITTED
  • 다른 트랜잭션의 변경 내용이 커밋, 롤백 여부에 상관없이 내 트랜잭션에 보여진다.
  • 이런 현상을 더티 리드(Dirty Read)라 하며, 정합성에 문제가 많은 격리 수준이다.
• READ COMMITTED
  • 다른 트랜잭션이 데이터를 변경하더라도 커밋이 완료된 데이터만 내 트랜잭션에서 조회할 수 있다.
  • 사용자 A가 커밋하기 전에 이미 데이터의 변경이 일어났어도, 사용자 B는 언두 영역의 백업된 레코드에서 변경 전 데이터를 읽어올 수 있다.
  • 사용자 A가 데이터를 변경하고 커밋하는 과정 앞뒤로 사용자 B가 같은 데이터를 두 번 조회할 경우 서로 다른 결과값이 나올 수 있다는 문제가 있다. (NON-REPEATABLE READ)
• REPEATABLE READ
  • InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다.
  • 바이너리 로그를 가진 MySQL 장비에서는 최소 REPEATABLE READ 이상의 격리 수준을 사용해야 한다.
  • 트랜잭션의 롤백 작업을 대비해 변경 전 레코드를 언두 영역에 백업해두고 변경 작업을 진행하는 MVCC 방식을 위해, 읽기 작업 시에도 필요하다면 언두 영역의 데이터를 읽어 같은 트랜잭션 내에서는 동일한 결과를 보장해준다.
    • 각 레코드에는 (순차적으로 증가하는) 트랜잭션의 고유 번호가 있는데, 읽기 작업 시에 현재 트랜잭션 번호보다 해당 레코드의 트랜잭션 번호가 더 높다면(이후라면), 언두 영역의 변경 전 레코드의 값을 읽어오기 때문에 이러한 일이 가능하다.
  • 이 격리 수준에서도 부정합이 발생할 수 있는데, 사용자 A가 INSERT 를 진행할 경우 사용자 B의 조회 결과 값의 수가 달라질 수 있다는 점이다. (PHANTOM READ)
    • InnoDB 에서는 독특한 특성 때문에 REPEATABLE READ 격리 수준에서도 PHANTOM READ 가 발생하지 않는다.
• SERIALIZABLE
  • 가장 단순한 격리 수준이지만 가장 엄격한 격리 수준이다. PHANTOM READ 현상이 발생하지 않으나 동시성이 많이 떨어진다.
  • 읽기 작업까지도 읽기 잠금을 획득해야만 읽기를 진행할 수 있다.
  • InnoDB 에서는 REPEATABLE READ 격리 수준이이도 PHANTOM READ 가 발생하지 않기 때문에 굳이 사용할 필요는 없어 보인다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.