SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to Locking in SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/12/2007 :  14:17:27  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/12/2007 :  14:53:22  Show Profile  Reply with Quote
really nice article. looking forward to the next.



-ec
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 12/12/2007 :  15:29:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Great article, very detailed !

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Nils
Starting Member

5 Posts

Posted - 12/13/2007 :  14:58:25  Show Profile  Reply with Quote
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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 12/13/2007 :  15:47:13  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

orcus
Starting Member

2 Posts

Posted - 01/08/2008 :  18:22:05  Show Profile  Reply with Quote
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

lm007
Starting Member

USA
5 Posts

Posted - 07/05/2008 :  17:17:10  Show Profile  Reply with Quote
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

BEGIN TRANSACTION
SELECT * FROM Person.Address WITH(HOLDLOCK)
WHERE AddressId >= 4 AND AddressId <= 5

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

ROLLBACK TRANSACTION
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/06/2008 :  07:10:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes, but you're using WITH(HOLDLOCK) which is essentialy the same as what serializable does by default.


_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

simonxy
Starting Member

1 Posts

Posted - 08/14/2013 :  11:26:58  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000