Problem in c# windows service using linq

I am having a bit of a problem in a windows service I have developed, I seem to be having a DB conflict using linq, I think i understand why the problem is happening but would like to get some advice in resolving it.

I have a database with a table that holds messages that need to be sent every at certain intervals from the service here is my code snippet.

protected override void OnStart(string[] args)
    {
        timer = new Timer(10000);
        timer.Elapsed += new ElapsedEventHandler(TimeElapsed);
        timer.Interval = 10000;
        timer.Start();
        EventLogger.LogEvent("Timer started succesfuly", EventLogEntryType.Information);

    }

    protected override void OnStop()
    {
        if (timer != null)
            timer.Stop();
        EventLogger.LogEvent("Timer stopped.", EventLogEntryType.Information);
    }

    private void TimeElapsed(object source, ElapsedEventArgs e)
    {
        try
        {
            EventLogger.LogEvent("Checking for reversals", EventLogEntryType.Information);
            //We now need to check for reversals that are waiting to be sent
            ReversalsDataContext db = new ReversalsDataContext();
            var reversals = db.FiReversals.Where(r => r.Status == Reversal.ReversalStatus.WAITING_TO_SEND);

            if (reversals.Any())
            {
                EventLogger.LogEvent(reversals.Count() + " reversals found and being processed.", EventLogEntryType.Information);
                //Mark the reversal
                foreach (var rev in reversals)
                {
                    MarkReversal(rev);
                }
                db.SubmitChanges();



                //We now need to send the reversal
                foreach (var rev in reversals)
                {
                    SendReversal(rev);
                }
                db.SubmitChanges();
                EventLogger.LogEvent("Finished processing reversal queue", EventLogEntryType.Information);
            }
        }
        catch (Exception ex)
        {
            EventLogger.LogEvent(ex.Message + "\n" + ex.StackTrace, EventLogEntryType.Error);
        }
    }

The error I get now is

Row not found or changed. at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges() at EftcObReversalService.EftcObReversalService.TimeElapsed(Object source, ElapsedEventArgs e)

From what I can see is this is caused from the following code,

if (reversals.Any())
            {
                EventLogger.LogEvent(reversals.Count() + " reversals found and being processed.", EventLogEntryType.Information);
                //Mark the reversal
                foreach (var rev in reversals)
                {
                    MarkReversal(rev);
                }
                db.SubmitChanges();



                //We now need to send the reversal
                foreach (var rev in reversals)
                {
                    SendReversal(rev);
                }
                db.SubmitChanges(开发者_如何转开发);
                EventLogger.LogEvent("Finished processing reversal queue", EventLogEntryType.Information);
            }

What happens here is, I read in from the table what is marked with a "WAITING_TO_SEND", once I have that I decide to mark the entries with a "PENDING" status. I do this as i do not want the entries to be read in again when the timer swings through and this thread has not completed yet.

Once I have marked all the entires I then handle each one in the SendReversal method, where I also mark the status as "COMPLETED" when it was completed OK or change the status back to "WAITING_TO_SEND".

I think this error is being cause from the 2 db.SubmitChanges, as the entry has changes and been written by the first submit and is different from the second submit?

If anyone has any thoughts or solutions please let me know.

I was about to suggest that the simplest solution would be to requery for "pending"... but of course that doesn't actually solve the problem if the process is going to overrun.

There are a couple of options that I can see quickly:

1. Change the logic so that you can only run one instance of the processing loop at a time - basically if you're already processing you can't start so skip or reschedule.
2. In addition to changing the state you also need to add a batch number so that you can requery after the first save but restrict that query to items pending for a particular batch number - I've done this in the past and had it work reasonably well providing the error handling was smart enough to be able to tidy up afterward if something went strange.

Ok thanks to Murph's advice I have found my solution, here it is if anyone wants a reference,

In my service i have

private ReversalSender _revSender;
    private Thread _revSenderThread;

    public EftcObReversalService()
    {
        InitializeComponent();
    }

    protected override void OnStart(string[] args)
    {
        _revSender = new ReversalSender();
        _revSenderThread = new Thread(new ThreadStart(_revSender.Run));
        _revSenderThread.Start();

        var timer = new System.Timers.Timer(5000);
        timer.Elapsed += new ElapsedEventHandler(TimerElapsed);
        timer.Start();
    }

    void TimerElapsed(object sender, ElapsedEventArgs e)
    {
        _revSender.Signal();
    }

    protected override void OnStop()
    {
        _revSender.Stop();
        _revSenderThread.Join(60000);
    }

Now to make sure that we have no concurrency issues accesing the DB the ReversalSender has a ManualResetEvent

public class ReversalSender
{
    private bool _running = true;
    private ManualResetEvent _reset = new ManualResetEvent(false);

    public void Run()
    {
        while (_running)
        {
            _reset.WaitOne();
            //We now need to grab all the waiting reversals
            ReversalsDataContext db = new ReversalsDataContext();
            var reversals = db.FiReversals.Where(r => r.Status == Reversal.ReversalStatus.WAITING_TO_SEND);
            if (reversals.Any())
            {
                foreach (var rev in reversals)
                {
                    if (_running)
                        SendReversal(rev);
                }
            }
            db.SubmitChanges();
        }
    }

    private void SendReversal(FiReversal rev)
    {
       .....
    }

    public void Signal()
    {
        _reset.Set();
    }

    public void Stop()
    {
        _running = false;
    }
}

Just a hunch, but I think you are on the right track. Try removing the first call to db.SaveChanges().

if (reversals.Any())
{   
    foreach (var rev in reversals)
    {
        MarkReversal(rev);
    }       

    //We now need to send the reversal
    foreach (var rev in reversals)
    {
        SendReversal(rev);
    }

    db.SubmitChanges();                
}

EDIT: Could you not do this:

if (reversals.Any())
{   
    foreach (var rev in reversals)
    {
        MarkReversal(rev);
        SendReversal(rev);
    }

    db.SubmitChanges();
}

Do the two calls in the same context for each object.