[MySQL] Transaction Isolation Level

Hocaron·2022년 4월 3일

DB

목록 보기

5/16

트랙잭션?

데이터베이스의 어떤 변경을 유발하는 작업 단위를 위미한다.
착각하지 말아야 할 것은, 작업의 단위는 질의어 한문장이 아니라는 점이다.
사용자에 의해 실행된 SQL 문의 집합을 의미하며, 변경된 데이터는 TCL에 의해 데이터베이스에 반영되어야 한다는 것을 의미한다.

START TRANSACTION;
# 쿼리
COMMIT;

#또는

ROLLBACK;

트랜잭션의 특징(ACID)

ACID란?

데이터베이스 트랜잭션이 안전하게 수행된다는 것을 보장하기 위한 성질을 가르키는 약어이다.

Atomicity(원자성)

: 트랜잭션은 DB에 모두 반영되거나, 전혀 반영되지 않아야 한다.
(= 완료되지 않은 트랜잭션의 중간 상태를 DB에 반영해서는 안된다.)

Consistency(일관성)

: 트랜잭션 작업처리결과는 항상 일관성 있어야 한다.
(= 데이터베이스는 항상 일관된 상태로 유지되어야 한다.)

Isolation(격리성, 독립성)

: 둘 이상의 트랜잭션이 동시 실행되고 있을 때, 어떤 트랜잭션도 다른 트랜잭션 연산에 끼어들 수 없다.
(= 각각의 트랜잭션은 서로 간섭 없이 독립적으로 이뤄져야한다.)

Durability(지속성)

: 트랜잭션이 성공적으로 완료되었으면 결과는 영구 반영되어야 한다.

트랜잭션 격리 레벨

동시에 DB에 접근할 때 그 접근을 어떻게 제어할지에 대한 설정이다.
⬆ 동시성 데이터 정합성 ⬇
- READ-UNCOMMITTED
- READ-COMMITED
- REPEATABLE-READ
- SERIALIZABLE
밑으로 갈수록 격리 수준이 높아지지만 성능이 떨어진다.
데이터 정합성과 성능은 반비례 관계이다.

트랜잭션 격리 레벨에 따라 발생할 수 있는 문제

READ-UNCOMMITTED

SELECT 문이 수행되는 동안 해당 데이터에 Shared Lock이 걸리지 않는 레벨
트랜잭션에 처리중이거나, 아직 commit되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.
Dirty Read 발생
- A 트랜잭션에서 1번의 사용자의 나이를 27에서 28로 변경했고, 아직 커밋은 하지 않음.
- B 트랜잭션에서 1번 사용자의 나이를 조회 : 결과 28
- A 트랜잭션에서 문제가 생겨, Rollback함
- B 트랜잭션은 1번 사용자의 나이가 여전히 28이라고 생각하고 로직을 수행함.

READ-COMMITED

SELECT 문이 수행되는 동안 해당 데이터에 Shared Lock이 걸리는 레벨
트랜잭션이 수행되는 동안 다른 트랜잭션이 접근할 수 없어 대기한다.
Non-Repeatble Read 발생
한 트랜잭션 내에서 값을 두번 읽어 봤는데, 값이 서로 다를 때
- B 트랜잭션에서 1번 사용자의 나이를 조회 : 결과 27
- A 트랜잭션에서 1번 사용자의 나이를 27에서 28로 바꾸고 커밋
- B 트랜잭션에서 1번 사용자의 나이를 조회 : 결과 28

REPEATABLE-READ

트랜잭션이 완료될 때까지 SELECT 문이 사용되는 모든 데이터에 Shared Lock이 걸리는 레벨
다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 불가능
트랜잭션이 번위 내에서 조회한 데이터 내용이 항상 동일함을 보장
MySQL DBMS에서 기본으로 사용

SHOW VARIABLES WHERE VARIABLE_NAME='transaction_isolation';

Phantom Read 발생(InnoDB 스토리지 엔진을 사용할 경우 PHANTOM READ 부정합이 발생하지 않음.)
자신의 트랜잭션 번호보다 낮은 트랜잭션 번호에서 커밋된 것만 보게 된다.
첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 현상.

InnoDB 스토리지 엔진에서 PHANTOM READ 해결
InnoDB 스토리지 엔진은 레코드 락과 갭 락을 합친 넥스트 키 락을 사용한다. t 테이블에 c1 = 13 , c = 17 인 두 레코드가 있다고 가정하자. 이때 SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE 쿼리를 수행하면, 10 <= c1 <= 12, 14 <= c1 <= 16, 18 <= c1 <= 20 인 영역은 전부 갭 락에 의해 락이 걸려서 해당 영역에 레코드를 삽입할 수 없다. 또한 c = 13, c = 17인 영역도 레코드 락에 의해 해당 영역에 레코드를 삽입할 수 없다. 참고로 INSERT 외에 UPDATE, DELETE 쿼리도 마찬가지이다.
이러한 방식으로 InnoDB 스토리지 엔진은 넥스트 키 락을 이용하여 PHANTOM READ 문제를 해결한다.

