Writers block readers

Writers block readers

Post by Tomislav Herce » Tue, 23 Oct 2001 18:25:49



Is it true that in SQL server writers block readers and that readers block
writers ? Tnx.

Sincerely,

Tomislav

 
 
 

Writers block readers

Post by Narayana Vyas Kondredd » Tue, 23 Oct 2001 19:30:53


To put it simply, the process which is modifying the data, acquires an
exclusive lock on data (row, page, table, index key etc.) and prevents other
processes from reading and modifying this data. The process that is reading
a piece of data acquires a shared lock on that piece and prevents other from
modifying the data, while allowing other processes to read. Of course, in a
well designed/developed environment, this locking period is going to be
extremely low and neglisible.

For a complete understanding, I suggest you to go through the BOL and readup
the chapters on Locking and Transaction Isolation levels.
--
HTH,
Vyas, Microsoft SQL Server MVP
SQL Server FAQ, articles, code samples,

http://vyaskn.tripod.com/


Quote:> Is it true that in SQL server writers block readers and that readers block
> writers ? Tnx.

> Sincerely,

> Tomislav


 
 
 

Writers block readers

Post by J. Eric Mortense » Tue, 23 Oct 2001 22:23:57


Yes, normally (i.e., when transaction isolation level = read commited)
writers block readers, but readers don't block writers.

Eric


Quote:> Is it true that in SQL server writers block readers and that readers block
> writers ? Tnx.

> Sincerely,

> Tomislav

 
 
 

1. Address Locks - Readers Blocking Readers?????

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
performance problems.

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:
--------------

- 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.

Questions:
----------

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

2. Record lock - from Access to SQL Server...?

3. Looking for info on reader/writer scenario

4. Download Microsoft Jet 4.0 Service Pack 3

5. Reader/Writer Question

6. Oracle Manufacturing 11i Consultant

7. Concurrency/reader-writer problem

8. SQL Replication

9. VB Acrobat Writer / Reader and signature

10. Native Java .DBF reader/writer

11. clipper MEM reader and writer

12. Writer not stop Reader ?

13. Concurrency/reader-writer problem