Locking in SQL Server

In this article we look at how locking happens in Ms SQL server and learn about their types

Whether you know or not, the credit for handling a number of transactions in SQL Server goes to its Locking strategy. There are in fact, different types of locks for different types of query plans. This is not all; a user can make the server create more locks like the shared, holdlocks and noholdlocks for specific queries. Locking in SQL server can be done both at a larger level and at a smaller level, where the former includes locking tables to restrict the access to some parts of the tables and the latter includes locking rows to increase concurrency. Learn in depth about locking in the SQL Server Database and the types of locks available and their uses in this article.

Two Phase Locking

The main idea behind the two phase locking is to look after what is done to whom in the SQL server. In other words, it helps in resolving the lock conflicts in case some locks are not compatible with each other. Using two different rules, this feature helps affecting a transaction that can be either releasing a lock or issuing a lock. The two way locking helps issuing a statement by an application and directs the server to start the operations once the locks are acquired and the transactions are in place.

Types of SQL Server Locks

Now, let us throw some light on two main types of SQL Server Locks. The two main types of locks in SQL are Page Locks and Table Locks. Page locks are the ones that lock the rows of a page, while the table locks helps locking the whole table. In comparison to the table locks, page locks are smaller and also less restrictive. Out of the two, table locks offer more efficiency in locking by locking the whole table. These two types of locks are further divided into different types of locks.

Types of Page Locks

There are basically three types of page locks, including, the Shared Locks, Exclusive Locks and the Update Locks. Shared locks are meant for the read operations and once this type of lock is applied to a data page, then the all the other attached transactions of the page can acquire a shared lock once the first transaction gets over. Exclusive Locks are meant for data modification operations and here if one transaction gets an exclusive lock, the other matching transactions can’t acquire the same lock on its own. Update Locks are the locks that work on the first and the initial part of a delete, update or fetch operation of a page when it is read and it allows shared locks but not the exclusive locks.

Types of Table Locks

There are three main table locks including the Intent Lock, Shared Lock and the Exclusive Lock. Intent Locks helps figuring out the types of page level locks that are found in a table. Shared Locks are quite similar to the Shared Page Locks but it unlike them, these affect the whole table. Exclusive Locks are also almost same like the Exclusive Page Locks but they too affect the whole table.

Backing up a SQL database may not be enough in the event of a SQL crash

Even if you have been regularly backing up the SQL database, it may not be enough in the event of a crash. Data stored from the time of the last backup to the moment when the system crashed would be at risk. To recover this data one needs to call in a specialized sql server recovery application like DataNumen SQL Recovery. This powerful too can bring back every single record stored in the application without fail and is exceptional successful in recovering indexes which other tools in its class may fail to bring back. Last but not the least, the tool is rather easy to use and the recovery process can be completed in quick time.

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 https://www.datanumen.com/