• Why don't you try it out? You can use the RAISERROR statement to force an error, or intentionally do an update that violates a constraint on the table.

    Be very careful when naming transactions. Names are NOT used by commit and only the name of the outermost transaction can be passed to rollback. From books online:

    COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] transaction_name: Is ignored by the SQL Server Database Engine

    ROLLBACK { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ]

    transaction_name: Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

    A rollback will always roll back all open transactions. If there's a commit after that point, the commit will throw an error, saying no open transactions.

    A commit in a nested transaction will decrement the transaction count . Only if the tran count reaches 0 will the transaction(s) be committed.

    In your first example, an error inside t2 will cause both transactions to rollback and an error to be thrown when the commit of t2 is reached and another when the  commit or rollback of t1 is reached. The first and second updates will be rolled back, but the third (which won't be in a transaction at that point) will auto commit.

    In your second example, an error in the stored proc will cause t1 to be rolled back. The third update will succeed, because it's no longer in a transaction, and the commit statement will throw an error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass