Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    9

    Unanswered: Can a rollback be delayed?

    Hi all, thanks for reading.

    Bit of a puzzler...

    A customer of ours has reported a intermittent error using one of our applications to receive some stock.

    Under normal circumstances the application updates about 10 tables in a single transaction, inserting to some, updating others. After the stock receipt they print off a Crystal report against the receipt data.

    The customer is saying that (very) occasionally they perform a receipt (no errors) and print off the report (no problems) yet when they come to access the data some time later it is almost as if no receipt has been made.

    I have seen their data and can see what they mean. None of the expected data updates seem to have taken place. And yet there is the report sat there, indicating that it must have...

    Looking closely at the data I can see breaks in the sequencing in certain identity column-carrying tables, during the period when the stock receipt was made. To all intents and purposes, therefore, it looks as though a rollback has occurred.

    Is this possible? Could a Crystal report show uncommitted data which is then rolled back? How 'long' can a rollback take? Can it be initiated in some other way?

    NB: We have done all the obvious things like:
    - checking they have received against the correct DB;
    - checking that no app or procedure can remove data in this way.
    - verified that the report is reporting from the correct place.
    etc.

    The 'missing' data is so perfectly removed, my instinct says it must be a rollback but I can't see how this can be (yet).

    All suggestions gratefully received - how could I track down whether this was occurring? Or is it my fevered imagination?

    Many thanks!!!!

    pmb

    ps: According to our records, this customer is running SQL 7 SP3.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The short answer is "it depends".

    The longer (less infuriating) answer is that in order for the rollback to remove the transaction, the transaction must be open during the entire time. This will have the effect that no other SPID can access any of the modified data until the transaction is committed or rolled back (i.e. a blocking problem). OK, sounds like you knew that already, but the practical upshot of all that is that the situation you describe is technically possible provided it all happens on the same connection. If the report is generated from the exact same place (and connection) as the transaction occurred on, then it is possible if one more piece falls exactly into place. The connection must end abnormally without committing the changes. An abnormal end to a connection is generally treated as a rollback, and would explain the situation above, but as I said before, if this was the case, you would very likely see a lot more blocking problems than lost data problems.

  3. #3
    Join Date
    Feb 2003
    Posts
    9

    Dirty reads/writes?

    Thanks for the response!

    The transactions in our apps are automatically bracketed by 'begin'/'commit transaction' calls as part of screen handling architecture. (ie: the user presses an on-screen button, a 'begin transaction' is fired off, the guts of the program does its SQL business and the final step then fires the 'commit transaction'). Any SQL errors encountered betweentimes are intercepted and displayed to screen (with a 'rollback transaction' performed before program abort).

    To the best of my knowledge, no errors are encountered during the stock receipt process, so it looks like the commit goes ok. Following the receipt, the user then spins up our Crystal front-end and runs the relevant report. I would have thought that this Crystal report step would be treated as a completely separate SQL process and thus the report would only be able 'see' the amended data from the previous process once it was committed.

    Is there some kind of 'dirty read' (or even 'dirty write') SQL setting that could be happening here? Assuming such a setting exists and is in use here, how would I find out? And how would such logic operate with a rollback?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It is possible that Crystal Reports is connecting and setting their session to READ_UNCOMMITTED, or using nolock hints, but that would not make a lot of sense (at least to me). You should be able to find out if Crystal is doing that, by running a few Profiler traces.

    My money is still on something/someone is deleting the data. It may be via Query Analyzer, rather than through your application, or even an MS Office application accessing the tables directly. From what you say about the architecture of the application, it sounds like the commit/rollback is instantaneous, and does not wait for any user interaction.

  5. #5
    Join Date
    Feb 2003
    Posts
    9

    Read uncommitted

    Thanks, fellow drone. Much appreciated. I'll look into the read uncommitted side of things.

    On the basis that a report *could* 'see' uncommitted data, I'm still left with the issue of why the rollback is occurring. That's my own problem of course (assuming there is no possibility of this being a SQL server-level issue).

    So, a question - if I wrote a trigger to store off transaction details to a logging table (say, writing off @@IDENTITY) to 'prove' the reciept was taking place), if a rollback occurs on the triggering table, will my logging table updates also be rolled back?

    I'm assuming the answer to the above is 'YES', so am open to suggestions on how to track the problem. (NB: Running Profiler not really an option. On average the issue occurs once a fortnight!)

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    That trigger idea made me wonder about something.
    Code:
    create table test1
    (col1 int identity (1, 1),
     col2 varchar(10))
    
    insert into test1 (col2) values ('hello')
    select * from test1
    begin transaction 
    
    insert into test1 (col2) values ('hi')
    
    select * from test1
    
    begin transaction 
     update test1
     set col2 = 'bye'
    commit transaction
    
    select * from test1
    
    rollback transaction
    select * from test1
    
    drop table test1
    A rollback statement will rollback any transactions committed within any transaction before it. Maybe you should have something check @@trancount and save that to a more durable location (say a text log?) Maybe there is some problem when the planets are aligned just right, you have some transaction still going from somewhere else. Still, I would expect that to cause huge blocking problems before lost data problems.

Posting Permissions

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