Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Data missing after a transaction is said comitted successfully

    I'm using SQL Server 2008.
    Hi,

    I've googled for a similar problem before but that is a 'SQL 2000'.

    My application requires several clients (can be at the same time) to write the data to the SQL Server 2008 through the WEB Service.

    The Web Service is just a transaction and return "success" or "fail". I found that after a few hundreds or thousands of transactions, though the Web Service return "success", the data is missing after that transaction is said committed successfully.

    The followings is the piece of code that the Web Service will call.

    public static int ExecSQL(string[] SQL)
    {
    int I = 0;
    int J = 0;
    string S = "";
    try {
    CN = GenConnection();
    CN.Open();
    SqlConnection.ClearPool((SqlConnection)CN);
    TR = CN.BeginTransaction();

    CM = GenCommand();
    CM.Connection = CN;
    CM.Transaction = TR;
    CM.CommandTimeout = 600;
    for (J = SQL.GetLowerBound(0); J <= SQL.GetUpperBound(0); J++) {
    S = SQL[J].Trim();
    if (!string.IsNullOrEmpty(S)) {
    CM.CommandText = S;
    I = CM.ExecuteNonQuery();
    }
    }
    TR.Commit();

    CN.Close();
    CN.Dispose();
    GC.Collect();

    return 0;
    } catch (Exception E) {
    try {
    if ((TR != null)) {
    if ((TR.Connection != null)) {
    TR.Rollback();
    }
    }
    } catch {
    }
    LogError(E, S);

    CN.Close();
    CN.Dispose();
    GC.Collect();
    return 255;

    } finally {
    }
    }

    I have log the data before calling this sub-routine and after this sub-routine. The data missing is found just after successfully committed. Please help.... it's been distributing me for several months.....

    Best regards

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    The above Java code seems ok: you start a transaction, execute several SQLs (stored as strings in the array) and commit. I suppose that 0 means "success" and 255 means "fail".

    It is not clear what do you mean by missing data: you have an INSERT and you don't have the new row, or you have an UPDATE that doesn't change the row(s).
    Also, what do you mean by "the data missing is found just after successfully committed"? The data is in the table and vanishes later?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    >>It is not clear what do you mean by missing data: you have an INSERT and >>you don't have the new row, or you have an UPDATE that doesn't change the row(s).
    >>Also, what do you mean by "the data missing is found just after >>successfully committed"? The data is in the table and vanishes later?

    Thanks for your reply. Normally it is an Insert but sometimes it's an update... both after committed, the data either doesn't have new row or doesn't change...

    I've tried to insert the same data into an other table at the same time with identity ON, the identity jumps over which means the SQL server does insert something into it but it may roll back... so I can't understand what's happening...

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by skchan12 View Post
    ... the identity jumps over which means the SQL server does insert something into it but it may roll back... so I can't understand what's happening...
    You are logging stuff. What is it telling you?

    It's hard to read the unformatted code you gave us. Is LogError(E, S) called after each Rollback() call? Using [CODE][ /CODE] tags would make your code a lot easier for us to understand.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    The errorlog is just used to record the exception and the SQL which has been executed. I also log the data which call before this sub-routine and after... it seems that the missing data is related to the time-out of calling Web service (the program will re-try to call the web service again in short period of time), while the SQL is still running the query...

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    As you have gaps in your identity column, I think that something sends a ROLLBACK to MSSQL: your Web service, or the driver when a timeout occurs.
    You should review your code to catch this scenario.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  7. #7
    Join Date
    Jun 2011
    Posts
    4
    I found that the bug may occur at ExecuteNonquery....
    The bug may occur IF ExecuteNonquery return "0" for insert or update without throwing error, it may pass to "commit" ... than the data may seem missing...

Posting Permissions

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