Transaction (트랜잭션)
START TRANSACTION;
COMMIT;
SQL
복사
1.
작업의 완전성을 보장한다.
2.
사용자의 작업셋을 모두 완벽하게 처리하거나
3.
처리하지 못하면 원상태로 복구한다
ACID
ACID - Atomicity
1.
더이상 쪼개질 수 없는 가장 작은 단위
a. transaction의 단위
2.
모두 성공하거나 모두 실패, 중간이 없음
a. Write 중에 실패했을 때
b. 어디부터 실패했는지 파악해서 retry 하지 않고
c. 그냥 다시 retry 가능
ACID - Consistency
1.
Transaction 전후로 데이터가 일정해야함
a. 오류가 없어야 한다는 뜻
b. 데이터베이스가 판단하기는 어렵고,
c. 어플리케이션 단에서 관리해줘야함
2.
예제
a. 마지막 한 개 남은 상품을 동시에 2명의 고객이 주문할 수 없도록
b. 카드 한도 초과되려고 하는데 동시에 2가지 아이템을 주문할 수 없도록
ACID - Isolation
1.
동시에 발생하는 transaction이 서로 독립적이어야함
2.
독립되지 않으면 발생할 수 있는 문제
ACID - Durability
1.
Commit 된 transaction은 손실되지 않는다
2.
단일 시스템
a. write-ahead log
b. 디스크가 corrupt되면 회복할 수 있게함
3.
복제 시스템
a. 데이터가 충분한 수의 node로 replicate 되었는지 확인
MySQL 스토리지 엔진
•
SQL 엔진 vs 스토리지 엔진
1.
요즘은 다 InnoDB
a.
MySQL 데이터베이스 관리 시스템의 가장 인기 있는 스토리지 엔진 중 하나
b.
트랜잭션 처리와 외래 키 제약 조건을 지원하는 데 중점을 둔다는 점에서 다른 스토리지 엔진과 구별
SHOW ENGINE INNODB STATUS;
-- innodb의 설정 확인 (락, 데드락, 트랜잭션 설정 확인 가능)
SQL
복사
2.
InnoDB 장점
a.
MVCC
i.
MVCC를 사용하여 여러 버전의 데이터를 유지합니다. 이를 통해 읽기 작업과 쓰기 작업 간의 락 경쟁을 최소화하며, 동시에 여러 트랜잭션이 동일한 데이터에 액세스할 수 있게 해줍니다.
b.
버퍼링
i.
InnoDB는 데이터와 인덱스를 캐싱하기 위한 버퍼 풀을 사용합니다. 이는 데이터베이스의 성능을 크게 향상시킵니다.
c.
Foreign key
i.
외래 키(foreign key) 제약 조건을 지원하여 관계형 데이터베이스의 무결성을 보장
d.
transaction
i.
ACID(원자성, 일관성, 고립성, 지속성) 속성을 준수하는 트랜잭션을 지원합니다. 이를 통해 데이터의 무결성과 일관성이 유지됩니다.
ii.
InnoDB는 행 수준의 락(row-level locking)을 지원하여 동시성을 향상시킵니다. 이는 여러 트랜잭션이 동시에 다른 행에 액세스할 수 있게 해주며, 동시성과 성능을 향상시킵니다.
iii.
InnoDB는 데이터의 무결성을 보장하기 위해 커밋, 롤백 및 크래시 복구 기능을 제공
e.
InnoDB 테이블은 기본 키에 의해 클러스터링되며, 이로 인해 기본 키를 기반으로 한 검색 및 범위 검색의 성능이 향상
f.
InnoDB는 데이터를 저장하기 위해 테이블스페이스를 사용하며, 트랜잭션 로그를 사용하여 데이터의 지속성과 복구를 보장
g.
쓰기-앞(WAL) 로깅: InnoDB는 데이터를 디스크에 직접 쓰기 전에 로그에 먼저 쓰는 방식을 사용하여 성능과 복구 능력을 향상시킵니다.
트랜잭션 예제
1.
트랜잭션을 지원하는 스토리지 엔진(InnoDB)
2.
트랜잭션을 지원하지 않는 스토리지 엔진(MyISAM)비교
트랜잭션 예제 - SHOW CREATE TABLE
•
알아두면 좋은 명령어
트랜잭션 예제
1.
언두로그(UNDO LOG)에 대해서 알아보자
a.
undo log는 트랜잭션의 일관성과 롤백 기능을 지원하기 위한 중요한 구성 요소
•
일관성 읽기 (Consistent Read): 트랜잭션 내에서 데이터를 읽을 때 다른 트랜잭션에 의해 변경된 데이터를 볼 수 없도록 합니다. 이를 통해 트랜잭션 내에서 데이터의 일관성을 보장합니다.
•
롤백 (Rollback): 트랜잭션이 실패하거나 사용자에 의해 중단될 경우, undo log를 사용하여 데이터 변경을 원래 상태로 되돌릴 수 있습니다.
INSERT INTO trans (id) VALUES (1), (2), (3);
SQL
복사
작동 방식: 트랜잭션이 데이터를 변경할 때 (예: INSERT, UPDATE, DELETE), InnoDB는 해당 변경에 대한 원래 데이터의 복사본을 undo log에 기록합니다. 이렇게 하면 필요한 경우 변경을 쉽게 되돌릴 수 있습니다.
버퍼풀로 1, 2, 3 레코드 적재
디스크로부터 언두로그에 기존의 레코드 임시 저장
버퍼풀로부터 disk에 write 시작
•
만약 롤백 시, 언두 로그로부터 데이터 복구
•
만약 트랜잭션이 커밋되면 해당 트랜잭션의 undo log는 더 이상 필요하지 않게 됩니다. 그러나 바로 삭제되지는 않습니다. 대신, InnoDB는 이를 재사용하거나 나중에 배경 작업으로 삭제합니다.
•
Transaction - States
1.
Active (활성):
•
이 상태에서 트랜잭션은 실행 중입니다.
•
트랜잭션이 시작되면 처음에는 이 상태로 들어갑니다.
•
SQL 문장들이 실행되는 동안 트랜잭션은 이 상태를 유지합니다.
2.
Partially Committed (부분 커밋):
•
트랜잭션의 마지막 SQL 문장이 실행되고, 그 결과가 일시적으로 저장되었지만 아직 커밋되지 않은 상태입니다.
•
이 시점에서는 트랜잭션을 롤백할 수 없습니다.
3.
Committed (커밋):
•
트랜잭션의 변경 사항이 데이터베이스에 영구적으로 반영된 상태입니다.
•
커밋된 트랜잭션은 롤백할 수 없습니다.
4.
Failed (실패):
•
트랜잭션 실행 중에 오류가 발생하여 트랜잭션을 계속 진행할 수 없는 상태입니다.
•
이 상태에서는 트랜잭션을 롤백해야 합니다.
5.
Aborted (중단):
•
트랜잭션이 실패 상태에 도달하거나, 사용자나 데이터베이스 관리자에 의해 중단된 상태입니다.
•
중단된 트랜잭션은 롤백되며, 해당 트랜잭션에서 수행한 모든 변경 사항은 취소됩니다.
•
롤백 후, 트랜잭션은 종료되거나 재시작될 수 있습니다.
Database Lock
목적: 락의 주요 목적은 동시성 제어입니다. 여러 사용자나 프로세스가 동시에 데이터베이스의 동일한 자원(예: 행, 테이블)에 액세스하려고 할 때, 데이터의 일관성과 무결성을 보장하기 위해 락을 사용합니다.
1.
단어 그대로 "잠금"
2.
하나의 데이터를 동시에 여러명이 조작할 수 없도록
a.
락은 특정 자원에 대한 액세스를 제한합니다. 예를 들어, 한 트랜잭션이 특정 행에 대한 락을 보유하고 있으면, 다른 트랜잭션은 해당 행을 수정할 수 없습니다.
3.
동시성(concurrency)를 보장함
4.
MySQL 엔진락 vs InnoDB 락
5.
Database Lock 종류
MySQL 엔진락 vs InnoDB 락
•
MySQL 엔진 락 (MySQL Server-Level Locking):
1.
글로벌 락 (Global Lock): 전체 데이터베이스에 대한 락으로, 주로 백업 또는 특정 관리 작업을 수행할 때 사용됩니다. FLUSH TABLES WITH READ LOCK 명령을 사용하여 글로벌 락을 획득할 수 있습니다.
2.
테이블 락 (Table Lock): 특정 테이블 전체에 대한 락입니다. MyISAM과 같은 일부 스토리지 엔진에서는 행 수준 락을 지원하지 않기 때문에 테이블 락을 주로 사용합니다. LOCK TABLES 및 UNLOCK TABLES 명령을 사용하여 테이블 락을 관리할 수 있습니다.
•
InnoDB 락 (InnoDB Locking):
1.
행 수준 락 (Row-Level Lock): InnoDB의 주요 특징 중 하나는 행 수준의 락을 지원한다는 것입니다. 이는 특정 행에 대한 동시 액세스를 세밀하게 제어할 수 있게 해주며, 동시성을 크게 향상시킵니다.
2.
인텐션 락 (Intention Locks): InnoDB는 행 락과 테이블 락 사이의 호환성을 관리하기 위해 인텐션 락을 사용합니다. 인텐션 락은 세션이 특정 유형의 락(예: 공유 락 또는 배타적 락)을 획득하려는 의도를 나타냅니다.
3.
공유 및 배타적 락 (Shared and Exclusive Locks): InnoDB에서는 행에 대한 읽기 또는 쓰기 액세스를 제어하기 위해 공유(S) 락과 배타적(X) 락을 사용합니다.
4.
레코드 락 (Record Locks): 특정 행에 대한 락입니다.
5.
갭 락 (Gap Locks): 행 간의 간격에 대한 락으로, Phantom Read 문제를 방지하기 위해 사용됩니다.
6.
넥스트-키 락 (Next-Key Locks): 레코드 락과 갭 락의 조합으로, 특정 행과 그 앞의 갭에 대한 락을 의미합니다.
요약하면, MySQL 서버 레벨에서는 주로 글로벌 락과 테이블 락을 제공하며, InnoDB 스토리지 엔진에서는 행 수준의 락 및 관련 락 메커니즘을 제공합니다. InnoDB의 락 메커니즘은 동시성을 최대화하면서 데이터의 일관성과 무결성을 보장하기 위해 설계되었습니다.
Global Lock
1.
스토리지 엔진 단에서 락을 잡는 것
a.
ROW 에 lock을 획득한다고 보면 됨
b.
정확히는 index에 lock을 잡음
mysql > FLUSH TABLES WITH READ LOCK;
Python
복사
•
위의 명령어를 통해 글로벌 락을 걸음
UNLOCK TABLES;
-- unlock을 하게 되면 read가 아닌 operation을 사용할 수 있게 됨
Python
복사
2.
말그대로 글로벌. 전역 LOCK
3.
특징
a.
READ만 가능하고 수정이 불가능
i.
수정하게 되면 대기에 걸림
b.
요즘은 TRANSACTION 적용돼서 잘 안씀
4.
그렇다면 쓰는 경우가 존재하지 않나?
a.
MySQL을 덤프할 때, 어떠한 오퍼레이션이 일어나지 않도록 Global Lock을 걸 수 있음
Table Lock
1.
테이블을 잠그는 것
a.
유효 범위 : 테이블
2.
특징
a.
READ LOCK - 내가 READ 할거니까 아무도 수정하지 말아라
LOCK TABLES {table name} READ;
UNLOCK TABLES;
Python
복사
b.
WRITE LOCK - 내가 WRITE 할거니까 아무도 READ하지 말아라
LOCK TABLES {table name} WRITE;
UNLOCK TABLES;
Python
복사
•
동시성 보장을 위해 UPDATE 불가
c.
동시성을 보장한다고 생각하면 됨
i.
수정하려고 하는데 누군가 읽어버린다면?
•
만약 락이 없다면, 읽는 트랜잭션은 수정 중인 "미완료"의 데이터를 읽게 될 수 있습니다. 이는 일관성 없는 데이터를 반환할 수 있습니다.
•
테이블 락 또는 더 세분화된 락(예: 행 락)을 사용하면, 수정 중인 데이터에 대한 읽기 작업을 잠시 대기 상태로 만들어 이러한 문제를 방지할 수 있습니다.
ii.
읽으려고 하는데 누군가 수정해버린다면?
•
READ LOCK : 읽기 작업이 먼저 시작되었을 경우, 수정 작업은 해당 데이터에 대한 락이 해제될 때까지 대기해야 합니다. 이렇게 하면 읽기 작업이 일관된 데이터를 얻을 수 있습니다.
•
WRITE LOCK : 반대로, 수정 작업이 먼저 시작되었을 경우, 읽기 작업은 수정이 완료될 때까지 대기하게 됩니다.
3.
MyISAM이나 MEMORY에서 자동으로 사용되는 LOCK
a.
InnoDB의 경우에는 트랜잭션을 활용하기 때문에 불필요
b.
task가 나뉘어서 작동하게 됨
Named Lock
MySQL에서 제공하는 애플리케이션 수준의 동기화 메커니즘입니다. 네임드 락 자체는 데이터베이스의 특정 테이블이나 행에 직접적인 락을 걸지 않습니다. 대신, 애플리케이션 로직에서 동시성을 제어하기 위해 사용됩니다.
•
명령어
GET_LOCK(), RELEASE_LOCK(), IS_USED_LOCK(), 및 IS_FREE_LOCK()
SQL
복사
1.
네임드 락은 MySQL 세션 간에 동기화 메커니즘으로 작동. 한 세션에서 획득한 락은 다른 세션에서는 획득할 수 없음. 그러나 동일한 세션 내에서는 동일한 락을 여러 번 획득 가능.
2.
그냥 내가 임의로 LOCK을 잡는것 (명시적 락)
SELECT GET_LOCK('wanted', 10);
Python
복사
•
락의 이름 ('wanted'와 같은 문자열)
•
대기 시간 (초 단위)
1.
10초 내에 락을 획득할 수 있다면 함수는 1을 반환
2.
0은 사용 중
여기서 10은 대기 시간을 나타냅니다.
SELECT RELEASE_LOCK('wanted'); -- 획득 가능하게 release
SELECT IS_FREE_LOCK('wanted');
Python
복사
위와 같이 특정 문자열에 해당하는 어플리케이션 로직에 락을 걸어 해당 로직에 대한 동시성 제어를 수행할 수 있게 됩니다.
3.
다양한 로직 처리에 유리할 수 있음
a.
네임드락의 범위는 글로벌 → 샤딩(여러 데이터베이스), 테이블 여러 개
b.
Named Lock은 서버 내 공유 자원에 대한 접근을 동기화하는데 유용하지만, 분산 시스템에서 적합하지 않을 수 있으므로 다른 분산 락 메커니즘을 고려해야 합니다.
4.
네임드 락은 MySQL 서버 전체의 모든 세션 간에 동기화를 제공하는 글로벌 락.
특정 테이블이나 행에 연결되지 않으며, 주로 애플리케이션 로직의 동기화를 위해 사용
•
네임드 락은 애플리케이션 수준의 동기화를 위한 것이며, 데이터베이스의 특정 테이블이나 행에 직접적인 락을 걸지 않습니다. 따라서, 네임드 락을 획득한 상태에서도 다른 사용자는 UPDATE 쿼리를 실행할 수 있습니다.
•
특정 테이블이나 행에 대한 동시 액세스를 제한하려면, InnoDB의 행 수준 락(row-level locking) 또는 테이블 락을 사용해야 합니다. 예를 들어, SELECT ... FOR UPDATE 쿼리를 사용하면 특정 행에 대한 락을 획득할 수 있습니다, 이로 인해 다른 사용자는 해당 행을 수정할 수 없게 됩니다.
5.
애플리케이션 로직에 따라 네임드 락을 사용하여 특정 작업의 동시 실행을 제어하려는 경우, 다른 세션에서 해당 작업을 수행하기 전에 동일한 네임드 락을 획득하려고 시도할 것이므로, 락을 획득할 수 없게 됩니다.
•
우아한형제들 기술블로그에서 광고시스템은 Named Lock을 활용해 분산락을 구현
•
분산락이란 : 멀티 스레드 환경에서 공유 자원에 접근할 때, 데이터의 정합성을 지키기 위해 사용하는 기술
6.
Redis를 이용해서 MySQL 분산 락 구현이 가능하다. 분산 락을 구현하기 위해 Lock에 대한 정보를 Redis에 보관하고, 분산 환경의 서버는 공통된 Redis를 통해 임계 영역(critical section)에 접근할 수 있는지 확인한다.
7.
Named Lock은 MySQL 인스턴스 내부의 메모리를 기반으로 동작하기 때문에 분산 시스템에 적합하지 않습니다. 분산 시스템의 장애를 관리하기 위해서는 분산 락 관리자(Zookeeper 등등)를 사용할 필요가 있습니다.
Metadata Lock
•
테이블 정보를 수정할 때 자동으로 획득
◦
테이블 이름
◦
컬럼 이름이나 컬럼 정보 등을 수정할 때 자동으로
Record Lock
•
스토리지 엔진단에서 락을 잡는 것
1.
ROW 에 lock을 획득한다고 보면 됨
2.
정확히는 index에 lock을 잡음
Auto Increment Lock
•
여러 클라이언트 동시에 데이터를 추가하려고 할 때를 대비함
1.
Auto increment 설정 해놨는데 동시에 데이터를 추가하면
2.
같은 pk를 가진 row들이 여러개가 될 수도 있음
Isolation level
Isolation level - 격리수준 → 면접 질문 단골
1.
READ UNCOMMITTED
2.
READ COMMITTED
3.
REPEATABLE READ
4.
SERIALIZABLE
사전 세팅
1.
SET SESSION autocommit = 0;
2.
SHOW VARIABLES LIKE 'autocommit';
3.
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
4.
SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';
Dirty Read - READ UNCOMMITTED
•
Commit 하기도 전에 read 가능
◦
읽지 않은 이메일의 내용은 확인 가능하지만
◦
읽지 않은 이메일의 갯수는 0
◦
만약 2번째 query가 commit되지 않는다면 에러가 계속됨
•
Need for Atomicity
1.
2번째가 실패하면 1번도 날아감
2.
transaction의 단위를 결정해야함
a.
일반적으로 TCP connection
b.
BEGIN TRANSACTION; ~ COMMIT
READ UNCOMMITTED - DIRTY READ
READ COMMITTED
1.
COMMIT된 record들만 READ할 수 있는 것
a.
Commit 되지 않은 사항들을 읽을 수 있는 dirty read를 방지함
b.
여러 row를 동시에 수정한다고 할 때, commit되지 않은 사항들을 read하면 아까 이메일 같은
상황 발생함
c.
에러가 발생해서 abort 후 rollback 된 데이터를 read할 위험이 있음
2.
COMMIT된 record들만 WRITE 할 수 있는 것
a.
Commit 되지 않은 사항들을 수정할 수 있는 dirty write를 방지함
b.
하지만 여러명이 동시에 같은 record를 수정하는 것을 방지할 수는 없음 ️
️3.
Row-level lock을 사용해서 dirty write를 예방함
a.
MySQL 활용 데모
4.
write를 시도할 때, lock을 획득해야 함
a.
해당 lock은 transaction이 commit; / abort; 될 때까지 가지고 있음
b.
다른 transaction이 해당 row를 수정하고자 할 때는, 해당 lock을 기다려야함
5.
하지만 lock을 가지고있는 transaction이 오래걸리면 client가 오래 기다려야함
NON-REPEATABLE READ (READ SKEW)
1.
READ COMMITTED의 한계
a.
같은 transaction인데 읽어오는 값이 다름
b.
READ가 repeatable 하지 않다
2.
READ COMMITTED 의 문제점
a.
consistency가 깨진다
b.
backup 할 때
i.
backup하는 중에 데이터가 계속 변경된다면?
ii.
backup 중에 변경된 데이터가 rollback 된다면?
iii.
backup은 온전한 backup이 아님
c.
Analytic queries and integrity checks
i.
데이터 분석할 때 데이터를 불러오는데
ii.
일부는 수정된 데이터고, 일부는 기존 데이터라면?
iii.
read하는 중에 에러가 발생해서 갑자기 데이터가 변경된다면?
REPEATABLE READ
1.
Snapshot isolation
a.
transaction이 발생하는 시점에 snapshot을 떠두고,
b.
해당 snapshot을 가지고 read/write를 하는 것
c.
Multi Version Concurrency Control
i.
transaction 별로 auto-increment id를 부여하고
ii.
해당 transaction id보다 낮은 숫자의 snapshot은 제거함
2.
Write lock을 사용해서 dirty write를 방지
a.
하지만 read는 snapshot 뜨기 때문에 별도의 lock을 사용하지 않음
b.
read는 write를 block 하지 않고
c.
write는 read를 block 하지 않음
https://nesoy.github.io/articles/2019-05/Database-Transaction-isolation
Write Skew
1.
SELECT를 통해 현재 상태를 확인하고
2.
현재 상태를 기점으로 어플리케이션 코드에서 다음 쿼리를 결정함
3.
어플리케이션 코드의 결정에 따라 INSERT, UPDATE, DELETE 실행