트랜잭션¶

트랜잭션이란?¶

트랜잭션은 원자 단위로 처리되는 SQL 문으로 구성된 시퀀스입니다. 트랜잭션의 모든 문은 함께 적용(즉, 커밋)되거나 실행 취소(즉, 롤백)됩니다. Snowflake 트랜잭션은 ACID 속성 을 보장합니다.

트랜잭션은 읽기와 쓰기를 둘 다 포함할 수 있습니다.

트랜잭션은 다음 규칙을 따릅니다.

• 트랜잭션은 결코 “중첩”되지 않습니다. 예를 들어, 커밋된 “내부” 트랜잭션을 롤백하는 “외부” 트랜잭션을 만들거나 롤백된 “내부” 트랜잭션을 커밋하는 “외부” 트랜잭션을 만들 수 없습니다.

• 트랜잭션은 단일 세션과 연결됩니다. 여러 세션이 같은 트랜잭션을 공유할 수 없습니다. 같은 세션에 스레드가 겹치는 트랜잭션을 처리하는 방법에 대한 자세한 내용은 트랜잭션과 다중 스레딩 을 참조하십시오.

용어¶

이 항목에서 사용되는 주요 용어는 다음과 같습니다.

• “DDL”이라는 용어는 CTAS 문(CREATE TABLE AS SELECT ...)은 물론이고 다른 DDL 문도 포함합니다.

• “DML”이라는 용어는 INSERT, UPDATE, DELETE, MERGE, TRUNCATE 문을 나타냅니다.

• “쿼리 문”이라는 용어는 SELECT 및 CALL 문을 나타냅니다.

(저장 프로시저 를 호출하는) CALL 문은 단일 문이지만, 이 문이 호출하는 저장 프로시저는 여러 문을 포함할 수 있습니다. 저장 프로시저와 트랜잭션에 대한 특별한 규칙 이 있습니다.

명시적 트랜잭션¶

트랜잭션은 BEGIN 문을 실행하여 명시적으로 시작할 수 있습니다. Snowflake는 BEGIN WORK 와 BEGIN TRANSACTION 을 동의어로 지원합니다. Snowflake에서는 BEGIN TRANSACTION 을 사용할 것을 권장합니다.

COMMIT 또는 ROLLBACK 을 실행하여 트랜잭션을 명시적으로 종료할 수 있습니다. Snowflake는 COMMIT WORK 를 COMMIT 의 동의어, ROLLBACK WORK 를 ROLLBACK 의 동의어로 지원합니다.

일반적으로, 트랜잭션이 이미 활성 상태이면 모든 BEGIN TRANSACTION 문이 무시됩니다. 하지만 사용자는 추가 BEGIN TRANSACTION 문을 피해야 하는데, 추가 BEGIN TRANSACTION 문을 사용하면 다른 사람이 읽을 때 COMMIT (또는 ROLLBACK) 문과 그에 대응되는 BEGIN TRANSACTION 을 짝지어 읽기가 훨씬 더 어려워지기 때문입니다.

이 규칙의 한 가지 예외는 중첩 저장 프로시저 호출과 관련된 것입니다. 자세한 내용은 범위 지정 트랜잭션 섹션을 참조하십시오.

암시적 트랜잭션¶

트랜잭션은 명시적 BEGIN TRANSACTION 또는 COMMIT / ROLLBACK 없이 암시적으로 시작하고 종료할 수 있습니다. 암시적 트랜잭션은 명시적 트랜잭션과 같은 방식으로 동작합니다. 하지만 암시적 트랜잭션이 시작되는 시점을 결정하는 규칙은 명시적 트랜잭션이 시작되는 시점을 결정하는 규칙과 다릅니다.

중지 및 시작에 대한 규칙은 문이 DDL 문, DML 문 또는 쿼리 문인지에 따라 다릅니다. 문이 DML 또는 쿼리 문인 경우 규칙은 AUTOCOMMIT 의 사용 여부에 따라 다릅니다.

트랜잭션의 암시적 시작 및 종료와 명시적 시작 및 종료 혼용하기¶

혼란을 일으키는 코드를 작성하지 않으려면 같은 트랜잭션에서 암시적 시작 및 종료와 명시적 시작 및 종료를 혼용하면 안 됩니다. 다음은 정당하긴 하지만 권장하는 방법은 아닙니다.

• 암시적으로 시작한 트랜잭션을 명시적 COMMIT 또는 ROLLBACK으로 종료할 수 있습니다.

• 명시적으로 시작한 트랜잭션을 암시적 COMMIT 또는 ROLLBACK으로 종료할 수 있습니다.

트랜잭션 내에서 실패한 문¶

트랜잭션이 한 단위로 커밋되거나 롤백되더라도 이는 한 단위로 성공하거나 실패한다는 것과는 다른 사안입니다. 트랜잭션 내에서 문이 실패하더라도 트랜잭션을 롤백하는 대신 커밋할 수 있습니다.

트랜잭션의 DML 문 또는 CALL 문이 실패하면 실패한 문으로 변경한 내용은 롤백됩니다. 하지만 전체 트랜잭션이 커밋되거나 롤백될 때까지 트랜잭션은 활성 상태를 유지합니다. 트랜잭션이 커밋되면 성공한 문에 의한 변경 내용이 적용됩니다.

예를 들어, 두 개의 유효한 값과 한 개의 잘못된 값을 삽입하는 다음 코드를 생각해보십시오.

CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.');    -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;

실패한 INSERT 문 이후의 문이 실행되면 트랜잭션에서 다른 문 중 하나가 실패하더라도 최종 SELECT 문의 출력에는 정수 값 1과 2가 있는 행이 포함됩니다.

트랜잭션과 다중 스레딩¶

여러 세션이 같은 트랜잭션을 공유할 수는 없지만, 단일 연결을 사용하는 여러 스레드 가 같은 세션을 공유하므로 같은 트랜잭션을 공유합니다. 이는 한 스레드가 다른 스레드에서 수행된 작업을 롤백하는 것과 같은 예기치 않은 결과로 이어질 수 있습니다.

이런 상황은 Snowflake 드라이버(예: Snowflake JDBC 드라이버) 또는 커넥터(예: Python용 Snowflake 커넥터)를 사용하는 클라이언트 애플리케이션이 다중 스레드로 처리될 때 발생할 수 있습니다. 둘 이상의 스레드가 같은 연결을 공유하는 경우에는 해당 스레드가 그 연결에서 현재 트랜잭션 역시 공유합니다. 한 스레드에 의한 BEGIN TRANSACTION, COMMIT 또는 ROLLBACK은 그러한 공유 연결을 사용하는 모든 스레드에 영향을 줍니다. 스레드가 비동기로 실행 중인 경우 결과를 예측할 수 없습니다.

마찬가지로, 한 스레드의 AUTOCOMMIT 설정을 변경하면 같은 연결을 사용하는 다른 모든 스레드의 AUTOCOMMIT 설정에 영향을 줍니다.

Snowflake에서는 다중 스레드 클라이언트 프로그램이 다음 중 한 가지 이상을 수행하도록 할 것을 권장합니다.

• 각 스레드에 대해 별도의 연결 사용.

  별도의 연결을 사용하더라도 코드가 여전히 예측할 수 없는 출력을 생성하는 경합 상태에 직면할 수 있습니다. 예를 들어, 한 스레드가 데이터를 삭제한 후 다른 스레드가 그 데이터의 업데이트를 시도할 수 있습니다.

• 스레드를 비동기가 아니라 동기로 실행하여 단계 수행 순서 제어.

겹치지 않는 트랜잭션¶

아래 섹션에서는 다음 내용을 설명합니다.

• 트랜잭션 내에서 저장 프로시저 사용하기.

• 저장 프로시저 내에서 트랜잭션 사용하기.

CREATE PROCEDURE p1()
...
$$
    begin transaction;
    statement C;
    statement D;
    commit;

    begin transaction;
    statement E;
    statement F;
    commit;
$$;

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

CALL p1();

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

BEGIN TRANSACTION;
statement A;
statement B;
COMMIT;

BEGIN TRANSACTION;
statement C;
statement D;
COMMIT;

BEGIN TRANSACTION;
statement E;
statement F;
COMMIT;

BEGIN TRANSACTION;
statement G;
statement H;
COMMIT;

범위 지정 트랜잭션¶

트랜잭션을 포함하는 저장 프로시저 는 다른 트랜잭션 내에서 호출할 수 있습니다. 예를 들어, 저장 프로시저 내부의 트랜잭션은 트랜잭션을 포함하는 또 다른 저장 프로시저에 대한 호출을 포함할 수 있습니다.

Snowflake는 내부 트랜잭션을 중첩된 것으로 취급하지 않으며, 대신 내부 트랜잭션은 별개의 트랜잭션 입니다. Snowflake는 이러한 “자율 범위 지정 트랜잭션”(또는 간단히 “범위 지정 트랜잭션”)을 호출합니다.

각 범위 지정 트랜잭션의 시작점과 종료점에 따라 트랜잭션에 포함되는 문이 결정됩니다. 시작과 끝은 명시적이거나 암시적일 수 있습니다. 각 SQL 문은 단 하나의 트랜잭션 중 일부입니다. 둘러싸는 ROLLBACK 또는 COMMIT은 포함된 COMMIT 또는 ROLLBACK을 실행 취소하지 않습니다.

아래 다이어그램에는 두 개의 저장 프로시저와 두 개의 범위 지정 트랜잭션이 표시되어 있습니다. 이 예에서 각 저장 프로시저는 자체 독립 트랜잭션을 포함합니다. 첫 번째 저장 프로시저는 두 번째 저장 프로시저를 호출하므로 프로시저는 시간상으로는 겹치지만, 내용상으로는 겹치지 않습니다. 음영 처리된 내부 상자 안의 모든 문은 한 트랜잭션에 있습니다. 다른 모든 문은 또 다른 트랜잭션에 있습니다.

다음 예에서 트랜잭션 경계는 저장 프로시저 경계와 다릅니다. 즉, 외부 저장 프로시저에서 시작하는 트랜잭션은 내부 저장 프로시저의 일부 문을 포함하되, 모든 문을 포함하는 것은 아닙니다.

위의 코드에서 두 번째 저장 프로시저는 첫 번째 트랜잭션의 범위에 있는 일부 문(SP2_T1_S2 및 SP2_T1_S3)을 포함합니다. 음영 처리된 내부 상자 안에 있는 문 SP2_T2_S1만 두 번째 트랜잭션의 범위에 있습니다.

다음 예에서는 트랜잭션이 같은 저장 프로시저 내에서 시작되고 끝나지 않을 경우 발생하는 문제를 보여줍니다. 이 예에는 BEGIN 문과 같은 수의 COMMIT 문이 있습니다. 하지만 BEGIN 문과 COMMIT 문은 올바른 쌍이 아니므로, 이 예에는 다음의 두 가지 오류가 있습니다.

• 외부 저장 프로시저는 범위 지정 트랜잭션을 시작하지만 명시적으로 완료하지는 않습니다. 따라서 범위 지정 트랜잭션은 그 저장 프로시저의 끝에서 오류를 일으키고 활성 트랜잭션이 암시적으로 롤백됩니다.

• 두 번째 저장 프로시저는 COMMIT 을 포함하지만, 그 저장 프로시저에는 대응하는 BEGIN 이 없습니다. 이 COMMIT 은 첫 번째 저장 프로시저에서 시작된 미해결 트랜잭션을 커밋하지 않습니다. 대신에, 잘못 쌍을 이룬 COMMIT 은 오류의 원인이 됩니다.

다음 예에서는 시간이 겹치는 세 개의 범위 지정 트랜잭션을 보여줍니다. 이 예에서는 저장 프로시저 p1() 이 트랜잭션 내부에서 다른 저장 프로시저 p2() 를 호출하고 p2() 는 자체 트랜잭션을 포함하므로, p2() 에서 시작된 트랜잭션도 독립적으로 실행됩니다. (이 예에서는 의사 코드를 사용합니다.)

