Re: row vs page locking...

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/05/04 

Date: Sun, 5 Sep 2004 10:24:17 -0500

There was a major rewrite of much of the core database engine code in SQL 7.
SQL 7 and above do a great job of row-level locking.

In versions prior to 7, one would sometimes implement convoluted techniques
to avoid 'hot spots' to circumvent page-level locking issues. Thanks to
row-level locking, hot spots are actually a good thing and can help
performance due to reduced i/o.

However, there is still no row versioning the current SQL Server version.
Snapshot isolation is planned for SQL Server 2005.

> And I've read that MSs row level locking is a band-aid on its unchanged
> core engine, although the author of that message did not expand further on
> this.

Perhaps the author is referring to version 6.5 insert row locking. This is
not true for versions 7 and above. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Randall Sell" <randall@bytewise.nospam.com.au> wrote in message 
news:UnE_c.20421$D7.10166@news-server.bigpond.net.au...
> Hello all,
>
> Somewhere on these newsgroups I recall reading that SQL Server 6 and prior 
> (when they were married with Sybase) used page locking and not row level 
> locking. Hence you could be locking a lot more records then what you think 
> when doing an UPDATE or INSERT SQL.
>
> Now I notice that SQL Server 7 and 2000 claim to use row level locking. 
> (As you can see, I have been out of the SQL arena for some time). So what 
> I'd like to know if this is all true? Or marketing mumbo-jumbo? Has 
> Microsoft made changes at the core of their engine to lock rows? I know 
> that other RDMSs like Interbase have a versioning engine so it was built 
> from the ground up for concurrence. And I've read that MSs row level 
> locking is a band-aid on its unchanged core engine, although the author of 
> that message did not expand further on this.
>
> so I figured I'd ask the experts what the truth is. Any help is much 
> appreciated.
>
> If indeed it has changed, what sort of test can I run to prove this to my 
> boss?
>
> regards,
> -randall sell

Relevant Pages

  • Re: Concurrency issue - Best Practice wanted
    ... Handling Data Concurrency Using ADO.NET ... 1/ Transactions and 2/ using timestamps. ... >I have been using for learning SQL is Petkovic, SQL Server 2000, A ... >explaining locking. ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: Web page not responding
    ... Thanks for the reply, although I dont think it is locking, as it can happen ... with only one active user on the system. ... Although I will try and ascertain what SQL is doing at the time of the ... > like a record locking problem during the update. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Concurrent executions of "fn_trace_gettable" function are failing.
    ... but definitely seems like a locking issue on ... Vyas, MVP (SQL Server) ... executing the query simulateously from multiple clients. ...
    (microsoft.public.sqlserver.server)
  • row vs page locking...
    ... Somewhere on these newsgroups I recall reading that SQL Server 6 and prior ... used page locking and not row level ... RDMSs like Interbase have a versioning engine so it was built from the ...
    (microsoft.public.sqlserver.server)
  • Re: SQL
    ... SQL lacks a lot of stuff. ... I see nothing that keeps an RDBMs engine from using such techniques to ... A custom engine does ...
    (comp.object)