MS SQL Procedure not thread safe?

ingriT
ingriT used Ask the Experts™
on
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)
AS

SELECT Queue.* FROM Queue
INNER JOIN Emails ON Emails.EmailId = Queue.OriginalEmailId
WHERE Queue.[Sent] = 0 AND Emails.EmailBox = @emailBox

GO

Open in new window


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.

Thanks,
Ingrid
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I would suspect that something is wrong with your data or code. You should not get incorrect (the same) results as long as emailBox parameters are different.

As the side note, you can emulate kind of "critical section" in SQL with sp_getapplock stored procedure. http://aboutsqlserver.com/2010/09/26/sunday-t-sql-tip-application-locks/

Author

Commented:
I would suspect that something is wrong with your data or code. You should not get incorrect (the same) results as long as emailBox parameters are different.

I also suspected this, but in the logfiles I can see the parameter passed to the procedure is different, but the result is the same e-mail, so it is sent twice. It usually is some error in the code in the end, but I really can't find it this time.

I'll give the sp_getapplock a try, thanks!

Author

Commented:
Code was OK, data was OK, stored procedure however wasn't, it had an OR and not an AND in the WHERE clause.

The lock eventually caused by using the sp_getapplock procedure made this clear.

Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial