트랜잭션의 격리수준이란
여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지 결정하는 것
- DIRTY READ가 발생하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 거의 사용하지 않고, SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 거의 사용되지 않는다.
- 4개의 격리 수준에서 순서대로 뒤로 갈수록 각 트랜잭션 간의 데이터 격리(고립) 정도가 높아지며, 동시 처리 성능도 떨어지는 것이 일반적이라고 볼 수 있다.
- 격리 수준이 높아질수록 MySQL 서버 처리 성능이 많이 떨어질 것으로 생각하는 사용자가 많은데, 사실 SERIALIZABLE 격리 수준이 아니라면 크게 성능의 개선이나 저하는 발생하지 않는다.
- 일반적인 온라인 서비스 용도의 데이터베이스는 READ COMMITED와 REPEATABLE READ 중 하나를 사용한다. 오라클 같은 DBMS에서는 주로 READ COMMITTED 수준을 많이 사용하며, MySQL에서는 REPEATABLE READ를 주로 사용한다.
READ UNCOMMITED
- 각 트랜잭션의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
- RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다. Mysql을 사용한다면 최소 READ COMMITTED 이상의 격리 수준을 사용할 것을 권장한다.
> Dirty Read : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상
- 사용자 A는 emp_no 가 500000이고 first_name 이 “Lara”인 새로운 사원 INSERT
- 사용자 B가 변경된 내용 커밋하기도 전에 emp_no 가 500000인 사원 검색한다. 이때 사용자A가 커밋하지 않더라도 조회한다.
- 그런데 사용자A에게 문제가 생겨서 롤백한다고 하더라도 여전히 사용자 B는 “Lara”가 정상적인 사원이라고 생각하고 계속 처리할 것이다.
READ COMMITED
- 오라클 DBMS에서 기본으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.
- 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT 이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
- 사용자 A는 emp_no 가 500000인 사원의 first_name 을 “Lara”에서 “Toto”로 변경했다. 이 때 새로운 값인 “Toto”는 employees 테이블에 즉시 기록되고 이전 값인 “Lara”는 un-do 영역으로 백업된다.
- 사용자 A가 커밋을 수행하기 전에 사용자 B가 emp_no 가 500000인 사원을 SELECT 하면 employees 테이블이 아니라 un-do 영역에 백업된 레코드에서 가져온다.
- 최종적으로 사용자 A가 변경된 내용을 커밋하면 그때부터 다른 트랜잭션에서 새롭게 변경된 “Toto”라는 값을 참조할 수 있게 된다.
> Repeatable Read : 사용자 B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을때 항상 같은 결과를 가져와야 한다
- 처음 사용자 B가 BEGIN 명령으로 트랜잭션을 시작하고 fisrt_name 이 “Toto”인 사용자를 검색했는데, 일치하는 결과 없다.
- 하지만 사용자 A가 emp_no 가 500000인 사원의 이름을 “Toto”로 변경하고 커밋 실행한 후, 사용자 B가 똑같은 SELECT 쿼리로 다시 조회하면 이번에는 1건이 조회한다.
REPEATABLE READ
- REPEATABLE READ 는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다.
cf) 모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가진다. un-do 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 un-do 영역의 백업 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
- REPEATABLE READ 는 이 MVCC를 위해 언두 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있게 보장한다.
> 이는 READ UNCOMMITTED 과도 비슷한데, 차이는 un-do 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.
> REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. ⇒ 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.
- employees 테이블에 번호가 6인 트랜잭션에 의해 INSERT 가 되었다고 가정.
- 사용자 A의 트랜잭션 번호는 12였고 사용자 B의 트랜잭션의 번호는 10이었다.
- 이때 사용자A는 사원의 이름을 “Toto”로 변경하고 커밋을 수행한다.
- 그런데 사용자 A의 트랜잭션의 변경 전후 각각 한 번씩 SELECT 했는데 결과는 항상 “Lara”라는 값을 가져온다.
- 사용자B가 BEGIN 명령으로 트랜잭션을 시작하면서 10번이라는 트랜잭션 번호를 부여받았는데, 그때부터 사용자 B의 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게된다.
> Phantom Read : 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안 보였다 하는 현상
- 사용자 A가 employees 테이블에 INSERT 를 실행하는 도중에 사용자 B가 SELECT ... FOR UPDATE 쿼리로 테이블을 조회했을때 결과가 다르다.
- SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에 잠금을 걸 수 없다. 그래서 un-do 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다.
SERIALIZABLE
- 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.
- 트랜잭션의 격리 수준이 SERIALIZABLE 로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.
cf) InnoDB 스트로지 엔진에서는 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 PHANTOM READ 가 발생하지 않기 때문에 굳이 SERIALIZABLE 을 사용할 필요성은 없어 보인다. 엄밀하게는 SELECT ... FOR UPDATE 또는 SELECT ... FOR SHARE 쿼리의 경우 REPEATABLE READ 격리 수준에서 PHANTOM READ 가 발생할 수 있다. 하지만 un-do영역에 잠금을 걸수 없기 때문에, 예외 상황이라고 볼 수 있다.
http://www.yes24.com/Product/Goods/6960931
위 책을 읽고 정리한 글입니다.
Real MySQL - YES24
Real MySQL, MySQL의 새로운 발견!더 이상 MySQL은 커뮤니티나 소셜 네트워크 서비스와 떼어놓을 수 없는 관계에 있다는 것은 누구나 잘 알고 있을 것이다. 하지만 MySQL은 여기서 그치지 않고 빌링이나
www.yes24.com
'개발 > 데이터베이스' 카테고리의 다른 글
| [mysql] replica 서버 구축하는 목적, 복제방법 w.바이너리 로그 (1) | 2023.07.29 |
|---|---|
| database. 클러스티드 인덱스/논 클러스티드 인덱스 (0) | 2022.05.09 |
| mysql. timestampdiff (0) | 2022.04.21 |
| mysql. create table 속성 정리 (0) | 2022.03.08 |
| mysql. 컬럼명 조회 (0) | 2021.09.07 |