[SQLite] 트랜잭션 (TRANSACTION)

데이터베이스에서 트랜잭션 (Transaction)은 SQL 작업의 무결성 (Integrity)을 유지하기 위한 매우 중요한 개념이다. 즉 우리가 현실 세계에서 이루어지는 "거래" 시스템과 마찬가지로 특정 작업이 모두 수행되거나 모두 수행되지 않게 함으로써 그 무결성을 유지하는 것이다. (돈은 냈지만 물건을 받지 않았다면 거래는 성립되지 않는다. 그 반대도 마찬가지이며 거래 당사자들의 이해 조건이 모두 충족된 경우에만 거래가 성립한다.)

 

 

트랜잭션 구문

 

트랜잭션 구문의 시작은 BEGIN 키워드로 시작한다. BEGIN 이후 실행하고자 하는 SQL 구문들을 추가하면 되며 마지막으로 COMMIT 명령어를 통해 트랜잭션이 시작된 이후 수행된 모든 SQL 연상이 데이터베이스 파일(물리적)에 추가된다. 즉 BEGIN 이후의 SQL 문들은 프로세스의 메로리상에서만 반영된 상태이고 실제 물리적인 데이터베이스 파일에는 반영된 상태가 아닌 것이다. 그렇기에 만약 전원 시스템 혹은 운영체제가 급작스럽게 종료되는 경우 그 동안 수행되었던 연산들은 데이터베이스 파일에는 반영되지 않게 된다.

 

BEGIN;

실행할 SQL 구문...

실행할 SQL 구문...

COMMIT;

 

그리고 ROLLBACK이라는 명령어는 트랜잭션 시작 이후로 수행된 모든 연산을 취소 한다. 즉 트랜잭션을 시작한 시점으로 데이터베이스를 복원하는 역할을 한다.

그럼 아래 예제를 통해 조금 더 살펴 보도록 하자.

 

sqlite> select * from cars; car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    4           2           K3          1200000    5           2           K7          2200000    7           1           Avante      1000000    8           2           K9          4000000    sqlite> BEGIN;   -> 트랜잭션의 시작 sqlite> DELETE FROM cars WHERE name = 'K9'; sqlite> SELECT * FROM cars;   car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    4           2           K3          1200000    5           2           K7          2200000    7           1           Avante      1000000    sqlite> ROLLBACK;  -> 처음 시점으로 복원 sqlite> SELECT * FROM cars;   car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    4           2           K3          1200000    5           2           K7          2200000    7           1           Avante      1000000    8           2           K9          4000000

 

AUTOCOMMIT 모드

 

사실 SQLite3는 AUTOCOMMIT 모드를 지원한다. 명시적으로 BEGIN, COMMIT/ROLLBACK 명령어를 사용하지 않더라도 내부적으로 모든 SQL 문을 하나의 트랜잭션 내에서 수행되도록 하는 것이다. 즉 INSERT/DELETE/UPDATE와 같은 SQL 연산들이 SQLite3 내부 트랜잭션에서 수행되도록 하고 만약 실패 시 자동으로 ROLLBACK 될 수 있도록 지원하는 것이다. 이러한 AUTOCOMMIT 모드는 명시적으로 트랜잭션을 시작하지 않는 이상 기본적으로 자동 적용된다.

 

중첩 트랜잭션

 

기본적으로 SQLite3에서는 중첩 트랜잭션을 지원하지 않는다. 즉 아래와 같은 형태의 트랜잭션 사용은 불가능하다는 것이다.

 

BEGIN;

SQL 구문

SQL 구문

BEGIN;          -> 트랜잭션 에러 발생

 

그러나 SAVEPOINT라는 명령어를 사용하면 중첩된 트랜잭션을 생성할 수 있다. 트랜잭션의 별칭 부여는 물론이고 롤백(특정 별칭의 위치)도 가능하다.

 

SAVEPOINT [별칭]

 

SAVEPOINT 는 BEGIN과 COMMIT 명령어 사이에서 사용하거나 BEGIN 없이 단독으로 사용될 수 있다. 다만 BEGIN 명령어 없이 단독으로 사용하기 위해서는 BEGIN DEFERRED TRANSACTION과 같은 동작을 SAVEPOINT 첫 문장에 수행해야 한다. DEFERRED는 데이터베이스 파일에 대한 자금 수준을 명시하는 옵션으로 DEFERRED, IMMEDIATE, EXCLUSIVE 3가지가 있다. 이는 나중에 다시 자세히 알아보도록 하겠다.

 

 

ROLLBACK TO SAVEPOINT [별칭]

 

이 명령어는 수행 중인 트랜잭션을 미리 표시해둔 [별칭]의 위치로 복원한다. 주의 할 점은 특정 시점으로 트랜잭션을 복원할 때 그 사이에 수행한 다른 트랜잭션이 있는 경우 해당 트랜잭션도 함께 복원된다는 것이다. 이때 BEGIN 구문을 통해 명시적으로 트랜잭션을 시작했거나 트랜잭션 스택(Transaction Stack)에 SAVEPOINT로 생성한 다른 트랜잭션이 있는 경우 전체 트랜잭션은 종료되지 않는다. 만약 [별칭]없이 해당 명령어를 수행하면 트랜잭션 스택 내 모든 트랜잭션이 복원되므로 주의하도록 하자.

 