SERIALIZABLE

트랜잭션이 완료될 때까지 SELECT 문이 사용되는 모든 데이터에 Shared Lock이 걸리는 레벨- - 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 및 입력 불가능

트랜잭션 격리 수준별 동작 테스트

테스트에 사용한 스키마

CREATE TABLE IF NOT EXISTS `students` (
    id int(6) AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    major varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

show table status;

두개의 서로 다른 세션을 사용하여, 진행한다.

$ mysql -u사용자ID -p

READ-UNCOMMITTED

테스트를 진행하기 전의 데이터 상태는 아래와 같다.

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

두 개의 세션으로 접속한 후, 각각 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'READ-UNCOMMITTED';

두 개의 서로 다른 트랜잭션을 각각 시작한다.

start transaction;

tx1에서 아래와 같이 데이터를 변경하되, commit은 실행하지 않는다.

mysql> update students set major = 'Math' where id = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

결과

tx1

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Math       		|
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

tx2

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Math       		|
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

tx1에서 rollback을 하느 순간, tx2에서도 반영이 되지만, tx2에서 데이터를 읽어서 어떤 비즈니스 로직을 처리하고자 했다면 문제를 일으킨다.

READ-COMMITED

테스트를 진행하기 전의 데이터 상태는 아래와 같다.

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

두 개의 세션으로 접속한 후, 각각 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'READ-COMMITTED';

두 개의 서로 다른 트랜잭션을 각각 시작한다.

start transaction;

tx1에서 아래와 같이 데이터를 변경하되, commit은 실행하지 않는다.

mysql> update students set major = 'Math' where id = 2;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

결과

tx1

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Math       		|
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

tx2

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

tx1에서 commit을 실행한다.
결과

tx1, tx2

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Math       		|
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

commit한 후의 신뢰성이 확보된 데이터가 다른 트랜잭션에 보인다는 점에서 신뢰성은 높아졌다. 그러나 트랙잭션이 실행중인 tx2에서 같은 select가 다른 결과를 보여줄 수 있는 문제는 해결되지 않았다.

REPEATABLE-READ

테스트를 진행하기 전의 데이터 상태는 아래와 같다.

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

두 개의 세션으로 접속한 후, 각각 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'REPEATABLE-READ';

두 개의 서로 다른 트랜잭션을 각각 시작한다.

start transaction;

tx1에서 하나의 레코드를 추가하고, commit은 하기 전이라면 tx1에만 해당 데이터가 나타난다.

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
|  5 | Emma  | Music            |
+----+-------+------------------+

tx1 commit을 진행한다.

tx1

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
|  5 | Emma  | Music            |
+----+-------+------------------+

tx2

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

읽어도 같은 값을 보여주도록 보장해준다는 것을 의미한다. MySQL은 이 문제를 Sanpshot을 통해서 지원한다.

SERIALIZABLE

테스트를 진행하기 전의 데이터 상태는 아래와 같다.

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

두 개의 세션으로 접속한 후, 각각 아래와 같이 변경한다.

SET @@session.transaction_isolation = 'SERIALIZABLE';

두 개의 서로 다른 트랜잭션을 각각 시작한다.

start transaction;

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
|  5 | Emma  | Music            |
+----+-------+------------------+

tx2

mysql> select * from test.students;
+----+-------+------------------+
| id | name  | major            |
+----+-------+------------------+
|  1 | Tom   | Computer Science |
|  2 | Amy   | Mechanical       |
|  3 | Steve | Art              |
|  4 | John  | Movie            |
+----+-------+------------------+

이 상태에서 tx2에서 임의의 레코드를 업데이트 시도해본다.

mysql> update students set major = 'Music' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

tx1에서 먼저 잡은 lock으로 인해 일정시간이 경과한 후에, 에러가 발생해 버린다. tx2에서 timeout이 발생하기 전에 tx1에서 commit 이나 rollback이 수행되었다면 tx2의 명령은 정상실행된다. lock으로 데이터는 보호하지만, 동시에 많은 요청을 처리하는데는 지연을 발생시킨다.

References

Hocaron

기록을 통한 성장을

이전 포스트

[SQL] 쿼리 튜닝을 위한 준비 : 실행 계획

다음 포스트

[MySQL] Transaction Isolation Level

DB

트랙잭션?

트랜잭션의 특징(ACID)

ACID란?

Atomicity(원자성)

Consistency(일관성)

Isolation(격리성, 독립성)

Durability(지속성)

트랜잭션 격리 레벨

트랜잭션 격리 레벨에 따라 발생할 수 있는 문제

READ-UNCOMMITTED

READ-COMMITED

REPEATABLE-READ

SERIALIZABLE

트랜잭션 격리 수준별 동작 테스트

테스트에 사용한 스키마

READ-UNCOMMITTED

READ-COMMITED

REPEATABLE-READ

SERIALIZABLE

References

[SQL] 쿼리 튜닝을 위한 준비 : 실행 계획

[DB] Exclusive lock과 Shared lock의 차이

0개의 댓글