사보정 프로젝트를 마무리하고 나서 다른 프로젝트에 투입되면서 프로젝트에서 사용하여 저번에 못다한 MYSQL에서 사용하는 LOCK에 대해서 알아보고 왜 MYSQL에서 Repeactable read 격리수준에서 Phantom Read 문제가 발생하지 않는 이유에 대해서도 알아보자.
lock
기존에 Oracle에서 발생할 수 있는 lock에 대해서 배웠으나 MySQL에서는 다르게 작용하는 부분이 있어서 작성하게 되었다.
MySQL에서 사용되는 락(LocK)은 크게 MySQL 엔진 레벨의 락과 스토리지 엔진 레벨의 락으로 나눠볼 수 있다.
mysql 엔진 레벨 락
글로벌 락
모든 테이블에 잠금을 거는 락이며
"FLUSH TABLES WITH READ LOCK" 명령으로 락을 걸 수 있다.
내용만 바라보면 원자성 제공을 위하다기 보다는 일시적으로 mysql 잠그려는 느낌이 강하다. 뒤에 binlog와 복제에 대해서 자세히 다룰 예정이지만 기존에 버전이 낮은 mysql에서 덤프를 뜰 때 글로벌 락을 걸어 최대한 원자성을 제공하려고 하였고
8.0버전 이후에는 향상된 백업을 위한 backup 락이 존재한다. 이는read-lock을 일시적으로 걸며write-lock에 대해서는 걸지 않고 , 일관적 snapshot을 제공한다.
테이블 락
각각 테이블에 테이블 단위로 락을 건다.
MySQL InnoDB 는 기본적으로 스토리지에서 레코드 락을 사용하므로 DML에서는 주로 사용되지 않지만, DDL 같이 테이블의 스키마에 변화를 줄 때는 사용된다.
일반적으로 테이블 락은
MyISAM이나MEMORY테이블에 데이터를 변경하는 쿼리를 실행하면 발생을 하게 되고 사용자가 데이터를 변경하면 MySQL 서버는 테이블에 잠금을 설정하고 데이터를 변경한 한 후 잠금을 해제 하는 형태로 사용 하며 innodb를 사용 하게 된다면 레코드를 변경하지 못하게 할 때 사용한다.
InnoDB 스토리지 엔진 테이블의 경우 대부분의 데이터 변경(DML) 은 레코드 기반의 잠금을 사용하고 테이블 레벨의 잠금은 스키마 변경을 막기 위해 사용 됩니다.
네임 락
테이블 또는 뷰등의 스키마 객체의 이름을 변경하는 경우(rename ...)에 자동으로 락을 걸며 이는 레디스 처럼 분산 락 처럼 작동한다.
metadate 락
데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경 하는 경우에 획득하는 잠금이다.
스토리지 엔진 레벨 락
기본적으로 MySQL은 Pessimistic locking을 사용한다.
비관적 락 : 트랜잭션에서 변경하려는 레코드에 대해 락을 획득하고 쿼리를 수행하는 방식
낙관적 락 : 트랜잭션에서 락 없이 일단 쿼리 수행을 하고 마칠 때 서로 다른 트랜잭션에서 충돌이 있었는지 확인하고 문제가 있으면 충돌이 난 트랜잭션을 롤백하는 방식
레코드 락
SELECT temp FROM tab_temp WHERE user=10 FOR UPDATE인덱스 레코드에 락을 건다. 따로 생성한 인덱스가 없는 테이블은 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 락을 건다.
기본키나 유니크 키에 의한 변경 작업은 갭 락 없이 딱 인덱스 레코드에만 락을 걸며 Oracle에서 레코드 잠금 방식이랑 유사하다고 생각하면 된다.
갭 락 (Gap lock)
인덱스 레코드와 인접한 앞/뒤 사이 공간에 락을 거는 것인데, 개념적인 용어로 단독으로는 사용되지 않고 넥스트 키 락에서 혼용되어 사용한다.
갭 락은 READ_COMMITED 이하에서는 대부분 발생하지 않고 REPEATABLE_READ 이상 격리 수준일 때에 주로 발생한다.
('대부분'라고 한 이유는 외래 키 검사나 중복 키 검사할 때는 READ_COMMITED 에서도 발생하기 때문이다.)
넥스트 키 락
레코드 락과 갭 락을 합쳐놓은 형태다. 인덱스 레코드도 잠그고 그 인덱스 레코드 앞, 뒤 갭도 잠근다.
select customer from tab_temp where customer_gubun 10 between 20 인 경우에 범위에 해당하는 값들에 대해서 락을 건다는 것이다. 이는 범위의 cadrinality 값이 높은 경우에는 일부분에 락이 걸리지만 대부분의 범위인 경우 자칫 잘못하면 해당 테이블에 lock이 걸리기 때문이다.
각 트랜잭션 격리 수준에 따른 락에 대한 예시는 이곳에 잘 설명되어 있으며
추가적으로repeatable-read인 경우에 Create table select ~ 문이나 CUD에서 서브쿼리에서 사용된 select에 락이 걸리는데 이는read-committed에서 작용되지 않는다.
Next Lock 에서 Phantom Read 가 발생하지 않는 이유
위의 사진에서 보이듯이 Transaction A에서 읽어드릴 때 테이블 내에 공간에 lock을 걸지 않아 발생하는 문제점이다. 하지만 Oracle의 Reapeatable read 인 경우네느 Next key lock 이 걸리게 되어 해당 문제점이 발생하지 않는다.
오토 인크리먼트 락
MySQL에서 자동 증가하는 숫자 값을 채번하기 위해 AUTO_INCREMENT라는 컬럼 속성을 정의할 때가 있다.
innodb_autoinc_lock_mode 는 총 3가지로 이루어져 있다.
• traditional(0) :
INSERT-like(모든 insert 범주에 들어가는 문장) 문장 실행시 테이블 레벨의 AUTO-INC 락을 사용하게 되며 해당 Insert 구문이 끝날때 까지 유지하게 된다.
• consecutive(1) :
Bulk insert 사용 시 테이블 수준 잠금 AUTO-INC을 사용하고 명령문이 끝날 때까지 이를 유지하게 된다. 여기서 Bulk Insert는 insert .. select문과 같은 것을 말한다.
• interleaved(2) :
이 잠금 모드에서는 INSERT-like 명령문이 테이블 수준 AUTO-INC 잠금을 사용 하지 않으며 그에 따라 여러 명령문이 동시에 실행될 수 있다. 그래서 이 모드가 가장 빠르고 확장 가능한 잠금 모드 이다.
mysql 5.7버전부터는 기본값으로 1을 사용하였으나 8.0부터 2를 기본적으로 사용하게 되어 별도의 잠금을 사용하지 않는다.
참고 : https://hoing.io/archives/1289
Auto-increment 를 사용하지않고 별도로 프로시저를 사용해서 시퀀스 넘버를 사용하는 부분도 있다.
이는 락을 피하기 위해 프로시저를 사용하는 줄 알았는데 내부 코드를 보니 transcation을 사용하고 있었다. 이는 락을 피하기 위해서가 아닌 프로시저가 작동할 때마다 meta 테이블에 시퀀스 내용을 저장함으로서 로깅을 편하기 위함과 배치사용시에 단건에 대해서 여러번 부르게 된다면 이는 성능상에 문제를 일으키니 미리 배치 chunk 수만큼 할당하고 Application단에서 할당하는 방식을 사용한다.
또 한 innodb_autoinc_lock_mode 값을 0이나 1값으로 설정한다고 해도 이는 순차적인 순번을 제공하지 않는다. 해당 기능은 UNIQUE 한 값을 제공하기 위함이 더 크기 때문이다. 따라서 만일 순번이 비는 것을 방지 하고 싶다면 프로시저를 사용하는 것도 생각해 볼 수 있다.
참고
추가적으로 8.0이상에서 부터는 auto-increment를 사용하게 된다면 서버를 다운하고 다시 뛰울 때 초기화하는 것을 방지하기 위하여 해당 값을 디스크에서 보관하고 있다.
트랜잭션 격리 수준
트랜잭션 격리 수준 과 전파수준 에 대해서 자세히 살펴 보았다.
ORACLE , MySQL 에서도 통용되는 트랜잭션 격리 수준이나 mysql 에서는 repeactable-read 를 일반적으로 사용한다. 따라서 위에서 언급했던 넥스트락으로 인해 phantom read가 발생하지 않는 것을 알 수 있다.
하지만 mysql에서 특정적으로 데드락이 발생하는데 자세한 설명 은 여기서 확인 가능하다.
정리하면 mysql에서 사용하는 gap lock은 동일한 lock에 대해서 서로 간섭하지 않아 락이 걸리지 않으나
insert Gap lock과는 충돌이 발생하여 데드락이 발생할 수 있다.
데드락을 막기 위한 방법
트랜잭션 격리 수준을 read-committed로 낮추고 binlog_format 형식을 row형식으로 변경해야 한다. 트랜잭션 수준은 그렇다 하더라고 binlog를 row형식을 취해야 하는 이유는 후에 binlog 종류에 대해 알아보고 복제 환경에 대해서 알아볼 때 자세히 다루겠지만 read-committed, binlog = statement 인 경우에 non-repeactable-read가 발생하여 원자성이 안지켜지는 경우가 있어 master-slave구조를 이룰 수 없기 때문이다. 따라서 mysql에서 binlog를 변경해주어야 한다.
무조건적으로 데드락을 피하기 위해서 격리수준을 낮추게 된다면 서로 다른 트랜잭션에서 INSERT한 레코드들이 섞여서 저장되면서 의도하지 않은 버그를 만들어낼 수도 있다.
해당 프로젝트에 맞는 격리수준을 심도있게 고민해봐야 한다.
MVCC
저번 시간에 배운 오라클의 MVCC는 이곳 에서 참고하도록 하자. MYSQL에서도 다른 RDBMS 처럼 MVCC를 사용하는데 공통점과 차이점에 대해서 알아보자.
먼저 공통점으로는 데이터베이스에서의 동시성을 제공한다는 점이다. 또 한 MVCC를 사용한다 하더라도 락이 걸릴 수 있다는 점 또 한 공통점이다.
오라클에서는 두 개 이사의 트랜잭션에서 동일한 데이터를 동시에 수정하는 경우에, 각 트랜잭션은 수정중인 데이터에 대한 락을 걸어 다른 트랜잭션의 수정 작업이 해당 데이터에 영향을 미치지 못하다록 막는다.
Mysql에서도 select 문에 where 절에 해당하는 인덱스를 사용하는 경우 해당 레코드의 이전 버전이나 현재 버전을 탐색하게 되는데 이 때 해당 레코드의 이전 버전이 다른 트랜잭션에 의해 사용되는 경우 해당 레코드에 대해서 락이 걸릴 수 있다.
이처럼 MVCC를 사용한다고 해서 무조건이 Lock이 안걸리는 것이 아닌 읽기 수준의 일관성을 제공하고, 최대한 Lock이 걸리는 상황을 방지하는 구나 정도로 이해했다.
차이점으로는 저장방식이 있다. Oracle에서는 undo로그를 사용하여 데이터를 저장하는 반면에 MySQL은 InnoDB 스토리지 엔진에서 각각 row의 데이터에 대한 버전 정보를 저장하고 이전 버전의 데이터는 변경되지 않고 유지되는 방식이다.
처음에는 binlog를 통해서 트랜잭션 원자성을 제공하는 줄 알았으나 binlog는 서버가 다운되었을 때 복구하기 위한 log이고 트랜잭션 로그라고 별도로 존재하여 트랜잭션 원자성을 제공한다.
정리
MySQL과 Oracle은 같은 RDBMS임에도 불구하고 서로 다른 점이 존재하며 MYSQL에서는 크게 mysql 락, innoDB 락이 존재한다.
해당 건에는 Mysql에서는 table flush(캐시 및 dirty buffer를 지우는 행위) , mysqldump 복제, 각 트랜잭션 격리 레벨에 따른 레코드, 넥스트 락 이 있으며 트랜잭션 레벨에 따른 binlog 설정을 해야 master-slave 구조를 진행 할 수 있다.
