[Real MySQL8.0 스터디 week2] 5장

Coen·2023년 1월 28일
Real MySQL
1

Real MySQL Study

목록 보기
3/5
post-thumbnail

이 글은 Real MySQL 스터디에서 종욱님이 발표한 발표자료입니다.





## 트랜잭션

Mysql의 동시성에 영향 주는 1. 잠금 2. 트랜잭션 3. 트랜잭션의 격리수준(ISOLATION LEVEL)에 대해 알아보자.

트랜잭션은 작업의 완정성을 보장해주는 것이다. 즉 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못한 경우에는 원 상태로 복구해주는 기능이다.

잠금은 동시성을 제어하기 위한 기능이다. 예를들어 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 된다. 잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을한다.

격리수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

5.1.1. Mysql에서의 트랜잭션

트랜잭션은 논리적인 작업 셋 자체가 100% 적용되거나(commit을 실행했을 때) 또는 아무것도 적용되지 않아야함을 보장해 주는것이다.

CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY(fdpk) ) ENGINE=MyISAM;
INSERT INTO tab_myisam (fdpk) VALUES (3);

CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY(fdpk) ) ENGINE=INNODB;
INSERT INTO tab_innodb (fdpk) VALUES (3);

SET autocommit=ON; // auto-commit 활성화

INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);

SELECT * FROM tab_myisam;
SELECT * FROM tab_innodb;

두 INSERT 문장 모두 프라이머리 키 중복 오류로 쿼리가 실패했다.
tab_myisam 테이블에는 오류가 발생했음에도 1과 2는 남아 있는 것을 확인할 수 있다.
이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 어려운 문제를 만들어 낸다.
트랜잭션이 지원되는 InnoDB 테이블에서는 부분 업데이트 현상이 발생되지 않는다.

5.1.2 주의사항

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 이는 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미다.

변경 전

1. 처리 시작
 - 데이터베이스 커넥션 생성
 - 트랜잭션 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
5. 사용자의 입력 내용을 DBMS 에 저장
6. 첨부 파일 정보를 DBMS에 저장
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송
9. 알림 메일 발송 이력을 DBMS에 저장
 - 트랜잭션 종료
 - 데이터베이스 커넥션 반납
10. 처리완료
  • DBMS에 데이터를 저장하는 작업은 5번부터 시작된다. 그래서 2번과 3번, 4번의 작업을 트랜잭션에 포함 시킬 필요는 없다. 커넥션의 개수는 제한적이기 때문에 불필요한 작업에 트랜잭션을 포함시킨다면 다른 사용자가 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.
  • 메일전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 작업들은 DBMS의 트랜잭션 내에서 제거하는 것이좋다. 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐아니라 DBMS 서버까지 위험해 지는 상황이 발생할 수 있다.
  • 사용자가 입력한 정보를 저장하는 5번(사용자의 입력 내용을 dbms에 저장)과 6번(첨부 파일 정보를 dbms에 저장) 작업은 반드시 하나의 트랜잭션으로 묶어야 한다.
  • 7번 작업(저장된 내용 또는 기타 정보를 dbms에서 조회)은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함할 필요는 없다.
  • 9번 작업(알림 메일 발송 이력 dbms 저장)은 조금 성격이 다르기 때문에 이전 트랜잭션(5번과 6번 작업)에 함께 묶지 않아도 무방해 보인다.

변경 후

1. 처리 시작
2. 사용자의 로그인 여부 확인
3. 사용자의 글쓰기 내용의 오류 발생 여부 확인
4. 첨부로 업로드된 파일 확인 및 저장
 - 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
 - 트랜잭션 시작
5. 사용자의 입력 내용을 DBMS에 저장
6. 첨부 파일 정보를 DBMS에 저장
 - 트랜잭션 종료
7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
8. 게시물 등록에 대한 알림 메일 발송
 - 트랜잭션 시작
9. 알림 메일 발송 이력을 DBMS 에 저장
 - 트랜잭션 종료(COMMIT)
 - 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10 .처리완료

프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화 해야 한다는 것이다.

5.2 Mysql 엔진의 잠금

Mysql에서 사용되는 잠금은 크게 스토리지 엔진 레벨(디스크에 저장, 디스크로 부터 데이터를 읽어오는 부분)과 Mysql 엔진 레벨(요청된 SQL 문장을 분석하거나 최적화)로 나눌 수 있다. Mysql 엔진은 Mysql 서버에서 스토리지 엔진을 제외한 나머지 부분으로 이해하면 되는데, Mysql 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간에 서로 상호 영향을 미치지는 않는다.

MySQL 엔진에서는
- 테이블 데이터 동기화를 위한 테이블 락

- 테이블의 구조를 잠그는 메타데이터 락

- 사용자의 필요에 맞게 사용할 수 있는 네임드 락
등등 의 기능을 제공한다.

5.2.1 글로벌 락

글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득 가능하다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT 제외한 대부분의 DDL 문장이나 DML 문장을 실행하는 경우 대기상태로 남는다. 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다. mysqldump로 일관된 백업을 받아야 할 때는 글로벌 락을 사용해야 한다.

