row vs page locking...

From: Randall Sell (randall_at_bytewise.nospam.com.au)
Date: 09/05/04 

Date: Sun, 05 Sep 2004 13:19:48 GMT

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: row vs page locking...
    ... 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. ...
    (microsoft.public.sqlserver.server)
  • 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)
  • Re: Reading sequential files in COBOL vs SQL
    ... And the SQL can also take advantage of parallel read algorithms, built into the DB2 for i5/OS query engine, to obtain the results quicker. ... So just as it might be generally considered easier to code the SQL request, it is also easier for the SQL to take advantage of an optimizer and underlying query features. ... If the files are all small, then the command SETOBJACC may prove beneficial for either row level or SQL access. ...
    (comp.sys.ibm.as400.misc)