Concurrent transactional calls resulting in duplicate responses in .net web service

开发者_如何学GoHi all I have a question on concurrent stored procedure calls from a .net web service written in 3.5 framework.

I am building a web service that is used by many users and needs to return contact information from oracle database. The issue is when more than 1 user clicks at the same time the db returns the same contact info. I have written the status update query in the SP. I am having this issue only when 2 or more requests read the same record before the status update happens. I have used transaction and transaction-scope but it doesn't solve the issue. Can anyone tell me if i am tackling the problem right or should i be looking at some other way. Any help is greatly appreciated.

Sounds like your stored procedure code is what we term in the trade 'dodgy'.

Generally it should be an

UPDATE table
SET status = 'READ'
WHERE ...
RETURNING col_1, col_2 INTO var1, var2;
RETURN;

It is probably doing a SELECT then an UPDATE based on ID without checking to see whether the status has been changed by another transaction

Your "read" operation on the database isn't really a read, since it updates the record in question. Sounds like what you need is for the first read-and-update operation to complete and commit before the second read-and-update operation begins.

I'm not a database/oracle guru, so I apologize if this answer is somewhat vague: is there a way to lock the table in question at the start of the read-and-update operation - perhaps an oracle transaction setting? If so, that should accomplish what you're looking for.

Note that you're essentially single-threading access to that table, which could potentially have performance implications, depending on the number of concurrent users and how often the table is accessed. You might want to consider alternate ways of accomplishing the requirement.