본문 바로가기

DATABASE

DB MySQL을 중심으로(innoDB엔진)

지연된 쓰기

일반 적인 사용 DBMS들의 경우 대부분 지연된 쓰기를 지원.

Insert, Update, Delete를 버펄이 후 일괄처리한다.


메모리

운영체제에 따라 실제 할당되는 메모리 계산이 쉽지 않다.

단순히 mysql 설정 파라미터로 할당해둔 메모리양을 항상 할당되었다고 생각하는것이 편하다.


쿼리 실행구조

쿼리를 요청하면

1. 쿼리 파서가 쿼리 문장을 토큰(msql이 인식하는 최소 단위)으로 분리해 트리 형태의 구조로 만듬

2. 전처리기 : 파서트리를 기반으로 구조적 문제가 있는지 확인. 각 토큰을 테이블 이름이나 칼럼이름 내장함수와 같은 개체를 매핑해 각각의 존재여부와 접근권한을 확인.

3. 옵티마이저 : 들어온 쿼리문장을 저렴한비용으로 가장 빠르게 처리할 방법을 찾는다.

4. 실행 : 옵티마이저에 의해 나온 실행 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청으로 입력을 연결한다.


복제

마스터 : 바이너리 로그 스레드 -> 바이너리 -> 바이너리 로그 덤프 스레드

슬레이브 : 리플리케이션 I/O 스레드(덤프를 받아옴) -> 릴레이로그 -> 리플리케이션 SQL 스레드 -> 데이터 파일


문장기반 복제(Statement) : 마스터에서 실행된 SQL을 그대로 실행. Repeatable Read 이상의 트랜잭션 격리수준이 요구되어 갭락이나 넥스트 키락이 필요. 느려질 수 있으나 네트웍 비용은 적음

레코드기반 복제(Row Based) : 변경된 레코드를 바이너리 로그에 기록. 네트웍 비용이 커질 수 있으나 read-committed 격리수준에서도 사용가능. 잠금 경합도 줄어듬.


언두로그

변경전 데이터를 보관한다. 롤백 대비용. 격리수준을 유지하면서 동시성 제공에도 사용


인서트 버퍼

인덱스가 많아 인덱스 업뎃이 필요한경우, 바로 인서트 업데이트를 실행하지 않고 임시 공간에 저장하고 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다.

단 전달전 중복여부를 체크해야하는 유니크인덱스는 인서트 버퍼 사용불가.


리두 로그 및 버퍼

ACID 보장을 위해 변경 내용을 순차적으로 디스크에 기록하는 로그 파일


MVCC(Multi Version Concurrency Control)

가장큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 것

상황에 따라 버퍼풀이나 데이터파일의 내용을 반환할지, 언두로그의 내용을 반환할지 선택

격리 수준에 따라 달라진다.


잠금없는 일관된 읽기

read-uncommited, read-commited, repeatale-read 격리수준에서 select는 잠금을 대기하지 않고 바로 실행되는 것.

변경이 있을경우 언두로그를 사용




트랜잭션 격리수준

글로벌 락

Flush tables with read lock 명령으로만 획득 가능. 가장 범위가 큰 잠금.

다른 세션에서 select를 제외한 대부분의 ddl문장이나 dml문장 실행이 대기 상태가 됨.

Flush tables with read lock는 락을 걸기전 테이블을 flush해야하기 때문에 그 전의 모든 쿼리가 완료되어야 한다.

따라서 오래 걸리는 select쿼리가 있거나 하면 select 쿼리가 끝나기까지 기다리느라 insert,update,delete 가 실행되지 못하고 오래 대기하게 된다.

실시간 웹 서비스에선 사용해선 안됨


테이블락

개별 테이블 단위로 설정되는 락, 명시적 or 묵시적으로 락 획득 가능.

명시적, - LOCK TABLES table_name [READ|WRITE] 명령으로 획득

명시적 락의 경우 UNLOCK TABLES 명령으로 해제 해야 한다. 명시적 락은 특별한 경우 아니면 사용을 많이 하지 않은다.

비용이 비쌈.


묵시적 락의 경우 테이블을 변경하는 쿼리를 실행하면 발생. 변경되는 테이블에 락을 걸고 끝나면 즉사 해제한다.

다만 innoDB의 경우 스토리지 엔진 차원에서 레코드기반의 자금을 제공하기 때문에 묵시적인 테이블 락이 설정되지는 않는다.

정확하는 테이블락이 설정되지만 대부분의 DML에선 무시되고 DDL의 경우 영향을 끼친다.


유저 락

데이터베이스 객체에 걸리는게 아닌 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금.

많은 레코드를 한번에 변경하는 트랜잭션의 경우 유용하다 배치성 프로그램의 경우 데드락의 원인이 되곤 하는데 이때 유저락을 걸면 간단히 해결될 수 있다고...?


