[MySQL] 트랜잭션과 잠금

Real MySQL 8.0 1권

Real MySQL 8.0 읽으면서 배웠던 부분들을 정리한 글입니다.

 

트랜잭션

---

트랜잭션이란 데이터베이스에서 더 이상 쪼갤 수 없는 작업의 최소 단위이다. 이 작업 안의 연산들은 모두 반영되거나(commit) 모두 반영되지 않아야 한다(rollback).

 

 

 

MySQL의 격리 수준

---

여러 트랜잭션이 동시에 처리되고 있을때, 트랜잭션 간의 데이터의 조회, 변경에 대한 고립 정도를 나타내는 것을 격리 수준이라고 한다.

 

 

READ UNCOMMITTED

각 트랜잭션이 commit, rollback 여부와 관계 없이 다른 트랜잭션의 값을 읽을 수 있다. 이름그대로 커밋되지 않은 데이터를 읽을 수 있는 격리 수준을 뜻한다. 이로 인해 Dirty Read 현상이 일어나게 된다.

 

https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation

 

Dirty Read

완료되지 않은 트랜잭션의 데이터도 볼 수 있는 현상. 조회한 데이터가 롤백이 될 경우 사용자는 잘못된 데이터를 읽을 수 있다. 

위의 그림과 같이 Transaction 1이 커밋되지 않았지만 Transaction 2에서 커밋되지 않은 데이터를 읽을 수 있다.

 

 

READ COMMITTED

Read Uncommitted의 Dirty Read를 방지할 수 있는 격리 수준으로, 커밋된 트랜잭션의 값만 읽을 수 있다. 오라클 DBMS의 디폴트 격리 수준으로, 온라인 서비스에서 가장 많이 선택된다.

 

https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation

 

Non-Repeatable Read

트랜잭션 중간에 다른 트랜잭션이 커밋될 경우 조회한 데이터의 값이 달라지는 현상. 

Transaction 2에서 첫 SELECT 절에선 222번의 데이터를 BUSAN으로 읽었지만, Transaction 1이 커밋된 이후엔 JEJU로 읽게 된다.

 

 

REPEATABLE READ

InnoDB 스토리지 엔진에서 디폴트로 사용되는 격리 수준. MVCC를 통해 각 트랜잭션마다 ID를 부여하여 현재 실행되는 트랜잭션보다 작은 ID를 가지는 트랜잭션에서 커밋한 데이터만 읽게된다. 이러한 데이터를 저장해두는 곳을 언두 영역이라고 한다. 

 

 

https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation

Phantom Read

트랜잭션 중간에 다른 트랜잭션이 INSERT 한 데이터가 조회되는 현상

InnoDB는 넥스트 키 락을 사용하여 Phantom Read를 방지하고 있다. 하지만 모든 쿼리에서 방지할 수 있는 것은 아니다. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE 쿼리의 경우 SELECT 쿼리이지만 잠금을 걸어야 한다. 하지만 언두 영역은 잠금을 걸 수 없으므로 언두 영역이 아닌 실제 레코드의 값을 SELECT 한 후 잠금을 걸게 되는 것이다. 따라서 해당 쿼리에선 InnoDB의 경우도 Phantom Read가 발생할 수 있다.

 

 

SERIALIZABLE

Dirty Read, Non-Repeatable Read, Phantom Read가 일어나지 않는 가장 엄격한 격리 수준이며 동시 처리성능이 가장 낮다.

 

 

격리 수준에 따른 발생하는 문제점

	Uncommitted Read	Committed Read	Repeatable Read	Serializable
Dirty Read	O	X	X	X
Non-repeatable Read	O	O	X	X
Phantom Read	O	O	O	X

 

 

 

MySQL 엔진의 잠금

---

MySQL의 잠금은 MySQL 엔진  레벨과 스토리지 엔진 레벨로 나눌 수 있다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간의 영향을 미치지 않는다.

 

 

글로벌 락

MySQL 서버 전체에 영향을 미치며 MySQL에서 제공하는 잠금 가운데 가장 범위가 크다. 세션에서 글로벌 락을 획득하면 다른 세션에선 SELECT를 제외한 모든 쿼리가 글로벌 락이 해제될 때까지 대기 상태로 남게 된다.

FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있다. mysqldump를 사용하여 백업을 수행한다면 MySQL 서버에서 어떤 잠금을 걸게 되는지 확인 후 사용하는 것이 좋다.

 

 

테이블 락

개별 테이블 단위로 설정되는 잠금이며, 명시적 혹은 묵시적으로 특정 테이블의 락을 획득할 수 있다.

명시적으로는 LOCK TABLES table_name [ READ | WRITE ]를 통해 테이블의 락을 획득할 수 있다. 묵시적으로는 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리(DML)를 실행할 경우 설정된다. InnoDB에선 DML 경우 레코드 잠금을 사용하고, DDL의 경우 테이블 락이 설정된다.

 

네임드 락

네임드 락은 GET_LOCK() 함수를 이용하여 임의의 문자열에 대해 잠금을 설정한다.

 

 

메타데이터 락

데이터베이스 객체( e.g. 테이블, 뷰 )의 이름이나 구조를 변경하는 경우 획득하는 잠금. 명시적 획득, 해제가 아닌 메타데이터를 변경하는 경우 자동으로 획득된다.

RENAME TABLE tab_a TO tab_b, tab_c TO tab_a

위 쿼리와 같이 테이블의 이름을 변경하는 경우 원본 이름과 변경 이름 모두 잠금을 설정한다. 위 쿼리를 두 개의 쿼리로 나누어 실행시킬 경우, 아주 짧은 시간동안 Table not found 'tab_a' 오류를 발생시킬 수 있다.

 

 

 

InnoDB 스토리지 엔진 잠금

---

InnoDB는 MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. 이를 통해 뛰어난 동시성 처리를 제공한다.

InnoDB 스토리지 엔진의 잠금 정보는 information_schema의 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAIT 테이블을 조인하여 확인할 수 있다.

 

 

레코드 락 (Record lock, Record only lock)

레코드 자체만을 잠그는 것을 레코드 락이라고 한다. InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스를 통해 레코드를 잠근다.

 

갭 락 (Gap lock)

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격 만을 잠그는 것을 의미한다. 이는 레코드 간격에 새로운 레코드가 생성되는 것을 제어한다. 

 

넥스트 키 락 (Next key lock)

넥스트 키 락은 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 의미한다. 해당되는 레코드 뿐만 아니라 인접된 레코드를 잠금으로써 Phantom Rows 현상을 막을 수 있다.

 

 

자동 증가 락(Autoincrement lock)

자동 증가하는 숫자를 추출하기 위해 제공하는 AUTO_INCREMENT 컬럼 속성으로 인해 제공되는 테이블 수준의 잠금이다. INSERT, REPLACE와 같이 새로운 레코드를 저장하는 쿼리에서 사용되며 명시적으로 획득할 수 없다.

 

 

innodb_autoinc_lock_mode

• 0
  • MySQL 5.0과 동일한 잠금 방식. 모든 INSERT 문장은 자동 증가 락을 사용한다.
• 1: 연속 모드
  • 여러 건의 INSERT 레코드의 개수를 정확히 예측할 수 있을 때는 자동 증가 락이 아닌 래치(뮤텍스)를 이용하여 처리 한다. 한 번에 여러 개의 값을 할당받아 남게 되면 누락된 값이 발생할 수 있다. 이 설정을 연속 모드라고 한다.
• 2: 인터리빙 모드
  • 모든 쿼리에 자동 증가 락이 아닌 래치를 사용한다. 이로 인해 동시 처리 성능은 높아지지만 하나의 INSERT 문장 이더라도 연속된 자동 증가 값을 보장하지 않는다.

 

 

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다.

UPDATE employees SET hire_date=NOW() WHERE first_name='John' AND last_name='Smith'

위 쿼리에서 first_name 은 인덱스가 설정되어 있고 last_name은 인덱스가 설정되어 있지 않은 경우, first_name=’John’에 해당 하는 컬럼들에 락을 걸고 스캔하게 된다. 따라서 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하게 되므로 인덱스 설계에 유의하도록 하자.

 

 

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

MySQL 8.0 버전부터는 performance_schema의 data_locks와 data_lock_waits 테이블을 통해 잠금과 잠금 대기 순서를 확인할 수 있도록 변경되었다.