트랜잭션과 동시성 제어

트랜잭션과 동시성 제어

STEP 01) 트랜잭션을 다룰 수 있는 테이블 작성

mysql> use sys;
Database changed
mysql> create table t1(i1 int not null primary key, v2 varchar(20)) engine = innodb;
Query OK, 0 rows affected (0.22 sec)
 
 
mysql> insert into t1 values(1, 'Firebird');
Query OK, 1 row affected (0.05 sec)
 
 
mysql> show create table t1 \g
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `i1` int(11) NOT NULL,
  `v2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

STEP 02) 다른 커넥션에서 STEP01에서 입력된 값 살펴보기

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.23-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use sys;
Database changed
mysql> select * from t1;
+----+----------+
| i1 | v2       |
+----+----------+
|  1 | Firebird |
+----+----------+
1 row in set (0.00 sec)
 
mysql>

트랜잭션 : 복수쿼리를 한 단위로 묶은 것. 한 덩어리의 퀴리 처리 단위

DBMS에서 에플리케이션 로직을 구성할 때 사용

트랜잭션의 특성

1. 원자성(Atomicity)

2. 일관성(Consistency)

3. 격리성(Isolation)

4. 지속성(Durability)

1. 원자성

데이터의 변경( INSERT / DELETE / UPDATE )을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조 (전부 성공하거나 전부 실패하게끔 처리한다는 뜻)

 1 → 2 → 3 → 4 → COMMIT 

 1 → 2 → 3 (ERROR) //- 4 - COMMIT 

 ↑            ↓

   ←  ←  ←  [ ROLLBACK ]실행

2. 일관성(Consistency)

데이터베이스는 데이터베이스 오브젝트(TABLE를 비롯한 DB 객체)에 대해 각종 정합성 제약 추가 가능

이는 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하기위한 구조

3. 격리성(Isolation)

복수 사용자가 동시에 데이터 조작을 실행해도 '각각의 처리가 모순없이 실행되는 것을 보증한다.'

테이블에 잠금을 걸어 후속처리를 블록처리

테이블 전체, 블록, 행 단위로 잠금이 가능하며 MySQL에서는 트랜잭션 처리를 할 때 주로 행단위의 잠금기능 이용 

'모순없이 실행' : 동시에 데이터 조작을 실행해도 복수의 트랜잭션이 순서대로 실행되는 경우와 같은 결과를 얻을 수 있다. 

이것을 DBMS에서 격리수준으로 구현하고 제공하는 것이 '직렬화 가능' 사양

하지만 직렬화 가능의 격리성은 성능면에서 실용적이지 않기때문에 격리 수준을 완화한 4개의 단계를 ANSI(규격단체)에서 정의

(MySQl에 해당. DBMS마다 따르는 격리 수준이 다르다.)

[격리 수준을 완화한 ANSI의 격리 수준 규격]

(아래로 갈 수록 격리수준 강화)

1. 커밋되지 않은 읽기(Read UnCommitted)

2. 커밋된 읽기(Read Committed)

3. 반복 읽기(Repeatable Read)

4. 직렬화 가능(Serialixable)

[격리수준의 완화에 따라 일어나는 현상]

현상	개요
1. 더티읽기	어떤 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽는 현상 ex) A가 값을 변경하고 아직 커밋하지 안아도 사용자 B가 변경한 후의 값을 읽는것
2. 애매한 읽기 (Fuzzy / NonReapeatable Read : 반복 불가능한 읽기)	어떤 트랜잭션이 이전에 읽어들인 데이터를 다시 읽어들일 때  2회 이후의 결과가 1회때와 다른 현상. ex) 최초의 사용자 A가 값 10을 읽고 그 후 사용자가 B가 값을 9로  변경하여 커밋했다고 하자. 계속해서 사용자 A가 select를 통해  9를 읽는 것=/= 처음 읽은 값 10
3. 팬텀 읽기(Phantom Read)	어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상이다.  ex) 최초의 사용자 A가 범위 검색을 수행 후 3행을  읽는다 > 계속해서 사용자 B가 막 그 범위에 들어가는 데이터 1행을 insert하고 커밋도 실행한다 > 사용자 A가 다시 같은 select 문을 실행하면 4행이 선택된다. 이처럼 나타나거나 사라지는 데이터가 유령과 닮아서 붙여진 이름

4. 지속성(Durability)

데이터 조작(트랜잭션)을 완료(COMMIT)하고 사용자가 완료 통지를 받는 시점에서 그 조작이 영구적으로 남아 결과를 잃지 않음.

(=시스템 장애에도 견딜수 있다는 의미)

MySQL을 포함한 많은 DB 구현은 트랜잭션 조작을 하드디스크에 로그로 기록하여 시스템 장애가 발생하면 

그 로그를 사용해 이상 발생 전의 상태까지 복원하는 것으로 지속성 실현 

다른 커넥션에서 보는 테이블

DDL과 DML에 의한 테이블 작성과 데이터 저장은 트래잭션이 커밋되기 전까지는 다른 커넥션에서 보이지 않는다.

1. DDL에 따른 암묵적인 커밋

MySQL이나 Oracle에서는 CREATE TABLE과 같은 DDL실행 시 암묵적인 커밋 발행 <<???

2. 오토커밋 설정

트랜잭션의 개시가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법

1. 하나의 SQL문을 하나의 트랜잭션으로 구분

2. COMMIT 또는 ROLLNACK을 실행하기까지가 하나의 트랜잭션

MySQL 은 기본 설정이 오토커밋

복수커넥션에서 읽고 쓰기

STEP 01)  두 개의 MySQL 커맨드라인 클라이언트 실행

mysql> prompt Transaction A>
PROMPT set to 'Transaction A>'
 
Transaction A>use sys;start transaction;
Database changed
Query OK, 0 rows affected (0.00 sec)
 
Transaction A>select * from t1;
+----+----------+
| i1 | v2       |
+----+----------+
|  1 | Firebird |
+----+----------+
1 row in set (0.00 sec)
 
Transaction A>rollback;
Query OK, 0 rows affected (0.00 sec)
 

mysql> prompt Transaction B>
PROMPT set to 'Transaction B>'
Transaction B>use sys;start transaction;
Database changed
Query OK, 0 rows affected (0.00 sec)
 
Transaction B>select * from t1;
+----+----------+
| i1 | v2       |
+----+----------+
|  1 | Firebird |
+----+----------+
1 row in set (0.00 sec)
 
Transaction B>rollback;
Query OK, 0 rows affected (0.00 sec)
 

-------------------------------------------------------------------------------(임시저장)

STEP 02) 두 개의 커넥션에서 읽기와 쓰기

STEP 03) 트랜잭션 격리수준 바꿔보기

트랜잭션 격리 수준에 따라 외관상 차이

잠금 타임아웃과 교착 상태 시험

STEP 01)

STEP 02)

잠금 타임아웃과 교착 상태가 발생하는 이유

잠금 타임아웃 :

교착 상태 :

DBMS의 전반적인 대책

MySQL(InnoDB)의 대책

해서는 안되는 트랜잭션 처리

주의 1) 오토커밋

주의 2) 긴 트랙잭션

 - 대량처리를 한 개의 트랜잭션이 실행

 - 아무것도 하지 않은 트랜잭션 유의

 - 트랜잭션 중에 대화처리 넣기

- 처리 능력 이상의 트랜잭션 수

트랜잭션 관련 설정 확인