[MYSQL] Real MySQL 8.0 5장 정리

잠금, 트랜잭션 → 동시성에 영향

• 잠금: 동시성 제어
• 트랜잭션: 데이터의 정합성 보장
• 격리 수준: 하나의 트랜잭션이나 여러 트랜잭션간의 작업 내용을 어떻게 공유하고 차단

트랜잭션

InnoDB: 트랜잭션 지원

MyISAM, MEMORY: 트랜잭션 지원 x

MySQL에서의 트랜잭션

트랜잭션: 하나의 논리적인 작업 셋에 쿼리의 갯수에 상관 없이 논리적인 작업셋 자체가 100%적용 되거나, 아무것도 적용되지 않아야 함을 보장

부분 업데이트 현상: 트랜잭션을 지원하지 않는 테이블에서 이미 처리된 쿼리는 그대로 두고 오류가 발생한 쿼리부분에서 실행을 종료해 버리는 현상

주의사항

최소의 코드에만 적용하는 것이 좋음=트랜잭션의 범위를 최소화

네트워크 작업은 트랜잭션에서 배제

MySQL 엔진의 잠금

스토리지 엔진 레벨: 스토리지 엔진 간 상호 영향을 미치지 않음

MySQL 엔진 레벨: MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분, 모든 스토리지 엔진에 영향 미침

글로벌 락

//아래 명령으로 획득
FLUSH TABLES WITH READ LOCK;

가장 영향을 미치는 범위가 큼

한 세션에서 글로벌 락 획득할 경우 다른 섹션에서 SELECT를 제외한 DDL문장, DML 문장을 실행하는 경우 락이 해제될 때까지 해당 문장을 대기 상태로 남김

영향 미치는 범위: MySQL 서버 전체

사용 상황: 여러 데이터베이스에 존재하는 MyISAM, MEMORY 테이블에 대해 mysqldump로 일관된 백업을 받아야 할 때

주의점: 웹서비스용으로 사용되는 서버에서는 사용 자제

백업 락

MySQL 8.0 부터는 백업 락이 도입, 백업 툴등의 안정적인 실행을 위함

주로 백업은 레플리카 서버에서 실행됨

일반적인 테이블의 데이터 변경 허용

특정 세션에서 백업 락을 획득 하였을때 세션에서 아래 정보들을 변경 불가

• 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
• REPAIR TABLE, OPTIMIZE TABLE 명령
• 사용자 관리 및 비밀번호 변경

역할: 정상적으로 복제는 실행되지만 백업의 실패를 막기 위해 DDL 명령이 실행되면 복제를 일시 중지하는 역할

테이블 락

개별 테이블 단위로 설정되는 잠금

명시적, 묵시적으로 특정 테이블 락 획득 가능

//명시적 명령
LOCK TABLES table_name [READ|WRITE];

UNLOCK TABLES : 명시적으로 획득한 잠금을 반납할 때

묵시적 테이블 락: MyISAM, MEMORY 테이블에 데이터를 변경하는 쿼리를 실행할 때, 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후 즉시 잠금을 해제하는 형태로 사용

→ InnoDB 경우 레코드 기반의 잠금을 제공하기 때문에 위와 같은 묵시적락이 설정되지 않는다. DDL의 경우에만 영향을 미침

네임드 락

GET_LOCK() 이용하여 임의의 문자열에 대해 잠금 설정

단순히 사용자가 지정한 문자열에 대해 잠금을 획득하고 반납하는 잠금

사용 예시

• 여러 클라이언트가 상호 동기화를 처리해야 할 때 네임드 락 이용
• 많은 레코드에 대해 복잡한 레코드를 변경하는 트랜잭션

8.0버전 부터는 네임드락을 중첩해서 사용 가능, 현재 세션에서 획득한 네임드 락을 한 번에 모두 해제하는 기능 가능

메타데이터 락

데이터베이스 객체(테이블, 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금

명시적 획득 X, 자동으로 획득하는 잠금

InnoDB 스토리지 엔진 잠금

MySQL에서 제공하는 잠금과 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식 탑재

이로인해 뛰어난 동시성 처리 제공

InnoDB의 트랜잭션, 잠금, 잠금 대기 중인 트랜잭션 목록 조회할 수 있는 방법 도입

→ 서버의 information_schema 디비에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_WAITS 테이블 조인

InnoDB 스토리지 엔진의 잠금

레코드 락→페이지 락, 테이블 락 으로 레벨업되는 경우가 없음

레코드 락

레코드 자체만을 잠그는 것

인덱스의 레코드를 잠근다

인덱스가 하나도 없는 테이블의 경우 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금 설정

갭 락

레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것

역할: 이 간격에 새로운 레코드가 생성되는 것을 제어함, 넥스트 키 락의 일부로 사용됨

넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금

innodb_locks_unsafe_for_binlog 0으로 설정될 시 변경을 위해 검색하는 레코드에 넥스트 키 락 방식으로 잠금을 검

목적: 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장

단점: 데드락 발생, 다른 트랜잭션 대기

자동 증가 락

AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 각 레코드가 중복되지 않고 저장된 순서대로 증가하는 일련번호를 갖기 위해 사용되는 테이블 수준의 잠금

INSERT, REPLACE: 새로운 레코드를 저장하는 쿼리, 사용 O

UPDATE, DELETE: 사용X

AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제

테이블에 단 하나만 존재

작동 방식 변경(innodb_autoinc_lock_mode 값)

• 0
  • 모든 INSERT 문장은 자동 증가 락 사용
• 1
  • 연속 모드
  • 최소한 하나의 INSERT 문장으로 INSERT되는 레코드는 연속된 자동 증가 값을 가지게 됨
  • 단순히 한 건 또는 여러건의 레코드를 INSERT하는 것중 이 레코드 건수를 정확히 예측 가능 할 때 더 가볍고 빠른 래치(뮤텍스)를 이용하여 처리, 아주 짧은 시간동안 잠금 걸고 필요한 자동 증가 값 가져오면 잠금 해제
• 2
  • 인터리빙 모드
  • 절대 자동 증가 락을 걸지 않고 경량화된 래치(뮤텍스)를 사용
  • 하나의 INSERT 문장으로 INSERT되는 레코드여도 연속된 자동 증가 값을 보장하지 않음

자동 증가 값이 한 번 증가하면 줄어들지 않는 이유: 자동 증가 잠금 최소화

인덱스와 잠금

InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 것

= 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야함

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

레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸림

→ 그 레코드가 자주 사용되지 않는다면 오랜 시간 잠겨진 상태로 남아있을 수 있다

⇒ 레코드 잠금, 잠금 대기에 대한 조회 가능

performance_schema의 data_locks, data_lock_waits 테이블을 이용해 확인 가능

+강제 잠금해제= KILL

MySQL의 격리 수준

트랜잭션의 격리 수준 = 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것

4가지 격리 수준

• 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높음, 동시 처리 성능 떨어짐

1. READ UNCOMMITTED
   1. 거의 사용 안함
2. READ COMMITTED
   1. 오라클 같은 DBMS에서 자주 사용
3. REPEATABLE READ
4. SERIALIZABLE
   1. 동시성이 중요한 디비에서 사용하지 않음

READ UNCOMMITTED

각 트랜잭션의 변경 내용이 COMMIT, ROLLBACK 여부에 상관 없이 다른 트랜잭션에 보인다

더티 리드(Dirty Read): 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상

선호되지 않는 격리 수준

READ COMMITTED

어떤 트랜잭션에서 데이터를 변경 했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회 가능

오라클 DBMS에서 사용되는 격리 수준

더티 리드 발생 X

NON-REPEATABLE READ라는 부정합 문제 = 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 정합성이 어긋나는 것

REPEATABLE READ

MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장

• 사실 READ COMMITED도 MVCC를 이용해 COMMIT되기 전의 데이터 보여줌
• 차이점: 언두영역에 백업된 레코드의 여러 버전중 몇 번째 이전 버전까지 찾아 들어가야 하느냐

InnoDB 스토리지 엔진에서 사용되는 격리 수준

바이너리 로그를 가진 MySQL 서버에서 최소 REPEATABLE READ 격리 수준 이상을 사용해야 함

NON-REPEATABLE READ 부정합 발생 X

MVCC(Multi Version Concurrency Control): 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경

실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제 불가

PHANTOM READ: 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상

SERIALIZABLE

가장 엄격한 격리 수준

동시 처리 성능 떨어짐

한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다

PHANTOM READ 발생 X

→ InnoDB 에서는 갭 락과 넥스트 키 락을 이용하여 REPEATABLE READ 격리 수준에서도 발생하지 않는다