Transaction Modes and Nested Transactions

Although chained mode implicitly begins transactions with data-retrieval or modification statements, you can nest transactions only by explicitly using begin transaction statements.

Once the first transaction implicitly begins, further data-retrieval or modification statements no longer begin transactions until after the first transaction commits or aborts. For example, in the following query, the first commit transaction commits all changes in chained mode; the second commit is unnecessary:

insert into publishers
    values ("9907", null, null, null)
    insert into publishers
        values ("9908", null, null, null)
    commit transaction
commit transaction
Note: In chained mode, a data-retrieval or modification statement begins a transaction whether or not it executes successfully. Even a select that does not access a table begins a transaction.