CREATE PROCEDURE p2()
...
$$
    BEGIN TRANSACTION;
    statement C;
    COMMIT;
$$;

CREATE PROCEDURE p1()
...
$$
    BEGIN TRANSACTION;
    statement B;
    CALL p2();
    statement D;
    COMMIT;
$$;

BEGIN TRANSACTION;
statement A;
CALL p1();
statement E;
COMMIT;

Copy

이 세 가지 범위 지정 트랜잭션에서는 다음 사항이 적용됩니다.

• 저장 프로시저 외부의 트랜잭션은 A 및 E 문을 포함합니다.

• 저장 프로시저 p1() 의 트랜잭션은 문 B 및 D 를 포함합니다.

• p2() 의 트랜잭션은 문 C 를 포함합니다.

범위 지정 트랜잭션에 대한 규칙은 재귀 저장 프로시저 호출에도 적용됩니다. 재귀 호출은 특정 유형의 중첩 호출일 뿐이며, 중첩 호출과 동일한 트랜잭션 규칙을 따릅니다.

AUTOCOMMIT이 해제되어 있을 때는 암시적 트랜잭션과 저장 프로시저의 결합에 특히 주의하십시오. 저장 프로시저 끝에서 실수로 트랜잭션을 활성 상태로 두면 트랜잭션이 롤백됩니다.

예를 들어, 다음 의사 코드 예에서는 저장 프로시저 끝에서 암시적 ROLLBACK 이 발생합니다.

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;
COMMIT WORK;

이 예에서 AUTOCOMMIT을 설정하는 명령은 모든 활성 트랜잭션을 커밋합니다. 새 트랜잭션이 즉시 시작되지 않습니다. 이 저장 프로시저는 새 트랜잭션을 암시적으로 시작하는 DML 문을 포함합니다. 해당 암시적 BEGIN TRANSACTION 은 저장 프로시저에 일치하는 COMMIT 또는 ROLLBACK 이 없습니다. 저장 프로시저 끝에 활성 트랜잭션이 있으므로 해당 활성 트랜잭션이 암시적으로 롤백됩니다.

단일 트랜잭션에서 전체 저장 프로시저를 실행하려면 트랜잭션을 시작한 후 저장 프로시저를 호출하고, 호출 후에 트랜잭션을 커밋합니다.

CREATE PROCEDURE p1() ...
$$
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
BEGIN TRANSACTION;
CALL p1;
COMMIT WORK;

이 경우, BEGIN 과 COMMIT 이 올바로 쌍을 이루고 코드가 오류 없이 실행됩니다.

대안으로서, 다음 의사 코드 예에서 볼 수 있듯이 BEGIN TRANSACTION 과 COMMIT 을 둘 다 저장 프로시저 내부에 넣습니다.

CREATE PROCEDURE p1() ...
$$
    BEGIN TRANSACTION;
    INSERT INTO parent_table ...;
    INSERT INTO child_table ...;
    COMMIT WORK;
$$;


ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL p1;

범위 지정 트랜잭션에서 BEGIN/COMMIT 블록이 올바른 쌍을 이루지 않으면 Snowflake에서 오류를 보고합니다. 그 오류는 저장 프로시저가 완료되지 않도록 방지하거나 둘러싸는 트랜잭션이 커밋되지 않도록 방지하는 것과 같은 추가적인 영향을 미칠 수 있습니다. 예를 들어, 다음 의사 코드 예에서는 포함된 저장 프로시저뿐 아니라 둘러싸는 저장 프로시저의 문도 롤백됩니다.

CREATE or replace PROCEDURE outer_sp1()
...
AS
$$
    insert 'osp1_alpha';
    BEGIN WORK;
    insert 'osp1_beta';
    CALL inner_sp2();
    INSERT 'osp1_delta';
    COMMIT WORK;
    INSERT 'osp1_omega';
$$;

CREATE or replace PROCEDURE inner_sp2()
...
AS
$$
    BEGIN WORK;
    insert 'isp2';
    -- Missing COMMIT, so implicitly rolls back!
$$;

CALL outer_sp1();

SELECT * FROM st;

