MySQL 트랜잭션의 4가지 격리 수준
- WBOY원래의
- 2016-08-04 08:56:161044검색
개발 작업에서 트랜잭션을 사용할 예정인데 어떤 트랜잭션 유형이 있는지 알고 계시나요?
MYSQL 표준은 네 가지 유형의 격리 수준을 정의합니다. 이는 트랜잭션 내부 및 외부의 변경 사항이 표시되고 보이지 않는 것을 제한하는 데 사용됩니다.
격리 수준이 낮을수록 일반적으로 더 높은 동시성을 지원하고 시스템 오버헤드가 더 낮습니다.
격리 수준은 낮음부터 높음까지입니다. 커밋되지 않은 읽기 < 커밋된 읽기 <
커밋되지 않은 읽기
이 격리 수준에서는 모든 트랜잭션이 커밋되지 않은 다른 트랜잭션의 실행 결과를 볼 수 있습니다.
이 격리 수준은 성능이 다른 수준보다 그다지 좋지 않기 때문에 실제 응용 프로그램에서는 거의 사용되지 않습니다.
커밋되지 않은 데이터를 읽는 것을 더티 읽기(dirty read)라고도 합니다. [窗口A]:<br />
<br />
mysql> set GLOBAL tx_isolation='READ-UNCOMMITTED';<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> quit;<br>
Bye<br>
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
------------------ <br>
| @@tx_isolation |<br>
------------------ <br>
| READ-UNCOMMITTED |<br>
------------------ <br>
1 row in set (0.00 sec)<br>
<br>
mysql> use test;<br>
Database changed<br>
mysql> begin;<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> select * from user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 1 | a |<br>
| 2 | b |<br>
---- ------ <br>
2 rows in set (0.00 sec)<br>
<br>
[窗口B]:<br>
mysql> select @@tx_isolation;<br>
------------------ <br>
| @@tx_isolation |<br>
------------------ <br>
| READ-UNCOMMITTED |<br>
------------------ <br>
1 row in set (0.00 sec)<br>
<br>
mysql> begin;<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> insert into test.user values (3, 'c');<br>
Query OK, 1 row affected (0.00 sec)<br>
<br>
mysql> select * from user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 1 | a |<br>
| 2 | b |<br>
| 3 | c |<br>
---- ------ <br>
3 rows in set (0.00 sec)<br>
<br>
//目前为止，窗口B并未commit;<br>
<br>
[窗口A]:<br>
mysql> select * from user ;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 1 | a |<br>
| 2 | b |<br>
| 3 | c |<br>
---- ------ <br>
3 rows in set (0.00 sec)[창 A]:<b>
</b>
mysql> GLOBAL tx_isolation='READ-UNCOMMITTED';<br> 설정
쿼리 확인, 0개 행이 영향을 받음(0.00초)<br>
<br>
mysql> 종료;<br>
안녕<code class="prettyprint linenums lang-php">
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx (다시 로그인)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
------------------ <br>
| @@tx_isolation |<br>
------------------ <br>
| 읽기-커밋되지 않음 |<br>
------------------ <br>
1행 세트(0.00초)<br>
<br>
mysql> 테스트 사용;<br>
데이터베이스가 변경되었습니다<br>
mysql>시작;<br>
쿼리 확인, 0개 행이 영향을 받음(0.00초)<br>
<br>
mysql> 사용자로부터 * 선택;<br>
---- ------ <br>
| 아이디 | 이름 |<br>
---- ------ <br>
1 |<br>
|2 |<br>
---- ------ <br>
2줄 세트(0.00초)<br>
<br>
[창 B]:<br>
mysql> @@tx_isolation;<br>을 선택하세요.
------------------ <br>
| @@tx_isolation |<br>
------------------ <br>
| 읽기-커밋되지 않음 |<br>
------------------ <br>
1행 세트(0.00초)<br>
<br>
mysql>시작;<br>
쿼리 OK, 0개 행이 영향을 받음(0.00초)<br>
<br>
mysql> test.user 값에 삽입(3, 'c');<br>
쿼리 OK, 1개 행이 영향을 받음(0.00초)<br>
<br>
mysql> 사용자로부터 * 선택;<br>
---- ------ <br>
| 아이디 | 이름 |<br>
---- ------ <br>
1 |
|2 |
|3 |
---- ------
3줄 세트(0.00초)
//지금까지 창 B는 커밋되지 않았습니다.
[창 A]:
mysql> 사용자로부터 * 선택 ;
---- ------
| 아이디 | 이름 |
---- ------
1 |
|2 |
|3 |
---- ------
3행 세트(0.00초)읽기 커밋됨(제출된 콘텐츠 읽기)
이는 대부분의 데이터베이스 시스템에 대한 기본 격리 수준입니다(MySQL 기본값은 아님).
이는 격리의 간단한 정의를 충족합니다. 트랜잭션은 커밋된 트랜잭션에 의해 변경된 내용만 볼 수 있습니다.
이 격리 수준은 소위 반복 불가능한 읽기(NonrepeatableRead)도 지원합니다. 동일한 트랜잭션의 다른 인스턴스가 이 인스턴스를 처리하는 동안 새로운 커밋을 가질 수 있으므로 동일한 선택이 다른 결과를 반환할 수 있기 때문입니다. [창 A]:
mysql> SET GLOBAL tx_isolation='READ-COMMITTED';
쿼리 확인, 0개 행이 영향을 받음(0.00초)
mysql> 종료;
안녕
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx (다시 로그인)
mysql> SELECT @@tx_isolation;
----------------
| @@tx_isolation |
----------------
| 읽기 커밋됨 |
----------------
1행 세트(0.00초)
mysql>시작;
쿼리 OK, 0개 행이 영향을 받음(0.00초)
mysql> test.user에서 * 선택;
---- ------
| 아이디 | 이름 |
---- ------
1 |
|2 |
---- ------
2줄 세트(0.00초)
[창 B]:
mysql> SELECT @@tx_isolation;
----------------
| @@tx_isolation |
----------------
| 읽기 커밋됨 |
----------------
1행 세트(0.00초)
mysql>시작;
쿼리 확인, 0개 행이 영향을 받음(0.00초)
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 1 | ㄱ |
| 2 | b |
---- ------
세트 내 2행(0.00초)
mysql> test.user에서 삭제(id=1;
)
쿼리 확인, 1개 행이 영향을 받음(0.00초)
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 2 | b |
---- ------
세트 내 1행(0.00초)
[窗口A]:
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 1 | ㄱ |
| 2 | b |
---- ------
세트 내 2행(0.00초)
[窗구B]:
mysql> 커밋;
쿼리 확인, 0개 행이 영향을 받음(0.02초)
[窗口A]:
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 2 | b |
---- ------
1행 세트(0.00초)반복 읽기(可重读)
这是MySQL의 默认事务隔离级别, 它确保同一事务的多个实例는 并发读取数据时, 会看到同样的数据行.
不过理论上，这会导致另一个棘手的问题：幻读 (Phantom Read)。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又에서 该范围内插入了new行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB와 Falcon은 MVCC, 다중 버전 동시성 제어(Multiversion Concurrency Control)를 지원합니다.[窗口A]:<code class="prettyprint linenums lang-php">[窗口A]:<br>
<br>
mysql> SET GLOBAL tx_isolation='REPEATABLE-READ';<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> quit;<br>
Bye<br>
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
----------------- <br>
| @@tx_isolation |<br>
----------------- <br>
| REPEATABLE-READ |<br>
----------------- <br>
1 row in set (0.00 sec)<br>
<br>
mysql> begin;<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
[窗口B]:<br>
<br>
mysql> quit;<br>
Bye<br>
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
----------------- <br>
| @@tx_isolation |<br>
----------------- <br>
| REPEATABLE-READ |<br>
----------------- <br>
1 row in set (0.00 sec)<br>
<br>
mysql> insert into test.user values (4, 'd');<br>
Query OK, 1 row affected (0.00 sec)<br>
<br>
mysql> select * from test.user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 2 | b |<br>
| 4 | d |<br>
---- ------ <br>
2 rows in set (0.00 sec)<br>
<br>
[窗口A]:<br>
<br>
mysql> select * from test.user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 2 | b |<br>
---- ------ <br>
1 rows in set (0.00 sec)<br>
<br>
mysql> commit;<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> select * from test.user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 2 | b |<br>
| 4 | d |<br>
---- ------ <br>
2 rows in set (0.00 sec)
mysql> 전역 설정 tx_isolation='REPEATABLE-READ';
쿼리 확인, 0개 행이 영향을 받음(0.00초)
mysql> 그만둬;
안녕
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(중신登录)
mysql> SELECT @@tx_isolation;
-----------------
| @@tx_isolation |
-----------------
| 반복 읽기 가능 |
-----------------
세트 내 1행(0.00초)
mysql> 시작;
쿼리 확인, 0개 행이 영향을 받음(0.00초)
[窗구B]:
mysql> 그만둬;
안녕
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(중신登录)
mysql> SELECT @@tx_isolation;
-----------------
| @@tx_isolation |
-----------------
| 반복 읽기 가능 |
-----------------
세트 내 1행(0.00초)
mysql> test.user 값(4, 'd')에 삽입합니다.
쿼리 확인, 1개 행이 영향을 받음(0.00초)
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 2 | b |
| 4 | d |
---- ------
세트 내 2행(0.00초)
[窗口A]:
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 2 | b |
---- ------
세트 내 행 1개(0.00초)
mysql> 커밋;
쿼리 확인, 0개 행이 영향을 받음(0.00초)
mysql> test.user에서 *를 선택하세요.
---- ------
| 아이디 | 이름 |
---- ------
| 2 | b |
| 4 | d |
---- ------
세트 내 2행(0.00초)직렬화 가능(序列化执行)
这是最高 隔离级别，它通过强序，使之不可能序，互冲突，从而解决幻读问题。즉, 읽은 각 데이터 행에 공유 잠금을 추가합니다. 이 수준에서는 시간 초과와 잠금 경합이 많이 발생할 수 있습니다. [窗口A]:<br>
<br>
mysql> SET GLOBAL tx_isolation='SERIALIZABLE';<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> quit;<br>
Bye<br>
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
---------------- <br>
| @@tx_isolation |<br>
---------------- <br>
| SERIALIZABLE |<br>
---------------- <br>
1 row in set (0.00 sec)<br>
<br>
mysql> select * from test.user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 2 | b |<br>
| 4 | d |<br>
---- ------ <br>
2 rows in set (0.00 sec)<br>
<br>
mysql> begin;<br>
Query OK, 0 rows affected (0.00 sec)<br>
<br>
mysql> insert into test.user values (5, 'e');<br>
Query OK, 1 row affected (0.00 sec)<br>
<br>
[窗口B]:<br>
<br>
mysql> quit;<br>
Bye<br>
<br>
[root@vagrant-centos65 ~]# mysql -uroot -pxxxx(重新登录)<br>
<br>
mysql> SELECT @@tx_isolation;<br>
---------------- <br>
| @@tx_isolation |<br>
---------------- <br>
| SERIALIZABLE |<br>
---------------- <br>
1 row in set (0.00 sec)<br>
<br>
mysql> select * from test.user;<br>
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction<br>
<br>
[窗口A]:<br>
<br>
mysql> commit;<br>
Query OK, 0 rows affected (0.01 sec)<br>
<br>
[窗口B]:<br>
<br>
mysql> mysql> select * from test.user;<br>
---- ------ <br>
| id | name |<br>
---- ------ <br>
| 2 | b |<br>
| 4 | d |<br>
| 5 | e |<br>
---- ------ <br>
3 rows in set (0.00 sec)감사합니다~
출처: http://mp.weixin.qq.com/s?__biz=MjM5NDM4MDIwNw==&mid=2448834642&idx=1&sn=c02c5cc8ab0c1f29142ac8f8aa6b78af#rd
더 자세한 [마른 정보 공유]를 원하시면 제 개인 구독 계정을 팔로우해주세요.
