score:1

Accepted answer

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;
    }
}

score:0

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.

score:2

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.

Related Query

More Query from same tag