The Snowflake docs (https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html) have some seemingly contradictory information about stored procedures and transactions: On the one hand: "If a stored procedure is called outside an explicit transaction, then each statement inside the stored procedure executes as a separate transaction." On the other: "Currently, transaction control commands (BEGIN, COMMIT, ROLLBACK) are not allowed inside a stored procedure. This means that a stored procedure is executed entirely within a single transaction (explicitly or implicitly)." Which of the two is it?
1 Answers
2
votes
If you are just calling the stored procedure, it's not explicitly in a transaction. Each operation will be visible as it completes..
If you start a transaction and call a stored procedure it's in a transaction, and as long as you do not do any DDL that breaks snowflake transactions, you are atomic.
That how we read/understand it.
The docs say what I said:
If a stored procedure is called outside an explicit transaction, then each statement inside the stored procedure executes as a separate transaction.
this is the part that explains where the explicit/ implicit part comes from
Remember that DDL statements (CREATE TABLE, etc.) cause an implicit COMMIT. Such statements should not be used inside a stored procedure if the procedure will be called inside a transaction. For example, the following pseudo-code demonstrates what not to do:
this part is explaining the reason transactions are broken
Currently, transaction control commands (BEGIN, COMMIT, ROLLBACK) are not allowed inside a stored procedure. This means that a stored procedure is executed entirely within a single transaction (explicitly or implicitly).
Thus this part is referring to those other parts. It's effectively says you cannot do nested/recursive transactions. Like you can in other DBs.
