Locking in SQL Server

Locking in SQL Server is mechanism to control and manage concurrency maintaining data integrity and validation. This allows multiple users to access  and use database concurrently.

Consider the scenario and let me paint the complete picture here. When we talk about concurrency in DBMS , we are talking about DBMS allowing multiple users conducting multiple transactions and operations all at the same time. The operation can be read , update , delete or create. All of this , should execute on correct data. If Transaction T1 updates a row transaction T2 should be able to read the updated data. All transactions should satisfy their ACID properties.

Must-Read : SQL Server Concurrency & Transaction Management

100 transactions are trying to use the same table. Some of these transactions will only read data others will change the data. There has to be some way or mechanism in place , which should decide whether to show committed data or uncommitted data to other transactions. Maybe , we need to make sure that unless a transaction is not complete , no other transaction can read the data. Depending on situation , may be some other approach can be used. However , To do this SQL Server offers isolation levels.

Also-Read  : SQL Server Isolation Levels

Now that all the pieces are in place and if you are familiar with the topics from above . Can you tell about transaction life cycle?

Transaction Life Cycle

Locking in SQL Server

Transaction has ACID properties and its execution is very simple. If a transaction is successful and all the operations in it are complete. Transaction is successful and it gets commit. If not, all operations which were previously completed in that transaction will face a rollback.

Lets take two transactions here .

Transaction T1


SELECT * FROM Students

Transaction T2


delete FROM Students

SQL Server take these two transactions to execute them concurrently  checks the isolation level . Default Isolation level for SQL Server is Read committed isolation level , so naturally T2 won’t be able to delete data unless T1 is complete. SQL Server does it by using LOCKS. 

Locking in SQL Server Explained

Mechanism to control and manage concurrency maintaining data integrity and validation. This allows multiple users to access  and use database concurrently.

Default Isolation level of SQL Server will lock the data for Transaction T1 and will only allow T2 to access the same data once T1 is complete. This way it will manage the concurrency and data integrity.

Now we have tables , diskpages in database . Tables have rows. A transaction may engage all database , or simply a row or may be just a diskpages in database. SQL Server decides upon seeing the transaction what to lock and what not. Because if a transaction is reading only a row of one table , it won’t make sense to block all the tables . Similarly , if one table is involved in the transaction other tables should be available to execute any operations. This way concurrency is achieved and at the same time data validation and integrity also remains intact.

Lock Granularity

Lock granularity tells us about the level of lock use. We have following levels

  1. Database Level Locks
  2. Table Level Locks
  3. Page Level Locks
  4. Row-Level Locks
  5. Field-Level Locks

Database Level Lock

  1. Good for batch processing but unsuitable for online multi-user DBMSs
  2. T1 and T2 can not access the same database concurrently even if they use different tables

Locking in SQL Server

See how two transaction behaves when this lock is in action? Whole database is locked until T1 is complete.

Table Level Locks

  1. T1 and T2 can access the same database concurrently as long as they use different tables
  2. Can cause bottlenecks when many transactions are trying to access the same table (even if the transactions want to access different parts of the table and would not interfere with each other)
  3. Not suitable for multi-user DBMSs

Locking in SQL Server

Here T1 requires row 5 to be updated , so the lock and after that T2 gets the lock .

Page-Level Lock

  1. An entire disk page is locked (a table can span several pages and each page can contain several rows of one or more tables)
  2. Most frequently used multi-user DBMS locking method

Locking in SQL Server

Row-Level Lock

  1. Concurrent transactions can access different rows of the same table even if the rows are located on the same page
  2. Improves data availability but with high overhead (each row has a lock that must be read and written to)

Locking in SQL Server

Field-Level Lock

  1. Allows concurrent transactions to access the same row as long as they require the use of different fields with that row
  2. Most flexible lock buy requires an extremely high level of overhead

These diagrams illustrates how concurrency and locks works together on the basis of isolation levels to make sure that data is always consistent . Each transaction exhibits ACID properties.

 

Advertisements

Leave a Reply

%d bloggers like this: