- 어떤 사람도 문제 없이 파일을 여는 것이 가능하고 나중에 수행된 쪽의 갱신이 반영된다는 경우를
Dirty Write라고 합니다. - 남보다 늦게 손을 내밀어 이기는 가위바위보 같은 것입니다. 데이터베이스에서 이런 식의 제어는 일반적으로 데이터 무결성 관점에서 기피하는 경향이 있습니다.
- Atomicity 원자성
- Consistency 일관성
- Isolation 고립성 또는 격리성
- Durability 지속성
Atomicity 원자성 이란 데이터의 변경을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조입니다.
데이터베이스에는 데이터베이스 오브젝트에 대한 각종 정합성 제약을 추가할 수 있습니다. 이는 이련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는, 즉 일관성을 유지하기 위한 구조입니다.
예를 들어 시스템에 사용자를 등록할 때 사용자를 임의로 식별하기 위해 일련번호를 사용자에게 발급합니다. 이때 이 번호 이 번호에 유니크 제약을 설정하면 중복된 사용자 번호를 지정할 수 없습니다.
일련의 데이터 조작을 복수 사용자가 동시에 실행해도 각각의 처리가 모순 없이 실행되는 것을 보증한다. 입니다.
- 현재 빈 싱글룸의 수를 확인한다. 10개 (select)
- 빈 싱글룸 수에서 1을 빼고 결과를 빈 싱글룸 수로 돌려 쓴다. 9개 (update)
- 이 요청을 A,B 가 동시에 할 경우 2명이 예약했지만 방은 1개 밖에 줄지 않았다.
이런 사태가 발생하는 것을 막기 위해 데이터베이스에는 데이터베이스 오브젝트인 테이블에 대해 Lock을 걸어서 후속 처리 블록 하는 방법이 있습니다.
장금 단위에는 테이블 전체, 블록, 행 등이 있는데 MySQL에서는 트랜잭션 처리를 할 때 주로 행 단위 잠금 기능을 이용합니다.
위 예제로 설명하면 (1) 현재 빈 싱글룸의 수를 확인한다을 처리할 때는 select for update 실행하면 select한 행에 잠금이 걸립니다. 이렇게 되면 후속 처리 해당 잠금이 해제될 때 commit or rollback 까지 대기하게 되며 올바른 처리를 계속할 수 있게 됩니다.
| 격리 수준 | 설명 |
|---|---|
| DEFAULT | DB 기본 격리 수준을 사용합니다. |
| READ_UNCOMMITTED | 다른 트랜잭션이 아직 커밋하지 않은 값을 한 트랜잭션이 읽을 수 있습니다. 따라서 오염된 읽기, 재현 불가한 읽기, 허상 읽기 문제가 발생할 가능성이 있습니다. |
| READ_COMMITTED | 한 트랜잭션이 다른 트랜잭션이 커밋한 값만 읽을수 있습니다. 이로써 오염된 값 읽기 문제는 해결되지만 재현 불가한 읽기, 허상 읽기 문제는 여전히 남습니다. |
| REPEATABLE_READ | 트랜잭션이 어떤 필드를 여러번 읽어도 동일한 값을 읽도록 보장합니다. 트랜잭션이 지속되는 동안 다른 트랜잭션이 해당 필드를 변경할수 없습니다. 오렴된 값 읽기, 재현 불가한 읽기 문제는 해결되지만 허상 읽기는 여전히 남습니다. |
| SERIALIZABLE | 트랜잭션이 테이블을 여러 번 읽어도 정확히 동일한 로우를 읽도록 보장합니다. 트랜잭션이 지속되는 동안에는 다른 트랜잭션이 해당 테이블에 삽입, 수정, 삭제를 할 수 없습니다. 동시성 문제는 모두 해소되지만 성능이 현저히 떨어집니다. |
�
| 현상 | 설명 |
|---|---|
| 더티 읽기(Dirty Read) | 어떤 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽는 현상이다. 예를들면 사용자 A가 값을 변경하고 아직 커밋하지 않아도 사용자 B가 변경한 후의 값을 읽는 것을 가리킨다. |
| 애매한 읽기/반복 불가능 읽기(Fuzzy/NonRepeatable Read) | 어떤 트랜잭션이 이전에 읽어 들인 데이터를 다시 읽어 들일 때 2회 이후의 결과가 1회 때와 다른 현상이다. 예들들면 최초에 사용자 A가 빈 싱글룸 수 10을릭고 그 후 사용자 B가 값을 9로 변경해 커밋했다고 하자. 계속해서 사용자 A가 SELECT를 다시 실행하면 최초에 SELECT한 결과는 9가 된다. 사용자 A가 최초에 읽은 값 10이 2회 이후에 SELECT에서 보증되지 못하고 애매하게 된다. |
| 팬덤 읽기(Phantom Read) | 어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상이다. 최초에 사용자 A가 범위 검색을 수행 후 3행을 읽었다고 가정 하자. 계속해서 사용자 B가 그 범위에 들어가는 데이터 1행을 INSERT하고 커밋도 실행했다. 계속해서 사용자 A는 다시 같은 SELECT 문을 실행하면 최초에 SELECT 문을 실행하면 최초 SELECT 3행이 아닌 4행이된다. 이처럼 나타거나 사라지는 데이터가 유령과 닮아서 붙여진 이름이다. |
지속성은 일련의 데이터 조작을 완료하고 완료 통지를 사용자가 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것을 나타냅니다.
MySQL(InnoDB형 테이블)은 현재 DBMS의 주류가 된 MVCC (Multi Versioning Concurrency Control)라는 기술을 사용하고 있습니다. 아래와 같은 특성을 중에 1, 4번이 중요합니다.
- 읽기를 수행할 경우 개인 중이더라도 블록되지 않는다(읽기와 읽기도 서로 블록되지 않는다.)
- 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.
- 갱신 시 배타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제 감금 하는 행의 범위가 다른경우가 있다.
- 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 블록된다. 일정 시간을 기다리며 그사이에 잠금을 획득할 수 없는 경우
Lock Timeout이 발생한다. - 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로
롤백 세그먼트라는 영역에 유지한다.
Mysql Default 격리 수준입니다. 반복 읽기는 최초 쿼리를 실행한 시점에 커밋된 데이터를 읽어 들입니다. 이 시점에서 READ_COMMITTED와 같습니다.
다른 점은 같은 쿼리를 복수 회 실행하면 최초 읽은 내용의 결과 세트가 반환됩니다. 복수 회의 쿼리 실행 사이에 다른 트랜잭션이 커밋했어도 그 내용은 반영되지 않습니다.
select * from t1 from i1 = 1; -- 의 결과는 1과 'MySQL'
select * from t1 from i1 = 1; -- 의 결과도 1과 'MySQL'커밋된 읽기는 쿼리를 실행한 시점에서 커밋된 데이터를 읽어 들입니다. 같은 쿼리를 복수 회 실행하면 그사이에 다른 트랜잭션에서 커밋할 때가 있는데, 이 경우 최신 쿼리의 실행 개시 시점에서 커밋된 데이터를 읽습니다.
select * from t1 from i1 = 1; -- 의 결과는 1과 'MySQL'
select * from t1 from i1 = 1; -- 의 결과도 1과 'PostgreSQL'갱신과 참조는 서로를 블록하지 않지만, 갱신과 갱신이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 됩니다. 잠금을 건 쪽이 언제 잠금이 풀지 알 수 없어서 잠금 해제를 기다리고 있는 쪽에서는 잠금을 기다리거나 기다리지 않거나, 기다린다면 어느정도 기달릴지(Time out을 지정하거나 무한으로 대기 하거나)설정 할 수 있습니다.
MySQL에서는 innodb_lock_wait_timeout 이라는 시스템 변수로 다음과 같이 설정 할 수 있습니다.
mysql> set innodb_lock_wait_timeout = 1;
이때 잠금 대기 타임아웃이 발생하는 경우 DBMS로부터 롤백되는 단위가 다를 때가 있는데, 해당 트랜잭션 전체를 롤백하는 경우와 쿼리만 롤백하는 것입니다. MySQL에서 잠금 대기로 타임아웃이 발생한다면 롤백되는 것을 기본으로 오류가 발생한 쿼리입니다.
트랜잭션 전체를 롤백하고 싶다면 다음 방법으로 할 수 있습니다.
- 타임아웃 오류 후 명시족으로 ROLLBACK을 실행한다.
- innodb_lock_wait_timeout 시스템 변수를 설정한다.
예를들어, 트랜잭션 A가 테이블 a의 잠금을 얻고 트랜잭션 B가 테이블의 b의 잠금을 얻었다고 가정했을 경우
이 잠금을 유지한 채 서로 잠금을 건 자원에 접근이 필요한 처리 (INSERT/UPDATE/DELETE)를 실행하면 아무리 기다려도 살황이 바뀌자 않는 상태가 됩니다.
잠금 타임아웃은 일정 시간 기다리면 상황이 개선될 가능성이 있지만, 교착 상태는 상황이 개선될 가능성이 없습니다. 이 때문에 일반적인 DBMS에서는 교착 상태를 독자적으로 검출해 교착 상태를 보고합니다.
MySQL도 교착 생태가 일어나면 이를 즉시 인식해 시스템에 영향이 작은 쪽의 트랜잭션을 트랜잭션 개시 시점까지 롤백합니다.
교착 상태는 일반적인 데이터베이스에서 발생할 가능성이 있으므로 모든 것을 업얠 수는 없습니다. 따라서 애플리케이션 쪽에서는 항상 트랜잭션이 교착 상태를 일으켜 롤백되는 경우 트랜잭션을 재식핼 할 수 있는 구조로 만들어야 합니다.
- 트랜잭션을 자주 커밋한다. 이에 따라 트랜잭션은 더 작은 단위가 되어 교착 상태의 가능성을 낮춘다.
- 정해진 순서로 테이블 순서로 엑세스하게 한다.
- 필요 없는 경우에는 읽기 잠금 획득 (SELECT ~ FOR UPDATE 등)의 사용을 피한다.
- 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 한다.(Isolation Level을 낮춘다.)
- 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착 상태가 발생한다. 동시에 많은 연결에서 갱신 때문에 교착 상태가 자주 발생한다면 테이블 단위의 잠금을 회득해 갱신을 직렬화하면 동시선은 떨어지지만 교착 상태는 피할 수 있어서 전체 처리로 보면 좋은 예도 있다.
- 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용하지 않은 경우 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸리게 된다.
MySQL에서는 새로운 연결은 모두 기본값으로 오토커밋이 실행됩니다. 애플리케이션의 잠금을 실행하는 데는 커밋의 부하가 너무 높습니다. 일정 수 이상의 갱신을 수행하는 처리나 트랜잭션의 기능등은 적절한 단위와 트랜잭션 처리 수준에서 트랜잭션을 이용하지 않도록 합니디.
긴 트랜잭션은 데이터베이스 트랜잭션의 동시성이나 자원의 유효성을 저하합니다. 갱신을 포함한 트랜잭션은 같은 테이블과 행을 갱신하려는 다른 트랜잭션을 블록하고 이것이 장시간 이어지면 블록된 트랜잭션을 타임아웃시킵니다.
- 데이터베이스는 해당 SQL 문으로부터 어떤 계획으로 데이ㅌ에 도달할지를 결정합니다. 이 계획을 실행 계획(Execution Plan) 또는 액세스 플랜 이라고 합니다.
- 이 실행 계획을 결정하는 내부 프로그램을
옵티마이저라고 합니다.
쿼리 최적화 프로그램은 일반적으로 쿼리에 대한 최상의 실행 계획을 선택하므로 숙력된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다.
실제로 대부분 경우 옵티마이저는 바른 판단을 합니다. 100%는 아니지만 옵티마이저가 제대로 동작할 수 있는 환경을 갖추는게 데이터베이스 사용자로서 올바른 태도입니다.
- 테이블의 행수, 열수
- 각 열의 길이와 데이터형
- 테이블의 크기
- 열에 대한 기본기나 NOT NULL 제약의 정보
- 열 값의 분산과 편향
- 레인지 스캔을 실행하려면 인덱스가 꼭 필요합니다. 적절한 인덱스가 없다면 데이터베이스는 어쩔 수 없이 풀 스캔을 해야만 합니다.
인덱스를 사용하지만 풀 스캔이 빠르다고옵티마이저에서 판단하면 인덱스를 사용하지 않은 경우도 있습니다. 이 또한 옵티마이저의 일입니다.
- SQL 문을 변경하지 않아도 성능을 개선할 수 있다.
- 테이블의 데이터에 영향을 주지 않는다.
- 일정한 효과를 기대할 수 있다.
일반적으로 B-tree 구조를 갖습니다. B-tree는 관계형 데이터베이스에서 튜닝의 기본이 되는 인덱스입니다.
B-tree는 반드시 데이터를 정렬된 상태로 유지하고 있습니다. 즉 순서를 유지하고 있는 것이 B-tree의 핵심입니다.
- 인덱스 갱신의 오버헤드로 걍신 처리의 성능이 떨어진다.
- 인덱스는 테이블에 새로운 데이터 추가되거나 기존의 데이터에 대해 갱신, 제거가 실행되면 자동으로 인덱스 자신도 갱신하는 기능을 갖추고 있습니다.
- SELECT 문을 고속화할 수 있지만 INSERT, UPDATE 같은 갱신 SQL의 성능은 낮아집니다.
- 의도한 것과 다른 인덱스가 사용된다.
- 한 개의 테이블에 복수의 인덱스를 작성한 경우 발생하는 문제입니다.
- 옵티마이저도 만능은 아니기 때문에 사용할 수 있는 인덱스 후보가 많으면 옵티마이저도 헤매게 됩니다.
-
크기가 큰 테이블에만 만든다.
- 크키가 작은 테이블에는 애초에 인덱스도 풀 스캔도 큰 차이가 없습니다. 따라서 작은 테이블은 고려 대상으로 삼을 필요 가 없습니다.
-
기본키 제약이나, 유일성 제약이 부여된 열에는 불필요하다
- 기본키, 유일성에는 인덱스가 자동으로 생성됩니다.
-
Cardinality 높은 열에 만든다.
- 운전면허증 번호 >>>>> 넘을 수없는 벽 >>> 행정구역 > 성별
- 위 처럼 운전면허는 Cardinality 높습니다. 이 런곳에 인덱스를 거는것이 바람직합니다. 반대로 낮은 곳에는 인덱스의 장점을 그다지 받지 못하게 됩니다.