MySQL은 트랜잭션, 락, 격리수준을 제공한다. 이들에 대해 하나씩 알아보자.
Transaction
트랜잭션은 데이터 베이스의 상태를 바꾸는 작업 단위이다. 이는 작업의 완정성을 보장해준다.
--> 작업의 셋을 모두 완벽하게 처리하거나, 처리하지 못하면 원상태로 복구시켜준다.
MySQL에 insert, update, delete등의 SQL 명령으로 데이터의 상태를 바꿀때마다 자동으로 commit을 하여 변경된 내용을 데이터 베이스에 반영한다.
트랜잭션은 아래와 같은 4가지 특성을 가진다.
1. 원자성(atomicity)
- 트랜잭션이 모두 DB에 반영되거나 아예 반영되지 않아야 한다.
2. 일관성(consistency)
- 트랜잭션의 작업 처리 결과는 항상 일관적이어야 한다.
3. 독립성(isolation)
- 둘 이상의 트랜잭션이 동시에 병행 실행될 때, 어떤 트랜잭션도 다른 트랜잭션의 연산에 끼어들 수 없다.
4. 영구성(durability)
- 트랜잭션이 성공적으로 완료되었다면, 결과는 영구적으로 반영되어야 한다.
MySQL은 AutoCommit이 디폴트이다. 하여 클라이언트가 직접 commit 명령어를 하지 않아도 자동으로 commit되어 데이터베이스에 반영된다. 이를 해제하기 위해서는 아래의 명령어로 AutoCommit을 꺼주면 된다.
SET AUTOCOMMIT = 0;
이렇게 되면 트랜잭션의 시작 명시적으로 알려줘야한다.
START TRANSACTION;
그리고 데이터를 변경하는 쿼리를 실행한 후,
INSERT,,,,, ;
UPDATE,,,,,;
DELETE,,,,,,;
아래와 같이 커밋을 해줘야 데이터베이스에 변경한 내용이 반영된다.
COMMIT;
transaction state
MySQL 스토리지 엔진
스토리지 엔진은 데이터 읽기/쓰기를 담당한다. 즉 어떤 스토리지 엔진을 사용하느냐에 따라 MySQL이 데이터를 쓰고 읽는 방식이 달라진다.
몇가지 잘 알려진 스토리지 엔진이 있다.
1. MyISAM (마이아이삼)
MySQL 5.7버전 이전까지는 해당 엔진이 디폴트로 쓰였다고 한다. 해당 엔진은 키캐싱 방법을 디폴트로 한다고 한다.
2. MEMORY
해당 엔진은 메모리에 DB가 존재한다. 하여 서버를 종료하면 데이터가 날라간다.
3. InnoDB ⭐️
현재(5.7버전 이상) MySQL의 디폴트 엔진으로 위의 다른 엔진들과 다르게 트랜잭션을 제공하며, FK(외래키)를 제공한다.
테이블을 create할때 별도의 엔진 설정 명령어를 추가해주지 않으면 디폴트로 해당 엔진을 사용한 테이블이 생성된다.
추가적으로 유용한 MySQL 명령어
1. SHOW CREATE TABLE [테이블 명]
테이블이 어떻게 생성되었는지를 보여준다. ORM으로 생성된 테이블이 어떻게 생성되었는지 알 수 있어 매우 유용
2. charset
어떤 character(문자열)의 데이터를 저장할지
3. collate
저장된 데이터를 어떤 식으로 비교, 정렬할지
DataBase Lock
락은 데이터 베이스의 동시성을 제어하기 위한 기능이다.
* 동시성 : 하나의 데이터를 동시에 여러명이 조작할 수 없도록 방어하는 것이다. 즉 여러명이 같은 데이터를 요청할 경우 한 시점에는 하나의 커넥션만 변경할 수 있도록 하는 것이다.
---> 동시성이 제어되지 않을 때는 concurrency problem이 발생할 수 있다.
하여 concurrency problem을 막기 위해 MySQL은 락을 제공한다. MySQL이 제공하는 락에는 몇가지 종류가 있다.
1. 글로벌 락
- 범위가 가장 넓은 락으로 데이터베이스 서버 전체에 락을 건다. 즉 이때 SELECT를 제외한 모든 쿼리들(insert, update, delete와 같은 쿼리들과 DDL을 포함하여)이 대기상태가 된다. 글로벌 락을 풀면 이때서야 대기 상태이던 쿼리들이 실행될 수 있다.
- mysql 백업 솔루션인 mysqldump와 같은 것을 할때 사용될 수 있다.
2. 테이블 락
- 데이터 베이스의 테이블에 락을 거는 것.
- 테이블에 데이터를 변경하는 쿼리를 실행하면 자동으로 락이 걸린다.
- - (1) 데이터 추가 시 lock 설정
- - (2) 데이터 변경
- - (3) 데이터 변경 commit시에 lock이 release
- - InnoDB의 경우에는 DML 쿼리에서는 lock이 작동하지 않고 DDL의 경우에만 영향을 미침
3. 네임드 락
- GET_LOCK()이라는 명령어로 임의의 문자열에 대해 잠금을 설정함
- 예로 특정 유저가 동일 작업을 하는 것을 막기 위해 트랜잭션시에 GET_LOCK("lee", 5)로 5분간 락을 걸어두면 다른 사용자가 같은 락을 걸면서 특정 작업을 하려 하면 다른 세션에서 걸어둔 락이 해제될때까지 기다려야 한다.
- 여러 클라이언트가 상호 동기화를 처리해야 할때, 사용할 수 있다.
4. 메타데이터 락
- 데이터 베이스 객체의 이름이나 구조를 변경할 때 획득하는 락.
- 테이블 락과 같이 별도의 명령어를 사용하는 것이 아니라, 테이블을 변경하는 등의 작업을 할 때, 자동으로 가져온 뒤, release한다.
5. 레코드 락
- record/row에 lock을 거는 것.
커넥션과 세션
커넥션 : 클라이언트 프로세스와 데이터 베이스 인스턴스 간의 물리적 경로(클라이언트와 인스턴스간의 네트워크 커넥션)
세션 : 인스턴스 안의 논리적인 실체. 현재 로그인한 유저의 상태.
커넥션이 이루어짐 -> 세션이 성립 -> 세션을 통해 프로세스가 작동
Client initiates a connection to the database so it connects to the listenerListener starts (fork) a dedicated database process that will receive this connection (session)After this dedicated process is started, the listener passes the connection from the client to this processThe server process takes the connection from the listener to continue the handshake with the clientServer process and client exchange information required for establishing a session (ASO, Two Task Common, User logon)Session is opened
참고 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=hanccii&logNo=220582029740
Isolation Level (격리 수준)
여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것. 역시 격리 수준에도 몇가지 종류가 있다. 아래에서 소개하는 순서대로 아래로 갈수록 점점 격리 수준의 높아진다. 즉 read uncommitted의 격리 수준이 가장 낮다.
1. read uncommitted (dirty read)
트랜잭션의 변경 내용이 commit이나 rollback의 여부에 상관없이 다른 트랜잭션에서 볼 수 있다.
하여 이는 dirty read 문제가 발생한다. <- 에러가 발생해서 rollback된 항목을 commit 전에 접근해서 에러가 발생할 수 있음
2. read committed (unrepeatable read)
- 트랜잭션이 완료된 데이터만 다른 트랜잭션에서 조회가능
- 이는 커밋되기 전까지는 undo log에 있는 데이터를 불러온다.
- 정합성(unrepeatable read)에 어긋난다.
- - 트랜잭션 A가 특정 시점 T1에 데이터를 update하고 T2에 commit한다고 해보자.
- - 트랜잭션 B는 같은 ID의 데이터를 2번 SELECT하는 쿼리를 실행할 것이다.
- - 트랜잭션 A가 커밋하기 전에(T2전에) 트랜잭션 B가 데이터를 SELECT한다.
- - 트랜잭션 A가 커밋을 한 뒤에 트랜잭션 B가 데이터를 SELECT한다.
- - 이때 트랜잭션 B가 각 다른 시점에 SELECT한 데이터가 다를 수 있다.
- - > 이는 정합성에 어긋난다. 하나의 트랜잭션에서 동일한 데이터를 여러번 읽고 변경하는 작업에서 문제가 발생한다.
3. repeatable read
- MySQL의 InnoDB엔진에서 디폴트로 사용하는 격리 수준.
- undo log 영역에 백업된 이전 데이터를 이용해서 동일 트랜잭션에서는 같은 내용을 보여줄 수 있도록 한다. 이러한 방식을 MVCC(Multi Version Concurrency Control)이라고 한다.
- REPEATABLE READ는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 통해 동일한 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다.
- read committed 역시 MVCC를 사용하여 commit 이전의 데이터를 보여주지만 repeatable read는 undo log에 백업된 레코드의 여러 버전 가운데 몇 번째 이전의 버전까지 찾아 들어간다.
- - 즉 undo log에 트랜잭션 ID를 함께 저장하여 하나의 트랜잭션에서 같은 트랜잭션ID의 undo log 데이터를 읽어온다. 하여 데이터 정합성이 깨지는 문제가 발생하지 않는다.
- panthom read 발생
- - undo log에서는 lock을 걸 수 없어서 같은 트랜잭션에서 조회 가능. 하여 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안보였다가 하는 현상이 발생함.
* 그러나 InnoDB에서는 repeatable read 격리 수준에서도 팬텀 리드가 발생하지 않음
4. serializable
- 가장 엄격한 격리수준 (효율성이 매우 낮아 거의 사용하지 않음)
- read를 하기 위해서도 lock을 획득해야 한다. 즉 read를 하는 동안에 lock이 걸려 insert, update, delete를 할 수 없음
* undo log
undo log는 update나 delete와 같은 쿼리를 사용하여 데이터를 변경했을 때, 변경 전의 데이터를 보관해두는 곳이다.
update문을 실행시키고 commit를 하면 테이블의 데이터를 수정하기 전에 undo log에 update 이전의 데이터를 보관한다. 그리고 나서 테이블을 update한다. 만약 commit이 아닌 rollback을 한다면 undo log에 있는 데이터를 테이블 레코드에 덮어쓴다.
*buffer pool
버퍼 풀은 InnoDB가 엑세스할 때, 테이블 및 인덱스 데이터를 캐시하는 메인 메모리 영역. 즉 자주 사용하는 데이터를 메모리에서 직접 처리할 수 있도록 하여 처리 속도를 높인다.
참고:
https://gyoogle.dev/blog/computer-science/data-base/Transaction.html
https://velog.io/@fortice/MySQL-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EC%9E%A0%EA%B8%88Lock
https://swknight13.medium.com/mysql-%EC%9D%98-undo-log-a65a1f154330
https://zzang9ha.tistory.com/381
ㅁㄴㅇ
'MySQL' 카테고리의 다른 글
| wanted 백엔드 첼린지 2-1 MySQL 기본개념 (0) | 2023.02.20 |
|---|---|
| wanted 백엔드 첼린지 1-1 다양한 데이터 베이스의 특징 (0) | 2023.02.10 |
