트랜잭션

트랜잭션은 한 단위로 커밋되거나 롤백되는 SQL 문으로 구성된 시퀀스입니다.

이 항목의 내용:

소개

트랜잭션이란?

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

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

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

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

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

용어

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

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

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

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

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

명시적 트랜잭션

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

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

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

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

참고

명시적 트랜잭션은 DML 문과 쿼리 문만 포함해야 합니다. DDL 문은 암시적으로 활성 트랜잭션을 커밋합니다(자세한 내용은 DDL 섹션 참조).

암시적 트랜잭션

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

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

DDL

각 DDL 문은 별개의 트랜잭션으로 실행됩니다.

트랜잭션이 활성 상태에서 DDL 문이 실행될 경우 DDL 문은 다음을 수행합니다.

  1. 암시적으로 활성 트랜잭션을 커밋합니다.

  2. DDL 문을 별개의 트랜잭션으로 실행합니다.

DDL 문은 자체 트랜잭션이므로 DDL 문을 롤백할 수 없습니다. DDL을 포함한 트랜잭션이 완료되어야 명시적 ROLLBACK을 실행할 수 있습니다.

DDL 문 바로 다음에 DML 또는 쿼리 문이 뒤따를 경우 그 DML 또는 쿼리 문은 암시적으로 새 트랜잭션을 시작합니다.

AUTOCOMMIT

Snowflake는 AUTOCOMMIT 매개 변수를 지원합니다. AUTOCOMMIT의 기본 설정은 켜짐입니다.

AUTOCOMMIT이 활성화된 상태에서는 다음 사항이 적용됩니다.

  • 명시적 트랜잭션 외부의 문은 각각 그 자신의 암시적 단일 문 트랜잭션 내부에 있는 것처럼 처리됩니다. 다시 말해, 해당 문이 성공하면 자동으로 커밋되고 실패하면 자동으로 롤백됩니다.

    명시적 트랜잭션 내부의 문은 AUTOCOMMIT의 영향을 받지 않습니다. 예를 들어, 명시적 BEGIN TRANSACTION ... ROLLBACK 내부의 문은 AUTOCOMMIT이 TRUE이더라도 롤백됩니다.

AUTOCOMMIT이 비활성화된 상태에서는 다음 사항이 적용됩니다.

  • 암시적 BEGIN TRANSACTION 은 다음에서 실행됩니다.

    • 트랜잭션 종료 후 첫 번째 DML 문 또는 쿼리 문. 이는 무엇이 선행 트랜잭션(예: DDL 문, 명시적 커밋 또는 롤백)을 종료했는지에 상관없이 적용됩니다.

    • AUTOCOMMIT을 비활성화 후 첫 번째 DML 문 또는 쿼리 문.

  • 암시적 COMMIT 은 다음 시점에 실행됩니다(트랜잭션이 이미 활성 상태인 경우).

    • DDL 문 실행 시.

    • 새 값이 TRUE 또는 FALSE인지 여부와 새 값이 이전 값과 다른지 여부에 상관없이 ALTER SESSION SET AUTOCOMMIT 문의 실행 시. 예를 들어, AUTOCOMMIT이 이미 FALSE일 때 이를 FALSE로 설정하더라도 암시적 COMMIT 이 실행됩니다.

  • 암시적 ROLLBACK 은 다음 시점에 실행됩니다(트랜잭션이 이미 활성 상태인 경우).

    • 세션 종료 시.

    • 저장 프로시저 종료 시.

      저장 프로시저의 활성 트랜잭션이 명시적으로나 암시적으로 시작되었는지에 상관없이, Snowflake는 활성 트랜잭션을 롤백하고 오류 메시지를 발행합니다.

조심

저장 프로시저 내에서 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가 있는 행이 포함됩니다.

참고

실패한 INSERT 문 이후의 문은 해당 문의 실행 방식과 오류 처리 방식에 따라 실행되거나 실행되지 않을 수 있습니다.

예:

  • 이러한 문이 Snowflake Scripting 언어로 작성된 저장 프로시저 내부에 있는 경우 실패한 INSERT 문에서 예외가 발생합니다.

    • 예외가 처리되지 않으면 저장 프로시저가 결코 완료되지 않고 COMMIT이 결코 실행되지 않으므로, 열린 트랜잭션이 암시적으로 롤백됩니다. 그 경우에는 테이블에 1 또는 2 가 없습니다.

    • 저장 프로시저가 예외를 처리하고 실패한 INSERT 문 이전의 문을 커밋하지만, 실패한 INSERT 문 이후의 문을 실행하지 않을 경우에는 값이 1 인 행만 테이블에 저장됩니다.

  • 이러한 문이 저장 프로시저 내부에 없을 경우에는 동작이 문의 실행 방식에 따라 결정됩니다. 예:

    • Snowsight 를 통해 문이 실행되면 첫 번째 오류 발생 시 실행이 중지됩니다.

    • -f (파일 이름) 옵션을 사용하여 SnowSQL에서 문이 실행되면 첫 번째 오류 발생 시 실행이 중지되지 않고 오류 발생 이후의 문이 실행됩니다.

트랜잭션과 다중 스레딩

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

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

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

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

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

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

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

저장 프로시저와 트랜잭션

일반적으로, 이전 섹션에서 설명한 규칙은 저장 프로시저에도 적용됩니다. 이 섹션에서는 저장 프로시저에 특정한 추가 정보를 제공합니다.

트랜잭션이 저장 프로시저 내부에 있거나 저장 프로시저가 트랜잭션 내부에 있을 수 있지만, 트랜잭션이 일부는 저장 프로시저 내부에 있고 일부는 외부에 있거나, 한 저장 프로시저에서 시작되고 다른 저장 프로시저에서 완료될 수는 없습니다.

예:

  • 저장 프로시저를 호출하기 전에 트랜잭션을 시작한 다음, 저장 프로시저 내에서 트랜잭션을 완료할 수 없습니다. 이렇게 하려고 하면 Snowflake가 Modifying a transaction that has started at a different scope is not allowed. 와 비슷한 오류를 보고합니다.

  • 저장 프로시저 내에서 트랜잭션을 시작한 다음, 프로시저에서 반환 후 트랜잭션을 완료할 수 없습니다. 트랜잭션이 저장 프로시저 내에서 시작되고 저장 프로시저 완료 시에도 계속 활성 상태인 경우에는 오류가 발생하고 트랜잭션이 롤백됩니다.

이러한 규칙은 중첩 저장 프로시저에도 적용됩니다. 프로시저 A 가 프로시저 B 를 호출하는 경우 BA 에서 시작된 트랜잭션을 완료할 수 없고, 그 반대도 마찬가지입니다. A 의 각 BEGIN TRANSACTIONA 에 그에 상응하는 COMMIT (또는 ROLLBACK)이 있어야 하고, B 의 각 BEGIN TRANSACTIONB 에 그에 상응하는 COMMIT (또는 ROLLBACK)이 있어야 합니다.

저장 프로시저가 트랜잭션을 포함하는 경우 그 트랜잭션은 저장 프로시저 본문의 일부 또는 전부를 포함할 수 있습니다. 예를 들어, 다음 저장 프로시저에서는 문 중 일부만 트랜잭션에 있습니다. (이 예와 그 뒤를 잇는 여러 예에서는 단순성을 기하기 위해 의사 코드를 사용합니다.)

create procedure ...
    as
    $$
        ...
        statement1;

        BEGIN TRANSACTION;
        statement2;
        COMMIT;

        statement3;
        ...

    $$;

겹치지 않는 트랜잭션

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

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

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

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

