Mysql isolation Level 설정

지난번에 말한 Transaction에서 isolation을 다루어 보려고 합니다. 제가 말하는 것은 mysql 기준입니다. 다른 dbms에서는 다르게 동작할 수 있음을 먼저 말씀 드립니다.

트랜잭션에 대한 많은 parameter가 있는데 하나하나 살펴보겠습니다.

http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

mysql에서는 isolation level을 조절 할 수 있습니다. isolation은 이전에 독립성이라고 했습니다.

show variable status ‘tx_isolation’; 이라는 명령어로 현재 값을 알 수 있는데요. default value는 REPEATABLE-READ입니다. 그럼 그 외의 값은 무엇이고 또 그 값이 독립성을 바꾸는지 보겠습니다.

 

우선 각각에 나오는 특성을 간단히 표로 보면 아래와 같습니다.

	dirty read	non-repeatable read	phantom read
READ-UNCOMMITTED	O	O	O
READ-COMMITTED	X	O	O
REPEATABLE-READ	X	X	O(mysql X)
SERIALIZABLE	X	X	X

 

dirty read – commit 되지 않은 데이터. 즉, 신뢰성 없는 데이터를 읽을 수 있습니다. rollback 해버리면 데이터는 사라지게 됩니다.

[Mon Dec  7 15:10:59 2015][chunge] > show variables like 'tx_isolation'; +---------------+------------------+ | Variable_name | Value            | +---------------+------------------+ | tx_isolation  | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec)  [Mon Dec  7 15:11:05 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec) [Mon Dec  7 15:11:09 2015][chunge] > insert into chunge.chung values(4); Query OK, 1 row affected (0.00 sec) [Mon Dec  7 15:11:18 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    4 | +------+ 2 rows in set (0.01 sec) [Mon Dec  7 15:11:22 2015][chunge] > rollback; Query OK, 0 rows affected (0.00 sec) [Mon Dec  7 15:11:32 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec)

[Mon Dec  7 15:10:59 2015][chunge] > show variables like 'tx_isolation'; +---------------+------------------+ | Variable_name | Value            | +---------------+------------------+ | tx_isolation  | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec) [Mon Dec  7 15:11:05 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec)     [Mon Dec  7 15:11:18 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    4 | +------+ 2 rows in set (0.01 sec)     [Mon Dec  7 15:11:32 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec)

 

non-repeatable read – 나의 commit 유무에 따라서 데이터가 달라지는 것을 말합니다. session 1에서 update 후 commit을 한 후 session2에 영향을 끼치게 되면 non-repeatable read가 발생한다고 합니다.

[Mon Dec  7 15:25:12 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    5 | +------+ 2 rows in set (0.00 sec)   [Mon Dec  7 15:25:26 2015][chunge] > update chunge.chung set no=4 where no=5; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0   [Mon Dec  7 15:25:51 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    4 | +------+ 2 rows in set (0.00 sec)

[Mon Dec  7 15:25:12 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    5 | +------+ 2 rows in set (0.00 sec)   [Mon Dec  7 15:25:19 2015][chunge] > [Mon Dec  7 15:25:51 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    4 | +------+ 2 rows in set (0.00 sec)

 

phantom read – 유령 읽기로 보면 되는데, 없던 데이터가 갑자기 나타나거나 있던 데이터가 사라지는 경우 phantom read라고 합니다. 이것은 session 1이 commit을 한 이후 데이터를 변경하는데 session2가 그 사이에 작업을 하면서 seesion1이 작업을 하는 범위에 영향을 끼치는 경우입니다.

[Mon Dec  7 15:19:23 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec) [Mon Dec  7 15:19:26 2015][chunge] > insert into chunge.chung values(5); Query OK, 1 row affected (0.00 sec)   [Mon Dec  7 15:19:32 2015][chunge] > commit; Query OK, 0 rows affected (0.00 sec)   [Mon Dec  7 15:19:35 2015][chunge] > [Mon Dec  7 15:19:39 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    5 | +------+ 2 rows in set (0.00 sec)

[Mon Dec  7 15:19:16 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | +------+ 1 row in set (0.00 sec)   [Mon Dec  7 15:19:26 2015][chunge] > [Mon Dec  7 15:19:26 2015][chunge] > [Mon Dec  7 15:19:39 2015][chunge] > select * from chunge.chung; +------+ | no   | +------+ |    3 | |    5 | +------+ 2 rows in set (0.00 sec)

 

READ-UNCOMMITTED

session1이 insert, update, delete를 한 이후 commit을 치지 않아도 다른 session2가 session1이 수행한 것을 볼 수 있는 상태입니다. dirty read, non-repeatable read, phantom read 모두가 발생하는 단계입니다. 보통 commit을 하면 undo에 있는 값이 data에 씌어지게 되는데요. data에 쓰여지기 전 데이터. 즉, commit을 하지 않은 상태의 값을 읽어서 session 1이 rollback을 할 경우 데이터가 사라지는 것을 볼 수 있습니다.

 

위 그림은 제가 PPT로 작성한 그림입니다. timeline을 위로 보시면 됩니다. 말씀 드린 것처럼 read-uncommitted는 session1이 한 행위에 대해서 commit을 하지 않아도 session2가 영향을 받는 것입니다. 아래와 같이 설명을 드리겠습니다.

- session 1 : 1~3 time동안 B를 insert합니다.

- session 2 : 1~2 time동안 select를 했을 때 data는 A가 나옵니다. 왜냐하면 3time에 B가 insert 되기 때문입니다.

- session 2 : 4~5time에 select를 하면 session1이 commit을 하지 않았어도 session2가 영향을 받아서 A, B값이 나타나게 됩니다.

- session 1 : 6~7 time에 rollback을 하게 되면 결국 transaction이 취소되고 다시 A만 남게 됩니다.

- session 2 : 8~9 time에 select를 하면 rollback이 되었으므로 session2도 영향을 받아서 A값만 나오게 됩니다.

정리해 보면 session2는 select만 했는데 데이터가 나타났다 사라졌다 하는 것을 알 수 있습니다.

transaction이 완료된 것도 아닌데 데이터가 나오는 dirty read가 제대로 나타나는 것을 알 수 있습니다.

 

READ-COMMITED

session1이 commit한 데이터를 session2가 읽어옵니다. 이때 주의해야 할 점은 session2가 원했든 원하지 않았든 session1의 값이 session2에 영향을 미치게 됩니다. seesion2가 query를 날린 시점의 데이터가 아니라 날리고 난 후 중간에 원하지 않는 데이터의 개입이 생기는 위험이 있는 것이 read-committed입니다. 그리고 5.5버전 이상에서는 READ-COMMITTED에는 innodb_locks_unsafe_for_binlog라는 파라미터가 있는데 이 파라미터가 활성화 되어야 합니다. innodb_locks_unsafe_for_binlog는 또 말을 하겠습니다.

 

 

- session 1 : 1~3 time동안 B를 insert합니다.

- session 2 : 1~2 time동안 select를 했을 때 data는 A가 나옵니다. 왜냐하면 3time에 B가 insert 되기 때문입니다.

- session 2 : 4~5time에 select를 하면 session1이 commit을 하지 않았기 때문에 session2가 영향을 받지 않아서 A값만 나타나게 됩니다.

- session 1 : 6~7 time에 commit을 하게 되면 결국 transaction이 완료되고 A,B값이 들어가 있습니다.

- session 2 : 8~9 time에 select를 하면 session1이 commit을 했기 때문에 session2도 영향을 받아서 A, B값이 나오게 됩니다. session2가 session1의 값을 원하지 않았는데도 session2는 session1의 transaction이 완료되었기 때문에 영향을 받은 것입니다.

 

REPEATABLE-READ

repeatable-read의 경우엔 non-repeatable read와 dirty read가 발생하지 않습니다. 하지만 phantom read가 발생합니다. 이론적으로 먼저 repeatable read를 설명하자면 session1이 commit을 한 후 session2가 commit을 하기 전까지는 session1이 변경한 데이터가 session2에 영향을 끼칠 수 없는 것입니다. 이때 session2가 commit을 하면 여전히 session2가 쿼리를 날린 시점의 데이터가 아니지만 데이터가 나타나게 됩니다. 이때 mysql은 phantom read를 막기 위해 record lock과 gap lock을 걸어서 next-key lock을 구현합니다. record lock은 해당 record에 대한 lock이며 아무도 건들 수 없습니다. 그리고 gap lock은 해당 primary key와 다음과 이전 primary key 사이에는 데이터를 넣을 수 없는 것을 말합니다. 이로써 phantom read를 막는 기법이 완성이 됩니다.

 

mysql 공식 가이드에서는 아래와 같이 말하고 있습니다.

이 값은 InnoDB에서 기본 isolation 레벨입니다. 일관성 있는 읽기에서, 이것은 READ COMMITTED와 중요한 다른 점이 있다. 같은 트랜잭션 안에서 모든 일관성 있는 읽기는 첫 번째 읽기로부터 snapshot을 구축하고 읽습니다. 이것은 몇 가지 non-locking 상태의 select 절이 같은 transaction 안에서 이루어 지면 이러한 select 절은 각각 일관성을 존중 받습니다.   lock이 걸린 읽기(for update나 lock in share mode를 포함한 select)를 위해서 update, delete 문은 유니크 검색 상태를 가진 유니크 인덱스 또는 범위 타입 검색상태인지에 따라서 신뢰하는 lock 사용이 달리합니다. 유니크 검색 상태 일 때, InnoDB lock은 해당 인덱스 record를 보호하고 해당 key 이전의 gap을 발견해서 insert를 막습니다. 또한 InnoDB lock은 인덱스 범위 스캔일 때, gap lock이나 next-key lock을 사용하면서, 해당 범위 내에 insert 하는 것을 막습니다.

 

- session 1 : 1~3 time동안 B를 insert합니다.

- session 2 : 1~2 time동안 select를 했을 때 data는 A가 나옵니다. 왜냐하면 3time에 B가 insert 되기 때문입니다.

- session 1 : 4~5 time에 commit을 합니다.

- session 2 : session 2도 동일하게 4~5time에 select를 하지만 A값이 나옵니다. 아직 session2가 commit을 하지 않았기 때문에 session 2에는 B라는 데이터가 나오지 않습니다.

- session 2 : session2가 6~7 time에 commit을 합니다.

- session 2 : session2가 8~9 time에 select를 해보면 8~9에 A와 B값이 나오는 것을 알 수 있습니다.

 

이 과정에서 중요한 것은 양쪽 다 commit을 해야 session 1의 데이터가 나온다는 점입니다. phantom read가 발생할 구멍이 있는데 이것도 설명을 드리겠습니다.

 

 

 

 

SERIALIZABLE

serializable은 select하는 데이터에 무조건 lock을 거는 것입니다. 절대로 데이터가 변경이 될 수 없고 그 사이 데이터를 넣을 수도 없게끔 설정된 것입니다. 그래서 동시성이 제일 낮지만 데이터 관여가 가장 적고 독립성이 높은 모드입니다. INNODB는 auto commit이 비활성화 되어 있다면 모든 select절을 select ~ lock in share mode로 함축적으로 변환시킵니다. 만약 auto commit이 활성화되어 있다면, select는 자신만의 트랜잭션을 가집니다. 그러므로 이것은 읽기전용으로 알려져 있지만 non-locking이기 때문에 값을 읽을 수 있고, 다른 트랜잭션을 위해 block을 걸 필요가 없습니다.

 

추후에는 innodb_locks_unsafe_for_binlog에 대해 간단히 설명하도록 하겠습니다.