Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to Locking in SQL Server

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2007-12-12 : 14:17:27

Locking is a major part of every RDBMS and is important to know about. It is a database functionality which without a multi-user environment could not work. The main problem of locking is that in an essence it's a logical and not physical problem. This means that no amount of hardware will help you in the end. Yes you might cut execution times but this is only a virtual fix. In a heavy multi-user environment any logical problems will appear sooner or later.

Read Introduction to Locking in SQL Server

Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-12-12 : 14:53:22
really nice article. looking forward to the next.

Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 2007-12-12 : 15:29:41
Great article, very detailed !

- Jeff
Go to Top of Page

Starting Member

5 Posts

Posted - 2007-12-13 : 14:58:25
Our application has been showing more timeout errors due to lock contention since upgrading from SQL Server 2000 SP3 to SP4 and SQL Server 2005. Did the locking semantics change between these versions? I haven't found any clues on the Microsoft support web site.
- Nils B.
Go to Top of Page

Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 15:47:13
i don't think that you're problems are caused by changes in locking mechanism which are very few that i could find out about.
they're probably caused more by the lack of updated statistics and good indexing.

Causing trouble since 1980
SSMS Add-in that does a few things: <- new version out
Go to Top of Page

Starting Member

2 Posts

Posted - 2008-01-08 : 18:22:05
It is worth nothing that SQL2000 may ignore the ROWLOCK hint and intermittently (based on server load ) take out a PAGELOCK or TABLELOCK.

This can cause DML to fail to insert into a table it selected from.
Go to Top of Page

Starting Member

5 Posts

Posted - 2008-07-05 : 17:17:10
The article states that "Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level." I don’tthink that is true. Key-range locks will be obtained while in the default read-committed isolation level too. Can be easily shown y running:

use AdventureWorks

WHERE AddressId >= 4 AND AddressId <= 5

SELECT resource_type, request_mode, resource_description, *
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

Go to Top of Page

Cybernetic Yak Master

11752 Posts

Posted - 2008-07-06 : 07:10:18
yes, but you're using WITH(HOLDLOCK) which is essentialy the same as what serializable does by default.

Causing trouble since 1980
Speed up SSMS development: <- version 1.0 out!
Go to Top of Page

Starting Member

1 Post

Posted - 2013-08-14 : 11:26:58
I think there is some mistake here.
"In the example you can't see the shared locks because they're taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks."

If there is optimistic transaction level as here, then there is no SHARED LOCK!
It will be Sch-S lock for the time of select statement.

Here you can see Shared lock only because HOLDLOCK is added.
It looks that HOLDLOCK escalate Sch_S lock to Shared lock.

I have tested on SQL2008.
Go to Top of Page

- Advertisement -