Re: row vs page locking...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: ben brugman (ben_at_niethier.nl)
Date: 09/05/04 

Date: Sun, 5 Sep 2004 19:39:46 +0200

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:#AAKqx1kEHA.3632@TK2MSFTNGP09.phx.gbl...
> 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.

The penalty of snapshot isolation being that there is no true
'serializable' possible.

The OP suggested that row-level locking is inferior compared
to multi versioning.

Personally I think that both have their advantages.
And using locks in concurrency situations is a choice.
Using multi versioning for concurrencty is another choice.
Both have their advantages and disadvantages.

ben brugman

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

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • Re: Unable to Apply SP4 to SQL 2000 Cluster (new Node)
    ... Rebuild the node in the failover cluster. ... Scenario 1" in SQL Server 2000 Books Online. ... This setup process updates to SP4 only the binaries on the new ...
    (microsoft.public.sqlserver.clustering)
  • Re: WSS 3.0 question
    ... I followed the advise given in removing WSS 3.0 etc, ... the server is complaining that the SQL service(?) was tempered with or corrupt. ... I may just instal the SQL server as I was going eventuall use it anyway. ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ...
    (microsoft.public.windows.server.sbs)
  • Re: WSUS
    ... I'm not seeing performance issues with the full enchilada installed, and 25 users busy hitting SQL. ... WSUS isn't difficult to uninstall - if you have WSUS v2 (installed with SBS R2) uninstall R2 from add/remove programs. ... How can anyone work with 4 instances of SQL Server on the same box? ...
    (microsoft.public.windows.server.sbs)
  • Re: SQL Resets
    ... If it were SQL that was falling short, ... The default backlog for SQL Server is 5. ... System.InvalidOperationException: Internal connection fatal error. ... From time to time, under heavy loads, we are getting resets at ...
    (microsoft.public.sqlserver.connect)