Re: Oracle and Microsoft SQL Server

Hi

Before SQL Server 6.5, the locking granularity was at page (2KB) level or table level, with automatic escalation. In most situations this is the optimal solution, since the lock management is considerable simplified. With row-level locking the overhead of lock management can become greater than the actual work done. The case for row locking is not as simple as Oracle would have you believe.

However inserts pose a problem since there is a much greater change of contention for the same page (the last page where rows are inserted). In version 6.5 SQL Server added the option of row level locking for inserts. This allows for much greater scalability in situation where inserts come from many users into the same table. Again lock escalation is automatic, where appropriate.

AFAIK Microsoft plans to add a row locking option for select/update/delete in a future version. A lot of the SQL Server development effort goes into automatic tuning of the database. They are working toward dynamic locking which would escalate and deescalate locks dynamically. Some marketing propoganda is available at hhtp://www.microsoft.com/sql/prod/whtpprLock.htm I think this is the right path to follow though. Some users like/need to have many variables to play with, most want a reasonably tuned solution as effortlessly as possible.

In terms of locking schemes, Oracle's great advantage is not row level locking, but the row versioning capabilities. This versions each row, making most locking unneccessary because you are just adding a new version. I don't know enough about this though.

G

rrbatra <rrbatra_at_feist.com> wrote in article <33A9FB0E.4ED_at_feist.com>...
> For the SQL server experts, I have a honest question, how does SQL
> server
> handle data insert/update without row-level locking? It cannot be
> locking the
> whole table??
>
>
> rrbatra_at_feist.com
>
Received on Fri Jun 20 1997 - 00:00:00 CDT