RELEASE SAVEPOINT [별칭]

 

RELEASE SAVEPOINT의 경우 기본적으로 해당 별칭의 트랜잭션을 데이터베이스에 반영하도록 되어 있다. 하지만 트랜잭션 스택 내 SAVEPOINT로 생성한 모든 트랜잭션이 RELEASE 명령어로 반영되거나 명시적으로 COMMIT을 사용하지 않은 경우에는 실제 데이터베이스 파일은 수정하지 않고 프로세스의 내무 메모리상에서만 반영된다. 그러므로 RELEASE SAVEPOINT 수행 후 ROLLBACK TO SAVEPOINT 로 해당 위치보다 앞선 시점으로 이동하거나 프로세스가 비정상적으로 종료되는 경우에는 RELEASE SAVEPOINT로 커밋한 내용일지라도 실제 데이터베이스 파일에는 반영되지 않는다. 그리고 트랜잭션 스택 TOP에 위치한 트랜잭션이 RELEASE되면 그 사이에 진행 된 모든 트랜잭션 또한 데이터베이스 파일에 반영된다.

 

그럼 지금까지 배운 내용들을 실제 예제를 통해 연습해 보도록 하자.

 

먼저 트랜잭션의 시작과 동시에 SAVEPOINT를 통해 start_point를 시작 지점으로 표시해 보도록 하자.

 

 

sqlite> select * from cars; car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    4           2           K3          1200000    5           2           K7          2200000    7           1           Avante      1000000      sqlite> BEGIN; sqlite> SAVEPOINT start_point;

 

그리고 특정 레코드를 삭제하고 이를 delete_point 로 표시해두록 하겠다. 이렇게 하면 지금까지 트랜잭션 스택에는 start_point와 delete_point가 쌓여 있는 상황이 된다.

 

sqlite> DELETE FROM cars     ...> WHERE name='K3';   sqlite> SAVEPOINT delete_point;   sqlite> SELECT COUNT(*) FROM cars;   COUNT(*)   ---------- 4

 

 

이번에는 특정 레코드를 삽입하고 이를 insert_point로 표시해 두도록 하겠다. 그럼 지금까지 트랜잭션 스택에는 start_point, delete_point와 insert_point 이 3개가 쌓여 있게 된다.

 

sqlite> INSERT INTO cars    ...> VALUES (8, 2, 'K9', 4000000);   sqlite> SAVEPOINT insert_point; sqlite> SELECT COUNT(*) FROM cars;   COUNT(*)   ---------- 5

 

마지막으로 cars 테이블에 있는 모든 레코드를 삭제하고 이를 delete_all로 표시해 두도록 하겠다. 이로써 현재 트랜잭션 스택에는 총 4개(start_point, delete_point, insert_point, delete_all)가 존재하게 되었다.

 

sqlite> DELETE FROM cars;   sqlite> SAVEPOINT delete_all; sqlite> SELECT COUNT(*) FROM cars;   COUNT(*)   ---------- 0

 

delete_all

insert_point

delete_point

dtart_point

트랜잭션 스택

 

위와 같이 트랜잭션 스택이 쌓여 있는 상황에서 이번에는 한번 insert_point 지점으로 복원을 해보도록 하자. 명령어는 ROLLBACK TO SAVEPOINT [별칭]을 사용하면 된다.

 

sqlite> ROLLBACK TO SAVEPOINT insert_point; sqlite> SELECT COUNT(*) FROM cars;   sqlite> SELECT * FROM cars;   car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    5           2           K7          2200000    7           1           Avante      1000000    8           2           K9          4000000

 

ROLLBACK TO SAVEPOINT 명령어를 통해 insert_point 지점으로 정상적으로 복원이 된 것을 확인할 수 있다.  트랜잭션 스택에서 insert_point와 그 위에 위치한 delete_all 이 함께 삭제되면서 작업한 내용이 복원된 것이다.

 

delete_all (삭제)

insert_point (삭제)

delete_point

dtart_point

트랜잭션 스택

 

마지막으로 RELEASE SAVEPOINT를 통해 delete_point 시점까지 작업한 내용을 메모리에 반영해보도록 하자. 메모리에만 반영되어 있으므로 만약 비정상적으로 전원이 꺼지거나 프로세스가 종료되면 지금까지의 작업 내용은 실제 데이터 베이스 파일에는 반영되지 않을 것이다. 그러므로 실제 데이터베이스 파일에 지금까지 작업한 내용을 적용하기 위해 명시적으로 COMMIT 명령어를 호출하도록 하겠다. (모든 내용을 RELEASE 해도 된다).

 

sqlite> RELEASE SAVEPOINT delete_point; sqlite> COMMIT; sqlite> SELECT COUNT(*) FROM cars;   sqlite> SELECT * FROM cars;   car_id      brand_id    name        price      ----------  ----------  ----------  ---------- 1           1           Sonata      2000000    3           2           K5          2000000    5           2           K7          2200000    7           1           Avante      1000000    8           2           K9          4000000