트랜잭션과 동시성 제어(ACID, Isolation level, 복수 커넥션, MVCC, Lock TimeOut, DeadLock)

WOOK JONG KIM·2023년 1월 31일
DB첫걸음
0

DB첫걸음

목록 보기
7/10
post-thumbnail

데이터베이스를 사용한 실제 시스템이나 애플리케이션에서는 단일 쿼리만으로 조작하는 일은 거의 없고, 복수의 쿼리를 연속적으로 사용해 일관성 있는 형태의 한 단위로 취급해야 함
-> 이러한 한 덩어리의 쿼리 처리 단위를 트랜잭션이라고 함

트랜잭션 다루기 위한 테이블 생성

create database test;

use test;

/* innoDB형의 테이블 t1 생성 */
create table t1(
i1 int not null primary key,v2 varchar(20)) engine = innodb;

insert into t1 values(1, "FireBird");

트랜잭션이란?

DBMS에서 애플리케이션 로직을 구성할때 트랜잭션이나 잠금 구조를 다룸

테이블 갱신(INSERT/DELETE/UPDATE)시, 갱신은 단일 쿼리만으로 구성된 것이 아니고 복수 쿼리를 연속적으로 수행하는 경우가 많음
-> 갱신 전의 데이터로 SELECT를 사용할때 이를 포함해 복수 쿼리를 일관된 형태의 한덩어리로 다루어야 함

ACID

  1. Atomicity(원자성)
    데이터의 변경을 수반하는 인련의 데이터 조작이 전부 성공할지 실패할지를 보증하는 구조

ex) 서울에서 KTX를 타고 부산까지 가서 하루 잔뒤, 서울로 돌아온다 생각

  1. 서울 -> 부산행 좌석을 예매
  2. 부산에서의 숙박을 예약
  3. 부산 -> 서울행 좌석 예매
  4. 위 3단계의 대금을 지급

위 예시처럼 절차가 모두 잘 진행되면 트랜잭션에서는 1~4의 절차를 처리한 후에 COMMIT을 실행해 처리를 확정
-> 이 경우 각 데이터의 조작은 영구적으로 저장되어 결과가 손실되지 않음

만약 위 절차중 오류가 발생하는 경우
-> ex) 부산->서울행 좌석 꽉참, 돈이 부족한 경우
-> ROLLBACK을 실행해 1의 직전 상태까지 되돌아갈수 있음

  1. 일관성

데이터베이스에는 데이터베이스 오브젝트(테이블을 비롯해 데이터베이스 내에서 정의할 수 있는 오브젝트)에 대해 각종 정합성 제약을 추가 가능
-> 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는, 즉 일관성을 유지하기 위한 구조

  1. 고립성

일련의 데이터 조작을 복수 사용자가 동시에 실행해도, 각각의 처리가 모순없이 실행되는 것을 보장한다는 것
-> 즉 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미

예를 들면, 부산의 지정 호텔에 남은 싱글룸 수가 10개였을 때 실제로 숙박하는 로직은 다음과 같음

  • 현재 빈 싱글룸의 수를 확인한다(SELECT)
  • 빈 싱글룸 수에서 1을 빼고 결과를 빈 싱글룸 수로 되돌려 쓴다(UPDATE)
  • 이것을 사용자 A와 사용자 B가 동시에 수행하면 어떻게 될까? 2명이 방을 확보한다면 원래 빈 싱글룸 수는 2개가 줄어야 하지만, 같은 방을 동시에 확보하게 되면 빈 싱글룸 수는 1개만 줄어들게 됨

이러한 사태를 막기 위해 DB에서는 DB 오브젝트인 Table에 대해 Lock을 걸어서 후속 처리를 Block 하는 방법이 있음
-> 잠금 단위에는 테이블 전체, 블록, 행 등이 있는데, MySQL에서는 트랜잭션 처리를 할 때 주로 행 단위의 잠금 기능을 이용

앞의 두 가지 중 ‘1. 현재 빈 싱글룸 수를 확인한다’를 처리할 때 ‘SELECT ~ FOR UPDATE’를 실행하면 SELECT한 행에 잠금이 걸린다. 이렇게 되면 후속 처리는 해당 잠금이 해제될 때(COMMIT 또는 ROLLBACK)까지 대기하게 되며 올바른 처리를 계속할 수 있게 됨

앞서 각각의 처리가 모순 없이 실행되는것을 보증한다라고 기술
-> 어떠한 상태가 모순이 없는 것일까?
-> 이는 복수의 트랜잭션이 순서대로 실행되는 경우와 같은 결과값을 얻을 수 있는 상태
-> 직렬 상태로 모순 없음을 보장 가능

  1. 지속성
    일련의 데이터 조작을 커밋하고 완료 통지를 사용자가 받는 시점에 그 조작이 영구적으로 되어 결과를 잃지 않는 것

ex) MySQL을 포함해 많은 DB 구현에서는 트랜잭션 조작을 하드 디스크에 로그로 기록하고, 시스템에 이상이 발생하면 그 로그를 사용해 이상 발생 전의 상태까지 복원하는 것으로 지속성 실현

격리 상태

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

  1. 커밋 되지 않은 읽기(Read Uncommitted)
  2. 커밋된 읽기(Read Committed)
  3. 반복 읽기(Repeatable Read)
  4. 직렬화 가능(Serializable)

고립성이 강한 트랜잭션은 한 번에 하나의 트랜잭션만이 실행되어야 하기 때문에, 많은 트랜잭션이 동시에 수행되려면 긴 시간이 소요되어 성능상의 문제가 발생
-> 위에서 1에서 4로 갈수록 엄격해짐
-> 격리 수준이 완화되면서 Serializable에서는 발생하지 않았던 현상이 발생

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

  1. Dirty Read
    어떤 트랜잭션이 커밋 되기 전에 다른 트랜잭션에서 데이터를 읽는 현상

  2. Fuzzy Read(애매한 읽기)
    어떤 트랜잭션이 이전에 읽어 들인 데이터를 다시 읽어 들일 때 2회 이후의 결과가 1회 때와 다른 현상
    -> ex) 사용자1이 빈 싱글룸 수 10을 읽고 그후 사용자 B가 값을 9로 변경하여 커밋했다고 가정
    -> 계속해서 사용자가 SELECT 실행 시 최초에 SELECT 했던 값은 10이 아닌 변경 후의 9를 읽어 드리게 됨

  3. Phantom Read
    어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상
    -> ex) 사용자1이 빈 싱글룸수가 10 이상의 호텔을 수행한 후 3행을 읽었다고 가정
    -> 이후 계속해서 사용자2가 그 범위안에 들어가는 데이터 1행을 INSERT하고 커밋을 하였음
    -> 계속해서 사용자1이 다시 같은 SELECT 문을 실행하면 최초에 SELECT 해서 얻었던 3행이 아닌 4행이 선택됨

Read Uncommitted : Dirty, Fuzzy, Phantom
Read Committed : Fuzzy, Phantom
Reapeatable REad : Phantom
Serializable : X

다른 커넥션

mysql> select * from test.t1;
+----+----------+
| i1 | v2       |
+----+----------+
|  1 | FireBird |
+----+----------+
1 row in set (0.01 sec)

앞서 트랜잭션을 위한 테이블을 커밋하지 않았지만 다른 커넥션에서 조회가 가능함
함

이유

  1. MySQL이나 오라클에서는 CREATE TABLE과 같은 DDL 실행 시 암묵적인 커밋이 발행 됨

  2. 트랜잭션 시작(BEGIN, START, SET TRANSACTION 등)이 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법은 2가지가 있음

하나의 SQL 문이 하나의 트랜잭션으로 구분, 사용자가 COMMIT 또는 ROLLBACK을 실행하기까지가 하나의 트랜잭션

MySQL, PostgreSQL, SQL server는 기본 설정이 오토커밋 모드임

복수의 커넥션에서 읽고 쓰기

다른 터미널에서는 transAction A로 설정

이후 두 커넥션에서 use test;start transaction; 입력

commit이나 rollback 시 트랜잭션이 종료된다는점 유의하기!

예시

타 트랜잭션의 Insert가 자신의 트랜잭션 select에 어떻게 보이는지

Transaction A> start transaction;
Transaction B> start transaction;

Transaction B> insert into t1 values(2, "MySQL"); commit; start transaction;
Transaction A> select * from t1; // 이때의 트랜잭션에서 조회를 진행할시엔 mysql 값이 테이블에 반영되어 있음