Copy

이 예에서 삽입되는 유일한 값은 ‘osp1_alpha’입니다. COMMIT이 BEGIN과 올바르게 쌍을 이루지 않으므로 다른 값은 어떤 것도 삽입되지 않습니다. 오류는 다음과 같이 처리됩니다.

1. 프로시저 inner_sp2() 가 완료되면 Snowflake가 inner_sp2() 의 BEGIN에 그에 해당하는 COMMIT(또는 ROLLBACK)이 없음을 감지합니다.

   1. Snowflake는 inner_sp2() 에서 시작한 범위 지정 트랜잭션을 암시적으로 롤백합니다.

   2. inner_sp2() 에 대한 CALL이 실패했으므로 Snowflake 역시 오류를 내보냅니다.

2. inner_sp2() 의 CALL이 실패하고 CALL 문이 outer_sp1() 에 있었으므로, 저장 프로시저 outer_sp1() 자체도 실패해 계속하지 않고 오류를 반환합니다.

3. outer_sp1() 이 실행을 완료하지 않으므로 다음 사항이 적용됩니다.

   • ‘osp1_delta’ 및 ‘osp1_omega’ 값에 대한 INSERT 문은 실행되지 않습니다.

   • outer_sp1() 에서 열린 트랜잭션은 커밋되지 않고 암시적으로 롤백되므로, ‘osp1_beta’ 값의 삽입은 커밋되지 않습니다.

READ COMMITTED 격리¶

문에서 READ COMMITTED 격리를 사용할 경우 문이 시작되기 전에 커밋된 데이터만 보게 됩니다. 커밋되지 않은 데이터는 절대 보지 못합니다.

다중 문 트랜잭션 내에서 문이 실행될 때 다음 사항이 적용됩니다.

• 문에서는 문 이 시작되기 전에 커밋된 데이터만 봅니다. 같은 트랜잭션에 있는 두 개의 연속적인 문의 경우, 첫 번째 문과 두 번째 문이 실행되는 사이에 다른 트랜잭션이 커밋되면 서로 다른 데이터를 볼 수 있습니다.

• 문은 같은 트랜잭션 내에서 실행된 이전 문의 변경 사항이 아직 커밋되지 않았더라도 이러한 변경 사항을 봅니다.

잠금 대기 시간 초과¶

차단된 문은 기다리고 있던 리소스에 대한 잠금을 획득하거나 리소스를 사용할 있을 때까지 기다리다가 시간이 초과됩니다. 문이 차단해야 하는 시간(초)은 LOCK_TIMEOUT 매개 변수를 설정하여 구성할 수 있습니다.

예를 들어, 현재 세션의 잠금 시간 제한을 2시간(7,200초)으로 변경하는 방법은 다음과 같습니다.

ALTER SESSION SET LOCK_TIMEOUT=7200;

SHOW PARAMETERS LIKE 'lock%';

+--------------+-------+---------+---------+-------------------------------------------------------------------------------+
| key          | value | default | level   | description                                                                   |
|--------------+-------+---------+---------+-------------------------------------------------------------------------------|
| LOCK_TIMEOUT | 7200  | 43200   | SESSION | Number of seconds to wait while trying to lock a resource, before timing out  |
|              |       |         |         | and aborting the statement. A value of 0 turns off lock waiting i.e. the      |
|              |       |         |         | statement must acquire the lock immediately or abort. If multiple resources   |
|              |       |         |         | need to be locked by the statement, the timeout applies separately to each    |
|              |       |         |         | lock attempt.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+

Copy

교착 상태¶

교착 상태는 동시 트랜잭션이 서로에 의해 잠긴 리소스를 기다리고 있을 때 발생합니다.

Snowflake에서는 자동 커밋 DML 또는 쿼리 문을 동시에 실행하는 동안 교착 상태가 발생할 수 없습니다. 하지만 명시적으로 시작된 트랜잭션과 각 트랜잭션의 여러 문에서 교착 상태가 발생할 수 있습니다. Snowflake는 교착 상태를 감지하고 교착 상태의 일부인 가장 최근 문을 희생하는 선택을 합니다. 이 문은 롤백되지만, 트랜잭션 자체는 활성 상태로 유지되므로 이를 커밋하거나 롤백해야 합니다.

