트랜잭션 수준의 일관성 설정

• 파라미터 : transaction_isolation

mysql> show variable status 'tx_isolation';
mysql> show variables like 'tx_isolation';

동시성 vs 일관성?

• 동시성 : 여러 사용자가 동일한 데이터에 접근
• 일관성 : 내가 발생시킨 변경 사항과 다른 트랜잭션의 변경사항을 포함하여 일관성 있는 데이터 제공

Isolation Level

• Dirty Read

commit 되지 않은 신뢰성 없는 데이터를 읽을 수 있는 상태다.

• Non-Repeatable Read

commit 여부에 따라 데이터가 달라진다.

• Phantom Read

없던 데이터가 갑자기 나타나거나, 있던 데이터가 갑자기 사라지는 경우다.

레벨 - 상세

Level 0. READ UNCOMMITTED

• 아직 commit되지 않은 데이터를 타 트랜잭션이 읽는 것을 허용한다. 반대로 말하면 타 트랜잭션이 commit 전 상태를 볼 수 있다. 즉, INSERT, UPDATE, DELETE를 하고 난 후에 commit을 치지 않아도 볼 수 있는 것이다.
• Dirty Read, Non-Repeatable Read, Phantom Read 모두 발생한다.
• 만약 트랜잭션을 발생시킨 세션이 rollback을 하면 데이터는 사라진다.
• 자동으로 Row-based Binary Log가 기록된다.
• Oracle은 지원하지 않는다.

Level 1. READ COMMIITED

• commit된 내용만 읽을 수 있다.
• 트랜잭션이 다르더라도 타 트랜잭션이 commit하면 해당 데이터를 read할 수 있다.
• 문제는 타 트랜잭션이 쿼리를 한 시점의 데이터가 아니라, 중간에 원치 않는 데이터 개입의 위험성이 생긴다.
• Oracle은 lock을 사용하지 않고 쿼리 시작 시점의 undo를 제공하는 방식으로 구현.
• DB2, SQLServer, Sybase 등은 read shared lock을 통해 구현.

Level 2. REPEATABLE READ

• SELECT시 현재 버전의 snapshot을 만들고 그 snapshot으로부터 데이터를 조회한다.
• 동일 트랜잭션 내에서 일관성 보장, 하지만 데이터를 다시 읽어들이기 위해서는 트랜잭션을 다시 시작해야 한다.
• Dirty Read와 Non-Repeatable Read가 발생하지 않고 Phantom Read만 발생한다.
• MySQL은 Phantom Read를 막기 위하여 record lock과 gap lock을 걸어 next-key lock을 구현한다.
  - record lock : 현재 record에 대한 lock
  - gap lock : 해당 primary key와 이전 primary key 사이에 데이터를 넣을 수 없다.
• Oracle은 SELECT ~ FOR UPDATE로 구현. DB2, SQLServer는 read lock을 commit할 때까지 유지하는 방식으로 구현.

Level 3. SERIALIZE

• 가장 높은 레벨이다.
• 트랜잭션이 끝날 때까지 SELECT 문이 사용하는 모든 데이터에 shared lock이 걸린다.
• 동시성이 가장 적지만 독립성은 가장 높다.

기타

그런데 Isolation Level이 MySQL 기본인 REPEATABLE READ인 상태에서, INSERT INTO SELECT, CREATE TABLE AS SELECT 등을 통해 전체 테이블 참조 쿼리가 발생하면 참조 테이블에 데이터 변경 작업이 "대기" 상태로 빠질 수 있다.

이는 InnoDB의 기본 Isolation Level이 REPEATABLE READ이기 때문이다.

REPETABLE READ에서는 현재 SELECT 버전을 보장하기 위해 snapshot을 이용하는데 이 경우 해당 데이터에 대한 암묵적 lock이 발생한다. 따라서 해당 조회 작업이 완료될 때까지 데이터 변경이 불가능하다.

해결책 :

INSERT INTO SELECT : READ UNCOMMITTED 혹은 READ COMMITTED로 변경한다.

1. 일시적 해결책

mysql> set tx_isolation = 'READ-COMMITTED';

 혹은

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. 영구적 해결책 (my.cnf)

transaction-isolation = READ-COMMITTED

MySQL lock 종류

• Table Lock

특정 세션에서 테이블에 접근하여 데이터를 읽거나 쓸 때 타 세션에서 접근하지 못하도록 거는 lock

• Global Lock (Global Read Lock)

특정 세션에서 글로벌하게 Read Lock을 사용할 때 사용, flush tables 명령어 사용

• Name Lock

테이블의 이름을 변경하거나 삭제할 때 암묵적으로 걸리는 lock

• User Lock

사용자 레벨에서 lock의 이름과 타임아웃을 정의하여 사용하는 방법

• Row Level Lock

오직 InnoDB