# the two sql statements must function as one single, atomic transaction
# we check for errors after the eval block.
    
LABEL_START_TRANSACTION:

    eval
    {
        # AutoCommit must be off for transaction handling, it is put back on
        # after the transaction.
        
        $dbh->{AutoCommit} = 0;
        $dbh->{RaiseError} = 1;
        
        # if nothing is being updated, then we don't want to fail
        if ($setFields)
        {
            $sql = "UPDATE $table SET $setFields WHERE mrID=$mrNumber";
            
            # prepare the sql statement
            $sth = $dbh->prepare($sql);
            
            # bind the parameters (description and any multi-line fields)
            for (my $i=0; $i<=$#projValuesToBind; $i++)
            {
                $sth->bind_param($i+1, $projValuesToBind[$i], DBI::SQL_LONGVARCHAR);
            }
            
            # execute the sql statement to insert row into MASTERx table
            $sth->execute();
        }
        
        # get the new address book fields
        if($ENV{'ABMASTER'} && $setABFields) 
        {
            $sql = "UPDATE ${table}_ABDATA SET $setABFields WHERE mrID=$mrNumber";
            
            # prepare the sql statement
            $sth = $dbh->prepare($sql);
            
            # bind the parameters (any multi-line fields) 
            for (my $i=0; $i<=$#ABValuesToBind; $i++)
            {
                $sth->bind_param($i+1, $ABValuesToBind[$i], DBI::SQL_LONGVARCHAR);
            }
            
            # execute the sql statement to update row into MASTERx_ABDATA table
            $sth->execute();
        }
    
        # there are sql statements that need to be comitted
        if (($setABFields && $ENV{'ABMASTER'}) || $setFields)
        {
            # everything worked, so commit the database changes.
            $dbh->commit();      
        }
    };
    
    
    if ($@) # error during transaction, rollback and report information
    {   
        $errorCount++;
        my $originalError = $@;
        
        # retry for up to five errors
        if ($errorCount <= $DBI_RETRY_COUNT)
        {
            # roll back the transaction
            #  (catch errors from rolling back.  otherwise the script will just die.)
            eval {
                $dbh->rollback();
            };
            if ($@)  # error rolling back!
            {
                $errStr .= "$originalError <BR><BR>\n";
                $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
                &errorExit(203, "mrChange transaction error: ", $errStr, "<BR>\nalso failed while rolling back: $@<BR><BR>\n");
            }
            
            # sleep a random max of $DBI_RETRY_SECONDS
            my $retrySeconds = int(rand($DBI_RETRY_SECONDS)+1);
            
            $errStr .= "Slept ${retrySeconds}s... ";
            sleep $retrySeconds; 
            
            $errStr .= "Trying again after reconnect.<BR>\n";

            # reconnect
            &disconnectFromDBD;
            &connectToDBD;
            
            goto LABEL_START_TRANSACTION;
        }
        
        # roll back the transaction
        #  (catch errors from rolling back.  otherwise the script will just die.)
        eval {
            $dbh->rollback();
        };
        if ($@)  # error rolling back!
        {
            $errStr .= "$originalError <BR><BR>\n";
            $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
            &errorExit(203, "mrChange transaction error: ", $errStr, "<BR>\nalso failed while rolling back: $@<BR><BR>\n");
        }
        
        # rollback was successful:
        # append warning from dbi to error string
        $errStr .= "$originalError <BR><BR>\n";
        
        $errStr .= "$sql <BR><BR>\n $ABsql <BR>\n";
        
        &errorExit(203, "mrChange transaction error: ", $errStr);
    }
    
    # AutoCommit MUST get turned back on, as the rest of FootPrints does not
    # use transactions.
    
    $dbh->{AutoCommit} = 1;
    $dbh->{RaiseError} = 0;