MySql의 InnoDB 스토리지 엔진은 MyISAM, MEMORY 스토리지 엔진과 달리 트랜잭션을 지원해준다.
트랜잭션은 작업의 완전성을 보장해 주는 것. 데이터의 정합성을 보장하기 위한 기능이다.
즉, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 오류가 발생해서 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능이다.
트랜잭션은 **"모두 완벽하게 처리(Commit)되거나, 원 상태로 복구(Rollback)되어 일부만 적용되지 않음"**
[ 초기 데이터 상태 ]
│
▼
===================================================
[ 트랜잭션 시작 (Logical Work Set) ]
│
├─▶ (작업 1) 정상 처리
│
├─▶ (작업 2) 정상 처리
│
▼
[오류 발생 여부 확인]
│
├─▶ (오류 없음!) ───▶ [ COMMIT ] ───▶ [ 변경된 데이터 영구 반영 ]
│ (작업 완전성 보장)
│
└─▶ (오류 발생!) ───▶ [ ROLLBACK ] ─┐
│
============================================│======
▼
[ 초기 데이터 상태로 복구 ]
(일부만 적용되는 현상 방지)
잠금은 동시성을 제어하기 위한 기능.
잠금은 **"여러 커넥션이 동시에 자원을 요청할 때, 순서대로 한 번에 하나씩만 접근"**하도록 제어하는 기능이다.
[ 커넥션 A ] [ 커넥션 B ] [ 커넥션 C ]
│ │ │
(요청 1) (요청 2) (요청 3)
│ │ │
▼ ▼ ▼
=========================================================
[ 동시성 제어 (Concurrency Control) ]
=========================================================
│ │ │
[잠금 획득 🔑] [잠금 대기 ⏳] [잠금 대기 ⏳]
│ │ │
▼ │ │
[ 동일한 자원 ] ◀──────────┘ │
(데이터 변경 중) │
│ │
[작업 완료] │
│ │
[잠금 해제 🔓] ─────────────────────────────────┘
│
▼
[잠금 획득 🔑] (커넥션 B가 순서대로 접근)
│
▼
[ 동일한 자원 ]5.2 MySQL 엔진의 잠금
MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다.
MySQL 엔진 레벨의 잠금은 주로 테이블 구조를 건들일 때 발생하는듯?
5.2.2 테이블락
개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
명시적인 테이블 락은 특별한 상황이 아니면 거의 사용할 필요가 없다. 작업에 상당한 영향을 끼치기 때문임.
묵시적인 테이블 락은 MyISAM 이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생함.
MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.
즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득됐다가 쿼리가 완료된 후 자동 해제된다.
InnoDB 테이블의 경우에는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 묵시적인 테이블 락이 설정되지 않음.
더 정확히는 InnoDB 테이블에도 테이블 락이 설정은 되지만 DML(데이터 변경) 쿼리에서는 무시되고 DDL(스키마 변경)의 경우에만 영향을 미친다.
결론 : MyISAM, MEMORY 테이블은 데이터를 변경할 때 테이블락이 걸리고 InnoDB는 레코드 락이 걸린다.
5.3 InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있음.
레코드 기반의 잠금 방식 덕분에 MyISAM 보다는 훨씬 뛰어난 동시성 처리를 제공할 수 있다.
5.3.1.1 레코드 락
레코드 자체만을 잠그는 것을 레코드 락이라고 한다.
한 가지 중요한 점은 InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다는 점이다.
인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
레코드 자체를 잠그느냐 인덱스를 잠그느냐는 상당히 크고 중요한 차이를 만들어 낸다.
5.3.2 인덱스와 잠금
InnoDB의 잠금과 인덱스는 상당히 중요한 연관 관계가 있다.
InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리가 된다.
즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.
예제 테이블에서는 first_name 컬럼에 인덱스가 걸려져있음.
employees 테이블에는 first_name이 Georgi 인 인원은 253명 존재
employees 테이블에는 first_name이 Georgi, last_name이 Klassen 인 인원은 1명 존재
해당 사원의 hire_date를 오늘로 변경하는 쿼리를 실행해보자.
UPDATE 문장이 실행이 되면 1건의 레코드가 업데이트가 될 것임. 이 1건의 업데이트를 위해 몇 개의 레코드에 락을 걸어야 할까?
이 UPDATE 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi' 이며 last_name 컬럼에는 인덱스가 없기 때문에 first_name='Georgi' 인 레코드 253건의 레코드가 모두 잠긴다.
밑의 그림은 예제의 UPDATE 문장이 어떻게 변경 대상 레코드를 검색하고, 실제 변경이 수행되는지를 보여준다.
(first_name 컬럼에만 인덱스를 걸었지만 emp_no 컬럼도 같이 존재하는 이유는 모든 보조 인덱스(Secondary Index)는 자신이 가진 컬럼 데이터 끝에 항상 '기본키(Primary Key)'를 달고 다니기 때문이다.)
이 예제에서는 몇 건 안되는 레코드만 잠그지만 UPDATE 문장을 위해 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어져서 한 세션에서 UPDATE 작업을 하는 중에는 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다.
이 테이블에 만약 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하는데, 이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠그게 된다.
이것이 MySQL의 방식이며, MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이다.
5.4 MySQL의 격리 수준
트랜잭션의 격리 수준(isolation level) 이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것임.
격리 수준은 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE 로 4가지로 나뉜다.
DIRTY READ라고도 하는 READ UNCOMMITTED는 일반적인 DB에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 DB에서는 거의 사용되지 않는다.
4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이다.
격리 수준이 높아질수록 처리 성능도 떨어질 것으로 생각하는 사용자가 많은데, SERIALIZABLE 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
일반적인 온라인 서비스 용도의 DB에서는 READ COMMITTED(오라클)와 REPEATABLE READ(MySQL) 중 하나를 사용한다.
5.4.1 READ UNCOMMITTED
READ UNCOMMITTED 격리 수준에서는 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라고 한다.
5.4.1 READ COMMITTED
READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리수준. 이 레벨에서는 더티 리드(Dirty Read)와 같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문.
READ UNCOMMITTED 격리 수준에서도 NON REPEATABLE READ (REPEATABLE READ가 불가능하다) 라는 부정합의 문제가 있다.
사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋난다.
5.4.3 REPEATABLE READ
위 예제를 살펴보면 사용자 A의 트랜잭션 번호는 12고, 사용자 B의 트랜잭션 번호는 10이다. 이때 사용자 A가 레코드를 변경했더라도 사용자 B는 자신보다 작은 트랜잭션 번호(트랜잭션 10보다 낮은 트랜잭션)의 변경사항만 보게 된다.
REPEATABLE-READ 격리 수준에서도 PHANTOM READ라는 부정합 현상이 발생할 수 있다.
REPEATABLE -READ 격리 수준에서 배웠던 것처럼 사용자 B가 실행한 두 번의 SELECT 쿼리 결과는 똑같아야 한다. 하지만 사용자 B가 실행한 두 번의 SELECT .. FOR UPDATE 쿼리는 결과가 다르게 나온다.
SELECT .. FOR UPDATE 쿼리의 경우 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 따라서 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 현상이 발생하기 때문이다.
이렇게 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ라고 부른다.
InnoDB 스토리지 엔진에서는 SELECT ... FOR UPDATE나 SELECT ... FOR SHARE 등 잠금을 동반한 SELECT 쿼리에서 PHANTOM READ 현상이 발생할 수 있지만, 이는 예외적인 상항으로 볼 수 있다. 일반적인 상황이라면 InnoDB 스토리지 엔진의 REPEATABLE-READ 격리 수준에서는 갭 락과 넥스트 키 락 덕분에 PHANTOM READ가 발생하지 않는다.
5.4.4 SERIALIZABLE
가장 단순하면서도 가장 엄격한 격리 수준인 만큼 동시 처리 성능이 다른 트랜잭션 격리 수준보다 떨어진다.
읽기 작업에도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그 레코드를 변경할 수 없다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서 절대 접근할 수 없는 것이다.
InnoDB에서는 REPEATABLE-READ 격리 수준에서도 PHANTOM READ가 발생하지 않으므로 굳이 SERIALIZABLE 격리 수준을 사용할 이유가 없다.