Sporadically old data is read

Question

I'm not sure if this is the right forum, but I've the following problem.

I'm using SQL Server 2008 R2 installed on Windows Server 2008 R2 Standard. My application runs on the same machine. I'm using .NET 4 with ADO.NET data access. I've the following scenario.

I open a DbConnection to the database, create a DbDataAdapter and

1. Put my new row that should be inserted into a database table in a DataTable object and call DbDataAdapter.Update. This update triggers a "After Update trigger" that change data in another table.
2. Later in the application flow I create a new DbDataAdapter and query the field that's changed in the other table. I see that the field has the correct value and I'm done.

In nearly all the cases this is working fine, but I've encountered cases where this gets wrong. After I read the field that's changed in the other table, I found that the field is unchanged.
I've recorded the return value DbDataAdapter.Update and found that also in this case the return value was 1. So that means that my new row was inserted and the trigger was executed without an error. Or is that conclusion wrong?
I also read my inserted row back via a view and found that the line was not inserted at all. How can this be? DbDataAdapter.Update returned that one row was inserted, but if I read it back the row is not inserted.

In my application flow I thougt that the line does not exists in the database table, so I insert it again with a new primary key (The ID column is set to identity). I end up with duplicated lines in my database.

This week I've recorded a case where I've inserted the line 5 times in a timespan of 3 seconds. So I didn't get the new row for 4 times. After these 3 seconds where I didn't find the new row all is "fine". All 5 rows are inserted but I've the row is dupplicated.

The customer is working with the application 24/7 and I've hearded this problem this week and 6 weeks ago. So it seems that's very sporadically.

Does anybody had seen this behavior, that the inserted line can't read back and no error occured?

Regards,
Matthias

Answer 1

Hello,

With my poor english, i am not sure that i will be able to give you an understandable explanation.

When you are updating a row inside a table, the modification is posted in the transaction log file in a chronological way and treated later . The "wait" time  may depend of many factors like the "state" of this transaction log file.

See https://www.google.com/url?q=http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/&sa=U&ei=JsrtT56TL5DI0AXmp7TyDQ&ved=0CAUQFjAA&client=internal-uds-cse&usg=AFQjCNEp_FZ2jeZUuTX-eH0cBzTG-eXQig

http://www.mssqltips.com/sqlservertutorial/8/sql-server-transaction-log-backups/

If the transaction log is not cleaned by backups, the transaction log is growing, growing and the transfer from the transaction log file o the database may need an important time.

This question is more related to Database Engine.If you post your agreement, a moderator will move your thread towards the Database Engine Forum ASAP ( no thread to recreate, no lost posts, no thread staying unanswered because it is not in the "good" forum ). But only a moderator can do this move.

We are waiting for your feedback to try to help you more efficiently.

Have a nice day 

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Thank for the explanation and sorry for my late reply. I was on vacation.

But I'm not sure if I understand you correctly. Does this mean that if DbDataAdapter.Update returns 1 that my inserted row is "only" posted to the transaction log file? At which time is my new row available in the table?

BTW the transaction log is currently 40GB in size and 0.3% is used. The recovery modell is set to full, replication is not used.

Papy, if my question is more related to the Database Engine, please move it to this forum.

Thanks for your help.
Matthias