Transactions

• default is autocommit = true, explicit transactions use BEGIN
• Only the read committed isolation is supported, repeatable read is supported via STREAM objects
• DDL statements implicitly commit any open transaction and runs the DDL as separate transaction
• An implicit transaction is started when autocommit = false is off and when the first DML statement is encountered
• If an implicit transaction starts in a stored-procedure call, and the stored-procedure doesn't contain commit, it will be rolled-back.
  • to prevent stored-procedures from starting an implicit transaction, start an explicit transaction before the call.

    CREATE PROCEDURE p1() ...
    $$
            INSERT INTO parent_table ...;  -- this would start an implicit transaction if not already in a transaction
            INSERT INTO child_table ...;
    $$; -- this would cause a rollback if the stored-procedure started an implicit transaction
    
    ALTER SESSION SET AUTOCOMMIT = FALSE;
    BEGIN TRANSACTION; -- this starts explicit transaction
    CALL p1;
    COMMIT WORK;
    

• A failed statement still keeps the transaction active that can be either committed (except for the failed statement) or rolled back
• locks are obtained at object level.
  • INSERT, COPY and SELECT can run in parallel with each other (and sometime even with statements such as UPDATE that lock objects)
  • UPDATE, DELETE and MERGE lock the object
• lock timeouts:
  • LOCK_TIMEOUT the amount of time a transaction waits (default 12 hours)
  • Idle, open transactions that are holding locks are cancelled after 5 minute if another transaction is waiting for the lock, 4 hours otherwise

Locks

• select, insert, copy - no lock
• update, delete, merge - partition lock
• transaction commit - table lock
• BP: use MERGE instead of INSERT/DELETE/UPDATE (Ref: Admin course)

Scoped Transaction

• A transaction started in a different scope (stored-procedure call) is considered autonomous, scoped transaction
  • scoped transactions behave like an independent transactions and thus can deadlock and not see uncommitted updates
• A transaction boundaries must be in the same scope, that is, transaction must either start and end outside of the stored-procedure, or start-end in the same stored procedure
  • A stored-procedure call can either be fully inside a transaction, or a transaction can be fully inside a stored-procedure call
• A scoped transaction can commit/rollback without affected or affected by the enclosing transaction
• A transaction includes all statements, including contained in stored-procedure, until a new scoped transaction is started in the stored-procedure

Commit

• An active transaction is implicitly:
  • committed when either a DDL or alter session set autocommit is executed
  • rolled back when session ends or stored-procedure ends
• A transaction started in a SP, if not explicitly committed, is rolled back.
  • this includes implicitly started transaction (ie autocommit = false and the first DML is in stored-proc). To prevent this, explicitly start a transaction (begin transaction) outside of the stored procedure
• If a transaction is started implicitly outside a SP call, commit inside the SP is an error