5.4Handling Transactions

What are Transactions Used For?

은행 거래와 관련된 데이터베이스를 관리한다고 해보자.

Alyson의 $50을 Gia에게 보내는 거래 상황을 가정하면, 데이터베이스는 이렇게 작동해야 한다.

1. Alyson의 계좌에서 $50을 차감한다.

sql
UPDATE accounts
SET balance = balance - 50
WHERE name = 'Alyson';

1. Gia의 계좌에서 $50을 더한다.

sql
UPDATE accounts
SET balance = balance + 50
WHERE name = 'Gia';

굉장히 단순해보이지만, 실제로는 두 개의 쿼리가 연결된게 아니다보니 첫번째 쿼리만 실행되고 다음 쿼리가 실행되기 전에 에러가 날 수도 있다.

Transaction은 두 쿼리 모두가 실행되거나, 또는 하나가 실행 안될거면 모두 실행이 되지 않게 하는 기능이다.

Transactions

우선 간단한 예시 데이터를 만들어보자.

sql
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY, 
  name VARCHAR(20) NOT NULL,
  balance INTEGER NOT NULL
);

Alyson과 Gia에게 $100을 넣어주자.

sql
INSERT INTO accounts (name, balance)
VALUES
  ('Gia', 100)
  ('Alyson', 100)

Transaction을 만드는 방식은 데이터베이스에 일종의 가상 워크스페이스를 만들고 여기에 Connection들을 만드는 것이다. 즉, 메인 데이터 풀이 있으면(우리 예시의 경우 accounts 테이블) 여기에 여러개의 connection을 만들고 해당 커넥션들끼리 서로 연결되게 한다.

PGAdmin4에선 Query Tool을 열고 BEGIN 키워드를 입력하고 실행한다.

sql
BEGIN;

이렇게 하면,

BEGIN 키워드를 실행해 Valid transaction block으로 표시된 쿼리 툴에서 데이터를 업데이트 해보자.

sql
UPDATE accounts
SET balance = balance - 50
WHERE name = 'Alyson';

이렇게 업데이트를 하고 다시 accounts 테이블을 확인해보면, Alyson의 계좌에는 $50불이 차감되어 있다.

하지만, 다른 쿼리 툴 창에서 확인해보면 Alyson의 계좌는 그대로이다. 즉, BEGIN 키워드를 실행한 쿼리 툴은 별도의 워크스페이스를 만들고 여기에서만 유효한 업데이트를 한 것이다. 아래의 쿼리 역시 BEGIN 키워드를 실행한 쿼리 툴(Valid transaction block)에서 실행해보자.

sql
UPDATE accounts
SET balance = balance + 50
WHERE name = 'Gia';

역시 해당 쿼리 툴에서만 거래가 처리되었고, 다른 쿼리 툴에서 확인해보면 아무런 변화가 없다.

그러면 이렇게 별도의 워크스페이스에서 진행한 거래를 실제 데이터베이스에 반영하려면 어떻게 해야할까? 이럴 땐 COMMIT 키워드를 활용할 수 있다.

sql
COMMIT;

반대로, 잘못된 쿼리를 입력하고 실행했거나 다른 이유로 에러가 나서 다시 원상복구하려면 ROLLBACK 키워드를 사용한다.

sql
ROLLBACK;

잘못된 쿼리를 입력했으면(예를 들면 SELECT * FROM 존재하지 않는 테이블명 등) 에러가 나서, 해당 쿼리 툴에서는 정상적인 쿼리 작업을 추가적으로 할 수가 없게 된다. 이럴 때 ROLLBACK을 통해 원점으로 돌아갈 수 있다.

처음에 만든 Connection이 에러가 나서 의도치 않게 종료되었을 때는 Postgres가 자동으로 ROLLBACK을 실행해서 문제가 없도록 한다.

이런 의도치 않은 종료 상황을 시뮬레이션 해보려면, PGAdmin4에서 Dashboard 탭에 들어가 Session에 보면 작동하고 있는 쿼리 툴을 강제로 종료해보면 된다. 이렇게 강제로 종료하면, 별도로 만든 워크스페이스에서 진행한 거래는 실제 데이터베이스에 반영이 되지 않는 것을 확인할 수 있다.