[MySQL] ACID 개념과 Procedure를 활용한 Transaction 실습하기

[MySQL] 데이터베이스 ERD 설계하는 법(drawSQL)

[MySQL] 데이터베이스 ERD 설계하는 법(drawSQL)

이전 글 읽으러 가기!

 

👋 소개

안녕하세요! 대학생 개발자 주이어입니다.

오늘은 평소에 자주 다루지 않았던 MySQL 카테고리로 글을 적게 되었습니다.

주제는 Procedure를 활용한 Transaction 실습하기 인데요.

 

이 주제를 적게된 이유는 제가 최근에 Nest로 Transaction을 구현하는 여러 시스템 설계에 대해서 공부하면서 관련 글을 적고 싶다는 생각을 했었고, 이 글을 적기 전에 MySQL로 Transaction이 뭔지 정리하는 글이 있으면 좋겠다는 생각이 들어서 이 글을 적게 되었습니다.

 

그럼 바로 본론으로 들어가도록 하겠습니다.

---

🧪 ACID란?

ACID는 전공생이라면 아마 한 번쯤은 들어봤을 법한 트랜잭션이 지켜야 하는 4가지 성질을 의미합니다.

각각의 성질과 함께 왜 지켜져야 하는지 예시도 같이 보여드리도록 하겠습니다.

 

1️⃣ Atomicity (원자성)

먼저 A는 Atomicity(원자성)로 "트랜잭션의 작업은 모두 성공하거나 모두 실패해야 한다"를 의미합니다.

보통 ACID는 계좌이체로 예시를 드는 경우가 많습니다.

돈과 관련된 중요한 작업인 만큼 ACID 규칙을 꼭 지켜야 하기 때문입니다.

 

그럼 Atomicity를 지키지 않았을 경우 어떤 문제가 생기는지 예시로 보여드리도록 하겠습니다.

사진 1

먼저 위와 같이 Juyear와 Siwoo 2명의 사람이 있다고 가정을 해보겠습니다.

사진 2

Juyear 계좌에서 Siwoo 계좌로 3000원을 송금했다고 하면 위와 같은 하나의 트랜잭션 작업이 일어나게 될 것입니다.

 

근데 사진처럼 3000원을 빼고나서 오류가 발생한다면 어떻게 될까요?

Juyaer 계좌에서는 3000원이 분명 줄었지만, Siwoo 계좌에서는 3000원이 늘어나지 않게 될 것이고,

3000원의 존재는 사라지게 될 것입니다.

 

이럴 경우 Atomicity 성질을 어겼다고 말할 수 있습니다.

Atomicity 성질은 위에서 말했듯 트랜잭션의 모든 작업이 성공하거나 모든 작업이 실패해야 합니다.

하지만 3000원을 빼는 과정까지만 성공하고, 3000원을 더하는 과정은 실패했으니 부분성공, 부분실패라는 문제가 발생합니다.

 

이럴 경우 Rollback을 사용하여 성공했던 작업을 되돌릴 수 있습니다.

 

2️⃣ Consistency (일관성)

C는 Consistency(일관성)로 "트랜잭션의 전과 후에 DB 규칙이 항상 유지되어야 한다" 입니다.

예시를 보는게 가장 이해하기 쉬우니 바로 넘어가겠습니다.

사진 3

만약 기존 DB에 위와 같은 규칙이 있었다고 가정을 해봅시다.

사진 4

근데 트랜잭션을 수행하고 난 이후 돈이 0보다 작아진다면, 이는 기존 DB의 규칙을 위반하게 될 것입니다.

또한 돈이 음수가 되는 말도 안되는 상황이 발생할 수 있습니다.

 

이 경우 일반적으로 Table에 Check로 규칙을 추가하거나, 트랜잭션에서 확인하는 작업을 거쳐야 합니다.

 

3️⃣ Isolation (격리성)

I는 Isolation(격리성)은 "각각의 트랜잭션이 독립적이어야 한다" 입니다.

이 또한 바로 예시로 보여드리도록 하겠습니다.

사진 5

만약 위와 같이 2개의 트랜잭션이 동시에 수행된다고 가정해보겠습니다.

첫 번째 트랜잭션은 Juyear 계좌에서 Alex 계좌로 3000원을 송금하는 트랜잭션이고,