Transaction B> insert into t1 values(3, "postgreSQL");commit;start transaction;
Transaction A> select * from t1; // 이때는 3값이 반영되어 있지 않음을 이해해야함
Transaction A> rollback; // 롤백후에 다시 조회를 하면 3값이 반영되어 있음

트랜잭션 A와 B에서 같은 행을 갱신하려할때, 먼저 갱신하러 들어간 트랜잭션의 경우는 문제가 없지만, 이후 갱신을 할려는 트랜잭션은 Lock wait timeout exceeded; 오류 발생

MVCC(Multi Versioning Concurrency Control)

MySQL(InnoDB형 테이블)은 MVCC 기술 사용으로 인해 다음과 같은 특성을 가짐
-> 그냥 MySQL은 MVCC 기술을 사용하지 않음(읽기가 블록될 수 있음)

  1. 한 트랜잭션이 읽기를 수행하는 경우, 다른 트랜잭션이 갱신 중이라도 블록되지 않음
    (읽기와 읽기도 서로 블록되지않음)

-> 데이터베이스에서 읽기 작업을 수행할 때 다른 트랜잭션이 데이터를 갱신하더라도, 읽기 작업은 블록되지 않는다는 것을 의미
-> 이는 isolation level이나 작업 순서에 따라 결과가 달라질수있음 유의!!(그냥 MySQL의 경우, MVCC X)

예를 들어

격리 수준이 "read committed" 또는 "read uncommitted"로 설정되어 있다면, 읽기 작업은 여전히 진행될 수 있지만, 쓰기 작업이 아직 커밋되지 않았을 경우 결과가 불일치할 수 있다

반면에, 격리 수준이 "repeatable read" 또는 "serializable"로 설정되어 있다면, 읽기 작업은 쓰기 작업이 완료될 때까지 블록될 수 있음
-> MVCC의 경우에는 읽기 작업이 블록되지 않는다는점 유의!

  1. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있음

mySQL의 격리수준 디폴트 값은 Repeatable read
-> 이는 최초 쿼리를 실행한 시점에 커밋된 데이터를 읽어들임
-> 이후 같은 조회 쿼리를 여러 해 실행할때, 다른 트랜잭션이 커밋을 하였어도 그 내용은 반영되지 않음!!!
-> 만약 Committed Read의 격리 수준의 경우, 최신 쿼리의 실행 개시 시점에서 커밋된 데이터를 읽음(즉 변경 내용이 반영됨)

갱신을 수행하는 주체 트랜잭션은, 격리 수준이나 COMMIT,ROLLBACK과 상관없이 자신이 수행한 갱신을 볼수 있음

  1. 갱신시에는 베타적 잠금을 얻음. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션 종료 시 까지 유지(격리 수준에 따라 다를수 있음 유의)
  2. 두 트랜잭션이 같은 행에 대해 갱신 수행 시 나중에 온 트랜잭션이 잠금을 획득할려 할때 블록됨(일정 시간 내 잠금을 획득하지 못하면 Lock TimeOut이 됨)
  3. 갱신 하는 경우 갱신 전의 데이터를 UNDO 로그로 롤백 세그먼트라는 영역에 유지

이는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리거나, 복수의 트랜잭션으로 부터 격리 수준에 따라 대응하는 갱신 데이터를 참조할때 사용됨
-> 즉 같은 행을 갱신할때마다 UNDO 로그가 작성되어 같은 행에 대한 복수 버젼이 존재함

잠금 타임아웃과 교착 상태(Lock TimeOut && DeadLock)

잠금 타임아웃이란?

갱신과 참조는 서로를 블록하지 않지만, 갱신과 갱신이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 됨
-> 먼저 갱신 상태에 진입한 트랜잭션이 잠금을 걸고, 이 잠금을 언제 풀지 알수 없어서 잠금 해제를 기다리는 트랜잭션에서는 잠금을 기다리지 않거나, 기다린다면 어느 정도를 기다릴지 설정 가능

set innodb_lock_wait_timeout = 1;

만약 시간이 지나 잠금 대기로 인해 타임아웃이 발생하는 경우, DBMS로부터 롤백되는 단위가 다를 수 있음
-> 트랜잭션 전체를 롤백하거나 쿼리만을 롤백하거나
-> MySQL에서는 오류가 발생한 쿼리만 롤백 시킴
-> 만약 트랜잭션 전체를 롤백하고 싶다면 타임아웃 오류 후 명시적으로 ROLLBACK을 실행하거나 innodb_rollback_on_timeout 시스템 변수를 설정

교착상태란?

두 개 이상의 트랜잭션이 각자 상대방의 리소스를 점유하는 동안 대기하고 있을 때 발생
-> 두 트랜잭션은 계속해서 대기하고 있지만 절대로 완료되지 않음

예를들어 트랜잭션 t1이 student에 대한 잠금을 얻고, 트랜잭션 t2가 grade에 대한 잠금을 얻었다 가정
-> 이후 이 잠금을 유지한 채, 서로 잠금을 건 자원에 잠금이 필요한 처리(INSERT/UPDATE/DELETE)를 실행하면, 아무리 기다려도 상황이 바뀌지 않는데 이를 교착 상태라고 함

코드 예시

-- 첫 번째 연결
START TRANSACTION;
-- 어떤 테이블의 레코드 수정
UPDATE t1 SET column1 = 'new_value' WHERE column2 = 'some_value';
-- 두 번째 연결
START TRANSACTION;
-- 같은 테이블의 레코드 수정
UPDATE t1 SET column1 = 'other_value' WHERE column2 = 'some_value';
-- 첫 번째 연결
COMMIT;
-- 두 번째 연결
COMMIT;

위의 예시에서 두 트랜잭션은 동시에 동일한 테이블의 레코드를 수정하려 함
-> 첫 번째 트랜잭션이 완료될 때까지 두 번째 트랜잭션은 대기하게 되는데 이러한 경우에 두 번째 트랜잭션이 대기 상태에 빠져있어서 어떤 동작을 할 수 없게 되는 것이 교착 상태

잠금 타임아웃의 경우에는 일정 시간 기다리면 상황이 개선될(잠금을 건 곳에서 잠금을 풀) 가능성이 있지만, 교착 상태는 상황이 개선될 가능성이 있음
-> 이 때문에 보통 DBMS에서는 교착 상태를 독자적으로 검출해 교착 상태에 보고

교착상태 낮추기

  1. 트랜잭션 자주 커밋(더 작은 단위로 트랜잭션을 구성하여 교착 상태 가능성 낮추기)
  2. 정해진 순서대로 테이블(또는 행)에 엑세스 하게 함
  3. 필요 없는 경우에는 읽기 잠금 획득(SELECT ~ FOR UPDATE 등)의 사용 피하기
  4. 잠금 범위를 좁히거나 더 작은 것으로

ex) mysql에서 트랜잭션의 격리수준을 Committed READ로 하기

  1. 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착상태 발생하기 쉬움

위 경우 테이블 단위의 잠금을 획득해 갱신을 직렬화하면 동시성은 떨어지지만, 교착 상태 회피 가능

innodb형 Mysql에서는 테이블에 적절한 인덱스를 추가해 이를 이용하게 하여 교착 상태 회피 가능
-> 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸리게 됨
-> 인덱스란 특정 컬럼에 대한 데이터 검색을 빠르게 하기 위한 데이터 구조

예시

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX idx_users_email ON users (email);

주의해야 할 트랜잭션 처리

오토커밋

오토커밋이란 쿼리 단위로 커밋하는 설정
-> 간단한 쿼리의 실행과 테스트에는 편리하지만, 애플리케이션의 잠금을 실행하는 데는 커밋의 부하가 너무 높음
-> 이 경우엔 적절한 트랜잭션 단위와 격리수준을 이용해 오토커밋을 사용하지 않도록 해야 함

긴 트랜잭션

긴 트랜잭션은 트랜잭션의 동시성이나 자원의 유효성을 저하 시킴
-> 타임아웃이나 교착상태를 발생 시킬 가능성이 높아짐

대량 처리를 한 트랜잭션이 실행할 경우, 이를 롤백하기 위해 대량의 UNDO로그를 트랜잭션 종료 시까지 유지해야 함
-> UNDO로그의 크기가 쓸데 없이 커질 수 있음
-> 이를 막기 위해 대량 처리는 적당한 크기의 트랜잭션으로 나눠서 실행하자

profile
WOOK JONG KIM
Journey for Backend Developer
이전 포스트

SQL문의 기본

다음 포스트

테이블 설계의 기초(테이블 및 정규화)

0개의 댓글