[펌][MySQL] 왜 isolation-level Read-committed 에서 Binlog_format Mixed 이어도 모두 row format으로 binary 로그가 남을까?

완전 퍼온 글이다. 도움이 될 듯 싶어서 공유한다.

---

BINLOG FORMAT은 세가지가 있다. 

1) STATEMENT
2) MIXED
3) ROW 

이 중 MIXED는 1),3) 의 혼합 형태로 binary 로그를 남기게 되는데 
몇몇 일관성을 보장하지 못하는 케이스를 제외하고는 1) STATEMENT 형태로 남게 된다. 

그런데 ISOLATION LEVEL 도 binary 로그 포맷에 영향을 주게 되는데 
ISOLATION LEVEL 이 READ-COMMITTED의 경우 binlog_format을 MIXED 이상으로 설정을 해야 한다. 


ISOLATION LEVEL 

1) READ-COMMITTED 

The default isolation level for most database systems ( nut not MySQL ) is READ COMMITTED. It satisfies the simple
definition of isolation used earlier: a transaction will see only those changes made  by transactions that were already committed when it began, and its changes won't be visible to others until it has committed. This level still allows what's 
known as a nonrepeatable read. This means you can run the same statement twice and see different data.

2) REPEATABLE READ

It guarantees that any rows a transaction reads will "look the same" in subsequent reads within the same transaction, but 
in theory it still allows another tricky problem: phantom reads. Simply put, a phantom read can happen when you select some range of rows, another transaction inserts a new row into the range, and then you select the same range again: 
you will then see the new "phantom" row. InnoDB and XtraDB solve the phantom read problem with multiversion comcurrency control, which we explain later in this chapter. REPEATABLE READ is MySQL's default transaction isolation level.


Isolation level을 read committed로 하고 binlog format을 MIXED로 한 후 대부분의 binary 로그가 statement 형태로 남을 것으로 생각했다. 그런데 전부다 row format 으로 남았고, 이것이 정상이라고 한다. isolation level이 repeatable read 에서는 binlog format MIXED 일 때 대부분의 경우 statement 형태로 남고 일관성이 보장이 안되는 특정 경우에만 row 형태로 남았다. 

이건 isolation level의 개념만 다시 확인해 보면 당연한 결과이다. 

테스트를 해보자. 

1. transaction_isolation = repeatable-read

두 개의 세션에서 트랜잭션을 열고 아래 번호 순서대로 DML이 실행한다. 그리고 
 

 

[root@localhost] (test) 12:12> rollback;
Query OK, 0 rows affected (0.00 sec)

####### Session A

[root@localhost] (test) 12:13> start transaction ;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 12:14>
[root@localhost] (test) 12:14> update trans_test2 set b=999 where a=1;   ----------- 1)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> update trans_test2 set b=222 where a=10;  ----------- 3) 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> commit;
Query OK, 0 rows affected (0.00 sec)

 

 

####### Session B

[root@localhost] (test) 12:13> start transaction ;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 12:14>
[root@localhost] (test) 12:14> update trans_test2 set b=999 where a=20;  ------------ 2)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> update trans_test2 set b=222 where a=25;  ------------ 4)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 12:14> commit;
Query OK, 0 rows affected (0.00 sec)

 

####### binary log를 확인해 보면 먼저 commit 된 transaction 순서대로 기록된다. ( DML 실행 순서 아님 )

#131001 12:14:08 server id 12  end_log_pos 10509        Query   thread_id=2     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1380597248/*!*/;
update trans_test2 set b=999 where a=1f<9c>ⓒK
/*!*/;
# at 10509
#131001 12:14:21 server id 12  end_log_pos 10622        Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1380597261/*!*/;
update trans_test2 set b=222 where a=10oI^PØ
/*!*/;
# at 10622
#131001 12:14:47 server id 12  end_log_pos 10653        Xid = 127
COMMIT/*!*/;
# at 10653
#131001 12:14:13 server id 12  end_log_pos 10732        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1380597253/*!*/;
BEGINU<90>%²
/*!*/;
# at 10732
#131001 12:14:13 server id 12  end_log_pos 10845        Query   thread_id=3     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1380597253/*!*/;
update trans_test2 set b=999 where a=20AE§￥
/*!*/;
# at 10845
#131001 12:14:44 server id 12  end_log_pos 10958        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1380597284/*!*/;
update trans_test2 set b=222 where a=25^]GRA
/*!*/;
# at 10958
#131001 12:14:50 server id 12  end_log_pos 10989        Xid = 129
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

 

2. transaction_isolation = read-committed

 

=== session A

[root@localhost] (test) 13:27> start transaction;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:32>
[root@localhost] (test) 13:32> update trans_test2 set b=444 where a=1;    ---------- 1)
Query OK, 1 row affected (0.16 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:32> select * from trans_test2 where a=20;      ---------- 2) 
+----+------+
| a  | b    |
+----+------+
| 20 |  999 |
+----+------+
1 row in set (0.01 sec)

[root@localhost] (test) 13:33> select * from trans_test2 where a=20;      ----------- 5) 3) DML 결과가 보인다.  
+----+------+
| a  | b    |
+----+------+
| 20 |  777 |
+----+------+
1 row in set (0.00 sec)

[root@localhost] (test) 13:33> update trans_test2 set b=787 where a=20 and b=777;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> commit;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:33> select * from trans_test2 where a=20;
+----+------+
| a  | b    |
+----+------+
| 20 |  787 |
+----+------+
1 row in set (0.00 sec)

 

=== session B

[root@localhost] ((none)) 13:28> use test
Database changed
[root@localhost] (test) 13:28> start transaction;
Query OK, 0 rows affected (0.00 sec)

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> update trans_test2 set b=777 where a=20;    ----------- 3) 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[root@localhost] (test) 13:33>
[root@localhost] (test) 13:33> commit;   -------- 4) 
Query OK, 0 rows affected (0.00 sec)

 

위 테스트 처럼 isolation level READ-COMMITTED의 경우  현재의 트랜잭션이 종료 되지 않은 상황에서 다른 트랜잭션이 commit 이 되면 그 트랜잭션 통해 변경된 값이 보이게 된다. 그런데 이런 경우를 statement 방식으로 트랜잭션 단위로 묶어서 순서대로 로깅해서 그 순서대로 쿼리를 실행하게 될 경우 실제 상황을 그대로 재현할 수 없게 되고 다른 결과가 나올 수 있게 된다. 그렇기 때문에 isolation level READ-COMMITTED , binlog_format MIXED 에서 는 binary log가 row 형태로 남게 된다.