Understanding Locking in SQL Server

Anyone who is remote familiar with RDBMS would know about the concept of locking. To put it in simple words, the data stored in a database can get compromised if two or people simultaneously access it and locking is used to avoid such a scenario, SQL Server uses a whole set of locking options to ensure data stored in your database remains error free. Let’s look at some of them.

Basic Shared Locks

At the outset the easiest locking technique is a shared lock. Under this multiple users can view the same data, however none of them can change it. Once the share lock is released, no commit action can be implemented.

Exclusive Locks

Considered amongst the most effective forms of locking, an exclusive lock is utilized to lock the data related to certain transaction in manner that no other client can change the data. Once the commit action is completed and exclusive lock released, other clients can then modify the data.

Intent Locks

An intent lock is used to signal another transaction that you are looking to lock a specific data set. In practical terms it essentially prevents an exclusive lock from being applied on the data set that you have plans to lock. Thus it prevents are blocking a data set while still allowing them to view the data set. 

SQL Server supports Spinlocks

Spinlocks are an interesting concept that is directed to check the data on which a lock exists and on which another client transaction is attempting to place a lock. If the first lock frees up, it quickly transfers the lock to the waiting transaction. This feature goes a long way in increasing the productivity of the SQL Server application as it quickens the speed by which threads can be switched.

Consider Key Range locks to avoid ghost reads

At some time you have to deal with issue of unknown processes reading your data, also known as ghost or phantom reads. To avoid the scenario SQL Server offers you the Key Range locking mechanism and it can be used secure any range of rows that are under a transact statement in an implicit manner. 

Dealing with SQL file corruption

The SQL Server application has a stellar reputation for reliability and yet it can at times fail due to erroneous handling or experience a system crash that can end up corrupting its encompassed data. In such a situation it becomes imperative to use a sql recovery tool like DataNumen SQL Recovery to bring back the compromised data. This exceptionally skilled tool can dig out data from NDF files with perfection and has the potential of negotiating SQL files running into multiple gigabytes.  It is also useful in cases where the corrupted SQL file is present on an optical media or a removable SSD drive. Last but not the least if you have a whole bunch of SQL files to recover, you can get them all back at the same time using the tool’s batch recovery feature.

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit http://www.datanumen.com/