SQL Select data Delay

Question

I have two application(A & B) and a SQL table (Microsoft SQL Server 2014) with columns as below:

|| Guid || Random || WriteTime || Flag || ReadTime ||

Application A will insert the new row with new Guid, random, WriteTime and Flag.

(Code A)

private void WriteIntoDB(int random)
    {
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlTransaction trans = conn.BeginTransaction())
            {
                 SqlCommand command = conn.CreateCommand();
                 command.Connection = conn;
                 command.Transaction = trans;

                 command.CommandText = string.Format("INSERT INTO " + "MyTable" + "([Guid],[Random],[WriteTime],[Flag])" + "VALUES(@Guid,@Random,@WriteTime,@Flag)");
                 command.Parameters.AddWithValue("@Guid", Guid.NewGuid());
                 command.Parameters.AddWithValue("@Random", random);
                 command.Parameters.AddWithValue("@WriteTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                 command.Parameters.AddWithValue("@Flag", 0);

                 command.ExecuteNonQuery();
                 trans.Commit();
             }
    }

This function will be called six times in TimerA_Tick(with TimerA interval = 1500 millisecond)

And Application B also has a TimerB with interval = 1000 millisecond. In TimerB_Tick, it will select all rows where Flag=0, and change it to 1, also fill in column 'ReadTime'.

(CODE B)

private DataSet DS = new DataSet();    
private void ChangeFlag(SqlConnection conn)
   {
         string cmd = "SELECT * FROM MyTable WHERE Flag = 0";
         SqlDataAdapter da = new SqlDataAdapter(cmd, conn);
         DS.Tables["test"].Clear();
         da.Fill(DS.Tables["test"]);
         lock (DS.Tables["test"])
         {
             using (new SqlCommandBuilder(da))
             {
                 DataRow[] rows = DS.Tables["test"].Select("Flag=0 ", "WriteTime ASC");
                 foreach (DataRow row in rows)
                 {
                     if (Convert.ToInt32(row["Random"]) <= 50)
                     {
                         //do something.
                     }
                     row["ReadTime"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                     row["Flag"] = 1;
                     da.Update(DS.Tables["test"]);
                 }
             }
         }

     }

I expect 'Random' which was written in the database first also to be processed by ApplicationB first. Most of the data was, but some of them were not... The link below is part of that SQL Table, ordered with "WriteTime' ascendant.

There's a row that seems to be processed previously to the data which is written earlier. But I don't know why it happened(and more than one time), also got no idea how to fix it.

Are both processes running on the same machine? This question includes [a good answer](https://stackoverflow.com/questions/2143140/c-sharp-datetime-now-precision) on the accuracy of now in .Net. — David Rushton, May 23 '17 at 12:52
@scsimon I'm sorry that I don't understand your words...In picture, the table is ordered with 'WriteTime'(ASC), and 'ReadTime' should had been ordered ASC becuase the 'ReadTime' is updated with the order of 'WriteTime' — Claux, May 23 '17 at 14:05
You mention the write timer runs every 1.5 sec but the WriteTime values are less than a second apart, are you sure the timers are running as expected? — KMoussa, May 23 '17 at 14:08
@KMoussa the function,WriteIntoDB, will be called six times in a Timer_tick(in 1.5 sec), so there is no problem with Timer, I think. — Claux, May 23 '17 at 14:18
@destination-data I know DateTime.Now is not so accurate, but I thought since I had ordered the 'WriteTime' in func. ChangeFlag (ApplicationB) to make the flag changed by that order, then the 'ReadTime' that represents the time which Flag is changed, would also follow that order.(I'm sorry if my explain is unclear, my English is not very good.) — Claux, May 23 '17 at 14:24
My guess would be something to do with locking, not exactly sure what, somehow timer B ends up ticking while the previous tick is still working. Try disabling timer B at the start of `ChangeFlag` and enabling it again at the end maybe — KMoussa, May 23 '17 at 14:31
You are using "Flag = 0" in your command and your select function. This has probably nothing to do with your issue, but it seems redundant. — Peter, May 23 '17 at 14:37
You don't need the transaction in you first application.It may be better to leave it out. If the issue is still there, I would recommend a trace. — Peter, May 23 '17 at 14:44
@KMoussa I had stopped Timer B at the bigin of TimerB_Tick, then called ChangeFlag and then enabled it again at the end of TimerB_Tick. — Claux, May 23 '17 at 14:52
@Peter Thanks for your advice! I'll correct about that redundant. But why is transaction not needed? — Claux, May 23 '17 at 14:56
I can better ask you, why would you use a transaction? It seems unnecessary, because you won't do a rollback anyway. As long as the transaction is uncommitted the record is both yes and not inserted at the same time, this will affect other queries. — Peter, May 24 '17 at 08:09
@Peter I thought using Transaction can make sure data be written into database, but now it seems that I had misunderstood it. Now I delete the transaction part, and it works successfully!! The order of 'ReadTime' is correct now. Thank you! — Claux, May 24 '17 at 11:02

SQL Select data Delay

0 Answers0