Which isolation level should I use for the following insert-if-not-present transaction?
- by Steve Guidi
I've written a linq-to-sql program that essentially performs an ETL task, and I've noticed many places where parallelization will improve its performance. However, I'm concerned about preventing uniquness constraint violations when two threads perform the following task (psuedo code).
Record CreateRecord(string recordText)
{
using (MyDataContext database = GetDatabase())
{
Record existingRecord = database.MyTable.FirstOrDefault(record.KeyPredicate());
if(existingRecord == null)
{
existingRecord = CreateRecord(recordText);
database.MyTable.InsertOnSubmit(existingRecord);
}
database.SubmitChanges();
return existingRecord;
}
}
In general, this code executes a SELECT statement to test for record existance, followed by an INSERT statement if the record doesn't exist. It is encapsulated by an implicit transaction.
When two threads run this code for the same instance of recordText, I want to prevent them from simultaneously determining that the record doesn't exist, thereby both attempting to create the same record. An isolation level and explicit transaction will work well, except I'm not certain which isolation level I should use -- Serializable should work, but seems too strict. Is there a better choice?
