There’s a situation in our application where we only want to insert some data once into a table. Continuing on with my currency example from caching (this isn’t the problem I faced but it’s much easier to talk about currencies)… Let’s say we don’t pre-load the system with all currencies, let’s pretend that we get sent the currency information from a third party system – if we come across a new currency then we insert it. Obviously, we don’t want duplications and the easiest way to stop that is to put a unique constraint on the field in the database (let’s say on the CurrencyCode field).
There are two options, the first is if we don’t find it we insert it and catch a unique constraint exception, then look it up again. The other is we lock on insert so no other threads can insert the same item. In a single process scenario (i.e. the only process running on this machine, or any other machine, is the only thing that will ever insert this data is the current one) then we can just lock. But this won’t work if you’re in a load balanced server farm… For example, a consumer of your service makes two calls concurrently, both reference a currency which doesn’t exist (therefore needs creating), the first call goes to ServerA, the second to ServerB.
As we’re inserting into a database we can use the database to help us. We can take a lock out on the database table which will stop anyone else being able to insert the same record (or in this case any record as we have an exclusive table lock). There isn’t (or at least I’ve not found) a way of adding locking hints to the SQL entity framework generates. The code below takes out our lock:
Now we can lock we can build this in to something which stops us inserting duplicates. We want to be careful though, table locks are pretty awful things to take out, it’s ok if it’s rare (how often does a new currency come about?) but we certainly don’t want to do it for every read of a currency (it could get expensive!)…
The other thing to notice with the code above is that we create another session to actually create our currency. We don’t want to commit all the other data on the other session just to insert our new currency. There are two good reasons for this, the first is we are holding a very expensive lock at the database, we want to hold for the shortest amount of time (insert all the other data on the context may take a while). The other is the principle of least surprise – we don’t expect our data to be saved until we ask, we certainly don’t expect it to be saved based on the existence of a currency or not (it’s not very repeatable because next time it won’t happen).
I think a very important question you should ask yourself is “how often will I need to insert?” If it’s a low number, I think this approach is fine. We only take out the lock if we can’t find it – so it’s a very rare event indeed when we do it. If it’s a lot more often you’re inserting then I’d think hard about this, because it has the potential to create a bottleneck.