How does SQL Server handle simulataneous read/write requests?

Solution 1:

SQL Server is a multi-user database. It's primarily intended to handle multiple simultaneous requests.

Ideally, two queries that take A + B when executed serially will take MAX(A, B) when executed simultaneously.

In order to prevent corrupt data from being read or written, SQL Server uses Transactions and Locking. In addition to this, applications may be managing concurrency as well (optimistic, for example).

Solution 2:

I think that David B provided a good answer, but I'm just going to expand it a bit here with an example.

Let's say that you have two tables in your database, Table_A and Table_B. The server has multiple processors. Further, the disk subsystem puts each table on a separate drive/LUN/spindle (whatever the correct terminology is, I'm not a big hardware guy). Further, the server has multiple NIC cards.

If two users both access the database and one wants to write to Table_A while another wants to read from Table_B then hopefully you can see how the requests could come in simultaneously on different network cards, be handled simultaneously by different CPUs/cores, and the IO activity could all occur simultaneously on the different disks.

Now, in the real world it's seldom this simple. You usually have bottlenecks in one or more of these areas depending on your hardware and how the application is being used. Also, if both users are trying to access the same rows in the same table it could cause a resource bottleneck. So, in real life there are people who spend much of their time trying to find which resources are causing these bottlenecks for an application and getting rid of those bottlenecks. At the very least though, there are usually some areas where things can be processed in parallel.

Solution 3:

Your question implies that before the improvements, SQL access was sequential, i.e. subsequent queries are performed one after another, on the same connection, in the same code thread.

Moving the logic to separate threads will cause two distinct effects: firstly, your code no longer has to wait for each query to finish before continuing, and secondly, the SQL queries will be parallelized (to a degree), allowing SQL server to optimize data file access - which it is far, far better at than your code could ever be.

Assuming the code consists of more than pure SQL calls, the former can have as large an impact as the latter.