DB 세션
세션이란 client가 SQL 서버에 연결된시점부터 나가는 시점까지의 시간을 말한다.
※ Connection은 물리적인 커뮤니케이션 채널
show full processlists;
show processlist;현재 실행 중인 process list 확인
-- base query
select * from information_schema_processlist limit 10;
-- not start with 192
select * from information_schema_processlist
where host not like '192%' limi 10;
-- session time above 100 seconds
select * from information_schema.processlist
where time > 100;조건별로 구분해서 볼려면 information_schema_processlist를 조회해서 사용하면 된다.
Sleep session
client-mysql 서버와 연결 후 다음 query 수행까지 대기중인 상태의 세션
정리를 하지 않으면 connection full로 인해 신규 세션 접속이 불가능하고 세션 별 할당되는 메모리로 인해 메모리 부족 현상 발생 가능
Timeout options
타임아웃 설정으로 sleep 세션들을 정리할 수 있다.
Foreground Thread와 Background Thread
Mysql은 thread 기반으로 동작한다. 종류는 Foreground Thread와 Background Thread가 있다.
Foreground Thread
client thread 라고도 하며, MySql 서버에 접속한 client 수 만큼 존재한다. 각 client 사용자가 요청한 쿼리를 처리한다.
사용자가 작업을 마치고 session을 종료하면 해당 thread는 thread cache로 반환되고, thread cache에 일정 개수 이상의 thread가 있다면 종료 시켜 일정 개수만 유지한다.(thread_cache_size 변수로 관리)
InnoDB 에서는 메모리에 read / write 작업만 담당하며 이 외의 작업은 background thread가 담당한다
Background Thread
InnoDB의 경우 많은 작업을 Background Thread가 수행한다
MySQL 엔진 안에서 처리하는 여러 프로세스들, 즉 눈에 보이지 않는 백그라운드 작업들을 수행하는 것이다.
Commit이란
commit이란 모든 작업들을 정상처리 하겠다고 확정하는 명령어이다. 해당 처리 과정을 DB에 영구 저장하겠다는 의미이며, commit을 수행하면 하나의 트랜잭션 과정이 종료되는 것이다.
commit을 수행하면 이전 데이터가 완전히 반영되어 UPDATE된다.
Auto Commit이란
각각의 SQL문을 자동으로 트랜잭션 처리 해주는 개념
SQL문이 성공적으로 실행하면 자동으로 commit 한다
실행 중에 문제가 있었다면 알아서 롤백 한다
Mysql 에서는 디폴트로 autocommit이 활성화 되어 있다.
select @@AUTOCOMMIT; // 오토 커밋 확인
show variables like 'autocommit%' // 오토 커밋 확인2
set autocommit = 1; // 활성화
set autocommit = 0; // 비활성화
set autocommit = true; // 활성화
set autocommit = false; //비활성화
※ 주의점
START TRANSACTION이 실행과 동시에 autocommit은 off된다
commit / rollback과 함께 트랜잭션이 종료되면 원래 autocommit 상태로 되돌아간다.
Rollback이란
작업 중 문제가 발생되어 트랜잭션의 처리 과정에서 발생한 변경사항을 취소하는 명령어이다. 해당 명령을 트랜잭션에게 할당하면, 트랜잭션은 시작되기 이전의 상태로 되돌아간다. 그리고 트랜잭션은 종료된다.
이것은 마지막 commit을 완료한 시점으로 돌아간다는 말과 상통한다.
즉, Rollback은 Commit하여 저장한 예전 상태를 복구하는 것이다.
트랜잭션이란
단일한 논리적인 작업 단위
논리적인 이유로 여러 SQL문들을 단일 작업으로 묶어서 나눠질 수 없게 만든 것이 트랜잭션이다.
트랜잭션의 SQL문들 중에 일부만 성공해서 DB에 반영되는 일은 일어나지 않는다.
START TRANSACTION
-- 이 블록안의 명령어들은 마치 하나의 명령어 처럼 처리됨
-- 성공하던지, 다 실패하던지 둘중 하나가 됨.
A의 계좌로부터 인출;
B의 계좌로 입금;
COMMIT
💡 ＠Transaction이란?
스프링의 선언적 트랜잭션 방식으로 해당 어노테이션이 적용된 메소드 내에서 수행되는 모든 작업이 트랜잭션 컨텍스트에서 실행되도록 보장해주는 것
AOP와 프록시 객체 원리를 통해 실행된다.
ACID
Atomicity(원자성)
트랜잭션이 DB에 모두 반영되던가, 아니면 전혀 반영되지 않아야 한다.(ALL or NOTHING)
트랜잭션은 논리적으로 쪼개질 수 없는 작업 단위이기 때문에 내부의 SQL 문들이 모두 성공해야 한다.
중간에 SQL 문이 실패하면 지금까지의 작업을 모두 취소하여 아무 일도 없었던 것처럼 롤백한다.
Consistency(일관성)
트랜잭션의 작업 처리 결과가 항상 일관성이 있어야 한다.
트랜잭션은 DB 상태를 consistent 상태에서 또 다른 consistent 상태로 바꿔줘야 한다
constraints, trigger 등을 통해 DB에 정의된 rules을 트랜잭션이 위반했다면 rollback 해야 한다.
트랜잭션이 DB에 정의된 룰을 위반했는지는 DBMS가 commit 전에 확인하고 알려준다
그 외에 application 관점에서 트랜잭션이 consistent하게 동작하는지는 개발자가 챙겨야 한다.
=> 일관성은 데이터 불변식을 보장하는 것
=> 홈쇼핑 <-> PG사 거래내역 항상 동일해야 하는데 동일하지 않은 케이스가 발생하면 데이터 일관성이 깨진것
Isolation (독립성) ★
어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없다.
여러 트랜잭션들이 동시에 실행될 대도 혼자 실행되는 것처럼 동작하게 만든다
여러 종류의 isolation level을 제공한다.
개발자는 isolation level 중에서 어떤 level로 트랜잭션을 동작시킬지 설정할 수 있다.
concurrency control의 주된 목표가 isolation이다.
Durability (영구성)
트랜잭션이 성공적으로 완료되었을 경우, 결과는 영구적으로 반영되어야 한다.
DB system에 문제가 생겨도 commit된 트랜잭션은 DB에 남아 있는다.
'영구적으로 저장한다'라고 할 때는 일반적으로 '비 휘발성 메모리'에 저장함을 의미한다.
기본적으로 transaction의 durability는 DBMS가 보장한다.
Isolation 레벨
트랜잭션 격리수준이라고도 하며 어느선까지 잠글것인가 정하는 것이라고 보면 된다.
READ UNCOMMITTIED
- 커밋되지 않은 데이터를 읽는다. LOCK을 걸지 않는다
- 트랜잭션 commit, rollback 여부에 상관없이 다른 트랜잭션의 값을 읽을 수 있다.
- 정합성 문제가 많이 발생한다
- dirty read(트랜잭션 작업이 완료되지 않은 미완성 데이터를 읽음) 현상이 발생한다.
READ COMMITTED
- 커밋된 데이터를 읽는다. (당연히 롤백된 데이터는 읽지 못한다)
- 실제 테이블 값을 가져오는 것이 아니라, 백업되어 복구 가능한 Undo 영역 값을 가져오게 된다.
- 대부분 RDB에서 기본값으로 사용하는 격리 수준이다.
- Dirty Read를 방지할 수 있다.
문제점
- 한 트랜잭션내에서 같은 값을 여러번 읽었을 때, 동일함을 보장할 수 없다. (Non Repeatable READ)
- 트랜잭션 A가 같은 값을 다시 읽기전에 트랜잭션B에서 커밋이 일어날 수 있기 때문이다.
-은행의 입출금 같이 데이터의 정합성이 중요한 서비스에서 찾기힘든 치명적인 문제를 만들 수 있다.
REPEATABLE READ
- 한 트랜잭션 내에서 같은 값을 여러번 읽어도 정합성을 보장하는 읽기이다.
- DB마다 구현방법이 다르다. 보통 데이터를 처음읽을 때 스냅샷을 찍어 정합성을 보장한다.
- 스냅샷을 찍어서 사용하게 되면 Phantom Read가 발생할 수 있다.
문제점
- Phantom Read (한 트랜잭션에서 특정 레코드가 보였다가 안보였다가 하는 현상) 현상이 발생한다.
- 한 트랜잭션에서 값을 읽었을 때, 첫번째에서 안보이던 레코드가 두번째 조회에서 보이는 현상
- 이를 방지하기 위해서는 쓰기 잠금까지 걸어야 한다.
SERIALIZABLE
- 한 트랜잭션이 완료되기 전까지 해당 데이터에 수정이 불가능 하다(lock)
- 동시처리 성능이 가장 낮고 데드락 발생가능성이 매우 높다.
- 대부분의 문제는 막을 수는 있지만 성능이 너무 떨어져 거의 사용하지 않는다.
LOCK
DB 락이란?
트랜잭션들이 동시에 수행될 때, 데이터의 일관성을 해치지 않도록 데이터 접근을 제어하는 DBMS의 기능이다.
동시성 제어를 위해서 필요한 기능이다.
동시성 제어
- 동시성 제어란 다중 사용자 환경에서 둘 이상의 트랜잭션이 동시에 수행될 때, 일관성을 해치지 않도록 트랜잭션의 데이터 접근을 제어하는 것
동시성 제어 방법
낙관적 락
- 데이터 갱신 시 경합이 발생하지 않을 것이라고 봄
- 한 사용자가 업데이트를 완료하면, 동시 업데이트를 시도하는 다른 사용자들에게 충돌이 있음을 알림
- 버전 값을 기준으로 진행
- 처음에 읽은 버전과 후에 읽은 버전이 같으면 업데이트
- 버전이 다르면 충돌 발생 후 롤백
- 동시 업데이트가 거의 없는 경우 좋음
비관적 락
- 동일한 데이터를 동시에 수정할 가능성이 높다라고 봄
- 다른 사용자는 먼저 시도한 사용자가 변경을 확약해서 레코드 잠금을 릴리스할 때까지대기해야 함
- 동시 업데이트가 빈번한 경우 혹은 외부 시스템(롤백 하기 힘듬)과 연동한 경우에 좋음
비관적 락의 연산 종류
1. 공유잠금(Shared Locks)
- read 연산 실행 가능, write 연산 실행 불가능
- 데이터에 대한 사용권을 여러 트랜잭션이 함께 가질 수 있음
- s lock이 걸려있는 row에 다른 트랜잭션이 s lock은 걸 수 있으나 x lock은 걸 수 없다.
- select ... lock in share moode(5.7 이하)
- select ... for share(8.0버전부터) 사용해 read 작업을 수행할 때 사용
2. 배타잠금(Exclusive Locks, X Lock)
- read 연산과 write 연산을 모두 실행 가능
- 베타 lock 연산을 실행한 트랜잭션만 해당 데이터에 대한 독점권을 가짐
- X lock이 걸려 있으면 다른 트랜잭션은 S Lock, X Lock 둘 다 걸 수 없다
- select ... for update, update, delete 등의 수정 쿼리를 날릴 때 각 row에 걸리는 lock
레코드락
- InnoDB에서 row가 아닌 DB index record에 걸리는 lock
- 테이블에 index가 없다면 숨겨져 있는 클러스터드 index를 사용하여 record를 잠근다.,
갭락
- index record의 gap에 걸리는 lock
- gap이란 index record가 없는 부분
- 조건에 해당하는 새로운 row가 추가되는 것을 방지
1. DB 갱신 손실
- 하나의 트랜잭션이 갱신한 내용을 다른 트랜잭션이 덮어씀으로써 갱신이 무효화가 되는 것을 의미한다.
- 두개의 트랜잭션이 한 개의 데이터를 동시에 갱신(Update)할 때 발생한다.
2. 모순성
다른 트랜잭션들이 해당 항목 값을 갱신하는 동안 한 트랜잭션이 두 개의 항목 값 중 어떤 것은 갱신되기 전의 값을 읽고 다른 것은 갱신된 후의 값을 읽게 되어 데이터의 불일치가 발생하는 상황
3. 연쇄 복귀
- 두 트랜잭션이 동일한 데이터 내용을 접근할 때 발생
- 한 트랜잭션이 데이터를 갱신한 다음 실패하여 Rollback 연산을 수행하는 과정에서 갱신과 Rollback 연산을 실행하고 있는 사이에 해당 데이터를 읽어서 사용할 때 발생할 수 있는 문제
데드락
- 두 개 이상의 트랜잭션이 각각 자신의 데이터에 대하여 락을 획득하고 상대방 데이터에 대하여 락을 요청하면 무한 대기 상태에 빠질 수 있는 현상이다.
- 교착상태라고도 한다.
- innodb에서는 show engine innodb status; 로 데드락 히스토리를 볼 수 있다.
해결책
- 트랜잭션 진행방향을 같은방향으로 처리
- 트랜잭션 처리속도를 최소화
- LOCK TIMEOUT을 이용하여 잠금해제 시간을 조절
- deadlock detection 활성화
- rollback할 작은 트랜잭션을 선택
- 트랜잭션의 크기는 insert, update, delete된 행 수에 의해 결정
- mysql 기준, 오라클은 아닐 수도 있음
회복
DB를 장애가 발생했던 이전의 상태로 복구 시켜 일관된 DB 상태를 만드는 것이다.
크게 덤프를 이용하는 방법과 로그를 이용하는 방법이 있다.
덤프
- 일정 주기로 원본의 데이터베이스의 모든 내용을 다른 저장장치에 복사하는 것
로그
- 변경 이전의 데이터베이스를 기준으로 변경 연산이 발생할 때 마다 로그 파일을 작성하여 기록하고, 회복할 때 로그에 적힌 내용을 사용하여 복원하는 방법
로그 파일을 이용한 회복
트랜잭션이 성공적으로 종료될 때까지 데이터베이스의 갱신을 지연하는 기법으로, 트랜잭션이 실행되는 동안 변경된 내용을 로그에 전부 기록하여 두었다가 한꺼번에 실행시킴으로써 트랜잭션의 원자성을 보장하려는 것입니다. 만약 트랜잭션이 실행도중 장애가 발생되면 그냥 로그에 있는 정보를 버리면 됩니다. 따라서 이 기법은 Redo연산만 수행하면 됩니다.
트랜잭션의 실행 결과를 로그에 기록함과 동시에 데이터베이스에 즉시 기록하는 기법으로, 트랜잭션의 원자성을 보장하지 않기 때문에 장애가 발생하였을 때 Redo,Undo 모두 수행해 주어야 합니다.
REDO, UNDO
둘다 체크포인트 이후에 로그 기록을 보면서 완료되지 않은 트랜잭션에 대해서 수행
REDO(다시하다)
- 장애 발생 전 이미 완료된 트랜잭션을 재 수행
- 사용자가 했던 작업을 그대로 다시 함
- 트랜잭션의 영속성을 보장하기 위함
- 결과를 영구적으로 저장하고 보존해야 함
UNDO(원상태로 돌리다)
- 장애 발생 후, 진행중이던 트랜잭션을 취소하고 이전 상태로 복구
- 사용자가 했던 작업을 반대로 진행하여 사용자의 작업을 원상태로 돌린다.
- 원자성을 만족하기 위함
- 트랜잭션은 전부 실행되거나 아예 실행되지 않아야 한다.
체크 포인트 회복 기법
로그를 이용한 회복기법의 경우 원칙적으로 로그 전체를 조사해야 되는데, 이 경우 시간이 너무 많이 걸립니다. 이러한 문제점을 해결하기 위한 기법이 검사시점(checkpoint) 방법입니다. 즉. 로그는 그대로 기록을 유지하면서, 회복 관리자가 정하는 일정한 시간 간격으로 검사 시점을 생성하여 장애 발생시 검사점 이전에 처리된 트랜잭션은 회복 작업에서 제외하고 이후에 처리된 내용에 대해서만 회복 작업을 수행하는 회복 기법
- 체크 포인트 이전에 시작하여 체크포인트 이전에 완료된 트랜잭션은 회복 대상이 아님
- 체크포인트 이전, 이후와 관계없이 시작하여 장애 발생 시간 이전에 완료된 트랜잭션은 REDO 대상(트랜잭션이 끝난 이후의 검사점이 없음)
- 체크 포인트 이전, 이후에 관계없이 시작하여 장애 발생 시간에 진행중인 트랜잭션은 UNDO 대상
InnoDB의 기본 트랜잭션 고립 수준
REPEATABLE READ이다.
다중 버전 동시성 제어(MVVC)를 사용 한다. 그래서 PHANTOM READ 부정합이 발생하지 않는다..
InnoDB은 MVCC, 행 수준 잠금, 갭락과 레코드 락이 합쳐진 넥스트 키락을 사용하여 문제를 해결한다.
참고
https://juno-juno.tistory.com/93
https://www.youtube.com/watch?v=sLJ8ypeHGlM&t=1161s
https://www.youtube.com/watch?v=bLLarZTrebU
https://jiwondev.tistory.com/139
https://mangkyu.tistory.com/30
https://www.youtube.com/watch?v=ZXV6ZqMyJLg&t=124s
https://kosaf04pyh.tistory.com/299
https://brownbears.tistory.com/181
https://itpenote.tistory.com/628
https://star7sss.tistory.com/824
https://steady-coding.tistory.com/562
'외부활동 > CS 면접 끝장내기 - 데이터베이스 1기' 카테고리의 다른 글
| 데이터베이스 4주차 스터디 정리 (3) | 2023.08.15 |
|---|---|
| 데이터베이스 3주차 스터디 정리 (0) | 2023.08.10 |
| 데이터베이스 2주차 스터디 정리 (0) | 2023.08.02 |
| 데이터베이스 1주차 스터디 정리 (0) | 2023.07.25 |