SQL Server Concurrency & Transaction Management  is a process which allows multiple users to access data and perform multiple operations maintaining data integrity and smooth execution all at the same time. Microsoft SQL Server manages concurrency control by using locks to help protect data. The locks control how multiple users can access and change shared data at the same time without conflicting with each other.

This a concept which needs to be in developer’s mind all the time while writing the scripts to avoid any problems that may occur due to execution. Such as data loss , dirty reads and deadlocks which can eventually through out the whole system into darkness.

Hello subscribers and readers , I hope you guys are doing great and apologies for being absent for a long time. We all need a break from our routine from time-to-time. Don’t We?  Hopefully , you will see more posts in month of May as there are few new series on SQL Server that are in queue . In meantime , if you wan’t to ask any question or would like to have a post on a topic . Feel free to Contact us and we will publish it in our Questions Category.

SQL Server Concurrency

SQL Server Concurrency manages number of operations simultaneously  without letting them interfering with each other. Concurrency comes into play when a process that is changing data prevents other processes from reading that data . Similarly ,  when a process that is reading data prevents other processes from changing that data concurrency comes in to ensure safe execution of transactions .Concurrency is also affected when multiple processes are attempting to change the same data at the same time .

SQL Server Concurrency & Transaction Management Case Study

Think of the SQL Server Concurrency as a system which is responsible to make sure that operations such as read , write and update  always operate on correct data. An enterprise application manages multiple operations from hundreds of users all at the same time . Now if someone , adds a new record in the application. SQL Server tries to add the details in sql database . At the very same time it is possible that a manager is trying to access the reports. SQL Server then access data to fill out the reports.

Now you have two operations , one is changing the data and the other is reading the data. Can you see the problem here? The read operation and write operation both could be trying to access same data . Now can you guess which one will be able to access the information first? Read or Write?

SQL Server concurrency and Transaction management takes care of taking such decisions without slowing down the process. It makes sure that both these operations are performing correctly and that there is no data loss during writing and correct data is present in reports.

To understand the SQL Server Concurrency first we need to understand what is a Transaction Management .

Transaction Management

Transaction is a  logical unit of work that must be either entirely complete or not. Successful transaction changes the database from one consistent state to another

Using case study example , a user who wants to read the data when ask the system to do so. SQL Server treats the task as a single transaction. Similarly for the write operations our SQL Server will treat as another transaction. A transaction has ACID properties.

ACID Properties

Atomicity:  Requires that all operations (SQL requests) of a transaction be completed; if not, then the transaction is aborted. A transaction is treated as a single, indivisible, logical unit of work

Consistency: ensures that a transaction won’t allow the system to arrive at an incorrect logical state – the data must always be logically correct.  Consistency is honored even in the event of a system failure.

Isolation: Data used during execution of a transaction cannot be used by second transaction until first one is completed

Durability:Indicates permanence of database’s consistent state.When a transaction is complete, the database reaches a consistent state. That state can not be lost even if the system fails. If a system failure occurs while a transaction is in progress, the transaction is completely undone, leaving no partial effects on data.

These four principles govern how SQL Server or any other DBMS handles a transaction.

How Does SQL Server Concurrency Work?

Take a long breath and let everything settle in . We read about concurrency and why is it so important for SQL Server to manage multiple operations at a time and then we read about transactions and its properties.  Now we know that every task or single instruction (transaction) has to satisfy all four conditions in order to be successful . Otherwise transaction will be incomplete.

From our Case Study , imagine hundreds of write /update transactions coming in . At the same time , few users are viewing the reports to make important decisions. Different users accessing same data for different purposes. SQL Server Concurrency & Transaction management will make sure that all the tasks are properly aligned and that each transaction is completed before the other transaction.

Some transactions may change the state of the data and some may not . So it is very crucial for the SQL Server to maintain its ACID property even if the system fails. SQL Server Concurrency & Transaction management is achieved by the use of locks .  To understand the locks we need to understand about concurrency and transactions . DBAs can choose how these locks work by using isolation levels. The two topics that we will cover in Part 2 of this article.

I hope this proves useful. You can follow us on twitter , facebook or subscribe us to get the second part of this article and many more such articles in your mailbox. Feel free to comment below , if anything requires further clarification or if i have make a mistake. I still feel a bit rusty after my vacations.

See you with our next article!
Cheers!
Happy Learning!

About

Professional Data Analyst and Business Intelligence Developer with experience of delivering industrial projects for Supply Chain and Insurance Industry . Sharing all my experience and insights in databases and data warehousing and open to learn from fellows ! Happy Reading and Learning!

Leave a Reply

%d bloggers like this: