linq to sql and locking

I am stuck in a locking decision and thought could use your expertise.

I want to add a new row to the table and multiple threads are trying to insert that new row to the table. I want only one of them to scuceed. While insertion is being done, there can be requests from other threads for reading some data from the table and they should succeed.

What would be the best locking approach for this scenario ? Exc开发者_JAVA技巧lusive table locks won't work since I want read queries to succeed.

Thanks xoxo

Let the DB do the work- unique index the key so first inserter wins. There's no foolproof way to detect it before SubmitChanges that won't get you into more trouble than you're starting with.

If you want to serialize inserts on a particular key without disturbing reads, and you control all the code that would be doing it, have a look at sp_getapplock. Make the name of the applock correspond to the key you're trying to block inserts for, then have the concurrent insert code do a 0-timeout call to sp_getapplock. The one that succeeds should check again to make sure the record doesn't exist while holding the lock, do the insert, then release the lock. It's not pretty, it's not fast, but it does what you want. You can also hook up sp_getapplock and related SPs on your DataContext so you can still call them via L2S instead of sending a command.