Is it true that in SQL server writers block readers and that readers block
writers ? Tnx.
For a complete understanding, I suggest you to go through the BOL and readup
the chapters on Locking and Transaction Isolation levels.
Vyas, Microsoft SQL Server MVP
SQL Server FAQ, articles, code samples,
Quote:> Is it true that in SQL server writers block readers and that readers block
> writers ? Tnx.
We have been experiencing an interesting phenomena
with readers blocking readers on Sybase SQL Server
4.9.2 EBF 2830 for Sun Solaris.
Basically, sp_lock output shows only sh_ locks and no -blk locks,
but sp_who shows blocking. This, obviously, causes severe
We had Sybase dial in and look around while we re-created the
problem. The findings indicated the readers were being blocked
on 'address locks'. (Sybase used dbcc lock to determine this.)
SO ... What's an adress lock ??? It's not documented anywhere.
Here's what I've gathered so far:
(please correct me if I'm wrong - or need I ask?)
- Address locks are internal resource locks
maintained for navigation of b-trees.
- There are always at least 2 address locks held for the
duration of a query. One for the root node of the b-tree,
and one for the current node.
- Address locks do not show up in sp_lock output,
however, they DO cause blocking. So, you won't see any -blk
locks in sp_lock, but you will see blocking in sp_who.
- Address locks are held for selects as well as updates.
- Address locks are exclusive locks. Only one process may
obtain an address lock on a given b-tree node at a time.
This means READERS CAN BLOCK READERS.
- The only way to minimize address locking is to tune your
queries so they run faster and transactions are shorter.
- In System 10, there is less contention for address locks
because the address lock scheme has been re-written
and the address locks are held for a shorter period of time.
However, address locking still exists in system 10.
1) Why are address locks not documented anywhere?
2) Why does SQL Server need address locks?
3) Is it true that address locks are exclusive
on both selects and updates?
Does Sybase recognize that this means readers block readers?
4) When are address locks aquired?
During query resolution, query execution?
5) How long are address locks held?
For the duration of the transaction?
Until a row is read into memory?
Is the address lock on the root node held until all rows are read?
If so, is everyone else blocked until all rows are read?
6) Can we run the 'dbcc lock' command ourselves to diagnose
this problem when readers are blocking readers?
What should we look for in the 'dbcc lock' output?
What is the full syntax of the command? Are there any parameters?
How safe is it to run 'dbcc lock' on a production server?
7) Are these problems isolated to, or more common among certain
platforms? Why don't we see this problem more frequently and
on a variety of platforms?
Does anyone have any more insight/experience with what's going on here?
- Anne Mahoney
State Street Bank