Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Chicago
    Posts
    4

    Question Unanswered: Updating my table fails, not sure why?

    In Sybase 11.9.2, my application is updating a single status code in a table and it is getting done 99% of the time, my problem is that 1%.

    This table has a lot of contention for it via reads and writes.
    The table has data row locking and I am using Begin tran and commit tran code.

    I don't ever get an error message when this fails. Is there a code/variable/statement in Sybase that can tell me this update failed and why?

    some of my code....


    /* Begin a transaction */
    SP_BEGIN_TRAN( upd_extr_stat2 )

    /* Update the row */
    UPDATE
    pierdbb..pie_extr_que
    SET c_extr_stat = @c_extr_stat
    WHERE i_req_pkg_freq = @i_req_pkg_freq
    and d_nxt_proc = @d_nxt_proc
    and c_extr = @c_extr
    and i_perf_obj = @i_perf_obj
    and i_seq = @i_seq
    AND c_extr_stat not in (PIE_STATUS_RECYCLED, PIE_STATUS_ERRORED)
    SP_ERR_CHK_TRAN_AND_RETURN( upd_extr_stat2 )

    /* SP_ERR_CHK_AND_RETURN() */

    /* Check the transaction and commit */

    SP_COMMIT_TRAN( upd_extr_stat2 )

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Quote Originally Posted by mike noone
    I am using Begin tran and commit tran code.
    But you are not performing error checking to see if your code succeeded or failed
    Is there a code/variable/statement in Sybase that can tell me this update failed and why?
    Of course:
    @@error must be checked after EVERY verb (refer Error Msgs manual for values; note deadlocks)
    @@rowcount tells you how many rows were affected by the command
    @@transtate tells you how the transaction is doing

    Forget the sp's and your separation of code, after you get the code working inline, then package in an sp, or set of sp's.

    Here's an inline example but do read the manual for a proper understanding. This is the tiniest amount of error checking required, sp's should have a fuller and standardised form.

    Code:
    declare @err int,
        @rows int
    
    BEGIN TRAN upd_extr_stat2
    
    UPDATE
            pierdbb..pie_extr_que
        SET c_extr_stat = @c_extr_stat
        WHERE  i_req_pkg_freq = @i_req_pkg_freq
        and d_nxt_proc = @d_nxt_proc
        and c_extr = @c_extr
        and i_perf_obj = @i_perf_obj
        and i_seq       = @i_seq
        AND c_extr_stat not in (PIE_STATUS_RECYCLED,
            PIE_STATUS_ERRORED)
        SELECT @err=@@error,
            @rows = @@rowcount
    IF (@err != 0)
        BEGIN
        ROLLBACK TRAN upd_extr_stat2 
        PRINT "Error %1, transaction rolled back", @err  
        -- read up on raiserror for use in sp
        END
    
    COMMIT TRAN upd_extr_stat2
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  3. #3
    Join Date
    Jan 2003
    Location
    Chicago
    Posts
    4

    Thumbs up Thx Added @@Transtate - could IN_PROGRESS mean DB contention?

    Thanks so much for your help, I added the logic to check the transaction
    state - I am consistently getting the @@transtate = 0 ( IN_PROGRESS).

    Could it be possible to lose a track of transaction IN PROGRESS if there are too many of these going on? My code snipet is part of up to 15 processes running simultaneously. If they are all doing the same thing is it possible to lose a transaction with DB contention?

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    "Lost" Updates

    In case you are unaware, then note very soberly, "lost" updates, inserts and deletes are the well-known and predictable result of only two conditions:
    - poor/no transaction control or
    - inadequate error checking
    and can be entirely eliminated with ordinary transaction control and standardised error checking.
    ... I am consistently getting the @@transtate = 0 ( IN_PROGRESS).
    That is as expected.

    Checking @@trancount is more relevant. It should be:
    = 0 before you begin tran (abort with "Transaction already open" if not);
    = 1 within a tran (after begin tran and before commit or rollback) but checking is not nec;
    and nothing else. (Based on your question, you really should not be using nested trans, so do not go there). Read up on both @@transtate and @@trancount.

    Look for deadlocks (1205): you can use various utilities, sp_lock, server trace flags (or SQL code) to identify which spids are contending for the table/pages and which spid was rolled back and cancelled, but that is usually after the fact. In any event, the error detection and handling in your SQL code should identify and handle the error, during the fact.
    ... If they are all doing the same thing is it possible to lose a transaction with DB contention?
    Under no circumstances does Sybase "lose" transactions or the like. If there is any internal (Sybase server code) failure, it will be reported in the Sybase ASE errorlog; if there is an external failure (eg. SQL code being executed), then depending of the existence, correctness and method (retry; print msg; return success/failure; etc) of the error checking code therein, you will get an error msg and/or a success/failure return status.

    Needless to say, you will only get an error when it occurs (the 1%) and not in the 99%.

    Your code executes as an instance, so there is no need to worry about multiple instances of the same code executing concurrently: if the code is "good" then it works all the time (and reports when and why it did not); if the code is "poor" then you will have "lost" updates under contention. Note also, the contention is for db objects (tables, pages), not for code.

    I gave you a code segment to answer your question, not a full-blown standard structure for correct transaction handling, error reporting and abortion of incorrect attempts, which is the result of decent education, and contains related db components (user error msgs so that (a) they are standardised and (b) they can be RAISERRORed easily, regardless of what or where your client program is). Here's a code segment that is one step more advanced:
    Code:
    declare @err int, 
        @rows int 
    
    IF (@@trancount != 0)
        BEGIN 
        PRINT "A Transaction is already open by caller and should not be,"
        PRINT "execution aborted"
        RETURN 1 
        END 
    
    BEGIN TRAN 
    
    UPDATE pierdbb..pie_extr_que 
        SET c_extr_stat = @c_extr_stat 
        WHERE i_req_pkg_freq = @i_req_pkg_freq 
            and d_nxt_proc = @d_nxt_proc 
            and c_extr = @c_extr  
            and i_perf_obj = @i_perf_obj  
            and i_seq = @i_seq  
            AND c_extr_stat not in (PIE_STATUS_RECYCLED, 
            PIE_STATUS_ERRORED) 
    
    SELECT @err=@@error, 
        @rows = @@rowcount 
    
    IF (@err = 1205)
        BEGIN 
        PRINT "Error %1, Deadlock, transaction rolled back by server", @err
        -- no ROLLBACK necessary
        RETURN 1 
        END 
    
    IF (@err != 0)
        BEGIN 
        ROLLBACK TRAN 
        PRINT "Error %1, transaction rolled back", @err
        RETURN 1 
        END 
    
    IF (@rows != 1)
        BEGIN 
        ROLLBACK TRAN  -- for completeness
        PRINT "Update affected no rows !!!", @err
        -- this identifies where you may have "lost" something
        RETURN 1 
        END 
    
    COMMIT TRAN
    RETURN 0
    Reducing contention in the first place is an altogether different discussion.
    Last edited by DerekA; 02-05-05 at 23:05.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  5. #5
    Join Date
    Jan 2003
    Location
    Chicago
    Posts
    4

    Thanks - testing it now

    Thanks for your help in strengthening the existing query, the server code calling this procedure has a multiple levels to walk though but I think I am on my way. Will let you know more when I get this figured out.

  6. #6
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    It's not me who is uncommitted (and "lost"), it's my grandfather

    Of course the code segment above would identify if the calling code segment opened a transaction and left it open (intentionally or unintentonally), therefore you have open transaction occuring in lower level code segments like this one that do not expect it (and therefore the COMMIT TRAN in the nested code does NOTHING).

    For anyone reading this thread:
    Some people do not believe in transaction control or error checking. Deadlocks, if not ordinary contention, 1% failures, etc, forces them. Code that runs "perfectly" in development fails under load. If it does not have documented transaction control and documented, robust, standardised error checking, then it should be identified as such (an unfinished, substandard prototype) and remain in development; it should never be placed in test or production. Otherwise we waste huge amounts of resources finding the problem. And worse, when you fix one problem, the next problem, the next substandard code segment (which was hidden by the first problem) becomes visible. Ad nauseum, ad infinitum.

    Anything worth doing is worth doing right. Unbreakable code is not magic.
    Last edited by DerekA; 02-05-05 at 23:14.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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