在事务中重复插入

Question

I have scenario in which I got multiple records inserted into the Table even the checks are implemented, I explain the flow of the code below.

BEGIN Transection;
var model = select id from table1 where outlet_code = 123 and survey_id = 5
if(model){
     throw new Exception("Outlet is already Surveyed.");
}else{
     INSERT Record into the table
} 
Commit Transection;

Problem: I am getting multiple requests from the mobile side to upload survey, but the problem occur when I got multiple request with same outlet code.

First  Request outlet_code = 123abc and survey_id = 5
Second Request outlet_code = 123abc and survey_id = 5

Point is that Second request should not be inserted in the code however in some situations both records get inserted into the "table1"

I tested this scenario, but I got the exception which is fine, but there are records in the Database which violates this condition and I got both records in the database

kindly suggest what I am doing wrong,I just have an idea that it might be the problem of transaction committing order but I don't know how to solve this situation.

Database is SQL server 2008.

Answer 1

The easiest and cleanest way would be creating a primary key on ID columnn so table won't accept any inserts with duplicating IDs. This will throw an error though on a second insert so it should be handled as well.