MySQL Lock과 격리 수준이 진짜 문제가 되는 순간

트랜잭션과 잠금

트랜잭션

작업의 완전성을 보장, 논리적인 작업 셋을 완벽하게 처리하거나 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상 발생 방지 기능

• 논리적인 작업이 하나의 쿼리 이상이고, COMMIT시에 무조건 적용되거나 혹은 적용되지 않아야ROLLBACK 함을 보장

MyISAM와 MEMORY 스토리지 엔진의 경우에는 프라이머리키 중복이 발생하더라도 트랜잭션에서 묶여있는 중복이 발생하기 전의 작업들은 db에 반영이 된다. → Partial Update 정합성보장 X

MyISAM

InnoDB

• 커넥션 생성 시점의 최적화

많은 개발자가 트랜잭션 시작 전에 DB 커넥션을 미리 생성하고, 트랜잭션 종료 후에 커넥션을 닫는 패턴을 사용합니다. 하지만 이는 커넥션 점유 시간을 불필요하게 늘려 커넥션 풀 고갈을 유발할 수 있습니다. 커넥션 풀은 제한된 자원이므로, 실제 DB 작업이 필요한 순간에만 커넥션을 획득하고 즉시 반환하는 것이 중요합니다.

• 트랜잭션 내 외부 네트워크 작업 분리

트랜잭션 안에서 메일 전송, FTP 파일 전송, 외부 API 호출 등 네트워크 기반 작업을 수행하면 심각한 성능 저하가 발생합니다. 외부 서비스의 응답 지연 시간 동안 DB 커넥션과 스레드가 계속 대기 상태로 묶이기 때문입니다. 이는 다른 요청들까지 대기하게 만드는 병목 현상으로 이어집니다. 외부 네트워크 작업은 반드시 트랜잭션 범위 밖에서 처리해야 합니다.

• 트랜잭션 범위의 최소화

모든 DB 작업을 하나의 트랜잭션으로 묶을 필요는 없습니다. 원자성이 보장되어야 한다면 해당 작업들만 트랜잭션으로 묶고, 단순 조회 작업은 별도로 처리하는 것이 효율적입니다. 트랜잭션은 데이터 일관성 보장이 필요한 최소 단위로만 적용해야 커넥션 점유 시간을 최소화하고 시스템 처리량을 극대화할 수 있습니다.

예시

잠금

동시성을 제어하기 위한 기능 반면 트랜잭션은 데이터의 정합성을 보장하기 위한 기능

• 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할

잠금

• 스토리지 엔진 레벨의 잠금
  • 스토리지 엔진 간 상호 영향 미치지 않음
• MySQL 엔진 레벨의 잠금
  • 잠금이 모든 스토리지 엔진에 영향을 미침

글로벌 락

한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 락이 해제될 때까지 대기 상태로 남음.

테이블 락

개별 테이블 단위로 설정되는 잠금

네임드 락

임의의 문자열에 대해 잠금을 설정할 수 있다. 대상이 데이터 베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금

메타데이터 락

데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우 획득하는 잠금 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금.

InnoDB 스토리지 엔진 잠금

스토리지 엔진 내부에 레코드 기반의 잠금 방식을 탑재.

잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨없되는 경우는 없다.

레코드 락뿐 아니라 레코드 사이의 간격을 잠그는 갭(GAP)락이 존재

레코드락

• 레코드 자체만을 잠그는 락 단, 레코드 자체를 잠그는게 아니라 인덱스의 레코드를 잠근다. 만약 인덱스가 없다 하더라도, 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금

갭락

• 레코드 자체가 아니라 레코드와 인접한 레코드 사이의 간격만을 잠그는 락. → 레코드와 레코드 사이의 새로운 레코드 생성을 제어

넥스트 키 락

• 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
• 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 대 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어 내도록 보장.

자동 증가 락

• AUTO_INCREMENT 라는 칼럼 속성인 테이블에 동시에 여러 레코드가 INSERT되는 경우, 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야한다. → InnoDB 내부적으로 AUTO_INCREMTNT 락
• INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요.
• 이 락은 테이블에 무조건 단 하나만 존재!

인덱스와 잠금

• InnoDB는 레코드를 잠그는 것X → 인덱스를 잠금 O 따라서 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야함.

update 문장을 위해서 하나의 레코드만 업데이트됨. 하지만 이 1건의 업데이트를 위해서 아래의 사진과 같이 ix_firstname(first_name) 인덱스와 연관된 필드들이 모두 잠기는 현상이 발생함.

하지만 만약에 이 테이블에 인덱스가 하나도 존재하지 않는다면 풀테이블 스캔이 발생하면서 모든 몇십만 건의 레코드를 모두 잠그게 됨.

이러한 이유로 인해서 MySQL의 InnoDB에서 인덱스 설계는 중요!

레코드 수준의 잠금 확인 및 해제

