Hi Friends,
Last week I was in talk with some junior DBA’s and they wanted to understand different types of SQL Server locks with examples. I believe this question is very common for most of the new entrants. Locking is very important feature of any RDBMS which helps us in working in multiuser environments. In our day to day work we do come across different types of locks when we check SQL DMV’s and so on; it is very much important to understand them at first place before we start troubleshooting anything. I thought it would be good, if I post my demonstration here on my blog which may be helpful to the ones with similar questions on their minds.
Locking is not a physical problem and a logical one. I’ll explain some basic lock types with an example under READ COMMITTED isolation mode and as you can understand for simplicity, detailed explanation of all lock types are beyond scope of this blog post. I’ve divided my posts in two parts and today in Part 1 we will cover following lock types;
- Shared Locks (S)
- Update Locks (U)
- Exclusive Locks (X)
The intention of this post is just to demonstrate lock modes with simple examples. We would be using sys.dm_tran_locks DMV to identify different lock types used in my example(s).
Shared Locks (S) : Shared locks are held when data is read under pessimistic concurrency model. Other transactions can read data but cannot modify records that are locked. When the locked data is read, shared locks are released. For demo, I’ve used WITH (HOLDLOCK) in select statement to simulate them;
Update Locks (U) : When you execute any DML statements, at first SQL engine has to find the data that it wants to modify; to avoid any lock conversion to deadlocks an update lock is used. You can say, these kinds of locks are a combination of shared and exclusive locks (covered next). Only one update lock is allowed on data at a time but this lock is unable to modify data on its own until converted to exclusive locks (covered next). I’ve used WITH (UPDLOCK) hint for demo purpose;
Exclusive Locks (X) : Exclusive locks are held to lock data that are modified by one transaction in order to prevent data modifications by other concurrent transactions. Only when you specify NOLOCK hint or read uncommitted isolation level you can read data. As discussed on update lock, for DML statements first data needs to read data before they modify them as such you will always observe exclusive locks after a shared lock on the data.
I’ll stop here today; watch out this space for next part where I will explore some more lock types with examples.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
