Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009

    Unanswered: Commit transaction in case of error rather than rolling back the whole txn


    I came across an instance,details follows:

    a). tabA holds 1000 records with the columns as salary,name,dept,empid.
    b). Update is fired as below
    update tabA set salary=salary*1.5

    If at runtime the Nth record produces an error (@@error <>0),I want the n-1 records to be commited rather than having rollback of the entire transaction.

    if the error happens at 501,
    then 500 records should be commited
    501 should be ignored and logged for audit
    update presumes from 502 onwards.

    Appreciate the help on this, (looking for a set oriented method to achieve this).

  2. #2
    Join Date
    Mar 2009
    Sydney, Australia
    Yeah, well a set-oriented method is a great goal, so do not shift away from that.

    But ASE is also a transaction engine, therefore additionally you have to be aware of transactions and transaction requirements and consequences. If you have 1000 rows, you have to decide on what your transaction is before you execute: is it 500 or 1000 ? "1000 if it works, and 500 if it fails" is not a transaction. The whole idea is the transaction must be Atomic (either the whole thing succeeds xor the whole thing fails). Updating 1000 rows (or 1,000,000) is not a transaction for many reasons, just one of which is exactly the problem you have described. Autocommit (Chained) is deleterious to transaction handling.

    Third, each error has a Severity Level. Lower Severity Levels allow the code to continue; higher Severity Levels stop the code and the xact, automatically rollback the xact (and other Severity Levels do not) and allow error handling; highest levels entirely blow the batch [3] away (no error handling will catch it).

    The solution looks like this. Decide on your actual, real, transaction size, let's say it is 10. This construct keeps the transactions small, and provides a method of breaking up large batches of insert/update/delete into manageable units:
    WHILE (1 = 1)
        UPDATE tab_a SET
                salary = salary * 1.5
            WHERE salary != salary * 1.5
        SET @err = @@ERROR,
            @rows = @@ROWCOUNT
        IF @err != 0
            PRINT "Update failed with error %1!"
        IF @err = 0 AND @rows = 0
    1 Of course you need to add full error checking, I have given you the basics.

    2 If the update was based on a Key (rather than on non-key columns such as "salary != salary * 1.5), the code is more flexible and restartable. Eg. remove the BREAK, it will process 990 rows; that is, only the transaction with the error gets rolled back. Without a Key-based update, you cannot restart after the N+1 or N+transaction_size row.

    2.1 If your transaction size is 1, then you will get 999 hits and fail on just the row that encounters an error.

    3 A "batch" as per the manuals, is all code between two "go" statements.
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

  3. #3
    Join Date
    Nov 2009

    Commit transaction in case of error rather than rolling back the whole txn

    Hi Derek,

    Thanks for the reply.

    That's true if we do the DML's batch wise within a txn we can accurately identify the bad records.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts