SQLite 트랜잭션 처리

소개

이 랩에서는 SQLite 트랜잭션 처리를 탐구하고, 트랜잭션을 사용하여 데이터 무결성을 유지하는 데 중점을 둡니다. 여러 관련 작업을 단일 원자적 단위로 처리하도록 보장하면서 트랜잭션을 시작하고 커밋하는 방법을 배우게 됩니다.

이 랩에서는 BEGIN TRANSACTION을 사용하여 트랜잭션을 시작하고, 데이터베이스를 변경한 다음, COMMIT 문을 사용하여 해당 변경 사항을 영구적으로 저장하는 과정을 안내합니다. 또한 ROLLBACK을 사용하여 변경 사항을 실행 취소하고, 더 세분화된 제어를 위해 SAVEPOINT를 사용하는 방법도 배웁니다. 이 실습 경험을 통해 SQLite 에서 트랜잭션이 원자성 (atomicity), 일관성 (consistency), 격리성 (isolation), 지속성 (durability) (ACID 속성) 을 어떻게 보장하는지 이해도를 높일 수 있습니다.

데이터베이스 및 테이블 생성

첫 번째 단계에서는 SQLite 데이터베이스와 사용자 데이터를 저장할 테이블을 생성합니다. 이는 후속 단계에서 트랜잭션 처리를 탐구하기 위한 기반을 제공합니다.

먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.

이제 mydatabase.db라는 SQLite 데이터베이스를 생성해 보겠습니다. 다음 명령을 실행하여 데이터베이스 파일을 생성하고 SQLite 명령줄 도구를 엽니다.

sqlite3 mydatabase.db

SQLite 셸 내부에 있다는 것을 나타내는 프롬프트가 표시됩니다.

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

다음으로, 기본 사용자 정보를 저장할 users라는 테이블을 생성합니다. 이 테이블에는 id (고유 식별자), name, balance의 세 개의 열이 있습니다. sqlite> 프롬프트에서 다음 SQL 명령을 입력하고 Enter 키를 누릅니다.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

이 명령은 다음과 같이 users 테이블을 설정합니다.

• id는 각 사용자의 기본 키 역할을 하는 정수입니다.
• name은 사용자의 이름을 나타내는 텍스트 필드입니다.
• balance는 사용자의 계정 잔액을 나타내는 실수입니다.

이제 초기 데이터를 users 테이블에 삽입합니다.

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

이 명령은 Alice 와 Bob 이라는 두 명의 사용자를 추가하고, 각각 초기 잔액을 100.0 과 200.0 으로 설정합니다.

데이터가 올바르게 추가되었는지 확인하려면 이 명령을 실행하여 테이블의 모든 레코드를 봅니다.

SELECT * FROM users;

예상 출력:

1|Alice|100.0
2|Bob|200.0

이 출력은 각 레코드의 id, name, balance를 보여줍니다. SELECT * 명령은 지정된 테이블에서 모든 열을 검색합니다.

트랜잭션 시작 및 커밋

이 단계에서는 트랜잭션을 시작하고 커밋하는 방법을 배웁니다. 트랜잭션은 여러 관련 작업을 수행할 때 데이터 무결성을 보장하는 데 필수적입니다.

트랜잭션을 시작하려면 BEGIN TRANSACTION 명령을 사용합니다.

BEGIN TRANSACTION;

이 명령은 SQLite 에 변경 사항을 추적하도록 지시하지만, 트랜잭션을 명시적으로 커밋할 때까지 데이터베이스에 영구적으로 적용하지 않습니다.

이제 Alice 로부터 Bob 에게 50.0 을 이체해 보겠습니다.

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

이 명령은 트랜잭션 내에서 Alice 와 Bob 의 잔액을 업데이트합니다. Alice 의 잔액은 50.0 감소하고, Bob 의 잔액은 50.0 증가합니다.

변경 사항을 저장하려면 COMMIT 명령을 사용합니다.

COMMIT;

이 명령은 트랜잭션을 커밋하여 변경 사항을 영구적으로 만듭니다.

테이블을 다시 쿼리하여 변경 사항을 확인합니다.

SELECT * FROM users;

예상 출력:

1|Alice|50.0
2|Bob|250.0

이 출력은 트랜잭션이 성공적으로 커밋되었고 Alice 와 Bob 의 잔액이 그에 따라 업데이트되었음을 확인합니다.

트랜잭션 롤백

이 단계에서는 ROLLBACK 명령을 사용하여 트랜잭션 중에 수행된 변경 사항을 실행 취소하는 방법을 배웁니다. 이는 오류를 처리하고 데이터 무결성을 보장하는 데 필수적입니다.

먼저, 새 트랜잭션을 시작합니다.

BEGIN TRANSACTION;

이제 Alice 로부터 Bob 에게 100.0 을 이체하려고 시도하지만, 이번에는 오류 조건을 시뮬레이션합니다.

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Alice 는 50.0 만 가지고 있으므로, 첫 번째 업데이트는 음수 잔액을 초래합니다. SQLite 는 기본적으로 제약 조건을 적용하지 않지만, 이 작업 후에 오류가 발생하는 것을 시뮬레이션합니다.

