SNOW-782479: Context manager for Transactions

What is the current behavior?

Currently the user is required to keep track of what state transactions are in, and commit/rollback as needed based on different execution paths. This is particularly important when stored procs are nested, because you don't want an open transaction crossing the proc boundary.

Code inside a stored proc might look like this:

transaction_open = False
try:
  session.sql('begin transaction').collect()
  transaction_open = True
  session.sql('truncate table my_table').collect()
  # some other code that might error
  # ...
  session.sql('insert into my_table(col1) values(1)').collect()
  session.sql('commit').collect()
  transaction_open = False
  # some other stuff after the transactions (maybe more transactions?)
  # this might also error
  return {
    "success" : True
  }
except Exception as exception:
  if transaction_open is True:
    session.sql('rollback').collect()
  return {
    "success": False,
    "error": str(exception)
  }

What is the desired behavior?

Something more pythonic via a context manager, similar to the way you can manage connections.

The above code could look more like this:

try:
  with session.transaction(action_on_complete='commit',action_on_error='rollback') as txn:
    session.sql('truncate table my_table').collect()
    # some other code that might error
    # ...
    session.sql('insert into my_table(col1) values(1)').collect()
    # some other stuff after the transactions (maybe more transactions?)
    # this might also error
    return {
      "success" : True
    }
except Exception as exception:
  return {
    "success": False,
    "error": str(exception)
  }

Internally it would be running BEGIN TRANSACTION etc for you in the __enter__ and __exit__ methods.
You could also override behaviour, e.g. manual rollback with txn.rollback() followed by a return statement and the context manager then wouldn't do the commit when it exits.

How would this improve snowflake-snowpark-python?

It would allow people to use transactions with less cognitive overhead, less room for bugs, and more terse code.

References, Other Background

I am happy to contribute a PR, but it would be good to first know if people agree with the concept or have any feedback/concerns.

Start by reviewing the existing connection context-manager implementation and transaction handling in the Python API. Clarify the proposed transaction lifecycle, including enter, exit, commit, rollback, errors, nested stored procedures, and manual rollback. Done means the maintainers agree on the API and its behavior before implementation begins.