• 테이블 잠금에 비해서 레코드 수준의 잠금은 그 레코드를 사용하지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.

MySQL의 격리 수준

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

격리수준 (순서대로 뒤로 갈수록 각 트랜젝션 간의 데이터 격리 정도가 높아짐)

1. Dirty Read (오염된 읽기)

핵심: 아직 확정되지 않은 남의 데이터를 읽는 것

• 요지: 서로 다른 트랜잭션에서 발생하는 문제

1. Non-Repeatable Read (반복 불가능한 읽기)

핵심: 내가 읽었던 데이터가 다시 보니 바뀐 것 (수정과 삭제로 인해 발생하는 문제)

• 요지: 같은 트랜잭션 내에서 시점에 따라 발생하는 문제

1. Phantom Read (유령 읽기)

핵심: 없던 데이터가 갑자기 나타나는 것 (삽입으로 인해 발생하는 문제)

• 요지: 같은 트랜잭션 내에서 시점에 따라 발생하는 문제

READ UNCOMMITTED

트랜잭션에서의 변경 내용이 COMMIT이나 ROLLBACK과 상관없이 다른 트랜잭션에서 보임.

위의 사진에서 사용자 A는 emp_no가 500000이고 first_name이 “Lara”인 사원을 Insert하는데 사용자 B가 변경된 내용이 Commit이 되기도 전에 그 사원을 검색해서 아직 커밋되지 않은 상태에서 그 값이 조회가 되면서 A가 데이터 처리 중 문제가 발생한다고 하더라도 B는 계속해서 그 값을 정상이라 판단하고 처리함.

→ 이러한 문제를 Dirty Read라고 한다.

READ COMMITTED

오라클 DBMS의 기본 격리 수준로 방금 전과 같은 문제가 발생하지 않고 무조건 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음.

위 사진에서 사용자 A는 “Lara”를 “Toto”로 변경했는데 이때 새로운 값인 Toto는 즉시 employees 테이블에 기록되고 이전 값 Lara는 언두 영역으로 백업된다. 이때 만약에 사용자 B가 500000인 사원을 조회하면 결과가 Toto가 아니라 Lara로 조회된다. 이말은 즉슨 백업 영역 즉 언두 영역의 레코드에서 값을 가져온다는 것이다.

다음으로 현재 격리 수준에서도 NON_REPEATABLE READ라는 부정합의 문제가 있다.

처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 first_name이 “Toto”인 사용자를 검색했는데 결과 X 하지만 사용자 A가 사원번호 500000인 사원의 이름을 “Toto”로 변경하고 커밋을 실행한 후, 사용자 B가 똑같은 조회를 실행하면 이번에는 1건 조회.

→ 이는 사용자 B가 하나의 트랜잭션 내에서 똑같은 select 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋남

REPEATABLE READ

MySQL의 InnoDB 스토리지 엔진의 기본으로 사용되는 격리 수준이다. 이 격리 수준에서는 NON-REPEATABLE READ 문제가 발생하지 않는다. 이 스토리지 엔진은 트랜잭션의 ROLLBACK에 대비해서 변경되기 전 레코드를 Undo 공간에 백업해두고 실제 레코드 값을 변경한다.(MVCC- Multi Version Concurrency Control)

사실 Read Committed도 MVCC를 이용해서 Commit 되기 전의 데이터를 보여주지만 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 차이가 있다.

모든 InnoDB의 트랜잭션은 고유 번호(순차 증가)가 있고 언두 영역에 백업된 모든 레코드에도 변경을 발생시킨 그 번호가 포함된다. 현재 격리 수준에서는 MVCC 보장을 위해서 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 앞선 번호의 언두 영역 데이터를 삭제 불가능하다.

사용자 A의 트랜잭션 번호는 12, B의 TRX-ID는 10. 이때 사용자 A는 사원의 이름을 “Toto”로 변경하고 커밋을 수행하였지만 사용자 B가 사원을 A 트랜잭션의 변경 전후 각각 한 번씩 조회했는데 결과는 항상 “Lara”라는 값을 가져온다.

사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데 그때부터 사용자 B의 10번 안에서 실행되는 모든 조회 쿼리는 자신의 트랜잭션 번호 보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

원래는 이전의 그림과 같은 결과가 나와야한다. 하지만 위 그림에서 사용자 B가 실행하는 두 번의 Select 문의 결과는 서로 다르다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ 라고 한다. Select … for update 쿼리는 select하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다.

• SELECT ... FOR UPDATE 이 쿼리는 언두 로그를 무시하고 현재 테이블에 실재하는 데이터를 직접 찾아가서 자물쇠를 걸어버립니다.

SERIALIZABLE

가장 단순하고 가장 엄격한 격리 수준으로 순수한 select 작업은 아무런 레코드 잠금 없이 수행한다. 하지만 문제는 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근 할 수 없는 것.