MSCEWI3111

Transaction control statement requires additional intervention

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

Snowflake does support transaction control statements; however, there are some differences between the transaction handling between the two platforms, the major difference being the implicit transaction handling in Oracle, which Snowflake does not support. The second difference regards the default session variables' values: them being modifiable will change the desired migration for these statements.

In Oracle's transactions, by default, are started whenever an executable statement is started and finished when there is either a Rollback or Commit. In Snowflake the "AUTOCOMMIT" variable is set by default, meaning that each executable statement performs a transaction and then commits it on success, or rollbacks on failure.

Oracle's Simple Rollback and Commit statements can be uncommented (see their Snowflake equivalents for Rollback and Commit), but then additional work must be done to mimic the original transaction behavior by adding "BEGIN TRANSACTION" statements at the beginning of scope and then immediately after doing a Commit or a Rollback, and then making sure that each execution path does finish in either Rollback or Commit statement to avoid Snowflake errors due to unfinished Transactions.

More complex Rollback and Commit statements will need further refactoring since Snowflake supports neither "SAVEPOINTS" nor "FORCE TRANSACTION" functionalities, meaning these will need major refactoring.

Example Code

The next code will commit insertions of 1, and rollback insertions of 2. On Snowflake, doing a Commit and Rollback perform no action since the statement already committed the insertion.

Input Code:

CREATE TABLE t1(col1 INTEGER);

INSERT INTO t1 VALUES(1);
COMMIT;

INSERT INTO t1 VALUES(2);
ROLLBACK;

SELECT * FROM t1;

CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
    INSERT INTO t1 VALUES(1);
    COMMIT;
    
    INSERT INTO t1 VALUES(2);
    ROLLBACK;
END;
/

CALL proc1();

Output Code:

CREATE OR REPLACE TABLE PUBLIC.t1 (col1 INTEGER);

INSERT INTO PUBLIC.t1 VALUES(1);

-- ** MSC-ERROR - MSCEWI3111 - COMMIT STATEMENT REQUIRES ADDITIONAL INTERVENTION **
--COMMIT
      ;

INSERT INTO PUBLIC.t1 VALUES(2);

-- ** MSC-ERROR - MSCEWI3111 - ROLLBACK STATEMENT REQUIRES ADDITIONAL INTERVENTION **
--ROLLBACK
        ;

SELECT * FROM PUBLIC.t1;

CREATE OR REPLACE PROCEDURE PUBLIC.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      INSERT INTO PUBLIC.t1 VALUES(1);
-- ** MSC-ERROR - MSCEWI3111 - COMMIT STATEMENT REQUIRES ADDITIONAL INTERVENTION **
--      COMMIT
            ;

      INSERT INTO PUBLIC.t1 VALUES(2);
-- ** MSC-ERROR - MSCEWI3111 - ROLLBACK STATEMENT REQUIRES ADDITIONAL INTERVENTION **
--      ROLLBACK
              ;
   END;
$$;

CALL PUBLIC.proc1();

Manually Migrated Code:

CREATE OR REPLACE TABLE PUBLIC.t1 (col1 INTEGER);

BEGIN TRANSACTION;
INSERT INTO PUBLIC.t1 VALUES(1);
COMMIT;

BEGIN TRANSACTION;
INSERT INTO PUBLIC.t1 VALUES(2);
ROLLBACK;

SELECT * FROM PUBLIC.t1;

CREATE OR REPLACE PROCEDURE PUBLIC.proc1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      BEGIN TRANSACTION;
      INSERT INTO PUBLIC.t1 VALUES(1);
      COMMIT;
      
      BEGIN TRANSACTION;
      INSERT INTO PUBLIC.t1 VALUES(1);
      ROLLBACK;
   END;
$$;

CALL PUBLIC.proc1();

Recommendations

• You can add "BEGIN TRANSACTION" statements to mimic Oracle's implicit transactions.

• Make sure all new transactions are finished when control ends to avoid errors on Snowflake.

• If you need more support, you can email us at [e-mail]