Re: tx isolation

"Pablo Sanchez" <pablo_at_dev.null> wrote in message news:Xns92D85D3354004pingottpingottbah_at_209.242.64.107...
> "Brian E Dick" <bdick_at_cox.net> wrote in
> news:4KJG9.65122$wc2.2718284_at_news2.east.cox.net:
>
> > One of the biggest shortcomings of the TPC-C benchmark is its "shared
> > nothing" architecture. It allows you to infinitely scale the benchmark
> > without concern for resource contention.
>
> I think you might be misusing the term "shared nothing" ... I think
> you're intending to say that the TPC-C is a highly partitionable
> problem. Is that correct?

That's right. The TPC-C benchmark is "highly partitionable" because its transactions share little data across the partitions.

>
> I don't agree with this assertion ... take a look at the 'payment'
> transactions for an example of where interactions _must_ happen. Or
> perhaps you can tell me how the payment transaction can be implemented
> in a highly partitioned manner. I don't see it.

Maybe I was being a bit extreme in my assertion. I should have said "shared little". It's true that the payment transaction draws data from two different warehouses (the partitioning key). However, the payment transaction occurs 43% of the time and a remote warehouse is chosen 15% of the time. So, the payment transaction crosses partitions less that 7% (43%*15%) of the time.

>
> > By side-stepping the locking issue, this benchmark gives the SQL
> > Server locking model an unfair advantage over the Oracle locking
> > model. In a real world "shared something" or "shared mostly"
> > application, Oracle shines.
>
> I believe this assertion is incorrect because the first statement was
> wrong that the benchmark is a highly partitionable problem. The
> payment transaction clearly shows that we need to coordinate the
> updates to the affected tables among the clients.

But the benchmark IS highly partitionable, and that's how the vendors are blowing the TPM numbers so high. When IBM first crossed the 500K TPM mark, they did it by clustering many UDB machines and using over 7000 hard drives.

>
> > Also, multiversioning does not make for lazy
> > developers.
>
> This one is subjective ... I should have said, "In my experience ..."
>
> > Multiversioning resolves a difficult problem, resource
> > contention, and allows the developer to move on to bigger and
> > tougher problems.
>
> There's nothing difficult about working with a locking model.

Why does SQL Server promote optimistic locking by implementing timestamps? Why does Oracle do multiversioning? Why do you index your foreign key columns? Why do you carefully use "FOR UPDATE"? Why do you carefully hold open transactions while waiting for user interaction? Why ...?

Because locking will kill performance if it is not done right. And too often it is not done right.

>
> In general, all DBMS have their nuances and every database
> developer/dba must be responsible to learn them. How many times have
> people posted the same question to this newsgroup without having taken
> the time to read the docs? Same point is made with a locking model.
> It's easy to work with and if people spent a sliver of time to read
> it, they wouldn't wedge themselves ... as when people wedge themselves
> with improper RBS configuration.
> --
> Pablo Sanchez, High-Performance Database Engineering
> http://www.hpdbe.com
Received on Mon Dec 02 2002 - 15:29:07 CST