MySQL(InnoDB)
MySQL은 관계형 데이터베이스의 데이터 관리 시스템(database management system, 이하 DBMS)입니다. 이 DBMS는 Oracle(회사)에서 제공하는 오픈소스입니다.
또한 MySQL, MariaDB는 엔진으로 InnoDB를 선택하고 있어 InnoDB라고도 불립니다.
Lock 확인 및 해제
아래 쿼리를 통해 현재 프로세스를 확인할 수 있다.
SHOW PROCESSLIST;cs(위 쿼리는 100개까지 list를 노출하며, 모두 노출 하려면 SHOW FULL PROCESSLIST;로 하면 된다.)
State컬럼을 보면 Waiting for table metadata lock 으로 인해 해당 쿼리가 진행되지 않음을 확인 할 수 있다.
그러면 이 프로세스를 해제(kill)해보자.
KILL 255;cs위 쿼리를 통해 lock을 해제할 수 있다.
원인
해당 테이블이 DDL을 통해 open상태일 경우.
선행 트랜잭션이 종료가 되지 않았을 경우 종료를 위한 DDL 구문 실행 대기.
보통 metadata lock info라는 별도의 플로그인을 통해 확인 할 수 있으나 불가능한 경우도 있다.
(트랜잭션 중 autocommit이 false일 때, commit을 수행하지 않고 DDL을 실행할 경우)
MySQL의 Lock과 트랜잭션
Lock과 트랜잭션 모델은 엔진인 InnoDB에 종속적입니다.
따라서 MariaDB 또한 해당 매커니즘을 따른다고 봐주시면 될 것 같습니다.
트랜잭션
논리적인 작업 단위. 전부 처리되거나 처리되지 않는 (commit/rollback) 원자성을 보장하기 위한 기능이다.
락
서로 다른 작업에서 같은 자원을 동시에 필요로 할 때 자원 경쟁이 일어나는데,
이때 순서대로 사용되는 동시성을 보장하기 위한 기능이다.
MySQL에서 사용되는 락(LocK)은 크게 MySQL 엔진 레벨의 락과 스토리지 엔진 레벨의 락으로 나눠볼 수 있다.
동시성(Concurrency) : 여러 사용자가 데이터 무결성을 손실할 위험 없이 동시에 동일한 테이블 또는 뷰의 데이터에 액세스하고 변경할 수 있는 기능
락의 적용 요소에 따른 분류
- Shared lock 공유락 (S)
- Exclusive lock 배타락 (X)
-Intent lock 내재락
락이 적용되는 상황에따른 분류
- Record Lock 레코드락
- Gap Lock (= range lock) 갭락
- Next-Key Lock
MySQL의 Lock
MySQL은 Lock 레벨로는 행(row) 단위 Lock을 기본으로 사용하며
Lock의 종류로는 공유(Shared) Lock과 배타(Exclusive) Lock을 사용합니다.
- 락의 적용 요소에 따른 분류
공유 락(Shared Lock) - S
공유 락은 데이터를 변경하지 않는 읽기 명령에 대해 주어지는 락으로
Read Lock이라고도 불리며 Shared의 앞 글자를 따서 주로 S로 표기합니다.
여러 사용자가 동시에 데이터를 읽어도 데이터의 일관성에는 아무런 영향을 주지 않기 때문에, 공유 락끼리는 동시에 접근이 가능합니다.
베타 락(Exclusive Lock) - X
베타 락은 데이터에 변경을 가하는 쓰기 명령들에 대해 주어지는 락으로 Write Lock으로도 불리며, X로 표기합니다. 베타 락은 이름처럼 다른 세션이 해당 자원에 접근(ex, SELECT, INSERT..) 하는 것을 막습니다.
이러한 점에서 베타 락은 멀티 쓰레딩 환경에서, 임계 영역을 안전하게 관리하기 위해 활용되는 뮤텍스와 유사하다고 볼 수 있습니다. 베타 락은 트랜잭션 동안 유지됩니다.
업데이트 락(Update Lock)
업데이트 락은 데이터를 수정하기 위해 베타 락(X)을 걸기 전, 데드 락을 방지하기 위해 사용되는 락입니다. 일반적으로 업데이트 락은 UPDATE 쿼리의 필터(WHERE)가 실행되는 과정에서 적용됩니다.
서로 다른 트랜잭션에서 동일한 자원에 대해 읽기 쿼리 이후, 업데이트 쿼리를 적용하는 경우 컨버젼 데드락이 발생하는데, 이를 막기 위해 일부 SELECT 퀴리에서도 업데이트 락을 적용(WITH(UPDLOCK))하기도 합니다.
내재 락(Intent Lock)
내재 락은 앞서 소개한 락들과 사뭇 다른 기능을 합니다. 내재 락은 사용자가 요청한 범위에 대한 락(ex, 테이블 락)을 걸 수 있는지 여부를 빠르게 파악하기 위해 사용되는 락입니다.
내재 락은 공유 락과 베타 락 앞에 I 기호를 붙인 IS, IX, SIX 등이 있습니다.
사용자 A가 테이블의 하나의 로우(row)에 대해 베타 락(X)을 건 경우, 사용자 B가 테이블 전체에 대한 락을 걸기 위해서는(ex, 스키마 변경) 사용자 A의 트랜잭션이 끝날 때까지 기다려야 합니다. 그러나, 사용자 B가 테이블에 락(DDL Lock)을 걸 수 있는지 여부를 파악하기 위해 테이블에 존재하는 모든 로우와 관련된 락을 찾아보는 것은 매우 비효율적인 작업입니다.
따라서, 데이터베이스는 사용자 A가 로우에 베타 락(X)을 거는 시점에, 해당 로우의 상위 객체들(ex, 페이지, 테이블)에 대한 내재 락(IX)을 걸어, 다른 사용자가 더 큰 범위의 자원들에 대해 락을 걸 수 있는지 여부를 빠르게 파악할 수 있도록 돕습니다.
- 로우레벨 : 변경하려는 로우(실제로는 RID)에만 락 설정.
- 페이지 레벨 : 변경하려는 로우가 담긴 데이터페이지(또는 인덱스 페이지)에 락을 설정하는 것.
- 익스텐트 레벨 : 익스텐트 전체가 잠김. SQL 서버의 경우 하나의 익스텐트가 8개페이지로 구성되므로 8개페이지에 속한 모든 로우가 잠긴 것과 같은 효과가 나타남.
- 페이지 레벨 : 테이블 전체 그리고 관련 인덱스까지 모두 잠김.
- 데이터베이스 레벨 : 데이터베이스 전체가 잠긴다. 보통 데이터베이스 복구하거나 스키마 변경할 때 일어남.
- 락이 적용되는 상황에 따른 분류
레코드 락(Record Lock)
Record Lock이란 row의 index를 기준으로 Lock을 거는 것을 말합니다.
아래와 같은 쿼리에 해당 Lock이 걸립니다. 이 쿼리는 c1을 가져옵니다. 일반적으로는 공유락으로 가져오지만 이 경우 레코드락으로 가져오게 됩니다.
레코드 락으로 가져오게되면
다른 트랜잭션에서 해당 index의 row에 insert, update, delete 구문 사용이 락이 걸려있는 동안은 불가능해집니다.
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;레코드 락은 index를 기준으로 lock을 건다고 말씀드렸습니다.
그럼 index를 설정하지 않으면 레코드 락은 돌아가지 않을까요? 아닙니다.
사실 우리가 index를 명시적으로 걸지않아도 MySQL(InnoDB)은 숨겨진 clustered index를 건다고합니다. 따라서 아무런 index를 걸지 않으셨다면 숨겨진 index를 통해 Lock이 걸릴 것입니다.
Gap Lock (= range lock)
실제 존재하는 인덱스 레코드에 락을 거는것이 아니고 범위를 지정하기 위해 인덱스 레코드 사이의 범위(gap)에 락을 거는 것을 의미한다.
negative infinity (최초 레코드 이전), positive infinity (마지막 레코드 이후) 를 가상의 인덱스 레코드로 생각해서 lock을 적용하는것이 가능하다.
Gap lock을 통해서 같은 SELECT 쿼리를 두번 실행했을때 다른 트랜잭션에서 데이터가 수정되었더라도 같은 결과가 리턴되는 것을 보장할 수 있다. (Phantom read 방지)
예) SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE
위 쿼리를 실행하면 에서 c1=10~20 사이에 X락이 걸리기때문에 다른 트랜잭션에서 c1=15를 가지는 데이터를 INSERT하려면 대기 상태로 빠진다.
“READ COMMITTED” isolation level을 사용하면 트랜잭션 내부에서 “동일 SELECT 실행, 동일 결과” 보장을 하지 않아도 되기때문에 gap lock 비활성화 해도된다.
이경우 DB의 쿼리 처리량을 늘릴 수 있지만, 그만큼 대기시간이 줄어들고 동시 write가 증가하기때문에 데드락에 걸릴 확률이 높아진다.
컬럼에 대한 WHERE 절로 많은 row들을 제외시키고 하나의 레코드만 추출되었을때, Record lock과 Gap lock 어느것이 사용될까?
컬럼에 unique index가 걸려있는 경우에는 gap lock이 필요없다. (record lock이 사용됨)
컬럼에 index가 걸려있지 않거나, index가 걸려있어도 unique 하지 않다면 gap lock이 필요하다.
index가 걸려있다면 row를 찾기위해 스캔했던 index range에 대해서 gap lock 적용
index가 걸려있지 않다면 결국 테이블 전체를 스캔해야 되기때문에 모든 row에대해 lock이 걸림.
Next-Key Lock
범위를 지정한 쿼리를 실행하게되면 실제로는 위에서 각각 설명했던 record lock (찾아진 인덱스 레코드에 대해)과 gap lock (해당하는 인덱스 레코드 사이사이) 이 복합적으로 사용된다.
다음 그림을 통해 Next-Key lock이 어떤 것인지를 한눈에 살펴보자.
Insert Intention Lock
INSERT 구문이 실행될 때 InnoDB 엔진 내부적으로 implicit하게 획득하는 특수한 형태의 gap lock. (gap lock은 일반적으로 explicit하게 locking read를 위한 SELECT 구문이 실행될때 발생하지만 이 lock은 INSERT 시점에 implicit하게 자동으로 발생)
여러개의 트랜잭션들이 gap 안의 다른 위치에 INSERT를 동시 수행할 때 기다릴 필요가 없도록 하는것이 목적 (Insert intention lock들 간에는 충돌이 일어나지 않는다)
INSERT될 row에 대해서 exclusive lock을 걸기 전에 먼저 insert intention lock을 건다.
예) pk=3, pk=6의 레코드가 존재하는 테이블이 존재
A 트랜잭션에서 pk=5에 INSERT, B 트랜잭션에서 pk=4에 INSERT시도
만약 일반적인 gap lock 사용한다면:
A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 gap lock 걸림
B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 gap lock이 걸려있기때문에 A가 트랜잭션이 완전히 종료될 때 까지 기다려야 한다. 대기시간 존재!
Insert Intention Lock 사용시:
A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 insert intention lock 걸림
B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 insert intention lock이 걸려있더라도 pk가 겹치지 않기때문에 바로 진행 가능 대기시간 없음!
실제 InnoDB의 동작 방식
Lock Escalation
하나의 로우에 대해 락을 생성하면, 상위 객체들에 대한 내재 락들이 함께 생성됩니다. 그러나, 락은 많은 메모리 자원을 필요로 합니다. 따라서, 많은 데이터베이스들은 테이블 내의 일정 비율 이상의 로우에 대한 락을 생성할 경우, 모든 로우에 대해 락을 생성하는 대신, 더 상위 객체인 테이블에만 락을 걸도록하여 메모리 사용을 최적화하는 기능을 지원합니다. 이러한 데이터베이스의 기능을 Lock Escalation이라고 합니다.
Lock의 호환성과 Conversion Deadlock
내제 공유 락(IS)는 배타 락을 제외한 모든 락과 함께 실행이 가능하며, 베타 락은 이름 그대로 다른 락과 호환이 불가능하며, 다른 트랜잭션의 모든 락이 해제될 때까지 실행될 수 없습니다. 동일하게, 베타 락을 얻은 트랜잭션이 커밋/롤백 할 때까지 동일 범위에 대한 모든 락은 실행 권한을 얻을 수 없습니다.
운영 단계에서 종종 발생하는 컨버젼 데드락(Conversion Deadlock)은 다른 공유(S)락과 업데이트 락(U)이 동일한 자원에 접근하는 것을 허용하는 공유 락(S)의 성질에서 기인합니다. 데이터베이스는 업데이트(UPDATE ... SET ... WHERE...)를 실행할 때에, 먼저 해당 자원에 대한 공유 락(S)을 얻어온 후 이를 베타 락(X)으로 전환하게 됩니다. 이러한 업데이트 쿼리가 동시에 실행되어 두 트랜잭션이 모두 공유 락(S)을 얻은 후 이를 베타 락(X)으로 전환하려 할때, 상대 트랜잭션이 소유하고 있는 공유 락(S)으로 인해 모든 트랜잭션이 베타 락(X)을 얻지 못 해 업데이트에 실패하게 됩니다.