변경 사항을 실행 취소하려면 ROLLBACK 명령을 사용합니다.

ROLLBACK;

이 명령은 트랜잭션을 롤백하여 BEGIN TRANSACTION 문 이후에 이루어진 모든 변경 사항을 실행 취소합니다.

users 테이블을 쿼리하여 변경 사항이 롤백되었는지 확인합니다.

SELECT * FROM users;

예상 출력:

1|Alice|50.0
2|Bob|250.0

이 출력은 트랜잭션이 성공적으로 롤백되었고 Alice 와 Bob 의 잔액이 변경되지 않았음을 확인합니다.

세이브포인트 구현

이 단계에서는 트랜잭션 내에서 세이브포인트 (savepoint) 를 사용하는 방법을 배웁니다. 세이브포인트를 사용하면 전체 트랜잭션을 롤백하지 않고 롤백할 수 있는 트랜잭션 내의 중간 지점을 만들 수 있습니다.

먼저, 새 트랜잭션을 시작합니다.

BEGIN TRANSACTION;

savepoint1이라는 세이브포인트를 생성합니다.

SAVEPOINT savepoint1;

이제 Alice 로부터 Bob 에게 20.0 을 이체해 보겠습니다.

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

savepoint2라는 다른 세이브포인트를 생성합니다.

SAVEPOINT savepoint2;

'Charlie'라는 새 사용자를 초기 잔액 300.0 으로 추가해 보겠습니다.

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

이제 Charlie 를 추가하는 것이 실수였다고 결정했다고 가정해 보겠습니다. savepoint1으로 롤백할 수 있으며, 이는 INSERT 문과 Alice 와 Bob 간의 20.0 이체를 실행 취소합니다.

ROLLBACK TO SAVEPOINT savepoint1;

savepoint1으로 롤백한 후 변경 사항을 확인합니다.

SELECT * FROM users;

예상 출력:

1|Alice|50.0
2|Bob|250.0

Charlie 가 테이블에 없고 Alice 와 Bob 의 잔액이 savepoint1 이전의 값으로 돌아간 것을 볼 수 있습니다.

이제 Alice 로부터 Bob 에게 10.0 을 이체해 보겠습니다.

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

마지막으로, 트랜잭션을 커밋합니다.

COMMIT;

음수 잔액 방지를 위한 제약 조건 추가

이 단계에서는 users 테이블에 음수 잔액을 방지하는 제약 조건을 추가합니다. 이는 잘못된 거래를 방지하여 데이터 무결성을 보장하는 데 도움이 됩니다.

SQLite 는 ALTER TABLE ADD CONSTRAINT를 사용하여 기존 테이블에 제약 조건을 추가하지 않으므로, 제약 조건이 포함된 테이블을 새로 생성해야 합니다. 먼저 모든 작업이 원자적 (atomic) 으로 수행되도록 트랜잭션을 시작합니다.

BEGIN TRANSACTION;

동일한 구조이지만 CHECK 제약 조건이 포함된 새 테이블을 만듭니다.

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

기존 테이블의 모든 데이터를 새 테이블로 복사합니다.

INSERT INTO users_new SELECT * FROM users;

기존 테이블을 삭제합니다.

DROP TABLE users;

새 테이블의 이름을 원래 이름으로 변경합니다.

ALTER TABLE users_new RENAME TO users;

변경 사항을 영구적으로 적용하기 위해 트랜잭션을 커밋합니다.

COMMIT;

이제 users 테이블에는 balance 열이 항상 0 보다 크거나 같아야 함을 보장하는 CHECK 제약 조건이 있습니다.

이제 Alice 에서 Bob 에게 1000 을 이체하려고 하는데, 이는 제약 조건을 위반합니다.

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';

이번에는 UPDATE 문이 CHECK 제약 조건을 위반하기 때문에 오류가 발생합니다. Error: CHECK constraint failed: balance >= 0와 같은 오류 메시지가 표시됩니다.

트랜잭션을 롤백합니다.

ROLLBACK;

변경 사항이 롤백되었는지 확인합니다.

SELECT * FROM users;

예상 출력:

1|Alice|40.0
2|Bob|260.0

이것은 ROLLBACK이 실패한 트랜잭션 이전의 원래 상태로 데이터베이스를 성공적으로 되돌렸음을 확인합니다.

요약

이 랩에서는 SQLite 에서 트랜잭션 처리의 기본 사항을 배웠습니다. 트랜잭션을 시작하고 커밋하는 방법, 변경 사항을 롤백하는 방법, 더 세분화된 제어를 위해 세이브포인트 (savepoint) 를 구현하는 방법, 데이터 무결성을 보장하기 위해 제약 조건 (constraint) 을 추가하는 방법을 다루었습니다. 이러한 기술은 강력하고 신뢰할 수 있는 데이터베이스 애플리케이션을 구축하는 데 매우 중요합니다.