MSSQL Concurrency

[AIexplorer]

I have several questions:

Here is the context: The ASP.NET application I'm building - a web service in essence - will be connecting to MSSQL to log requests to a table. These are basic INSERT commands.

During high-volume hours, do I risk race conditions? How does MSSQL handle sequential INSERT queries? What about when there are multiple users? What is the cleanest way to connect the database and handle each query fast, so that no information is lost?

As a side note, the database will be handling other SELECT, UPDATE and DELETE query requests at the same time, but not nearly as frequently. HOWEVER, SELECT operations may involve between 100-2000 records and there may be multiple concurrent requests. How do I minimize concurrency problems?

Feel free to suggest reference material. Primarily I need some basic theory on how MSSQL2005 Express handles queries (its limits), and whether there is anything specific to look for. In other words, I know that I will run into concurrency problems, I just don't know which ones I should spend my time preempting.

Thanks in advance :)

[dickersonka]

i think you are seeming to be worried about the load, vs the concurrency

i would suggest using stored procedures with transactions

there is no problem with inserts, as long as they won't bust unique constraints or allow a user to join against them, when data isn't finished being populated

selects may involve 100-2000 records, for multiple clients, as long as the server can handle the load, then its not really a concurrency issue

here's an article that might be of interest
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

[AIexplorer]

So concurrency issues would only be the result of UPDATEs? Then, I think you are right, I'm more worried about load.

[dickersonka]

concurrency issues won't only be the result of updates, but from what you described what you were worried about, you only described about the amount of load on the system

how many users do you anticipate using your system at once? and is it a shared system, both web and db server?

[AIexplorer]

It could be up to 150 simultaneous users (roughly) for this particular application, though I don't have all the details on that.

I know that the DB server is used by 4 other applications, but these won't take up too many resources. It's not shared in the sense that it is hosted virtually.

[dickersonka]

i would suggest, not saying this is for sure the answer, using transactions with READ COMMITTED or REPEATABLE READ

this way while changing or selecting data, you ensure that it can't be modified while selecting or read until don't processing

http://msdn.microsoft.com/en-us/library/ms173763.aspx

[AIexplorer]

Sounds like in general I shouldn't run into problems, but if I do the references you offered will come in quite handy.

Thanks.