1. Transaction 트랜잭션?
- 작업의
완전성
을 보장해주는 것- 논리적인 작업 셋을
모두 완벽하게 처리
하거나, 처리하지 못할 경우에는원 상태로 복구
해서작업의 일부만 적용되는 현상(Partial update)
이 발생하지 않게 만들어주는 기능
잠금(Lock)
과 트랜잭션
은 서로 비슷한 개념 같지만, 사실 잠금은 동시성
을 제어하기 위한 기능이고 트랜잭션은 *데이터의 정합성
을 보장하기 위한 기능이다.
하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 여러 커넥션에서 동시에 변경할 수 있게 된다. 이는 결과적으로 해당 레코드의 값을 예측할 수 없는 상태가 된다.
트랜잭션 관점에서의 InnoDB
& MyISAM
엔진의 차이를 살펴보기 위해 예제를 만들었다.
1) MyISAM 엔진의 경우
CREATE TABLE tab_myisam (test INT NOT NULL, PRIMARY KEY (test)) ENGINE = MyISAM;
INSERT INTO tab_myisam (test) VALUES (3);
2) InnoDB 엔진의 경우
CREATE TABLE tab_innodb (test INT NOT NULL, PRIMARY KEY (test)) ENGINE = INNODB;
INSERT INTO tab_innodb (test) VALUES (3);
위와 같이 테스트용 테이블에 각각 레코드를 1개씩 저장한 후, AUTO-COMMIT
모드에서 다음 쿼리 문장을 InnoDB 테이블과 MyISAM 테이블에서 각각 실행해 보았다.
이때, 아래와 같이 AUTO-COMMIT
모드는 디폴트가 ON
상태이다.
SHOW VARIABLES LIKE 'autocommit'; 을 사용하여 확인할수도 있다.
두 개의 스토리지 엔진에서의 결과이다.
처음부터 살펴보면, 두 INSERT 문장 모두 프라이머리 키 중복 오류로 쿼리가 실패
했다.
그런데, 두 테이블의 레코드를 조회해보면 차이점
이 있다.
두 테이블의 차이점
1) MyISAM 테이블
- 오류가 발생했음에도 불구하고
'1'
과'2'
는INSERT
된 상태로 남아 있음.- INSERT 문장이 실행되면서 차례대로
'1'
과'2'
를저장
하고 그 다음'3'
을 저장하려고 하는 순간중복 키 오류가 발생
한 것.2) InnoDB 테이블
- 이미
INSERT
된'1'
과'2'
를 그대로 두고쿼리 실행을 종료
함.
InnoDB
는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구
한다.
MyISAM
테이블에서 발생하는 이러한 현상을 부분 업데이트 (Partial Update)
라고 표현하며 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 어려운 문제를 만들어 낸다.
트랜잭션은 꼭 필요한 최소의 코드
에만 적용하는 것이 좋다.
(프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미)
2. Lock 잠금?
- 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우
순서대로
한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할- 크게
스토리지 엔진 레벨
&MySQL 엔진 레벨
로 나눌 수 있음
MySQL 서버에서 스토리지 엔진을 제외한 나머지 부분.
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않음
명시적 | 묵시적
으로 특정 테이블의 락을 획득할 수 있음명시적 특정 테이블 락
LOCK TABLES table_name [ READ | WRITE ] // 해당 명령으로 특정 테이블의 락을 획득할 수 있음 UNLOCK TABLES // 해당 명령으로 잠금을 해제 할 수 있음
하지만 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 애플리케이션에서 사용할 필요가 거의 없다.
묵시적 테이블 락
- MyISAM | MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생.
- MySQL 서버가 데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용됨.
- 쿼리가 실행되는 동안
자동으로 획득
됐다가 쿼리가 완료된 후자동 해제
됨
GET_LOCK()
함수를 이용해 임의의 문자열
에 대해 잠금을 설정할 수 있다.mysql> SELECT GET_LOCK('string', time);
// mylock 이라는 문자열에 대해 잠금을 획득한다.
// 이미 잠금을 사용 중이면 2초 동안만 대기한다. (2초 이후 자동 잠금 해제)
mysql> SELECT GET_LOCK('mylock', 2);
// mylock 이라는 문자열에 대해 잠금이 설정돼 있는지 확인한다
mysql> SELECT IS_FREE_LOCK('mylock');
// mylock 이라는 문자열에 대해 획득했던 잠금은 해제한다.
mysql> SELECT RELEASE_LOCK('mylock');
mysql> SELECT RELEASE_ALL_LOCKS(); // 2개 이상을 동시에 해제
// 위의 3개 함수 모두 정상적으로 락을 획득하거나 해제한 경우에는 1, 그렇지않으면 NULL 이나 0을 반환함
특징
- 대상
이 테이블이나 레코드 또는 AUTO_INCREMENT
와 같은 데이터베이스 객체가 아님
- 단순히 사용자가 지정한 문자열(String)
에 대해 획득하고 해제하는 잠금.
자주 사용되지는 않음
구조
를 잠금변경
하는 경우에 획득하는 잠금자동
으로 획득하는 잠금RENAME TABLE A TO B
명령의 경우 원본 이름
& 변경될 이름
모두 한꺼번에 잠금3. Isolation Level 격리 수준?
- 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게
공유
하고차단
할 것인지를 결정하는 레벨을 의미- 여러 트랜잭션이
동시
에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서변경
하거나조회
하는 데이터를 볼 수 있게 허용할지 말지를결정
하는 것
COMMIT
이나 ROLLBACK
여부에 상관 없이 다른 트랜잭션에서 보임더티 리드
현상이 발생하지 않음COMMIT
이 완료
된 데이터만 다른 트랜잭션에서 조회 가능REPEATABLE READ
가 불가능하다는 NON-REPEATABLE READ
부정합의 문제 존재SELECT
쿼리를 실행했을 때는 항상 같은 결과를 가져와야 함NON-REPEATABLE READ
부정합이 발생하지 않음ROLLBACK
될 가능성에 대비해 변경되기 전 레코드를 언두(Undo) 공간
에 백업
해두고 실제 레코드 값을 변경함 (MVCC방식)동일한 결과
를 보여줄 수 있음MVCC
를 보장하기 위해, 실행 중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터를 삭제할 수 없음
PHANTOM READ
현상 발생하는 단점 존재 -> 쓰기 잠금을 걸어 방지
해야 함.READ COMMITTED & REPEATABLE READ 의 공통점과 차이점
공통점
MVCC
를 이용해COMMIT
되기 이전의 데이터를 보여줌차이점
언두 영역
에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가느냐?
모든 InnoDB
의 트랜잭션은 고유한 트랜잭션 번호
를 가진다. (순차적으로 증가하는 값)
언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함
된다.
언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요
하다고 판단하는 시점에 주기적으로 삭제
한다.
위의 4가지 격리 수준
은 순서대로, 뒤로 갈수록 각 트랜잭션 간의 데이터 격리 정도가 높아지고 동시 처리 성능도 떨어지는 것이 일반적이다.