네임 락

데이터베이스 객체의 이름을 변경시 획득하는 락. 이름을 변경할때 묵시적으로 획득.


innoDB는 레코드 기반의 잠금을 지원. 하지만 어떤식으로 트랜잭션이 이루어지는 지 알기가 좀 힘듬

5.1 부터 information_schema 데이터베이스 안에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAIT 테이블을 조인하여 조회하면 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있다.

장시간 잠금상태인 클라이언트를 종료시킬 수도 있다.



Optimistic locking

각 트랜잭션이 같은 레코드를 변경할 가능성은 희박할 것이라고 가정.

우선 변경하고 마지막에 잠금 충돌이 있었는지 확인하고 문제가 있다면 롤백한다.


Pessimistic locking

현 트랜잭션에서 변경하고자 하는 레코드에 잠금을 획득하고 변경작업을 처리.

이 레코드를 다른 트랜잭션에서도 변경할 수 있다는 비관적 가정으로 먼저 락을 획득.

일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 알려져 있다.


innoDB의 락

레코드락, 레코드만을 잠그는 락. 하지만 이노디비 엔진은 인덱스 레코드를 잠근다.


갭락

레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것. 레코드 사이에 새로운 레코드가 생성되는 것을 제어. 자체적으로 사용하진 않음.


넥스트 키 락

statement 바이너리 로그를 사용시엔 repeatable read 격리수준 사용 필요.

innodb_locks_unsafe_for_binlog 파라미터가 비활성화시 넥스트 키 락 방식으로 락이 걸림.

바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적.

의외로 갭락과 넥스트키락으로 인해 데드락이 발생하는 경우가 많다.


자동 증가 락 auto increment lock

auto_increent컬럼 속성이 상ㅇ되었을때 발생하는 테이블 수준의 락

insert, replcae의 경우에만 필요.

명시적으로 획득할 순 없음.

5.1 부턴 방식 설정이 가능

연속(Consecutive mode)모드 대량 삽입시 한번에 여러개의 자동증가 값을 할당 받아서 사용. 이경우 중간에 누락되는 값이 발생함.

적어도 최소한 하나의 삽입 문장으로 삽입되는 레코드는 연속된 자동증가 값을 가져서 연속모드라고...


인터리빙 모드

유니크함 만 보장. 락을 걸지않음. 처리성능은 향상되나 복제시 마스터와 슬레이브의 자동증가 값이 달라질 수 있음.


innoDb의 레코드 락

인덱스 레코드락. 1개 컬럼에 인덱스가 되어있고 해당 컬림의 값이 A인게 30개 있다면 A수정시 30개 레코드가 락이 걸림

인덱스가 없으면 풀스캔으로모든 레코드가 잠김


Read Uncommitted

아직 커밋 되지 않은 데이터도 다른 트랜잭션이 읽어 갈수 있음. 롤백되면 없는 데이터를 읽는 더티리드가 발생.

데이터가 나타났다 사라졌다...


Read Committed

커밋 완료된 데이터만 다른 트랜잭션에서 조회가능. 가장 일반적. 더티리드가 없음

(언두 로그의 데이터를 읽어갈 순 있다)

논 리피터블 리드가 발생.= 하나의 트랜잭션 내에서 똑같은 select 쿼리 실행시 항상 같은 결과가 필요하다는 Repeatable Read를 위반

웹에서는 큰 문제가 없으나 하나의 트랜잭션 내에서 데이터를 여러번 읽고 변경하는 금전적인 경우 문제가 될 수 있다.


Repeatable Read

바이너리 로그 사용시 최소 격리 수준

MVCC를 위해 언두영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여준다.

이를 위해 이 격리 수준에서는 MVCC를 보장하기위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제 할 수 없다.

이 수준에서는 장시간 하나의 트랜잭션이 종료되지 않으면 언두 영역의 백업 데이터가 무한정 커질 수 있다

Select for Update로 조회시 중간에 다른 트랜잭션의 업데이트한 내용이 보일 수 있음. 이처럼 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는걸 팬텀 리드 라고 함.

select for update - select 하는 테이블에 쓰기 잠금을 거는 것.


Serializable

가장 엄격한 트랜잭션. 읽기도 모두 잠금을 획득해야 한다. 읽기 잠금시 다른 트랜잭션에서 레코드를 변경할 수 없다.



갭락과 넥스트 키락


https://idea-sketch.tistory.com/46




https://12bme.tistory.com/138

https://idea-sketch.tistory.com/45


'DATABASE' 카테고리의 다른 글

데드락  (0) 2018.12.22
[DB & JSP] 각 Database 별 커넥션 풀 설정(계속 업데이트)  (0) 2011.11.08