가장 간단한 사례에서, 저장 프로시저는 다음 조건이 충족되는 경우 트랜잭션 내부에 있는 것으로 간주됩니다.

  • 저장 프로시저가 호출되기 전에 BEGIN TRANSACTION 이 실행됩니다.

  • 저장 프로시저가 완료된 후 해당 COMMIT (또는 ROLLBACK)이 실행됩니다.

  • 저장 프로시저의 본문은 명시적이거나 암시적인 BEGIN TRANSACTION 또는 COMMIT (또는 ROLLBACK)을 포함하지 않습니다.

트랜잭션 내부의 저장 프로시저는 자신을 둘러싸는 트랜잭션의 규칙을 따릅니다.

  • 트랜잭션이 커밋되면 프로시저 내의 모든 문이 커밋됩니다.

  • 트랜잭션이 롤백되면 프로시저 내의 모든 문이 롤백됩니다.

다음 의사 코드는 트랜잭션 내에서 전적으로 호출되는 저장 프로시저를 보여줍니다.

CREATE PROCEDURE my_procedure()
...
AS
$$
    statement X;
    statement Y;
$$;

BEGIN TRANSACTION;
statement W;
CALL my_procedure();
statement Z;
COMMIT;

이것은 다음 문 시퀀스를 실행하는 것과 같습니다.

begin;
statement W;
statement X;
statement Y;
statement Z;
commit;

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

저장 프로시저 내에서 0개, 1개 또는 그 이상의 트랜잭션을 실행할 수 있습니다. 다음 의사 코드는 한 저장 프로시저에 있는 두 트랜잭션의 예를 보여줍니다.

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을 실행 취소하지 않습니다.

참고

《내부》 및 《외부》라는 용어는 중첩된 저장 프로시저 호출과 같은 중첩된 작업을 설명할 때 흔히 사용됩니다. 하지만 Snowflake의 트랜잭션은 실제로 《중첩》되지 않으므로, 이 문서에서는 혼동을 줄이기 위해 트랜잭션이라고 지칭할 때 《내부》와 《외부》라는 말 대신 《포함된》과 《둘러싸는》이라는 용어를 자주 사용합니다.

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

Illustration of two stored procedures, each with its own scoped transaction.

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

Illustration of two stored procedures and two scoped transactions, in which one transaction includes some statements from the inner stored procedure as well as all statements from the outer stored procedure.

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

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

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

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

Illustration of two stored procedures that create improperly-scoped transactions.

다음 예에서는 시간이 겹치는 세 개의 범위 지정 트랜잭션을 보여줍니다. 이 예에서는 저장 프로시저 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;

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

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

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

  • 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;

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

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

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;

이 예에서 삽입되는 유일한 값은 〈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 격리

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

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

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

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

리소스 잠금

트랜잭션 작업은 테이블과 같은 리소스가 수정되는 동안 그 리소스에 대한 잠금을 획득합니다. 잠금은 잠금이 해제될 때까지 다른 문이 리소스를 수정하지 못하게 차단합니다.

대부분의 상황에서 다음 지침이 적용됩니다.

  • (AUTOCOMMIT과 명시적 COMMIT을 모두 포함하여) COMMIT 작업은 리소스를 잠그지만, 보통은 잠깐만 잠급니다.

  • UPDATE, DELETE, MERGE 문은 일반적으로 다른 UPDATE, DELETE, MERGE 문과 병렬로 실행되지 못하게 하는 잠금을 보유합니다.

  • INSERT 및 COPY 문은 대부분 새 파티션만 씁니다. 이러한 문은 종종 다른 INSERT 및 COPY 작업과 병렬로 실행될 수 있으며, 때로는 UPDATE, DELETE 또는 MERGE 문과 병렬로 실행될 수 있습니다.

문이 보유한 잠금은 트랜잭션의 COMMIT 또는 ROLLBACK 에서 해제됩니다.

잠금 대기 시간 초과

차단된 문은 기다리고 있던 리소스에 대한 잠금을 획득하거나 리소스를 사용할 있을 때까지 기다리다가 시간이 초과됩니다. 문이 차단해야 하는 시간(초)은 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.                                                                 |
+--------------+-------+---------+---------+-------------------------------------------------------------------------------+

