[Transaction] 01. Transaction

이번 포스트에서는 Transaction과 동작 방식에 대해서 살펴보자. 일반적인 쿼리의 기준은 mysql로 한다.

 

다 아는 Transaction이야기

 

Transaction의 정의

Transaction이란 데이터 처리의 한 단위로 개별 쿼리 단위가 아닌 사용자의 액션(업무)단위의 큰 틀로 묶어서 제어하게 된다. 트랜젝션의 목적은 데이터베이스 내의 데이터를 완전하게 유지하기 위해서 사용된다.

예를 들어 A 계좌에서 B 계좌로 100원을 이체해야하는 경우를 생각해보자.

이 과정에서는 2개의 update 문장이 필요하다. 즉 A 계좌에서 출금 update가 일어나고 B 계좌에서 입금 update가 일어나야 한다. 이 두 개의 sql을 하나로 묶은 것이 이체라는 업무이다.

만약 A 계좌에서 성공적으로 인출한 후 무언가 사고가 발생했다면 어떻게 해야할까? 성공한 update 한건이 아깝기는 하지만 업무가 실패 했으므로 과감히 되돌려야 한다. 이 과정을 rollback이라고 한다. 반면에 A 계좌에서 성공적으로 인출 후 B 계좌에 입금까지 완료되면 업무는 성공이다. 이제 callback이 필요하다. 이렇듯 트랜젝션은 완전히 성공하거나 완전히 실패해야 하기 때문에 all or nothing 이라고 한다.

 

commit과 rollback 동작 근거

그럼 commit 과 rollback은 어떻게 동작할까?

트랜젝션이 종료되기 전에는 모두 임시 데이터이며 변경 전 데이터로 복구될 수 있다. 이를 위해 데이터베이스는 UNDO tablespace를 사용한다.

만약 사용자의 계좌에 100원이 있고 여기서 100원을 출금해보자. 이를 위해 update 문장을 날리면 undo 테이블스페이스에 기존의 변경되기 전 데이터를 집어 넣는다. 그리고 실제 테이블에는 오히려 임시 데이터를 저장한다.(100-100=0)

이제 commit 이 진행되면 임시 데이터가 확정되고 undo의 값은 삭제될 수 있다.(정책에 따라 다르다). 그리고 만약 rollback 된다면 undo에 있던 데이터로 다시 테이블을 덮어쓰게 된다.

 

트랜젝션의 단계

 

트랜젝션의 시작

트랜젝션을 시작하기 위해서는 start transaction 명령을 사용한다.

start transaction;

또는 DML을 시작하는 경우 자동으로 트랜젝션이 시작된다.

 

종료 전 데이터의 상태

트랜젝션이 시작되고 종료되기 전까지의 데이터는 모두 임시적이며 변경 전의 데이터로 복구될 수 있다.

이때 신경써야할 것이 방금 추가/수정/삭제 된 데이터에 대한 접근 문제이다. 변경등을 유발한 트랜젝션은 해당 row에 대한 lock을 확보하게 된다. 따라서 읽기는 물론 수정, 삭제가 자유롭다. 

반면 다른 트랜젝션은 당연히 편집이 불가하다. 하지만 이 데이터를 읽을 수 있는가 또는 어느 시점에 읽을 수 있는가는 조금 다른 문제이다. 이 부분에 A.C.I.D와 transaction isolation level에 대한 이해가 필요하다.

 

트랜젝션의 종료

트랜젝션을 종료하기 위해서는 commit 또는 rollback을 입력한다. 또는 DDL(create, alter, drop, ...)이나 DCL(grant, revoke)문장을 실행하면 자동적으로 commit 된다. 마지막으로 사용하던 SQL 툴이 정상적으로 종료되면 commit, 비 정상적으로 종료되면 rollback 된다.

 

A.C.I.D 원칙과 처리 방식

 

A.C.I.D

트랜젝션이 처리되기 위해서는 A.C.I.D라는 원칙이 보장되어야 한다.

• Atomicity(원자성): 트랜젝션 작업은 부분적으로 성공하면 안되고 하나로 움직여야 한다. 즉 출금은 성공하고 입금이 실패한 상태로 존재하면 안된다. All or Nothing이다.
• Consistency(일관성): 데이터에 일관성이 있어야 한다. 이체 전, 후의 금액 합이 달라지거나 자료형이 갑자기 숫자에서 문자로 바뀌는 등의 모순이 발생하면 안된다. 100원을 출금했는데 90원만 입금 되면 안된다.
• Isolation(독립성): 다른 트랜젝션에 간섭을 주거나 받지 않아야 한다. 현재 트랜젝션의 수행 중간 내용을 다른 트랜젝션이 침범하면 안된다. 이체가 일어나는 과중에 다른 이체가 개입되면 안된다.
• Durablity(지속성): 트랜젝션이 성공적으로 완료되었을 때 결과는 영구적으로 반영되어야 한다. 송금이 완료되었으면 시스템의 장애가 발생했더라도 송금한 결과가 복구되어야 한다.

 

데이터의 무결성과 lock의 개념

이런 A.C.I.D의 원칙을 보장하기 위한 가장 완벽한 방법은 한번에 하나의 T.X만 동작하게 하는 것이다. 하지만 동시에 여러개의 T.X를 처리하지 못한다면 데이터베이스의 성능은 심각한 문제가 발생할 것이다. 이를 위해 데이터베이스가 꺼내든 칼은 lock이다.

lock은 동시에 진행되는 여러 트랜잭션 중에 특정 자원에 대한 접근을 제어하는 역할을 한다. lock은 T.X 처리의 순차성을 보장하기 위한 방법으로 대상을 수정하는 T.X가 그 권한을 가지게 한다. lock이 있으면 대상에 대한 전권을 가지지만 반대의 경우는 사용에 제약을 받을 수 있다.

lock은 여러 형태로 분류해볼 수 있다.

먼저 설정 범위에 따라 분류하면 데이터베이스, 파일, 테이블, 페이지, 컬럼, 행 단위의 lock이 있을 수 있는데 테이블 lock과 행 단위 lock이 주로 사용되고 나머지는 잘 사용되지 않는다.

• 테이블 수준의 lock(table lock): 테이블 자체에 걸리는 lock으로 주로 DDL 과정에서 걸린다. 즉 테이블의 alter가 진행중인 과정에는 테이블에 lock이 걸린다.
• 행 수준의 lock(row lock): 하나의 행에 걸리는 lock으로 DML에 대한 lock이며 가장 일반적인 lock이다.

동작 방식에 대해서도 여러가지 lock이 존재한다.

	공유 락(Shared Lock: S Lock)	베타 락(Execlusive Lock: X Lock)
정의	데이터 조회(Select)과정에서 주어지는 lock	데이터 변경 작업(Insert, Update, Delete 등)에 설정되는 lock
특징	여러 사용자가 동시에 같은 데이터를 읽도록 공유 락 끼리는 충돌하지 않음 S lock이 걸린 데이터에는 X Lock 설정 불가	해당 T.X가 완료될 때까지 다른 어떤 세션도 접근 불가  - 데이터 변경 중에 발생할 수 있는 충돌 방지를 통해 데이터의 무결성 유지에 중요

• S Lock은 데이터베이스의 성능과 관련이 깊다. 만약 너무 많은 S Lock이 사용될 경우 데이터베이스 성능에 부정적인 영향을 줄 수 있기 때문에 데이터에 대해 세밀한 제어가 필요할 때만 사용해야 한다.
• 이 외에도 업데이트 락(update lock), 내재 락(intent lock) 등이 있긴 하다.

 

lock 테스트

 

기본 설정 및 테이블 준비

기본적으로 autocommit 속성은 당연히 false 여야 한다. 

autocommit 속성은 @@autocommit 을 사용한다. 전역에서 설정할 때는 @@global.autocommit, 현재 session에서 적용할 때는 @@session.autocommit을 사용하며 @@autocommit 이라고도 할 수 있다. 모두 false로 해주자.

select @@global.autocommit, @@session.autocommit, @@autocommit;
set @@session.autocommit = false;
set @@global.autocommit = false;

mysql workbench에서는 session의 기본 autocommit 이 true로 되어있는데 Preference > Sql Editor > Sql Execution > General에서 해당 option을 꺼주도록 하자. (restart 필요)

 

이제 간단한 테이블을 만들어보자.

drop table if exists account;

create table account(
  no int auto_increment primary key,
  name varchar(100) not null,
  amount int not null
);
insert into account (name, amount) values('hong gil dong', 100);
commit;

select * from account;

 

테스트

이제 두 개의 Transaction을 통해 exclusive lock의 동작에 대해 살펴보자.

Transaction A	Transaction B
start transaction; select * from account;	TX A가 새로 시작됐고 Shared Lock 상태로 데이터를 조회한다.
Shared Lock이기 때문에 새로운 TX가 접근하는데 문제가 없다.	start transaction; select * from account;
update account set amount=200 where no=1; select * from account;	TX A가 DML을 실행하여 배타적 lock을 no=2인 행에 설정한다. TX A는 자신이 변경한 데이터를 조회/수정/삭제 가능하다.
TX B는 상황에 따라 다르기는 하지만 조회가 가능하다.  - 하지만 데이터는 다를 수 있다.	select * from account;
TX A가 lock을 가진 행과 상관 없는 DML은 얼마든지 가능하다.	insert into account (name, amount) values('jang gil san', 200); commit; select * from account;
하지만 TX A가 lock을 가진 행에는 select이외의 작업이 불가하다.	update account set amount=200 where no=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;	TX A가 transaction을 종료하면서 lock을 반납한다.
	update account set amount=200 where no=1; select * from account;

 

이렇게 하면 어느정도 데이터의 무결성이 확보되어 A.C.I.D가 달성될 것 같지만 독립성은 사실상 지키기는 너무 어렵다.(위에서도 select가 서로 교차로 진행되고 있다.)

독립성 측면에서, 특히 "다른 트랜잭션이 개입되면 안 된다"라는 부분은 고민이 많다. A 트랜잭션이 데이터 락을 가진 상태이고 B 트랜잭션은 데이터를 참조해야하는 경우를 생각해보자. B 입장에서는 데이터를 슬쩍 보기만 하고 다른 일을 하러 가야 하는데 만약 A가 작업 중이라고 안 보여주면 한없이 기다려야 하는 문제가 발생한다. 이러한 상황은 동시성을 저해하며 결국 애플리케이션의 전반적인 성능을 크게 저하시킬 수 있다.

하지만 다른 입장에서는 편집중인 데이터는 어차피 변경될 것이므로, 외부로 노출하지 않는 것이 좋다고 볼 수도 있다.

이런 다양한 상황에서 독립성을 유지하는 기준이 Isolation level이다. Isolation Level은 트랜잭션들이 서로 어떻게 영향을 주고 받을 수 있는지를 정의함으로써 데이터의 무결성과 동시성을 효과적으로 관리할 수 있게 해준다.

https://goodteacher.tistory.com/689

[Transaction] 02. Transaction Isolation Level