SnowPro Advanced: Data Engineer free question 8 / 10 · Domain 5: Data Transformation
Inside a procedure, after BEGIN TRANSACTION you ran an INSERT into table A, created a working table with CREATE TABLE, and ran an INSERT into table B. The INSERT into table B failed, so you ran ROLLBACK. What happens to the rows inserted into table A?
- They are rolled back. ROLLBACK undoes the statements since BEGIN TRANSACTION, including the CREATE TABLE
- They are rolled back. When the INSERT into table B fails, the whole transaction is rolled back
- They remain. The CREATE TABLE implicitly committed before it ran, and the INSERT into A was committed
- They remain. AUTOCOMMIT is TRUE by default, so each statement inside an explicit transaction is committed as well
Show the answer and explanation
Answer: C
Why this is correct
Each DDL statement is executed as a separate transaction. If you run DDL while a transaction is active, the DDL (1) implicitly COMMITs the active transaction and (2) runs itself as a separate transaction. So if you put a CREATE TABLE after BEGIN TRANSACTION, the earlier INSERT into table A is committed at that point and is not undone by the later ROLLBACK. A DML right after a DDL implicitly starts a new transaction. This is why the documentation recommends putting only DML and queries inside an explicit transaction.
Why the other options are wrong
"They are rolled back. ROLLBACK undoes the statements since BEGIN TRANSACTION, including the CREATE TABLE" … ROLLBACK undoes the statements of a transaction that has not been committed yet. The INSERT into table A was already committed by the implicit COMMIT before the CREATE TABLE, so it is not undone. A DDL is its own transaction, so the CREATE TABLE cannot be undone by ROLLBACK either.
"They are rolled back. When the INSERT into table B fails, the whole transaction is rolled back" … Even if a statement fails inside a transaction, the whole transaction is not automatically rolled back, and the rest can be committed. Moreover, the INSERT into A was committed at the time of the CREATE TABLE.
"They remain. AUTOCOMMIT is TRUE by default, so each statement inside an explicit transaction is committed as well" … Statements inside an explicit transaction are not affected by AUTOCOMMIT. The rows in table A remain not because of AUTOCOMMIT but because of the implicit COMMIT before the DDL.
Cautions in procedures
Do not change the AUTOCOMMIT setting inside a procedure. When AUTOCOMMIT is disabled, a transaction still active at the end of the procedure is implicitly rolled back. Since a DDL is a transaction by itself, the DDL itself cannot be undone with ROLLBACK.