db_transaction (public)

 db_transaction [ -dbn dbn ] transaction_code [ args... ]

Defined in packages/acs-tcl/tcl/01-database-procs.tcl

Usage: db_transaction transaction_code [ on_error { error_code_block } ] Executes transaction_code with transactional semantics. This means that either all of the database commands within transaction_code are committed to the database or none of them are. Multiple db_transactions may be nested (end transaction is transparently ns_db dml'ed when the outermost transaction completes).

To handle errors, use db_transaction {transaction_code} on_error {error_code_block}. Any error generated in transaction_code will be caught automatically and process control will transfer to error_code_block with a variable errmsg set. The error_code block can then clean up after the error, such as presenting a usable error message to the user. Following the execution of error_code_block the transaction will be aborted. If you want to explicitly abort the transaction, call db_abort_transaction from within the transaction_code block or the error_code block.

Example 1:
In this example, db_dml triggers an error, so control passes to the on_error block which prints a readable error.

    db_transaction {
        db_dml test "nonsense"
    } on_error {
        ad_return_error "Error in blah/foo/bar" "The error was: $errmsg"
    }
    

Example 2:
In this example, the second command, "nonsense" triggers an error. There is no on_error block, so the transaction is immediately halted and aborted.

    db_transaction {
        db_dml test {insert into footest values(1)}
        nonsense
        db_dml test {insert into footest values(2)}
    }
    

Switches:

-dbn (optional)

The database name to use. If empty_string, uses the default database.

Parameters:

transaction_code (required)

Partial Call Graph (max 5 caller/called nodes):

Testcases:

db__transaction, db__transaction_bug_3440

Source code:

    upvar "#0" [db_state_array_name_is -dbn $dbn] db_state

    set syn_err "db_transaction: Invalid arguments. Use db_transaction { code } \[on_error { error_code_block }\] "
    set arg_c [llength $args]

    if { $arg_c != 0 && $arg_c != 2 } {
        # Either this is a transaction with no error handling or there
        # must be an on_error { code } block.
        error $syn_err
    }  elseif { $arg_c == 2 } {
        # We think they're specifying an on_error block
        if {[lindex $args 0] ne "on_error"  } {
            # Unexpected: they put something besides on_error as a
            # connector.
            error $syn_err
        } else {
            # Success! We got an on_error code block.
            set on_error [lindex $args 1]
        }
    }
    # Make the error message and database handle available to the
    # on_error block.
    upvar errmsg errmsg

    db_with_handle -dbn $dbn db {
        # Preserve the handle, since db_with_handle kills it after
        # executing this block.
        set dbh $db
        # Remember that there's a transaction happening on this handle.
        if { ![info exists db_state(transaction_level,$dbh)] } {
            set db_state(transaction_level,$dbh) 0
        }
        set level [incr db_state(transaction_level,$dbh)]
        if { $level == 1 } {
            ns_db dml $dbh "begin transaction"
            ns_cache_transaction_begin
        }
    }
    # Execute the transaction code.
    set errno [catch {
        uplevel 1 $transaction_code
    } errmsg]
    incr db_state(transaction_level,$dbh) -1

    set err_p 0
    switch -- $errno {
        0 {
            # TCL_OK
        }
        2 {
            # TCL_RETURN
        }
        3 {
            # TCL_BREAK - Abort the transaction and do the break.
            ns_db dml $dbh "abort transaction"
            ns_cache_transaction_rollback
            db_release_unused_handles -dbn $dbn
            break
        }
        4 {
            # TCL_CONTINUE - just ignore.
        }
        default {
            # TCL_ERROR or unknown error code: Its a real error.
            set err_p 1
        }
    }

    if { $err_p || [db_abort_transaction_p -dbn $dbn]} {
        # An error was triggered or the transaction has been aborted.
        db_abort_transaction -dbn $dbn

        if { [info exists on_error] && $on_error ne "" } {

            if {"postgresql" eq [db_type]} {

                # JCD: with postgres we abort the transaction prior to
                # executing the on_error block since there is nothing
                # you can do to "fix it" and keeping it meant things like
                # queries in the on_error block would then fail.
                #
                # Note that the semantics described in the proc doc
                # are not possible to support on PostgreSQL.

                # DRB: I removed the db_release_unused_handles call that
                # this patch included because additional aborts further
                # down triggered an illegal db handle error.  I'm going to
                # have the code start a new transaction as well.  If we
                # don't, if a transaction fails and the on_error block
                # fails, the on_error block DML will have been committed.
                # Starting a new transaction here means that DML by both
                # the transaction and on_error clause will be rolled back.
                # On the other hand, if the on_error clause doesn't fail,
                # any DML in that block will be committed.  This seems more
                # useful than simply punting ...

                ns_db dml $dbh "abort transaction"
                ns_cache_transaction_rollback
                ns_db dml $dbh "begin transaction"
                ns_cache_transaction_begin

            }

            # An on_error block exists, so execute it.

            set errno  [catch {
                uplevel 1 $on_error
            } on_errmsg]

            # Determine what do with the error.
            set err_p 0
            switch -- $errno {
                0 {
                    # TCL_OK
                }

                2 {
                    # TCL_RETURN
                }
                3 {
                    # TCL_BREAK
                    ns_db dml $dbh "abort transaction"
                    ns_cache_transaction_rollback
                    db_release_unused_handles
                    break
                }
                4 {
                    # TCL_CONTINUE - just ignore.
                }
                default {
                    # TCL_ERROR or unknown error code: Its a real error.
                    set err_p 1
                }
            }

            if { $err_p } {
                # An error was generated from the $on_error block.
                if { $level == 1} {
                    # We're at the top level, so we abort the transaction.
                    set db_state(db_abort_p,$dbh) 0
                    ns_db dml $dbh "abort transaction"
                    ns_cache_transaction_rollback
                }
                #
                # We throw this error because it was thrown from the
                # error handling code that the programmer must fix.
                #
                error $on_errmsg $::errorInfo $::errorCode
            } else {
                # Good, no error thrown by the on_error block.
                if { [db_abort_transaction_p -dbn $dbn] } {
                    # This means we should abort the transaction.
                    if { $level == 1 } {
                        set db_state(db_abort_p,$dbh) 0
                        ns_db dml $dbh "abort transaction"
                        ns_cache_transaction_rollback
                        #
                        # We still have the transaction generated
                        # error.  We don't want to throw it, so we log
                        # it, unless it is "rollback tests"
                        #
                        if {$errmsg ne "rollback tests"} {
                            ns_log Error "Aborting transaction due to error:\n$errmsg"
                        }
                    } else {
                        # Propagate the error up to the next level.
                        error $errmsg $::errorInfo $::errorCode
                    }
                } else {
                    #
                    # The on_error block has resolved the transaction
                    # error.  If we're at the top, commit and exit.
                    # Otherwise, we continue on through the lower
                    # transaction levels.
                    #
                    if { $level == 1} {
                        ns_db dml $dbh "end transaction"
                        ns_cache_transaction_commit
                    }
                }
            }
        } else {
            # There is no on_error block, yet there is an error, so we propagate it.
            if { $level == 1 } {
                set db_state(db_abort_p,$dbh) 0
                ns_db dml $dbh "abort transaction"
                ns_cache_transaction_rollback
                error "Transaction aborted: $errmsg" $::errorInfo $::errorCode
            } else {
                db_abort_transaction -dbn $dbn
                error $errmsg $::errorInfo $::errorCode
            }
        }
    } else {
        # There was no error from the transaction code.
        if { [db_abort_transaction_p -dbn $dbn] } {
            # The user requested the transaction be aborted.
            if { $level == 1 } {
                set db_state(db_abort_p,$dbh) 0
                ns_db dml $dbh "abort transaction"
                ns_cache_transaction_rollback
            }
        } elseif { $level == 1 } {
            # Success!  No errors and no requested abort.  Commit.
            ns_db dml $dbh "end transaction"
            ns_cache_transaction_commit
        }
    }

XQL Not present:

Generic, PostgreSQL, Oracle