두 번째 트랜잭션은 Siwoo 계좌에서 Alex 계좌로 3000원을 송금하는 트랜잭션 입니다.

 

2개의 트랜잭션이 수행되고 난 이후 Alex의 계좌는 5000원 + 3000원 + 3000원 총 11000원이 되어야 할 것입니다.

하지만 2개의 트랜잭션 모두 5000원 이었던 Alex의 계좌를 참고하고 있기 때문에 결과는 모두 8000원이 되어버립니다.

(격리 수준과 LOCK 정책에 따라 이러한 문제가 발생하지 않을 수도 있지만, Isolation이 보장되지 않는 환경에서는 위와 같은 문제가 대표적으로 발생할 수 있습니다.)

 

이러한 문제를 해결하기 위해서 LOCK과 같은 기능을 사용하며, 해당 부분에 대해서는 기회가 된다면 다음에 설명을 드리도록 하겠습니다.

 

4️⃣ Durability (지속성)

마지막으로 D는 Durability(지속성)로 "Commit이 완료된 트랜잭션의 결과는 유지되어야 한다" 입니다.

이 부분은 트랜잭션의 로그 기록과 DB 반영의 순서와 관련되어 있는 부분인데요.

깊게 들어가면 내용이 너무 길어질 수 있으니 간단하게 설명드리도록 하겠습니다.

 

먼저 DB는 오류가 발생했을 때 회복하는 방법이 크게 2가지가 있습니다.

• 즉시 갱신 : 변경 전과 후의 값을 로그에 기록한 후 DB에 즉시 반영합니다. 오류가 발생할 경우 Commit이 실행된 트랜잭션은 복구하고, Commit이 실행되지 않은 트랜잭션은 되돌립니다.
• 지연 갱신 : 변경 내용을 로그에만 기록한 후 DB에는 반영하지 않습니다. 그 후 Commit 시점에 한 번에 반영합니다. 오류가 발생할 경우 Commit이 실행된 트랜잭션은 복구하고, Commit 실행되지 않은 트랜잭션은 애초에 DB에 반영하지 않았기 때문에 무시합니다.

이러한 회복 방법은 Commit이 완료된 트랜잭션이 서버 오류로 인해 트랜잭션이 수행되기 전으로 롤백됐다면,

이를 복구하기 위해서 존재합니다.

즉, Durability, Commit이 완료된 트랜잭션은 결과가 유지되어야 한다 라는 성질을 만족하기 위해 사용되는 것이죠.

---

⚙️ Procedure를 활용한 Transaction 실습하기

이제 ACID가 무엇인지 어느정도 공부를 했으니 MySQL Workbench 8.0에서 직접 실습을 해보도록 하겠습니다.

CREATE DATABASE prac_transaction;

USE prac_transaction;

CREATE TABLE accounts (
	id INT PRIMARY KEY,
	name VARCHAR(50),
	balance INT
);

먼저 실습을 진행할 DB와 TABLE을 생성해주었습니다.

TABLE은 accounts하나를 생성했고, id, name, balance로 계좌 정보를 담을 수 있도록 설정해주었습니다.

INSERT INTO accounts VALUES
(1,'Juyear', 10000),
(2,'Siwoo', 5000);

그 다음 ACID 설명 때 예시로 들었던 Juyear와 Siwoo 2명의 계좌 데이터를 넣어주었습니다.

Juyear는 10000원, Siwoo는 5000원으로 설정해주었습니다.

DELIMITER $$
DROP PROCEDURE IF EXISTS transfer_money;
CREATE PROCEDURE transfer_money()
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;
    
	START TRANSACTION;

	UPDATE accounts
	SET balance = balance - 3000
	WHERE name = "Juyear";

	UPDATE accounts
	SET balance = balance + 3000
	WHERE name = "Siwoo";

	COMMIT;
END $$
DELIMITER ;

그 다음은 실습의 핵심 부분인 PROCEDURE를 작성해주었습니다.

START TRANSACTION
COMMIT

트랜잭션 시작은 START TRANSACTION으로 완료는 COMMIT으로 지정할 수 있습니다.

트랜잭션은 START와 COMMIT 사이에 있는 작업을 진행하게 됩니다.

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	ROLLBACK;
END;

