📔설명
트랜잭션, MySQL 엔진의 잠금, InnoDB 스토리지 엔진의 잠금, MySQL의 격리 수준을 알아보자
🥂트랜잭션
트랜잭션 : 작업의 완전성 보장
=> 모두 완벽하게 처리 or 처리 못할 경우 원 상태로 복구
MyISAM : INSERT 문장이 실행되면서 차례대로 저장하다가 중복 키 오류등이 발생하는 경우 이미 INSERT된 데이터는 두고 종료
=> 부분 업데이트
InnoDB : 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만듦
🥃MySQL 엔진의 잠금
MySQL 엔진 레벨 잠금 : 스토리지 엔진을 제외한 나머지 부분
=> 모든 스토리지 엔진에 영향 미침
=> 테이블 데이터 동기화를 위한 테이블 락
=> 테이블의 구조를 잠그는 메타데이터 락
=> 사용자의 필요에 맞게 사용할 수 있는 네임드 락
1. 글로벌 락
글로벌 락(Global Lock) : 잠금 범위가 가장 큼
=> flush tables with read lock 명령으로 획득
=> 다른 세션에서 select 제외한 대부분의 ddl 문장이나 dml 문장 실행시 대기
=> MySQL 서버 전체에 영향
=> 테이블이나 데이터베이스가 다르더라도 동일하게 영향
=> MyISAM이나 MEMORY 테이블에 mysqldump로 백업시
InnoDB 엔진은 트랜잭션 지원하므로 일관된 데이터 상태를 위해 모든 데이터 변경 작업을 멈출 필요는 없음
백업 락
XtraBackup이나 Enterprise Backup 툴 실행 중 스키마 변경시 백업 실패가 되므로, 백업 실패를 막기 위해 DDL 명령 실행되면 복제 일시 중지하는 역할
2. 테이블 락
테이블 락(Table Lock) : 개별 테이블 단위로 설정되는 잠금
=> lock tables table_name [Read|Write] 명령으로 획득
=> MyISAM, InnoDB 동일 설정
=> Unlock Tables 명령으로 잠금 해제
3. 네임드 락
네임드 락(Named Lock) : 임의의 문자열에 대해 잠금 설정
=> GET_LOCK() 함수 이용
=> 단순히 사용자가 지정한 문자열에 대해 획득하고 반납
4. 메타데이터 락
메타데이터 락(Metadata Lock) : 데이터베이스 객체의 이름이나 구조를 변경하는 경우 획득
🧊InnoDB 스토리지 엔진 잠금
InnoDB 스토리지 엔진은 스토리지 엔진 내부에서 레코드 기반의 잠금 방식 탑재
=> 동시성 처리 뛰어남
1. InnoDB 스토리지 엔진의 잠금
갭(GAP) 락 : 레코드와 레코드 사이의 간격을 잠그는 것
락 에스컬레이션 발생 X
=> 레코드 락이 페이지 락 또는 테이블 락으로 업그레이드 되는 경우 없음
레코드 락
레코드 락(Record Lock) : 레코드 자체만을 잠그는 것
=> InnoDB는 인덱스의 레코드를 잠금
=> 프라이머리 키 또는 유니크 인덱스 변경 작업
갭 락
갭 락(Gap Lock) : 레코드와 바로 인접한 레코드 사이 간격만 잠금
=> 레코드와 레코드 사이 새로운 레코드가 생성되는 것을 제어
넥스트 키 락
넥스트 키 락(Next Key Lock) : 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금
=> 대부분 보조 인덱스를 이용한 변경 작업
=> 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주목적
자동 증가 락
Auto_increment Lock : Auto_increment 칼럼이 사용된 테이블에 동시에 여러 레코드가 insert되는 경우, 중복되지 않고 순서대로 증가하는 일련번호 값을 위한 테이블 수준 잠금
2. 인덱스와 잠금
변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락 걸어야 함
-- ix_firstname 인덱스만 존재
update employees
set hire_date=NOW()
where first_name='Georgi'
and last_name='Klassen';위 문장이 실행되면 1건의 레코드가 업데이트 될 것이다.
1건의 업데이트를 위해 253건의 레코드가 모두 잠긴다.
만약 인덱스가 없다면, 30여만 건의 모든 인덱스를 잠금
3. 레코드 수준의 잠금 확인 및 해제
-- 어떤 잠금을 가지고 있는지
select * from performance_schema.data_locks \G
--17번 스레드 강제 종료
kill 17;🥤MySQL의 격리 수준
트랜잭션의 격리 수준(isolation level) : 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것
| DIRTY READ | NON-REPEATABLE READ | PHANTOM READ | |
|---|---|---|---|
| READ UNCOMMITTED | 발생 | 발생 | 발생 |
| READ COMMITTED | 없음 | 발생 | 발생 |
| REPEATABLE READ | 없음 | 없음 | 발생 (Inno DB는 없음) |
| SERIALIZABLE | 없음 | 없음 | 없음 |
1. READ UNCOMMITTED
READ UNCOMMITTED : 각 트랜잭션에서의 변경 내용이 commit이나 rollback 여부에 상관없이 다른 트랜잭션에서 보임
Dirty Read : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
2. READ COMMITTED
READ COMMITTED : 어떤 트랜잭션에서 데이터를 변경했더라도 commit이 완료된 데이터만 다른 트랜잭션에서 조회 가능
=> NON-REPEATABLE READ 발생
3. REPEATABLE READ
REPEATABLE READ : MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장
PHANTOM READ : 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상
4. SERIALIZABLE
SERIALIZABLE : 읽기 작업도 공유 잠금을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 됨
=> InnoDB는 갭 락과 넥스트 키 락 덕분에 repeatable read 격리 수준에서도 이미 phantom read 발생X