교착 상태 감지에 시간이 걸릴 수 있습니다.

트랜잭션 중단을 위한 문 오류 허용하기¶

트랜잭션 내의 문 오류가 트랜잭션을 중단하도록 허용하려면 세션 또는 계정 수준에서 TRANSACTION_ABORT_ON_ERROR 매개 변수를 설정하십시오.

LOCK_WAIT_HISTORY 뷰로 차단된 트랜잭션 분석하기¶

LOCK_WAIT_HISTORY 뷰 는 차단된 트랜잭션 분석에 유용하게 쓰일 수 있는 트랜잭션 세부 정보를 반환합니다. 출력의 각 행에는 잠금을 기다리는 트랜잭션에 대한 세부 정보와 해당 잠금을 보유하고 있거나 해당 잠금을 앞서 기다리고 있던 트랜잭션의 세부 정보가 포함됩니다.

예를 들어 아래 시나리오를 참조하십시오.

이 시나리오에서는 다음 데이터가 반환됩니다.

• 트랜잭션 B는 잠금을 기다리고 있는 트랜잭션입니다.

• 트랜잭션 B가 타임스탬프 T1에서 잠금을 요청했습니다.

• 트랜잭션 A는 잠금을 보유한 트랜잭션입니다.

• 트랜잭션 A의 쿼리 2는 차단 쿼리입니다.

쿼리 2는 트랜잭션 B(잠금을 기다리는 트랜잭션)가 기다리기 시작한 트랜잭션 A(잠금을 보유한 트랜잭션)의 첫 번째 문이므로 차단 쿼리입니다.

하지만 트랜잭션 A에서 이후의 쿼리(쿼리 5)도 잠금을 획득했습니다. 이러한 트랜잭션의 후속 동시 실행으로 인해 트랜잭션 A에서 잠금을 획득하는 다른 쿼리에서 트랜잭션 B가 차단될 수 있습니다. 따라서 첫 번째 차단 트랜잭션의 모든 쿼리를 조사해야 합니다.

범위 지정 트랜잭션과 저장 프로시저의 간단한 예¶

다음은 범위 지정 트랜잭션의 간단한 예입니다. 저장 프로시저는 값이 12인 행을 삽입한 다음 롤백하는 트랜잭션을 포함합니다. 외부 트랜잭션이 커밋합니다. 출력은 외부 트랜잭션의 범위에 있는 모든 행이 유지되는 반면, 내부 트랜잭션 범위의 행은 유지되지 않음을 보여줍니다.

저장 프로시저의 일부만 자체 트랜잭션 내부에 있으므로, 저장 프로시저에 있지만 저장 프로시저의 트랜잭션 외부에 있는 INSERT 문으로 삽입된 값이 유지됩니다.

다음 두 테이블을 만듭니다.

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);

저장 프로시저를 만듭니다.

create procedure sp1()
returns varchar
language javascript
AS
$$
    // This is part of the outer transaction that started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

저장 프로시저를 호출합니다.

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1();
insert into tracker_1 values (09, 'outer_zulu');
commit;

결과는 00, 11, 13, 09를 포함해야 합니다. ID = 12인 행을 포함하면 안 됩니다. 이 행은 롤백되어 포함된 트랜잭션의 범위에 있었습니다. 다른 모든 행은 외부 트랜잭션 범위에 있고 커밋되었습니다. 특히 IDs 11과 13이 있는 행은 저장 프로시저 내부, 하지만 가장 안쪽 트랜잭션 외부에 있었는데, 둘러싸는 트랜잭션의 범위에 있고 그 트랜잭션과 함께 커밋되었습니다.

select id, name FROM tracker_1
union all
select id, name FROM tracker_2
order by id;
+----+-------------+
| ID | NAME        |
|----+-------------|
|  0 | outer_alpha |
|  9 | outer_zulu  |
| 11 | p1_alpha    |
| 13 | p1_charlie  |
+----+-------------+

