[MySQL] Transaction Isolation Level

들어가며

Transaction Read

• Dirty Read
  • 다른 트랜잭션에서 아직 COMMIT하지 않은 신뢰할 수 없는 데이터를 읽을 수 있는 현상
• Non-repeatable Read
  • 한 트랜잭션에서 동일한 SELECT 쿼리의 결과가 다르게 나타날 수 있는 현상
• Phantom Read
  • 이전의 SELECT 쿼리의 결과에 없던 row가 생기는 현상

Isolation Level 종류

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

현재 Isolation Level 확인하기

mysql> SHOW VARIABLES WHERE VARIABLE_NAME='tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

READ UNCOMMITTED

설명

• 다른 트랜잭션에서 아직 COMMIT하지 않은 신뢰할 수 없는 데이터를 조회할 수 있는 level
• Dirty Read, Non-repeatable Read, Phantom Read 현상 전체가 발생할 수 있음

예제

• session-1

  mysql> SET SESSION transaction isolation level READ UNCOMMITTED;
  mysql> START TRANSACTION;
  mysql> SELECT * FROM user;  
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

• session-2

  mysql> START TRANSACTION;
  mysql> UPDATE user SET name = 'updated' WHERE id = 1;
  mysql> INSERT INTO user (name) VALUES ('inserted');
  

• session-1

  mysql> SELECT * FROM user; 
  +----+----------+
  | id | name     |
  +----+----------+
  |  1 | updated  |
  |  2 | user2    |
  |  3 | user3    |
  |  5 | inserted |
  +----+----------+
  

READ COMMITTED

설명

• 다른 트랜잭션에서 COMMIT된 데이터만 읽을 수 있는 level
• Dirty Read 현상 방지, Non-repeatable Read, Phantom Read 현상은 재현됨

예제

• session-1

  mysql> SET SESSION transaction isolation level READ COMMITTED;
  mysql> START TRANSACTION;
  mysql> SELECT * FROM user;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

• session-2

  mysql> START TRANSACTION;
  mysql> UPDATE user SET name = 'updated' WHERE id = 1;
  mysql> INSERT INTO user (name) VALUES ('inserted');
  

• session-1

  mysql> SELECT * FROM user;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

• session-2

  mysql> COMMIT;
  

• session-1

  mysql> SELECT * FROM user;
  +----+----------+
  | id | name     |
  +----+----------+
  |  1 | updated  |
  |  2 | user2    |
  |  3 | user3    |
  |  6 | inserted |
  +----+----------+
  

REPEATABLE READ

설명

• MySQL 기본 isolation level
• 동시성과 안정성의 균형을 가장 잘 맞춘 level
• Dirty Read, Non-repeatable Read, Phantom Read 현상 모두 방지

예제

• session-1

  mysql> SET SESSION transaction isolation level REPEATABLE READ;
  mysql> START TRANSACTION;
  mysql> SELECT * FROM user;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

• session-2

  mysql> START TRANSACTION;
  mysql> UPDATE user SET name = 'updated' WHERE id = 1;
  mysql> INSERT INTO user (name) VALUES ('inserted');
  

• session-1

  mysql> SELECT * FROM user;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

• session-2

  mysql> COMMIT;
  

• session-1

  mysql> SELECT * FROM user;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  |  2 | user2 |
  |  3 | user3 |
  +----+-------+
  

SERIALIZABLE

설명

• REPEATABLE READ와 동일하지만 shared lock(read lock)을 걸어 다른 트랜잭션에서 읽기는 되지만 쓰기 작업은 block되는 특징을 갖음
• 동시성을 상당 부분 포기하고 안정성에 큰 비중을 두는 level

예제 - 같은 ROW 데이터를 수정하려 할 경우

• session-1

  mysql> SET SESSION transaction isolation level SERIALIZABLE;
  mysql> START TRANSACTION;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  +----+-------+
  

  mysql> UPDATE user SET name = 'user1-a' WHERE id = 1;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-a |
  +----+---------+
  

• session-2

  mysql> START TRANSACTION;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  +----+-------+
  

  mysql> UPDATE user SET name = 'user1-b' WHERE id = 1; // block됨
  

• session-1

  mysql> COMMIT;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-a |
  +----+---------+
  

• session-2

  mysql> COMMIT;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-b |
  +----+---------+
  

예제 - 다른 ROW 데이터를 수정하려 할 경우

• session-1

  mysql> SET SESSION transaction isolation level SERIALIZABLE;
  mysql> START TRANSACTION;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+-------+
  | id | name  |
  +----+-------+
  |  1 | user1 |
  +----+-------+
  

  mysql> UPDATE user SET name = 'user1-a' WHERE id = 1;
  mysql> SELECT * FROM user WHERE id = 1;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-a |
  +----+---------+
  

• session-2

  mysql> START TRANSACTION;
  mysql> SELECT * FROM user WHERE id = 2;
  +----+-------+
  | id | name  |
  +----+-------+
  |  2 | user2 |
  +----+-------+
  

  mysql> UPDATE user SET name = 'user2-b' WHERE id = 2; // block되지 않음
  mysql> SELECT * FROM user WHERE id = 2;
  +----+---------+
  | id | name    |
  +----+---------+
  |  2 | user2-b |
  +----+---------+
  

• session-1

  mysql> COMMIT;
  mysql> SELECT * FROM user;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-a |
  |  2 | user2   |
  |  3 | user3   |
  +----+---------+
  

• session-2

  mysql> COMMIT;
  mysql> SELECT * FROM user;
  +----+---------+
  | id | name    |
  +----+---------+
  |  1 | user1-a |
  |  2 | user2-b |
  |  3 | user3   |
  +----+---------+
  

Transaction, Lock 관련 테이블 조회

SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;
SELECT * FROM information_schema.innodb_trx;

참고

• https://jupiny.com/2018/11/30/mysql-transaction-isolation-levels/amp/