교착 상태

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

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

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

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

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

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

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

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

Example of blocked and blocker transactions.

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

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

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

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

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

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

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

장시간 실행 중인 문 검사하기

  1. 지난 24시간 동안 잠금을 기다린 트랜잭션에 대한 Account Usage QUERY_HISTORY 뷰를 쿼리합니다.

    SELECT query_id, query_text, start_time, session_id, execution_status, total_elapsed_time,
           compilation_time, execution_time, transaction_blocked_time
      FROM snowflake.account_usage.query_history
      WHERE start_time >= dateadd('hours', -24, current_timestamp())
      AND transaction_blocked_time > 0
      ORDER BY transaction_blocked_time DESC;
    

    쿼리 결과를 검토하고 높은 TRANSACTION_BLOCKED_TIME 값을 가진 쿼리의 ID를 기록합니다.

  2. 이전 단계에서 식별된 쿼리에 대한 차단 트랜잭션을 찾으려면 해당 쿼리 ID를 가진 행에 대해 LOCK_WAIT_HISTORY 뷰를 쿼리하십시오.

    SELECT object_name, lock_type, transaction_id, blocker_queries
      FROM snowflake.account_usage.lock_wait_history
      WHERE query_id = '<query_id>';
    

    결과의 blocker_queries 열에 여러 쿼리가 있을 수 있습니다.

트랜잭션 명령 및 함수

Snowflake는 트랜잭션을 지원하는 다음 SQL 명령을 제공합니다.

또한, Snowflake는 세션의 트랜잭션에 대한 정보를 얻기 위해 다음과 같은 컨텍스트 함수를 제공합니다.

트랜잭션 중단하기

트랜잭션이 세션에서 실행 중이고 세션 연결이 갑자기 끊어져 트랜잭션의 커밋 또는 롤백을 막는 경우, 이 트랜잭션이 리소스에 대해 보유하고 있는 잠금을 비롯해 트랜잭션이 분리된 상태로 남습니다. 이런 일이 일어나는 경우 트랜잭션을 중단해야 할 수 있습니다.

실행 중인 트랜잭션을 중단하려면 트랜잭션을 시작한 사용자나 계정 관리자가 시스템 함수 SYSTEM$ABORT_TRANSACTION 을 호출할 수 있습니다.

사용자가 트랜잭션을 중단하지 않는 경우 다음 사항이 적용됩니다.

  • 이 트랜잭션이 다른 트랜잭션에서 동일한 테이블을 잠그지 못하도록 차단하고 이와 동시에 5분 동안 유휴 상태인 경우, 트랜잭션은 자동으로 중단되어 롤백됩니다.

  • 이 트랜잭션이 다른 트랜잭션에서 같은 테이블을 수정하지 못하게 차단하지 않고 4시간 이상 경과하면 자동으로 중단되고 롤백됩니다.

