Re: Locking in SQL Server and Oracle

From: wei xiao[MS] (weix_at_online.microsoft.com)
Date: 02/17/05 

Date: Wed, 16 Feb 2005 18:13:31 -0800

I am glad you would like to stay with SQL Server :-)

In SQL Server 2005 beta 2, there is a new feature called "snapshot
isolation". This new feature reduces blocking and deadlocks a lot. Only
updates require a lock. Please see books online and this article for more
details.

http://www.microsoft.com/technet/prodtechnol/sql/2005/SQL05B.mspx

As for lock escalation, here is a way to disable that:

http://support.microsoft.com/default.aspx?scid=kb;en-us;323630&sd=tech

So if you want to, you can have effectively a row-locking only database with
SQL Server 2005 beta 2. There are still some advantages with lock escalation
in some cases. So we would like the user to have a choice. 

-- 
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey Roughgarden" <JeffreyRoughgarden@discussions.microsoft.com> wrote
in message news:56C0B915-E545-4689-9B95-FE8CE268DC10@microsoft.com...
> I am an MCDBA and MCSD in charge of several large PeopleSoft databases. I
> recently attended a 5-day Oracle 9i DBA Fundamentals 1 class to ascertain
how
> hard it would be to convert to Oracle during our next PeopleSoft upgrade.
I
> had thought that since databases are databases, tables are tables, and SQL
is
> an ANSI standard, it really would not be that different doing Oracle.
Well, I
> was wrong. Oracle is so dramatically different from SQL Server (and
awkward
> and complex) that we decided to carry on with SQL Server.
>
> That said, I was impressed by one feature of Oracle, namely the fact that
it
> uses row-level locks exclusively. Clearly, this reduces the liklihood of
> blocking and deadlocking significantly. I had thought that Microsoft does
> escalated locks because it was infeasible to do row locks, but apparently
> not. Oracle runs as well as SQL Server in spite of the overhead it incurrs
> managing more locks.
>
> I am curious why Microsoft does not adopt row-locking exclusively now that
> Oracle has shown it can be done.

Relevant Pages

  • Re: why>?
    ... On your desktop-- you'll be running SQL Server behind the scenes. ... it isn't risky to allow end users to create databases. ... it is no more risky than giving you the ability to create spreadsheets. ... I'm not saying that Oracle and IBM are going away. ...
    (microsoft.public.excel)
  • Re: database dilemma
    ... > Dot net uses SQL Server and Oracle (check the help for the supported ... > versions) most efficiently as it has native drivers for these databases ... > Other databases use ODBC which work but with an overhead. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: database dilemma
    ... Dot net uses SQL Server and Oracle (check the help for the supported ... Other databases use ODBC which work but with an overhead. ... boss last week dropped vb.net on my desk and wants to create a web app and a ...
    (microsoft.public.dotnet.languages.vb)
  • MS SQL server Meata data question
    ... I have sql server 2000 up and running and using the provided ... browsing tools poked around and found that the databases in the system were ... the native php support for sql server doesn't have meta data functions like ... I need to do this for Oracle too but I think the native support for Oracle ...
    (comp.lang.php)
  • Re: Data access perfomance
    ... Sorry, I'm using Sql Server and Oracle 9i, on both databases the process is ... >> Databases and I fell like I've perfomance problems inserting into the DB. ... >> I insert the data after I've finished reading the xml file. ...
    (microsoft.public.data.oledb)