이 부분은 트랜잭션 수행 도중 오류가 발생할 경우 ROLLBACK을 처리해주는 부분입니다.

MySQL에서는 TRANSACTION 도중 오류가 나도 자동으로 ROLLBACK 해주지 않기 때문에 HANDLER를 사용하였습니다.

CALL transfer_money();
SELECT * FROM accounts;

CALL로 PROCEDURE를 실행시킨 이후 테이블을 확인해보면

결과 사진 1

위와 같이 Juyear 계좌의 돈은 3000원이 줄었고, Siwoo 계좌의 돈은 3000원이 증가한 것을 확인할 수 있습니다.

하지만 트랜잭션을 실습하기 위해서는 중간에 오류가 발생하는 경우를 확인해봐야겠죠.

CREATE PROCEDURE transfer_money()
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	END;
    
	START TRANSACTION;

	UPDATE accounts
	SET balance = balance - 3000
	WHERE name = "Juyear";

 	INSERT INTO accounts VALUES
        (1,"Alex",20000);

	UPDATE accounts
	SET balance = balance + 3000
	WHERE name = "Siwoo";

	COMMIT;
END $$

트랜잭션 중간에 일부로 오류를 발생시키기 위해서 PRIMARY KEY가 중복되도록 값을 추가해보았습니다.

오류 발생 위치는 Juyear 계좌의 돈 3000원을 뺀 이후, Siwoo 계좌의 3000원을 추가하기 전으로 설정하였습니다.

그 후 다시 PROCEDURE를 실행시켜 보면,

결과 사진 2

분명 트랜잭션이 실행되었지만 돈이 줄지도, 증가하지도 않은 것을 알 수 있습니다.

만약 Atomicity 성질을 지키지 않았다면, Juyear 계좌의 돈은 감소하지만, Siwoo 계좌의 돈은 증가하지 않는 문제가 발생했을 것입니다.

CREATE TABLE accounts (
	id INT PRIMARY KEY,
	name VARCHAR(50),
   	balance INT, CHECK (balance >= 0)
);

Consistency를 테스트 하기 위해서 TABLE에 돈이 0 이상이어야 한다는 조건을 넣어주었습니다.

그 후 PROCEDURE를 INSERT문을 제외한 기존 코드로 되돌려주고,

계속해서 실행을 해보면,

결과 사진 3

처음 몇번은 계좌가 계속 업데이트 되다가 Juyear 계좌의 돈이 1000원 남았을 때, 더 이상 트랜잭션이 정상 Commit이 되지 않는 것을 확인할 수 있습니다.

이는 1000원에서 3000원을 차감하면서 기존 DB 규칙에 어긋나 오류가 발생하여 롤백이 되었다는 것으로 볼 수 있습니다.

 

 

Isolation의 경우 MySQL만으로 실습하기에는 조금 복잡한 부분이 있습니다.

왜냐하면 READ COMMITTED, REPEATABLE READ와 같은 격리 수준에 따라 동시에 실행되는 트랜잭션이 서로 어떤 영향을 주는지를 확인하기 위해서는 여러 세션을 동시에 다뤄야 하기 때문입니다.

(하나의 세션으로 시각적으로 보여드릴 순 있지만, 제대로된 실습은 진행하기 어렵습니다.)

 

핵심은 여러 트랜잭션이 동시에 실행될 때, 서로의 작업 결과에 영향을 주지 않도록 보장하는 것입니다.

이 부분에 대해서는 다음에 기회가 된다면 정리해보도록 하겠습니다.

---

😊 마무리

이렇게 오늘 ACID의 개념과 Procedure를 활용한 Transaction 실습까지 진행해보았습니다.

Transaction은 주로 백엔드 개발에서 자주 사용되는 개념이기 때문에 한 번씩 실습을 진행해보시길 추천드립니다.

이후 Nest에서 이러한 Transaction 개념을 이용해서 실제로 백엔드에서 어떻게 사용되는지도 정리해볼 예정입니다.

 

그럼 지금까지 읽어주셔서 감사드리며, 다음에 더 유익한 글로 찾아오도록 하겠습니다.

by. 대학생 개발자 주이어

 

https://discord.gg/8Hh8WgM4zp

KYT CODING COMMUNITY Discord 서버에 가입하세요!

KYT CODING COMMUNITY 가입하기!