DB Lock 심화 (어려운 부분이 너무 많아....작성중....)

1) DB Lock 심화

(1) 개요

• RDBMS 중 하나인 MySQL은 MariaDB와 같이 스토리지 엔진으로 InnoDB를 사용하고 있어 InnoDB 라고도 불린다.
• 데이터 무결성을 유지하는 데 있어 핵심적인 트랜잭션 모델 (Transaction Model), 격리성 수준 (Isolation Level), DB Lock은 스토리지 엔진인 InnoDB에 종속되기 때문에 MySQL과 MariaDB는 InnoDB의 트랜잭션 모델, 격리성 수준, DB Lock 메커니즘을 따르게 된다.
• 또한, 여기서 깊게 들어가보자면 MySQL에서 사용하는 DB Lock은 크게 MySQL 엔진에서 제공하는 DB Lock이 있고, 스토리지 엔진에서 제공하는 DB Lock이 있다.
• MySQL 엔진에서 제공하는 DB Lock과 InnoDB 스토리지 엔진에서 제공하는 DB Lock은 아래와 같다.
• MySQL 엔진 레벨의 Locks

① Global Locks

② Table Locks

③ Named Locks

④ Metadata Locks

• 스토리지 엔진 레벨의 Locks

① Shared and Exclusive Locks

② Intention Locks

③ Record Locks

④ Gap Locks

⑤ Next-key Locks

⑥ Insertion Intention Locks

⑦ Auto-INC Locks

⑧ Predicate Locks for Spatial Indexes

(1-1) InnoDB 스토리지 엔진 잠금 방식

• InnoDB 스토리지 엔진 레벨에서 제공되는 DB Lock은 MySQL에서 제공하는 DB Lock 과는 별개로 엔진 내부에서 레코드 기반의 잠금 방식을 사용한다.
• 보통 명시적으로 잠금을 사용하는 경우는 드물며, 격리 수준에 따라 묵시적으로 잠금이 사용되어진다.
• InnoDB 엔진에서는 비관적 잠금 방식을 사용한다.

① 비관적 동시성 제어 (PCC, Pessimistic Concurrency Control)

비관적 락은 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 먼저 획득하고 변경 작업을 처리하는 방식을 말한다.

이 처리 방식은 이름에서도 느낄 수 있듯이, “현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다” 라는 비관적인 가정을 하기 때문에 먼저 잠금을 획득한다.

일반적으로 Shared Lock, Exclusive Lock을 통해 이를 구현한다.

② 낙관적 동시성 제어 (OCC, Optimistic Concurrency Control)

낙관적 락은 트랜잭션이 같은 레코드를 변경할 가능성은 상당히 희박할 것이라고 “낙관적”으로 가정하기 때문에 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는 지를 확인해 문제가 있었다면 Rollback 처리를 하는 방식을 말한다.

(1-2) 실습을 위한 초기 테이블, 초기 데이터 생성

• MySQL에서 제공하는 DB Lock, 스토리지 엔진 레벨에서 제공하는 DB Lock의 실습을 위해 초기 테이블과 데이터를 생성한다.
• 또한, 원활한 실습을 위해 미리 DB 세션을 2개 준비해놓고 AutoCommit 기능을 끄고 격리 수준은 “Read-Uncommitted” 으로 설정한다.
• 만약, 격리성 수준에 대해 모른다면 https://shuu.tistory.com/76 참고하자!

트랜잭션 격리성 수준

// 테이블 생성
CREATE TABLE EMP (
ID INT not null auto_increment,
NAME VARCHAR(100) not null,
JOB VARCHAR(30) not null,
primary key (ID));

// 초기 데이터 생성
insert into EMP values (1, "userA", 'Student');
insert into EMP values (2, "userB", 'Student');
insert into EMP values (3, "userC", 'Student');

// 트랜잭션 격리 수준 조회
show variables like 'tx_isolation';

// Autocommit 설정값 확인
show variables like 'autocommit%';

// 트랜잭션 격리 수준 수정
set tx_isolation = 'READ-UNCOMMITTED';

// Autocommit 끄기
SET AUTOCOMMIT = FALSE;

(2) MySQL 엔진 레벨의 Lock

(2-1) Global Locks

• “Flush Tables with read lock” 명령으로 Lock 획득할 수 있고, 가장 범위가 큰 Lock으로 서버 전체에 영향을 미친다.
• 하나의 세션에서 글로벌 락을 획득하면 다른 세션에서 Select를 제외한 대부분의 DDL, DML 문장 실행 시, 글로벌 락이 해제될 때까지 계속 대기 상태로 남게 된다.
• MySQL 서버 전체에 영향을 주기 때문에 작업 중인 테이블이나 데이터베이스를 넘어서 동일하게 영향을 끼치게 된다.
• 만약, 글로벌 락이 실행되기 전에 베타 락이 걸린 SQL이 실행됐다면 기존의 베타 락이 완전히 해제된 이후에 글로벌 락이 걸리게 된다.
• 즉, 실행 중인 모든 쿼리가 완료되어야만 글로벌 락을 획득할 수 있는 것이며, MySQL 8.0 버전 이상부터는 Xtrabackup, Enterprise Backup 같이 백업 락이라는 조금 더 가벼운 락이 추가되었다.

[세션 1, 2 공통]
set tx_isolation = 'READ-UNCOMMITTED';
SET AUTOCOMMIT = FALSE;
START TRANSACTION;

[세션 1]
Flush Tables with read lock;

[세션 2]                                     
MariaDB [mysql]> insert into emp values (4, 'userD', 'student');    

→ 대기 상태로 빠지게 되고, 세션 1에서 “unlock tables“ 해주어야 요청이 처리된다.

(2-2) Table Locks

• 테이블 락의 경우, 테이블 단위로 설정하는 Lock 이며 명시적 (Explicit), 암시적 (implicit) 락을 획득할 수 있다.
• 명시적 락의 경우는 “Lock Tables 테이블명 [Read | Write]” 명령으로 Lock 획득할 수 있고, 묵시적 락의 경우 테이블을 변경하는 쿼리를 실행했을 때 MySQL 서버가 해당 테이블에 Lock을 설정하고 데이터를 변경한 다음 바로 Lock을 해제하는 형태로 사용된다.
• 자동으로 Lock 설정 및 해제가 일어나기 때문에 편리하지만 InnoDB의 경우 레코드 기반 Lock을 제공하기 때문에 테이블 락이 발생하더라도 DML에서는 무시되고 DDL 에서만 영향을 준다.

[세션 1, 2 공통]
set tx_isolation = 'READ-UNCOMMITTED';
SET AUTOCOMMIT = FALSE;
START TRANSACTION;

[세션 1]
LOCK TABLES emp Read;
SELECT * from mysql.user;    

→ 세션 1에서 emp 테이블에 대한 테이블 락 (Read)을 설정한다.

→ 테이블 락의 기본 원칙은 잠근 테이블 외의 테이블은 접근이 불가능하므로 user 테이블에 접근할 수 없다.

[세션 2]
LOCK TABLES emp Read;

→ 다른 세션이 동시에 같은 테이블에 대한 Read 락을 획득할 수 있으므로 세션 2에서 emp 테이블에 테이블 락(Read)을 설정할 수 있다.
[세션 1, 2 공통]
SELECT * FROM emp;
INSERT INTO emp VALUES (4, 'userD', 'student');

→ 세션 1, 2에서 emp 테이블에 테이블 락 (Read)을 설정하여 emp 테이블을 조회할 수는 있지만 수정, 삭제, 추가는 불가능하다.

→ 그리고 emp 테이블에 테이블 락 (Read)이 걸려있기 때문에 해당 락이 해제되기 전까지 해당 테이블에 테이블 락 (Write)을 설정할 수는 없다.

[세션 1, 2 공통]
UNLOCK TABLES;

→ 테이블 락 (Write) 실습을 위해, 기존에 설정한 테이블 락 (Read)는 해제한다.

[세션 1]
LOCK TABLES emp Write;
INSERT INTO emp VALUES (4, 'userD', 'student');

→ 세션 1에서 emp 테이블에 테이블 락 (Write)을 설정하고 해당 테이블에 데이터를 추가할 수 있다.

[세션 2]
LOCK TABLES emp Write;
INSERT INTO emp VALUES (4, 'userD', 'student');
SELECT * FROM emp;

→ emp 테이블에는 이미 세션 1에서 테이블 락 (Write)이 설정되어 세션 2에서 emp 테이블에 테이블 락 (Read, Write)을 설정할 수 없다. 

(2-3) Named Locks

• 네임드 락의 경우, 테이블 및 레코드, 데이터베이스 객체가 아닌 사용자가 지정한 “임의의 문자열”에 대해 Lock을 획득하고 반납하는 방식으로 한 세션이 Lock을 획득한다면 다른 세션은 해당 세션이 Lock을 해제한 이후에 획득할 수 있다.

→ 네임드 락 설정 : GET_LOCK(문자열), 네임드 락 해제 : RELEASE_LOCK(문자열)

• Lock에 이름을 지정하여 애플리케이션 서버에서 제어가 가능하며 단점으로는 Lock이 자동으로 해제되지 않기 때문에 별도의 명령어로 해제를
  수행하거나 선점 시간이 끝나야 해제하는 등 네임드 락의 획득, 반납에 대한 로직을 철저하게 구현해야 한다는 것이다.
• 네임드 락은 주로 분산 시스템에서 여러 노드가 동시에 접근하는 경우, 데이터의 일관성과 동시성을 보장하기 위해 사용되어진다.

[세션 1, 2 공통]
set tx_isolation = 'READ-UNCOMMITTED';
SET AUTOCOMMIT = FALSE;
START TRANSACTION;

[세션 1]
SELECT GET_LOCK('hello', 100);        

(2-4) Metadata Locks

(3) 스토리지 엔진 레벨의 Lock

(3-1) Shared and Exclusive Locks

(3-2) Intention Locks

(3-3) Record Locks

(3-4) Gap Locks

(3-5) Next-key Locks

(3-6) Insertion Intention Locks

(3-7) Auto-INC Locks

(3-8) Predicate Locks for Spatial Indexes

 

[Reference]

• https://m.blog.naver.com/pjt3591oo/222479605880
• https://kdhyo98.tistory.com/101
• https://idea-sketch.tistory.com/45
• https://greeng00se.tistory.com/40
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• https://jeong-pro.tistory.com/m/239
• https://scshim.tistory.com/477
• https://velog.io/@yangsijun528/MySQL-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84
• https://cl8d.tistory.com/m/108
• https://velog.io/@fortice/MySQL-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EC%9E%A0%EA%B8%88Lock

[MySQL] 트랜잭션, 잠금(Lock)

[Real MySQL 8.0] 트랜잭션과 MySQL / InnoDB의 락 (Lock)

MySQL 스토리지 엔진

[Real MySQL 8.0] 4장 - 아키텍처: MySQL 엔진과 스토리지 엔진(InnoDB, MyISAM)

MySQL 아키텍처 (MySQL 엔진 구조, 스토리지 엔진 구조, 스레드 구조, 메모리 구조, 쿼리 실행 구조, R

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking

[MySQL] InnoDB 스토리지 엔진 잠금

[MySQL]MySQL 벼락치기(4) - 트랜잭션과잠금(1)

[DB] 락(Lock) - MySQL 8.0 InnoDB

[MySQL] 락(Lock)너는 무엇을 잠그는 녀석이니?