트랜잭션의 성공과 독립적으로 정보 기록하기¶

다음은 범위 지정 트랜잭션을 사용하는 방법을 보여주는 간단하면서도 실용적인 예입니다. 이 예에서 트랜잭션은 특정 정보를 기록하며, 기록된 정보는 트랜잭션 자체의 성공 여부에 상관없이 보존됩니다. 시도한 각 작업의 성공 여부와 관계없이, 이 기법을 사용해 이러한 작업을 전부 추적할 수 있습니다.

다음 두 테이블을 만듭니다.

create table data_table (id integer);
create table log_table (message varchar);

저장 프로시저를 만듭니다.

create procedure log_message(MESSAGE VARCHAR)
returns varchar
language javascript
AS
$$
    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into log_table values ('" + MESSAGE + "')"}
        );
    snowflake.execute (
        {sqlText: "commit"}
        );

    // Dummy value.
    return "";
$$;

create procedure update_data()
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "begin transaction"}
        );
    snowflake.execute (
        {sqlText: "insert into data_table (id) values (17)"}
        );
    snowflake.execute (
        {sqlText: "call log_message('You should see this saved.')"}
        );
    snowflake.execute (
        {sqlText: "rollback"}
        );

    // Dummy value.
    return "";
$$;

저장 프로시저를 호출합니다.

begin transaction;
call update_data();
rollback;

트랜잭션이 롤백되었으므로 데이터 테이블이 비어 있습니다.

select * from data_table;
+----+
| ID |
|----|
+----+

하지만 로깅 테이블은 비어 있지 않습니다. 로깅 테이블로의 삽입은 data_table로의 삽입과는 별도의 트랜잭션에서 수행되었습니다.

select * from log_table;
+----------------------------+
| MESSAGE                    |
|----------------------------|
| You should see this saved. |
+----------------------------+

범위 지정 트랜잭션과 저장 프로시저의 예¶

다음 몇 가지 예에서는 아래에 표시된 테이블과 저장 프로시저를 사용합니다. 적절한 매개 변수를 전달함으로써, 호출자는 저장 프로시저 내에서 BEGIN TRANSACTION, COMMIT, ROLLBACK 문이 실행되는 위치를 제어할 수 있습니다.

테이블 만들기:

create table tracker_1 (id integer, name varchar);
create table tracker_2 (id integer, name varchar);
create table tracker_3 (id integer, name varchar);

이 프로시저는 둘러싸는 저장 프로시저로, 이 저장 프로시저로 전달된 매개 변수에 따라 둘러싸는 트랜잭션을 만들 수 있습니다.

create procedure sp1_outer(
    USE_BEGIN varchar,
    USE_INNER_BEGIN varchar,
    USE_INNER_COMMIT_OR_ROLLBACK varchar,
    USE_COMMIT_OR_ROLLBACK varchar
    )
returns varchar
language javascript
AS
$$
    // This should be part of the outer transaction started before this
    // stored procedure was called. This should be committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (11, 'p1_alpha')"}
        );

    // This is an independent transaction. Anything inserted as part of this
    // transaction is committed or rolled back based on this transaction.
    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (12, 'p1_bravo')"}
        );
    // Call (and optionally begin/commit-or-rollback) an inner stored proc...
    var command = "call sp2_inner('";
    command = command.concat(USE_INNER_BEGIN);
    command = command.concat("', '");
    command = command.concat(USE_INNER_COMMIT_OR_ROLLBACK);
    command = command.concat( "')" );
    snowflake.execute (
        {sqlText: command}
        );
    if (USE_COMMIT_OR_ROLLBACK != '') {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    // This is part of the outer transaction started before this
    // stored procedure was called. This is committed or rolled back
    // as part of that outer transaction.
    snowflake.execute (
        {sqlText: "insert into tracker_1 values (13, 'p1_charlie')"}
        );

    // Dummy value.
    return "";
$$;

이 프로시저는 내부 저장 프로시저로, 이 저장 프로시저로 전달된 매개 변수에 따라 포함된 트랜잭션을 만들 수 있습니다.

create procedure sp2_inner(
    USE_BEGIN varchar,
    USE_COMMIT_OR_ROLLBACK varchar)
returns varchar
language javascript
AS
$$
    snowflake.execute (
        {sqlText: "insert into tracker_2 values (21, 'p2_alpha')"}
        );

    if (USE_BEGIN != '')  {
        snowflake.execute (
            {sqlText: USE_BEGIN}
            );
        }
    snowflake.execute (
        {sqlText: "insert into tracker_3 values (22, 'p2_bravo')"}
        );
    if (USE_COMMIT_OR_ROLLBACK != '')  {
        snowflake.execute (
            {sqlText: USE_COMMIT_OR_ROLLBACK}
            );
        }

    snowflake.execute (
        {sqlText: "insert into tracker_2 values (23, 'p2_charlie')"}
        );

    // Dummy value.
    return "";
$$;

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'rollback', 'commit');
insert into tracker_1 values (09, 'outer_charlie');
rollback;

-- Should return only 12, 21, 23.
select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+------------+
| ID | NAME       |
|----+------------|
| 12 | p1_bravo   |
| 21 | p2_alpha   |
| 23 | p2_charlie |
+----+------------+

begin transaction;
insert into tracker_1 values (00, 'outer_alpha');
call sp1_outer('begin transaction', 'begin transaction', 'commit', 'rollback');
insert into tracker_1 values (09, 'outer_charlie');
commit;

select id, name from tracker_1
union all
select id, name from tracker_2
union all
select id, name from tracker_3
order by id;
+----+---------------+
| ID | NAME          |
|----+---------------|
|  0 | outer_alpha   |
|  9 | outer_charlie |
| 11 | p1_alpha      |
| 13 | p1_charlie    |
| 22 | p2_bravo      |
+----+---------------+

저장 프로시저의 트랜잭션에 오류 처리 사용하기¶

다음 코드는 저장 프로시저의 트랜잭션에 대한 간단한 오류 처리를 보여줍니다. 매개 변수 값 ‘fail’이 전달되면 저장 프로시저가 존재하는 두 개의 테이블과 존재하지 않는 한 테이블에서 삭제를 시도하고, 저장 프로시저는 오류를 포착해 오류 메시지를 반환합니다. 매개 변수 값 ‘fail’이 전달되지 않으면 이 프로시저는 존재하는 두 테이블에서 삭제를 시도하고 성공합니다.

테이블과 저장 프로시저를 만듭니다.

begin transaction;

create table parent(id integer);
create table child (child_id integer, parent_ID integer);

-- ----------------------------------------------------- --
-- Wrap multiple related statements in a transaction,
-- and use try/catch to commit or roll back.
-- ----------------------------------------------------- --
-- Create the procedure
create or replace procedure cleanup(FORCE_FAILURE varchar)
  returns varchar not null
  language javascript
  as
  $$
  var result = "";
  snowflake.execute( {sqlText: "begin transaction;"} );
  try {
      snowflake.execute( {sqlText: "delete from child where parent_id = 1;"} );
      snowflake.execute( {sqlText: "delete from parent where id = 1;"} );
      if (FORCE_FAILURE === "fail")  {
          // To see what happens if there is a failure/rollback,
          snowflake.execute( {sqlText: "delete from no_such_table;"} );
          }
      snowflake.execute( {sqlText: "commit;"} );
      result = "Succeeded";
      }
  catch (err)  {
      snowflake.execute( {sqlText: "rollback;"} );
      return "Failed: " + err;   // Return a success/error indicator.
      }
  return result;
  $$
  ;

commit;

저장 프로시저를 호출하고 오류를 강제로 발생시킵니다.

call cleanup('fail');
+----------------------------------------------------------+
| CLEANUP                                                  |
|----------------------------------------------------------|
| Failed: SQL compilation error:                           |
| Object 'NO_SUCH_TABLE' does not exist or not authorized. |
+----------------------------------------------------------+

오류를 강제하지 않고 저장 프로시저를 호출합니다.

call cleanup('do not fail');
+-----------+
| CLEANUP   |
|-----------|
| Succeeded |
+-----------+