| Author |
Topic |
|
AskSQLTeam
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 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-12 : 14:53:22
|
| really nice article. looking forward to the next.-ec |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Nils
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. |
|
|
|
spirit1
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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
orcus
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. |
|
|
|
lm007
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 AdventureWorksBEGIN TRANSACTION SELECT * FROM Person.Address WITH(HOLDLOCK)WHERE AddressId >= 4 AND AddressId <= 5 SELECT resource_type, request_mode, resource_description, *FROM sys.dm_tran_locksWHERE resource_type <> 'DATABASE'ROLLBACK TRANSACTION |
|
|
|
spirit1
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 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
simonxy
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. |
|
|
|
|
Article: Introduction to Locking in SQL Server