Re: lies damn lies and benchmarks

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CDA971D.87DA9797_at_exesolutions.com...
> Chris Weiss wrote:
>
> > Hmmm.... It seems that like in many product wars, people's
knowledge is out
> > of date:
> >
> > * Sql server had had robust row level locking since version 7.
Prior to
> > this it was page locking, which could be clumsy.
>
> This is only partially true. It is still quite easy to run out of
row leve locks
> in SQL Server and have the row level locks escallate to page level
locks.

You're incorrect.

Locks can be configured on Version 7 and SQL Server 2000 to be dynamic or to set a hard limit. Locks escalate to a next higher up level (row -> page -> table) to reduce the overhead in managing the locks and minimize the number of resources.

For example, given a table with 1,000,000 rows, would it be better to issue a million row level locks or one table lock?

Escalation is appropriate in some circumstances therefore it simply cannot be dismissed as a bad thing.

> And
> even if one doesn't there is no equivalent to multiversioning.

Multiversioning is both a blessing and a curse. With just about everything, there's a pro and a con. There are certain instances where a transaction requires serializability. Read consistency doesn't allow for that.

Does that make multiversioning bad? Absolutely not. Does it make it a 'must-have' feature in an RDBMS? Again, absolutely not.

People should be aware that multiversioning costs resources and this is another reason it 'costs' more resources to run Oracle. The nature of the beast.

> Page level locking, and lock escallation are both alive and well in
SQL Server
> and absolutely non-existent in Oracle.

Nonetheless row-level locking exists and more importantly the data is tracked in the data page - I/O rather than in-memory. It doesn't happen often but that's the penalty on using FOR UPDATE.

The fact remains, that the unbiased TPC-C benchmarks show that multiversioning and locking are non-issues.

> One example of how this impacts development is the need in SQL
Server to create
> on-the-fly temp tables, break up transactions, and perform frequent
commits, in
> order to avoid over-extending the resource.

Nope, this is incorrect. There's no _need_ to break up transactions, perform frequent commits that don't map to business units of work -- provide an example showing otherwise to disprove me.

The on-the-fly temp tables is more of a nomenclature issue than anything else. Think about how Oracle DISTINCT's data and how any other RDBMS engine would do the same. All done in memory.

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts