MySQL 잠금과 Isolation level에 대해서

트랜잭션은 작업의 완전성을 보장해줍니다. 함께 수행되어야 하는 작업들을 묶어서 전부 완벽하게 처리하거나 하나라도 처리가 되지 않으면 다시 원상태로 돌리는 작업을 하는 것을 의미합니다.

잠금과 트랜잭션은 비슷하면서 다릅니다.

• 잠금: 동시성을 제어하기 위함 → 여러 커넥션에서 동시에 같은 레코드나 테이블에 접근하여 변경할 경우 결과적으로 예측할 수 없는 값이 됨.
• 트랜잭션: 데이터의 정합성을 보장하기 위함.

격리수준이라는 것을 정의할 수 있는데 이것은 하나 혹은 여러 트랜잭션에서 작업내용을 어떻게 공유하고 차단할지를 결정하는 것입니다.

트랜잭션

트랜잭션은 묶여있는 논리적 작업 단위들이 전부 반영되거나 오류발생 시 하나도 반영되지 않아야 합니다. MySQL의 스토리지 엔진에 따라도 차이가 있는데요. MyISAM같은 경우는 Partial Update가 되어 (부분 업데이트) 문제가 있는 부분은 반영이 되지 않고, 일부만 성공하는 현상이 발생합니다. 그렇기 때문에 실패에 대한 재처리 작업을 애플리케이션 코드단에서 구현을 해주어야 합니다. 하지만 트랜잭션이 지원되는 InnoDB에서는 이런부분들을 내부적으로 처리하여 정합성을 보장합니다.

주의사항

트랜잭션도 최소한의 코드에서만 적용하는 것이 좋다고 하는데요. 꼭 필요한 부분에만 적용하라는 의미입니다. 데이터베이스 커넥션 숫자는 제한적이기 때문에 메서드에서 커넥션을 소유하는 시간이 길어질수록 사용 가능한 여유 커넥션 숫자는 줄어들게 됩니다. 트랜잭션 내에서 DBMS에 저장하는 작업같이 꼭 필요한 작업만 수행해야하며 기타 다른 네트워크 통신이 같이 트랜잭션 내부에 들어가게 되면 DBMS 서버가 위험한 상태에 빠질 수 있습니다.

잠금

MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL엔진 레벨로 나뉘어집니다. MySQL엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치게 되지만 스토리지 엔진 레벨의 잠금은 MySQL엔진에는 영향을 미치지 않습니다.

잠시 스토리지 엔진 영역과 MySQL엔진영역을 살펴보게 되면 아래와 같습니다.

 

MySQL 서버 구조

image출처: http://doc.docs.sk/mysql-refman-5.5/pluggable-storage-overview.html

 

MySQL엔진은 커넥션 핸들러, SQL 파서, 옵티마이저, 캐시와 버퍼 등이 위치를 하고 스토리지 엔진은 실제 디스크 스토리지를 읽거나 저장하는 역할을 수행합니다. MySQL 서버 내에서 MySQL엔진은 하나지만 스토리지 엔진을 여러개 둘 수 있습니다.

글로벌 락

MySQL이 제공하는 락 중에서 가장 범위가 큽니다. 한 세션에서 글로벌 락을 획득하면 다른 세션에서 select를 제외한 다른 ddl,dml이 대기됩니다. 글로벌 락은 웹서비스용 디비에서는 가급적 사용하지 않습니다.

테이블 락

개발 테이블 단위로 설정되는 락입니다. 명시적으로 거는일은 거의 없습니다. 묵시적으로 걸리는 경우는 MySQL서버가 데이터를 변경할 테이블에 자동으로 잠금을 설정하고 변경 이후 즉시 잠금을 해제하는 경우에 사용됩니다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드기반의 잠금을 사용하기 때문에 단순 변경 쿼리로 묵시적 테이블락이 걸리지는 않습니다. 다만 ddl(스키마변경)의 경우에만 영향을 미칩니다.

유저락

테이블이나 레코드가 아닌 사용자가 지정한 문자열을 잠그는 것입니다. 동일한 데이터베이스에 여러 클라이언트가 붙어있다면, 그리고 동일 데이터를 변경하려는 시도가 잦다면 그 액션을 취하는 작업끼리 묶어서 유저락을 걸고 쿼리를 실행하면 간단히 해결될 수 있다고 합니다.

네임락

테이블이나 뷰같은 데이터베이스 객체의 이름을 변경할 때 자동으로 획득되는 락입니다.

RENAME TABLE rank TO rank_Backup, rank_new TO rank

RENAME TABLE rank TO rank_Backup, rank_new TO rank

위와 같이 한 줄로 쓰게 된다면 원본과 변경될 이름에 한꺼번에 락을 걸게 됩니다. 하지만 아래와 같이 2줄로 나눠서 실행을 하게 되면

RENAME TABLE rank TO rank_backup

RENAME TABLE rank_new TO rank

RENAME TABLE rank TO rank_backup 
RENAME TABLE rank_new TO rank

짧은 순간이지만 저 사이에 rank테이블에 인서트 하는 작업이 있다면 table not found가 발생할 수 있습니다.

엔진별 잠금

• MyISAM

이 스토리지 엔진은 모두 테이블 단위의 잠금이 일어나기 때문에 테이블을 해제하지 않으면 다른 클라이언트에서 그 테이블을 사용할 수 없습니다. 하나의 테이블에서 다른 레코드에 작업을 하더라도 동시에 변경자체가 되지 않기 때문에 쿼리의 동시성이 떨어지게 됩니다.

• InnoDB

MySQL엔진에서 제공하는 잠금과 별개로 스토리지 엔진 레벨에서 레코드 기반의 잠금방식을 사용할 수 있습니다.

InnoDB

InnoDB는 아주 정통적인 트랜잭션에 대응하는 데이터 저장소입니다.

• ACID 트랜잭션
• 행 레벨의 lock
• 4개의 격리 레벨 지원 (READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)

InnoDB에는 풍부한 기능이 있고, 성능도 충분히 좋기 때문에 대부분의 경우 InnoDB를 선택을 하게 됩니다. 특히 웹서비스에서는 트랜잭션이 필수이기 때문에 InnoDB외에는 선택지가 거의 없다고 봐도 무방합니다.

InnoDB 구조적 특징

 

• 클러스터 인덱스

InnoDB는 클러스터 인덱스 구조로 되어있으며 데이터가 인덱스의 리프 노드에 저장됩니다. 즉 인덱스자체에 데이터를 포함하고 있는 것입니다.

클러스터 인덱스

image출처: MySQL 5.7 완벽 분석

 

위와 같이 인덱스를 검색하면 바로 데이터에 도달할 수 있습니다. 장점은 pk 검색 효율이 매우 좋습니다. 리프 노드까지 도달하게 되면 추가적으로 레코드를 찾는 작업을 할 필요가 없습니다. 하지만 pk가 아닌 다른 칼럼에 인덱스를 걸게 된다면 (세컨더리 인덱스) 리프 노드까지 인덱스를 찾은 후 다시 실제 데이터를 찾아가야 하기 때문에 효율이 느립니다. 이것은 커버링 인덱스로 해결할 수 있습니다.

 

• 체크포인트

클라이언트의 요청이 데이터 변경인 경우 데이터의 변경 작업은 모두 데이터베이스 캐시에서 일어나며 바로 쓰지는 않습니다. 그전에 로그에 기록을 하게 되는데 아직 디비에 반영되지 않은 것들을 더티 데이터라고합니다. 더티 데이터가 있는 상태에서 디비 서버가 손상이 될 경우 재기동후에 복구를 해야합니다. 더티 데이터는 로그에 존재하므로 이 내용을 재생하여 복원하게 됩니다. 더티 데이터를 어디까지 읽었는지 기록을 하려면 체크 포인트가 필요합니다.

InnoDB는 퍼지라는 체크포인트를 내장하고 있습니다. 퍼지 체크포인트는 더티 데이터를 조금씩 디스크로 플러시하여 어디까지 플러시 했는지 기록합니다.

 

• MVCC, UNDO 로그

InnoDB에서는 REPEATABLE-READ 격리레벨을 실현하기 위해 MVCC(Multi Version Concurrency Control)를 도입했습니다. 읽기 전용 트랜잭션에서 시차를 주어 일관성을 보장하는 것인데요. (격리레벨에 관한 내용은 하단에서 다루니 그쪽을 참고하세요)

어떤 트랜잭션이 변경한 행을 commit하기 전에는 별도의 트랜잭션이 읽었을 때 항상 일관된 값을 보장하고 싶다면 변경할 행 전체에 락을 걸어야 합니다. 그렇기 때문에 최신데이터로만 읽게 하고싶다면 오버헤드가 커지게됩니다. 그래서 등장한것이 과거의 데이터를 보여주는 것인데요.

 

이미 실행하고 있던 트랜잭션이 변경 된 행 데이터를 참조하더라도 최신 데이터가 아닌 변경 전의 데이터를 참조하게 되는 것입니다. 다시 말해 과거 데이터를 참조하기 때문에 항상 일관된 결과를 보게 됩니다. 이렇게 참조하게 되면 행에 락을 걸 필요가 없게 됩니다. 그럼 이 과거 데이터는 어디서 가져올까요?

 

행 데이터가 변경될 때 과거 데이터를 UNDO 로그 영역이라는 곳에 보냅니다. 각각의 행에는 롤백 포인터가 있어서 과거로 타고 올라갈 수 있습니다. InnoDB에서는 REPEATABLE-READ와 READ-COMMITTED에서 이 MVCC가 이루어집니다. 

InnoDB 잠금방식

• 비관적 잠금: 경합이 일어날 것이라고 미리 가정을 하고 잠금부터 획득하고 작업을 하는 방식입니다. InnoDB는 이 잠금 방식을 채택하고 있습니다. 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 하네요.

• 레코드 락: 레코드 자체만을 잠그는 락입니다. InnoDB 스토리지 엔진은 레코드가 아니라 인덱스의 레코드를 잠그는데요. 인덱스가 없더라도 내부적으로 자동생성된 클러스터인덱스를 활용하여 잠급니다.

• 갭 락: 레코드와 레코드 사이를 잠그는 논리적인 개념입니다. 갭 락 단독으로 사용되지는 않고 넥스트 키락이라는 것의 일부로 사용됩니다. 해당 내용은 하단에 넥스트 키락이라는 것이 등장할 때 다시한번 보도록하겠습니다.

인덱스와 잠금

InnoDB의 잠금은 레코드가 아니라 인덱스를 잠그는 방식으로 처리된다고 하였는데요. 책의 예시인데 아주 중요한 포인트가 있는거같아서 가져와봤습니다.

update employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';'

실제로 데이터베이스에 first_name과 last_name이 저런 사람은 딱 1명입니다. 하지만 first_name이 'Gerogi'인 사람은 253명입니다. 게다가 인덱스는 first_name에만 걸려있는 상태입니다. 그렇기 때문에 last_name으로는 특정 사람에 대한 레코드를 잠글 수가 없습니다. 결과적으로 253건의 레코드가 전부 잠겨버리게됩니다.

Lock

image출처: RealMySQL 4장

 

테이블의 rows가 굉장히 많다면 이 비지니스 로직은 동시성이 상당히 떨어지는것으로 나타날 것입니다. 혹여나 first_name에도 인덱스가 없다면 전체 테이블을 full scan하는 일이 벌어지게 됩니다. 결과적으로 모든 레코드가 잠기기 됩니다. 그렇기 때문에 InnoDB에서 인덱스 설계가 매우 중요합니다.

MySQL 격리 수준

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

격리 수준은 다음과 같습니다.

 

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

일반적인 웹서비스용 데이터베이스는 READ COMMITTED, REPEATABLE READ 둘 중에 하나를 사용하게 됩니다.

READ UNCOMMITTED

각 트랜잭션의 변경 내용이 commit이나 roll back여부에 상관없이 다른 트랜잭션에서 보여집니다.

 

• 1번 트랜잭션: a 사원을 insert를 하려하고 아직 commit은 하지 않음
• 2번 트랜잭션: a 사원을 조회. → 조회 됨
• 2번 트랜잭션: a 사원으로 로직을 수행.
• 1번 트랜잭션: 문제가 생겨 롤백.
• 2번 트랜잭션: ..??

결과적으로 존재하지 않는 사원인데 2번 트랜잭션에서는 정상 사원이라고 생각하여 로직을 수행하게 됩니다. 그렇기 때문에 이 격리레벨은 RDBMS표준에서는 격리 수준으로 인정을 하지 않을 정도로 정합성에 문제가 많은 격리 수준이라고 할 수 있습니다.

READ COMMITTED

위 에서 발생한 내용은 일어나지 않습니다.

 

• 1번 트랜잭션: a 사원의 first_name 이 a였는데 이를 b로 이름 변경 (아직 커밋 전)
• 2번 트랜잭션: a 사원을 조회. first_name은 a로 조회됨
• 1번 트랜잭션: commit
• 2번 트랜잭션: a 사원조회. first_name은 b로 조회됨 → 동일 트랜잭션 내에서 결과가 다름

2번 트랜잭션은 위에서 언급한 UNDO영역에서 데이터를 가져오게 됩니다. 결과적으로 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 과거데이터를 보게 됩니다.

하지만 이 경우에서도 1번 트랜잭션이 commit을 하고나서 2번 트랜잭션에서 다시 조회를 하게 되면 first_name이 b로 조회가 됩니다. 즉 같은 트랜잭션내에서도 select결과가 달라집니다.

REPEATABLE READ

위에도 언급했지만 InnoDB 스토리지 엔진은 트랜잭션이 롤백될 가능성을 염두에 두고 이전 레코드를 UNDO공간에 백업을 하고 실제 값을 변경을 합니다.

모든 InnoDB의 트랜잭션은 순차적으로 증가하는 고유한 트랜잭션 번호를 가집니다. UNDO영역에 백업된 레코드에는 변경을 발생시킨 트랜잭션의 번호를 포함합니다. 그리고 모든 select쿼리는 트랜잭션 번호가 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경한 것만 보기 때문에 반복적으로 읽어도 항상 같은 값을 반환합니다.

 

• 1번 트랜잭션: a 사원 조회. (first_name은 a)
• 2번 트랜잭션: a 사원의 first_name을 b로 변경
• 2번 트랜잭션: commit
• 1번 트랜잭션: a 사원 조회 (first_name은 a) → 동일 트랜잭션에서 항상 같은 결과값을 보장함.

하지만 이경우에도 select for update를 수행하게 되면 phantom read가 발생할 수 있습니다.

 

• 1번 트랜잭션: a 사원 조회. (first_name은 a)
• 2번 트랜잭션: a 사원의 first_name을 b로 변경
• 2번 트랜잭션: commit
• 1번 트랜잭션: a 사원 select for update로 조회 (first_name은 b)

다른 트랜잭션에서 레코드를 삭제해버렸습니다. 트랜잭션에서 select for update로 조회를 한다면 2번 트랜잭션 내에서 레코드가 반환되지 않습니다. 이렇게 다른 트랜잭션에서 수행한 변경 작업에 대해서 레코드가 보였다 안보였다 하는 것을 phantom read라고 합니다. select for udpate쿼리는 select하는 쿼리에 쓰기 잠금을 걸게 되는데 UNDO레코드에서는 잠글 수가 없습니다. 그렇기 때문에 항상 최신의 결과를 읽어오게 됩니다.

 

그렇기 때문에 주의해야할 포인트가 있습니다. 바로 아래에서 해당내용을 좀 더 살펴보죠.

InnoDB의 Locking과 Non-Locking

InnoDB를 사용을 할 때는 위에서 언급한 MVCC에서 Non Locking READ로 사용이 됩니다. 말 그대로 lock을 걸지 않는 것인데요. 읽으려는 행이 실제로 lock이 걸려있어도 읽을 때는 undo영역에서 읽기 때문에 lock이 걸리든 말든 상관없이 이전 버전의 데이터를 읽을 수 있는 것입니다.

하지만 REPEATABLE READ나 READ COMMITTED의 경우 쿼리에 의해서 위와 같이 Locking read가 발생할 수 있습니다. (select for update 같은)

그렇기 때문에 이론적으로 Phantom Read가 발생할 수 있습니다. 이런 참조모델의 혼재를 막으려면 레벨을 SERIALIZABLE로 설정을 해야하는데요. 그렇게 되면 조회시 공유 lock이 걸리게 되고 변경시 베타 lock이 걸리게 됩니다. 그럼 이 레벨에서는 어떻게 Phantom Read를 막는 걸까요?

Next Key Lock

위에서 언급한 Locking Read에서 Phantom을 막으려면 Next Key Lock이라는 구조를 사용합니다. Record 락과 Gap 락(논리적 공간, 실제로 물리적으로 공간이 있다는 것은 아닙니다)을 조합한 것인데요. 논리적 공간에 락을 걸게된다는 말은 새로운 행이 삽입 될 수 없음을 의미합니다.

예를 들어,

select * from USER where id > 10 for update;

id가 10 이후에것들의 레코드에 레코드락이 걸리고 10 바로 위에 있는 row사이에 갭락이 하나 걸리고 10 이상의 row 사이사이에 갭락이 걸립니다. 이렇게 두 락의 조합으로 걸리는것이 넥스트 키 락입니다.

 

 

참조) Real MySQL, MySQL 5.7 완벽분석