들어가며
트랜잭션을 말할 때 ACID 얘기를 하지 않을수가 없죠.
개인적으로 ACID의 다른 어떤 요소보다도 가장 중요한게 Isolation이 아닐까 하는데요.Isolation을 이해해야 비로소 트랜잭션에 대해 말 할 수 있다고 생각합니다.
Isolation을 처음 이해하고 설레였던(!) 기억이 있네요.
이 문서를 통해 MySQL InnoDB 엔진과 함께 격리 수준을 달리 하며, 각 수준에서 볼 수 있는 현상을 확인하는 hands-on을 진행합니다.
Transaction Isolation Level
트랜잭션 개념이 잘 정리된 자료는 어렵지 않게 찾아볼 수 있는데요.
개념과 특징에 대해 나열하는 것이 이 문서의 목적은 아니기에, 간단히만 다루고 넘어가겠습니다.
Transaction Isolation Level
- 트랜잭션의 격리 수준
- 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션의 영향을 받는 수준을 결정
READ UNCOMMITEDREAD COMMITED (DIRTY READ)REPEATABLE READSERIALIZABLE
READ UNCOMMITED에서SERIALIZABLE로 갈 수록 높은 수준의 격리- 데이터베이스 격리 수준을 이야기할 때, 부정합 현상이 나타나는 몇가지 문제가 있음
- 일반적으로 3개 정도 다룸
DIRTY READ,NON_REPEATABLE READ,PHANTOM READ
Hands-on
트랜잭션 격리 수준을 바꿔 가며, 각 격리 수준에서 볼 수 있는 현상을 확인합니다.
테스트 진행을 위해 테이블과, 몇 개의 레코드를 생성합니다.
CREATE TABLE ENGINEERS (
id INT AUTO_INCREMENT,
firstName VARCHAR(50),
lastName VARCHAR(50),
email VARCHAR(100),
specialty VARCHAR(100),
yearsExperience INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
INSERT INTO ENGINEERS (firstName, lastName, email, specialty, yearsExperience)
VALUES ('jude', 'jahng', 'jude.jahng@kakaoeneterprise.com', 'Database Engineering', 0),
('jay', 'jahng', 'wkdwoos@gmail.com', 'Software Engineer', 2),
('jaeyoung', 'jahng', 'wkdcloud@gmail.com', 'Cloud Engineering', 0);쿼리를 통해 생성한 테스트 테이블은 다음과 같은 모습입니다.
READ UNCOMMITED
첫 번째 격리 수준은 READ UNCOMMITED 입니다.
다른 트랜잭션에서 커밋되지 않은 데이터도 조회할 수 있는 가장 낮은 격리 수준인데요.
높은 동시성을 보장하지만, 반면 가장 낮은 격리 수준을 제공합니다.
핸즈온을 통해 직접 확인해보겠습니다.
MySQL 프로세스를 올리고, 두 개 세션에 접근합니다.
화면에서 위의 세션을 세션 1, 아래 세션을 세션 2 라고 하겠습니다.
mysql> SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
Query OK, 0 rows affected (0.00 sec)세션 2에서 조회를 하겠습니다. 세션 2의 트랜잭션 격리 수준을 READ-UNCOMMITED 로 설정합니다.
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE ENGINEERS
-> SET yearsExperience = 1
-> WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0세션 1에서 트랜잭션을 시작하고, ENGINEERS 테이블의 id = 1 레코드의 yearsExperiences 값을 업데이트합니다.
세션 2에서 조회했을 때, 해당 레코드가 변경된 것을 확인할 수 있습니다.
이때 트랜잭션은 아직 UNCOMMITED 상태입니다.
세션 1에서 트랜잭션을 COMMIT 하지 않고 ABORT(ROLLBACK) 하면,
업데이트한 레코드가 다시 이전으로 돌아간 것을 확인할 수 있습니다.
세션 2에서 이를 확인 하면,
여기서도 업데이트한 레코드의 yearsExperience 컬럼의 값이 다시 0으로 조회되는 것을 확인할 수 있습니다.
READ UNCOMMITED 격리 수준에서 볼 수 있는 DIRTY READ 현상이 바로 이것입니다.READ UNCOMMITED, 말 그대로 커밋되지 않은 트랜잭션에 대해 조회할 수 있는 수준의 격리가 되겠습니다.
READ COMMITED
커밋된 결과에 대해서만 조회할 수 있는 격리 수준으로,
Oracle DBMS의 default이며, OLTP에서 가장 많이 사용되는 격리 수준입니다.
mysql> SET @@session.transaction_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)조회 역할의 세션 2의 트랜잭션 격리 수준을 READ-COMMITED 로 설정합니다.
start TRANSACTION;
UPDATE ENGINEERS SET specialty = 'DevOps Engineer' WHERE id = 2;
SELECT e.id, e.firstName, e.lastName, e.specialty FROM ENGINEERS e WHERE e.id = 2;
세션 1에서 트랜잭션을 시작하고, id = 2 인 레코드를 변경합니다. 아직 트랜잭션은 UNCOMMITTED 입니다.
세션 2에서 해당 테이블을 조회했지만, 세션은 격리 수준을 READ-COMMITTED 로 설정했기 때문에 UNCOMMITTED 인 변경 사항을 조회할 수 없습니다.
이제 이 상황에서 세션 1에서 진행중인 트랜잭션을 COMMIT 하면,
COMMIT된 변경사항을 세션 2에서 확인할 수 있습니다.
NON REATABLE READ
앞서 READ COMMITED 수준의 격리는 NON-REPEATABLE READ 현상이 나타날 수 있음을 이야기 했는데요,
이번에는 NON-REPEATABLE READ 를 확인해 보겠습니다.
이번에도 서로 다른 두 개의 트랜잭션을 시작합니다.
현재 병렬되는 두 개 트랜잭션이 보고 있는 테이블의 상태는 다음과 같습니다.
이번에도 위의 세션을 세션 1 아래의 세션을 세션 2 라고 하겠습니다.
세션 2에서 id = 2 인 레코드의 현재 specialty의 값은 ‘DevOps Engineer’ 입니다.
UPDATE ENGINEERS SET specialty = 'Software Engineer' WHERE id = 2;
SELECT e.id, e.firstName, e.lastName, e.specialty FROM ENGINEERS e WHERE e.id = 2;
COMMIT;
세션 1에서 id = 2인 ROW의 specialty 컬럼 값을 ‘Software Engineer’ 로 변경하고, COMMIT 합니다.
그 다음, 세션 2에서 id = 2 인 레코드를 조회하면,
SELECT e.id, e.firstName, e.lastName, e.specialty FROM ENGINEERS e WHERE e.id = 2;
specialty의 값이 ‘Software Engineer'로 변경되어 있음을 확인할 수 있습니다.
이처럼 하나의 트랜잭션에서 동일한 테이블에 대해 반복하여(REPEAT) 조회 했을 때, 이전 조회와 결과가 서로 다른 상황,
반복 가능하지 않은 조회 NON-REPEATABLE READ 가 나타난 것을 확인할 수 있습니다.
REPEATABLE READ
REPEATABLE READ 는 MySQL InnoDB 스토리지 엔진이 default로 사용하고 있는 격리 수준입니다.
binlog가 있는 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 하는데요,
REPEATABLE READ는 말 그대로 반복 가능한 읽기를 보장하여, NON REPEATABLE READ 현상이 발생하지 않습니다.
InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK 될 가능성에 대비해 변경 전 레코드를 언두 로그 형태로 언두 테이블 스페이스(이하 언두 영역)에 백업해두고, 실제 레코드를 변경하여 MVCC를 보장합니다.
당연히 이 undo tablespace가 무한하진 않구요. 이 또한 엔지니어, 운영자의 관리 영역이 되겠습니다.
REPEATABLE READ는 언두 영역에 백업된 이전 데이터를 이용하여 MVCC를 제공하고, 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장합니다.
이제 REPETABLE READ 핸즈온을 진행해 보겠습니다.
mysql> SET @@session.transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)조회 용도 세션 2의 격리 수준을 REPEATABLE-READ 로 설정합니다.
이번에도 역시 서로 다른 두 개의 트랜잭션을 진행합니다.
현재 두 트랜잭션이 보고 있는 테이블의 상태는 다음과 같습니다.
이번에도 세션 1, 세션 2입니다.
세션 2에서 id = 2 인 레코드의 현재 specialty의 값은 ‘Software Engineer’ 입니다.
사실 핸즈온 도중 오타를(!) 냈는데요
이 값을 'Software Engineering' (!) 으로 업데이트 하겠습니다.
UPDATE ENGINEERS SET specialty = 'Software Engineering' WHERE id = 2;
COMMIT;
SELECT e.id, e.firstName, e.lastName, e.specialty FROM ENGINEERS e WHERE e.id = 2;세션 1에서 트랜잭션을 시작하고, 값을 변경합니다. 그리고 커밋을 찍습니다.
세션 1의 테이블 조회 결과는 당연히
레코드가 업데이트 된 것을 확인할 수 있네요.
그렇다면 세션 2에서는 어떨까요?
id = 2 인 레코드의 specialty 값이 이전 조회와 동일한 값(Software Engineer)으로 조회되었습니다.
REPEATABLE READ 가 보장된 것을 확인할 수 있네요.
지금까지 이야기를 종합해보면,
MySQL InnoDB 스토리지 엔진이 채택한 REPEATABLE READ는 DIRTY READ, NON-REPEATABLE READ 가 발생하지 않는, 언뜻 완벽한 격리 수준인 것 처럼 보입니다.
그렇다면 세션 2가 지금 진행중인 트랜잭션이 종료된 이후에는 어떨까요?
트랜잭션이 종료되는 데는 두 가지 경우가 있습니다.
트랜잭션 COMMIT트랜잭션 ABORT(ROLLBACK)
ABORT(ROLLBACK)의 경우에는 트랜잭션이 테이블에 영향을 미치지 않기 때문에(정확히 말하자면, 변경사항이 없음),
우리가 주목해야 할 케이스는 아닐 것 같습니다.
그러나 테이블 변경사항이 적용되는 COMMIT 은 이야기가 다르지 않을까요.
다시 화면으로 돌아가 보겠습니다.
이 상황에서 세션 2에서 id = 2인 레코드의 ‘firstName’ 컬럼의 값을 변경하고, 조회해보겠습니다.
어떤 결과를 가져올까요.
UPDATE ENGINEERS SET firstName = 'GERRARD' WHERE id = 2;
SELECT e.id, e.firstName, e.lastName, e.specialty FROM ENGINEERS e WHERE e.id = 2;
우리는 세션 2가 현재 진행중인 트랜잭션에서, id = 2인 레코드에 대해 firstName 컬럼의 값만 변경하고(‘jay’ -> ‘GERRARD’) , specialty 컬럼의 값은 변경하지 않았습니다.
그러나 specialty의 값이 트랜잭션 전과 후가 다르게 조회되었네요.(‘Software Engineer’ -> ‘Software Eingeering’)
이러면 안되는거 아닐까요? 왜 그럴까요?
다른 트랜잭션에서 수행한 변경 작업에 의해,
'진행중인 동일한 트랜잭션 내에서' 같은 쿼리에 대해 레코드가 보이거나, 안보이거나, 또는 값이 달라지는 이상 조회 현상,
PHANTOM READ가 발생한 것입니다.
세션 2의 트랜잭션에서
UPDATE ENGINEERS SET firstName = 'GERRARD' WHERE id = 2;쿼리는 ENGINEERS 테이블의 id = 2 레코드의 LOCK을 가져가지만, 언두 레코드에는 LOCK을 걸 수 없습니다.
때문에 세션 2는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라, 현재(latest) 언두 레코드의 값을 가져오게 되는 것입니다.
SERIALIZABLE
SERIALIZABLE 은 크게 설명할 것이 없습니다. 모든 작업이 잠금, 락 Lock을 가져가며, 순차적으로 진행하는데요.
한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없습니다.
읽기 작업도 공유 잠금(읽기 잠금)을 획득해야 하며, 가장 단순하면서도 엄격한 격리 수준이 되겠습니다.
그러나 성능이 매우 좋지 않아 거의 사용하지 않습니다.
결론
MySQL InnoDB 엔진과 함께 격리 수준을 달리 하며, 각 격리 수준에서 볼 수 있는 현상을 핸즈온을 통해 확인했습니다.
