Developer: Managing SQL Server Concurrency

Managing SQL Server Concurrency

When writing multi-user applications you can tune your database servers to handle concurrency. Here we tell you how it works in MS SQL Server

Thursday, May 14, 2009

BANGALORE, INDIA: Gone are the days when we used to write applications for the desktop which would be used by only one user at a time. Almost all applications that we write today are multi-user systems which would be used by many users concurrently.

And today we have sophisticated frameworks and application infrastructures that let the application developer focus on solving the business problem at hand, where the frameworks and infrastructures take care of concurrency. For any data-driven application, if you use a database server like Microsoft SQL Server, the server handles the database concurrency issues for you, whereas you, the developer, focuses on your core task of solving your client's business problem.

You will not have to write code to manage database level concurrency in most projects you would be involved in. SQL Server would figure out how to let multiple users access the same piece of data without them overwriting each other's changes. However, there would be times when you would need better control over how the server manages concurrency and then you would need to provide guidelines to the server as to how you want it to manage concurrency for you.

For example, when you start tuning your database server for performance, you will realize performance tuning is actually a tight rope walk. And more often than not, you gain performance at the expense of concurrency and you gain concurrency at the expense of performance. Managing database concurrency is quite an involved topic in itself.

For the remainder of the article, I would focus on one of the most important aspects of managing concurrency for Microsoft SQL Server, configuring the transaction isolation level. I will explain the term as we go along. I am running my code samples on a Microsoft SQL Server 2008 database, but most of what I am discussing would work on the older versions also.

Prepare the data
Let us first get some sample data ready that we will later try to modify concurrently.

CREATE TABLE Employee
(
empid int primary key IDENTITY(1, 1),
empname nvarchar(20),
city nvarchar(15)
)
INSERT INTO Employee(empname, city)
VALUES('Ashish', 'Mumbai')
INSERT INTO Employee(empname, city)
VALUES('Asha', 'Pune')

To keep matters simple, we will not have a front end with multiple users who try to modify data simultaneously. Instead, I will open up multiple query Windows in SQL Server Management Studio to simulate multiple users.

NEXT>>

Would you like to Comment on this Article?