I have two (the same) applications running, with a different configuration file. Both applications read a different POP3 inbox and depending on what is in the inbox, sends e-mails back.
Both applications use the same database. All e-mails that come in are stored in one table. In this table I have a column "To", "Parsed" and "Replied".
The "To" field contains the name of the inbox, so this is different in both applications. When the application runs, it checks all e-mails with it's own "To" address and sets them to Parsed and if there is a reply to be sent, it makes a reply body text and puts that in another table, that is related by an e-mailId to the original e-mail.
In the end of the application, there is a stored procedure, this checks if there are any replies to be sent (for the corresponding e-mail address) and then sends these e-mails.
The procedure is like this;
CREATE PROCEDURE Emails_GetQueued
@emailBox AS VARCHAR(255)
SELECT Queue.* FROM Queue
INNER JOIN Emails ON Emails.EmailId = Queue.OriginalEmailId
WHERE Queue.[Sent] = 0 AND Emails.EmailBox = @emailBox
Sometimes, I get two replies back, one from each application. So it seems like the stored procedure is run for both applications at the same time and returns the same recordset (even when they are called with a different @emailBox parameter).
Is it possible that it is not thread safe? Or could it be, because both applications are exactly the same and running at the same time?
And what would be easiest to avoid this, apart from creating a different set of tables for each application configuration?
Any help is appreciated.