모범 사례

  • 트랜잭션은 관련된 문을 포함하고 함께 성공하거나 실패해야 합니다(예: 한 계좌에서 돈을 인출하고 같은 돈을 다른 계좌에 입금). 롤백이 발생하는 경우 지급인이나 수취인이 결국 돈을 받게 되며, 그 돈은 결코 《사라지지》 않습니다(한 계좌에서 인출되었지만 다른 계좌에 입금되지 않음).

    일반적으로, 한 트랜잭션은 관련된 문만 포함해야 합니다. 문을 덜 세분화한다는 것은 트랜잭션이 롤백될 때 실제로는 롤백할 필요가 없었던 유용한 작업을 롤백할 수도 있다는 뜻입니다.

  • 더 큰 트랜잭션은 경우에 따라 성능을 향상할 수 있습니다.

    이전의 글머리 기호 항목에서는 실제로 그룹으로 커밋하거나 롤백해야 하는 문만 그룹화하는 것이 중요하다고 강조했지만, 때로는 더 큰 트랜잭션이 유용할 수 있습니다. Snowflake에서는 대부분의 데이터베이스와 마찬가지로, 트랜잭션 관리에 리소스를 사용합니다. 예를 들어, 한 트랜잭션에 10개의 행을 삽입하는 것이 일반적으로 10개의 트랜잭션에 따로 각각 한 행을 삽입하는 것보다 빠르고 저렴합니다. 여러 문을 단일 트랜잭션으로 결합하면 성능을 개선할 수 있습니다.

  • 지나치게 큰 트랜잭션은 병렬 처리를 줄이거나 교착 상태를 늘릴 수 있습니다. (이전 글머리 기호 항목에서 설명한 대로) 성능 향상을 위해 관련 없는 문을 그룹화하기로 한 경우, 트랜잭션이 리소스에 대한 잠금 을 획득할 수 있고, 이를 통해 다른 쿼리가 지연되거나 교착 상태 로 이어질 수 있습니다.

  • Snowflake에서는 AUTOCOMMIT을 활성화된 상태로 유지하고 명시적 트랜잭션을 가능한 한 많이 사용할 것을 권장합니다. 명시적 트랜잭션을 사용하면 이를 읽는 사람이 트랜잭션의 시작 및 종료 위치를 더 쉽게 알 수 있습니다. 이것이 AUTOCOMMIT과 함께 결합되면, 예컨대 저장 프로시저의 끝에서처럼, 코드에서 의도하지 않은 롤백이 발생할 가능성이 감소합니다.

  • 암시적으로 새 트랜잭션을 시작하려면 그냥 AUTOCOMMIT을 변경하지 마십시오. 대신에 새 트랜잭션이 시작되는 위치를 더 명확하게 하려면 BEGIN TRANSACTION 을 사용하십시오.

  • 한 행에서 둘 이상의 BEGIN TRANSACTION 문을 실행하지 마십시오. 추가 BEGIN TRANSACTION 문을 사용하면 트랜잭션의 실제 시작 위치를 알기 더 어려워지고, COMMIT/ROLLBACK 명령을 그에 대응되는 BEGIN TRANSACTION과 쌍을 이루기 더 어려워집니다.

트랜잭션 ID

모든 Snowflake 트랜잭션에는 고유한 트랜잭션 ID가 할당됩니다. 트랜잭션 ID는 부호 있는 64비트(long) 정수입니다. 값의 범위는 -9,223,372,036,854,775,808(-2 63)~9,223,372,036,854,775,807(2 63 - 1)입니다.

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

다음은 범위 지정 트랜잭션의 간단한 예입니다. 저장 프로시저는 값이 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 "";
$$;

세 가지 수준의 중간 수준 커밋하기

다음 예에는 3개의 트랜잭션이 있습니다. 이 예에서는 《중간》 수준(가장 바깥쪽 트랜잭션이 둘러싸고 가장 안쪽 트랜잭션을 둘러싸는 트랜잭션)을 커밋합니다. 그러면 가장 바깥쪽 트랜잭션과 가장 안쪽 트랜잭션이 롤백됩니다.

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;

그 결과, 중간 트랜잭션의 행만(12, 21, 23) 커밋됩니다. 외부 트랜잭션과 내부 트랜잭션의 행은 커밋되지 않습니다.

-- 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 |
+----+------------+

세 가지 수준의 중간 수준 롤백하기

다음 예에는 3개의 트랜잭션이 있습니다. 이 예에서는 《중간》 수준(가장 바깥쪽 트랜잭션이 둘러싸고 가장 안쪽 트랜잭션을 둘러싸는 트랜잭션)을 롤백합니다. 그러면 가장 바깥쪽 트랜잭션과 가장 안쪽 트랜잭션이 커밋됩니다.

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;

그 결과, 중간 트랜잭션의 행(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          |
|----+---------------|
|  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 |
+-----------+
맨 위로 이동