주의

글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다. FLUSH TABLES WITH READ LOCK 명령이 실행되기 전에 테이블이나 레코드에 쓰기 잠금을 거는 SQL 이 실행됐다면 이 명령은 해당 테이블의 읽기 잠금을 걸기 위해 먼저 실행된 SQL과 그 트랜잭션이 완료될 때까지 기다려야 한다. `FLUSH TABLES WITH READ LOCK 명령은 테이블에 읽기 잠금을 걸기 전에 먼저 테이블을 플러시해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료돼야 한다. 그래서 장시간 SELECT 쿼리가 실행되고 있을 때는 FLUSH TABLES WITH READ LOCK 명령은 SELECT 쿼리가 실행되고 있을 때까지 기다려야 한다.

장시간 실행되는 쿼리와 FLUSH TABLES WITH READ LOCK 명령이 최악의 케이스로 실행되면 MYSQL 서버의 모든 테이블에 대한 INSERT, UPDATE, DELETE 쿼리가 아주 오랜 시간동안 실행되지 못하고 기다릴 수도 있다. 글로벌 락은 MYSQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스 용으로 사용되는 MYSQL 서버에서는 가급적으로 사용하지 않는 것이 좋다. 또한 mysqldump 같은 백업 프로그램은 우리가 알지 못하는 사이에 이 명령을 내부적으로 실행하고 백업할 때도 있다.

DML, DDL, DCL 정리

5.2.2 테이블 락

테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블 락을 획득할 수 있다. 묵시적인 테이블 락은 MyIsam이나 Memory 엔진의 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다. Mysql 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다. 즉, 묵시적인 테이블 락은 쿼리가 실행되는 동안 자동으로 획득했다가 쿼리가 완료된 후 자동으로 해제된다. 하지만 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경 쿼리로 인해 묵시적인 테이블 락이 설정되지 않는다.

5.3 InnoDb 스토리지 엔진 잠금

InnoDB 스토리지 엔진은 MYSQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다. InnoDB는 레코드 기반의 잠금 방식 때문에 MyIsam보다는 훨씬 뛰어난 동시성 처리를 제공한다.

MySQL 서버의 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 하고 있고 해당 잠금은 어느 트랜잭젼이 가지고 있는지 확인 가능하며, 장시간 잠금을 가지고 있는 클라이언트를 찾아서 종료 시킬 수도 있다.

5.3.1 InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진에서는 레코드 락 과 레코드와 레코드 사이 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다. 아래 그림은 레코드 락과 레코드 간의 간격을 잠그는 갭 락을 보여준다.

5.3.1.1 레코드 락

레코드 자체만을 잠그는 것을 레코드 락이라고 하며, InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠든다는 점이다. 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.

5.3.1.2 갭 락

갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격을 잠그는 것을 의미한다. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어하는 것이다. 갭 락은 그 자체보다 넥스트 키락의 일부로 자주 사용된다.

5.3.1.3 넥스트 키 락

레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.

5.3.1.4 자동 증가 락

AUTO_INCREMNT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드는 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가져야 한다. 따라서 AUTO_INCREMNT 락이라고 하는 테이블 수준의 잠금을 사용한다.

AUTO_INCREMNT 락은 INSERT와 REPLACE 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요하다. AUTO_INCREMNT 락은 테이블에 단 하나만 존재하여서 두 개의 INSERT 쿼리가 동시에 실행되는 경우 나머지 쿼리는 AUTO_INCREMNT 락을 기다려야 한다.

5.3.2 인덱스와 잠금

InnoDB의 잠금은 인덱스를 잠그는 방식으로 처리된다. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

-- 예제 데이터베이스의 employees 테이블에는 아래와 같이 first_name 컬럼만
-- 멤버로 담긴 ix_firstname 이라는 인덱스가 준비돼 있다.
-- KEY ix_firstname (first_name)
-- employees 테이블에서 first_name='Georgi'인 사원은 전체 253명이 있으며,
-- first_name='Georgi' 이고 last_name='Klassen'인 사원은 딱 1명만 있는 것을 아래 쿼리
-- 에서 확인할 수 있다.
SELECT COUNT(*) FROM employees WHERE first_name="Georgi";
result -> 253
SELECT COUNT(*) FROM employees WHERE first_name="Georgi" AND last_name='Klassen'
result -> 1
-- employees 테이블에서 first_name='Georgi' 이고 last_name='Klassen' 인 사원의 입사 일자를 오늘로 변경하는 쿼리를 실행해 보자
UPDATE employees set hire_date=NOW() WHERE first_name="Georgi" AND last_name='Klassen'

UPDATE 문장이 실행되면 1건의 레코드가 업데이트될 것이다.
이 UPDATE 문장의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Georgi' 이며,
last_name 컬럼은 인덱스에 없기 때문에 first_name='Georgi' 인 레코드 253건의 레코드가 모두 잠긴다.
인덱스가 준비돼 있지 않다면, 한 세션에서 UPDATE 작업을 하는 중에서 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생할 것이다.

이 테이블에 인덱스가 하나도 없다면 어떻게 될까? 이러한 경우에는 테이블을 풀 스캔하면서 update 작업을 하는데, 이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠그게 된다.

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

레코드 수준의 잠금은 테이블의 레코드 각각에 잠금이 걸리므로 그 레코드가 자주 사용되지 않는다면 오랜 시간 동안 잠겨진 상태로 남아 있어도 잘 발견되지 않는다.
예전 버전의 mysql 서버에서는 레코드 잠금에 대한 메타 정보(딕셔너리 테이블)을 제공하지 않기 때문에 더더룩 어려운 부분이다. 하지만 MySQL 5.1 버전 이후 부터는 레코드 잠금과 잠금 대기에 대한 조회가 가능하다.


우선 MySQL5.1 부터는 information_schema라는 db에 INNODB_RTX라는 테이블과 INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 통해 확인이 가능했다. MySQL8.0 버전 이후 부터는 방식이 조금 달라졌다. performance_schema의 data_locks와 data_lock_waits 테이블들로 대체되는 중이다.

MySQL 8.0

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.blocking_engine_transaction_id;

6번 스레드는 5번 스레드를 기다리고 있고, 7번 스레드는 5번 스레드와 6번 스레드를 기다리고 있다는 것을 알수 있다.

5.4 Mysql 격리 수준

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
READ UNCOMMITED(1), READ COMMITED(2), REPEATABLE READ(3), SERIALIZABLE(4) 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시에 처리 성능도 떨어지는 것이 일반적이라고 할 수 있다. 사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하가 발생하지 않는다.**일반적인 온라인 서비스 용도의 데이터 베이스는 READ COMMITED와 REPEATABLE READ 중 하나를 사용한다.**

5.4.1 READ UNCOMMITTED**

이 격리 수준에서는 다른 트랜잭션에서의 변경 내용이 commit이나 rollback 여부와 상관없이 다른 트랜잭션에서 보인다.

사용자 b가 변경된 내용을 커밋하기도 전에 사용자 b는 emp_no=50000인 사원을 검색하는 상황에서 사용자 b는 사용자 a가 insert 한 사원의 정보를 커밋되지 않은 상태에서도 조회할 수 있다. 그런데 문제는 사용자 a가insert 된 내용을 롤백한다고 하더라도 여전히 사용자 b는 Lara가 정상적인 사원이라고 생각하고 계속 처리를 하는 상황이 발생할 수 있다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty read)라고 하고, 더티 리드가 허용되는 격리수준이 READ UNCOMMITTED이다.

5.4.2 READ COMMITTED

이 레벨에서는 위에서 언급한 더티 리드 같은 현상은 발생하지 않는다. 어떤 트랜잭션이 COMMIT 하기전에는 다른 트랜잭션에서 변경 사항에 대해서 확인 할 수 없다.

사용자 B 의 select 결과는 employees 테이블이 아니라 언두 영역에 백업된 레코드에서 가져온 것이다. 최종적으로 사용자 A가 변경 내용을 커밋하면 그 때부터 다른 트랜잭션에서도 백업된 언두 레코드(Jubal)가 아니라 새롭게 변경된 Toto라는 값을 참조할 수 있게 된다.

READ COMMITTED 격리수준 에서도 NON-REPEATABLE READ 라는 부정합의 문제가 있다.

동일 트랜잭션에서 사용자 B가 first_name 이 "Toto" 인 사용자를 검색했을 때, 일치하는 결과가 없다. 하지만 사용자 A가 사원번호가 50000 인 사원의 이름을 Toto로 변경하고 커밋을 실행한 후, 사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 결과가 1건이 조회된다.

5.4.3 REPEATABLE READ

REPEATABLE READ 는 MySQL 의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
InnoDB 스토리지 엔진은 트랜잭션이 rollback 될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경한다. REPEATABLE READ는 언두 영역에 백업된 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보장한다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역에 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

사용자 B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여 받았는데, 그때부터 사용자 B의 트랜잭션 안에서 실행되는 모든 select 쿼리는 트랜잭션 번호가 10(자신의 트랜잭션 번호보다 작은 트랜잭션 번호)에서 변경한 것만 보게된다.

하나의 레코드에 대해 백업이 하나 이상 얼마든지 존재할 수 있다. 한 사용자가 begin으로 트랜잭션을 시작하고 장시간 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 무한정 커질 수도 있다.

SERIALIZABLE

가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준.
동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.
트랜잭션의 격리 수준이 SERIALIZABLE 로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근 할 수 없는 것이다.
SERIALIZABLE 격리 수준에서는 일반적인 DBMS 에서 일어나는 PHANTOM READ라는 문제가 발생하지 않는다. 하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ 가 발생하지 않기에 SERIALIZABLE 격리 수준은 불필요해 보인다.

참고 : 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상을 PHANTOM READ(또는 PHANTOM ROW) 라고 한다.

profile
Coen
백엔드 프로그래머
이전 포스트

[Real MySQL8.0 스터디 week1] 4.2장

다음 포스트

[Real MySQL8.0 스터디 week2] 8.1 8.2 8.3 8.4장

0개의 댓글