Re: row vs page locking...

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/05/04 

Date: Sun, 5 Sep 2004 08:32:29 -0700

Hi Randall

The complete storage engine was rewritten for SQL Server 7. I worked for the
Sybase Corporation for many years (starting with their version 2), in
training and tech support, and I taught the high-end performance tuning and
internals classes.

When I started working with MS SQL Server, the architectures were almost
identical and the smallest unit of locking was the page. But when SQL 7 came
out, almost everything had to be relearned from an core engine perspective.
Yes, there is true row level locking.

Maybe the person you heard was referring to a pseudo-row-locking introduced
in SQL Servfer 6.5, but that was just because they couldn't rewrite the
whole engire for that version. For SQL 7, they did.

I even wrote a book about it. If you want all the gory internal details,
check out Inside SQL Server 7 and/or Inside SQL Server 2000. 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"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: Developer tool
    ... Access .mdb file, which still uses the Jet engine and tends to slow ... Server engine, and easier use of stored procedures, UDFs, etc. ... >>> will assist you in creating SQL Server databases? ...
    (microsoft.public.sqlserver.programming)
  • Re: CREATE TEMPORARY TABLE
    ... Multi-table select queries are updatable using pretty much the same rules. ... Jet VIEWs ... the Jet 4.0 implementation is better than that of SQL Server ... Regardless which engine wins that particular race, ...
    (microsoft.public.access.tablesdbdesign)
  • DTC Distributed transaction completed Issue | Multiple updates
    ... I have two machines one running IIS Server and the other SQL Server. ... Message: Distributed transaction completed. ... This engine does multiple hits in the database during processing. ... huge number of updates on a couple of tables and most of the times the engine ...
    (microsoft.public.sqlserver.connect)
  • DTC Distributed transaction completed Issue | Multiple updates in
    ... I have two machines one running IIS Server and the other SQL Server. ... Message: Distributed transaction completed. ... This engine does multiple hits in the database during processing. ... huge number of updates on a couple of tables and most of the times the engine ...
    (microsoft.public.dotnet.general)
  • Re: Access keyword "First" = SQL Top?
    ... captures the value presented by the db engine, ... By comparison with SUM, which start with an initialize with a NULL (result ... Unfortunately, we're using SQL Server 2000, and if I'm understanding ... and place the fields in a GROUP BY clause and the resultset should be the ...
    (microsoft.public.access.queries)
Loading